How To Retrieve Data From Database In Android Creating Database On Sqlitebrowser
I have jokes.db database. This database file imported from using this article., http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/. Now i have
Solution 1:
To read values from the table:
Create a cursor to read data from the database.
Write the following function.
public Cursor retrieveRecords(int category) { Cursorc=null; c = db.rawQuery("select title,body from tablename where category=" +category, null); return c; }Now get the values from the cursor.
publicvoidgetDataFromDatabase() { try { Cursor cursor = null; db.OpenDatabase(); cursor = db.retrieveRecords(); if (cursor.getCount() != 0) { if (cursor.moveToFirst()) { do { titleArrayList.add(cursor.getString(cursor.getColumnIndex("title"))); bodyArrayList.add(cursor.getString(cursor.getColumnIndex("body"))); } while (cursor.moveToNext()); } db.closeDatabase(); } cursor.close(); } catch(Exception e) { e.printStackTrace(); } }
Solution 2:
I an using the fallowing class for manage DataBase in my application.
publicclassPersonDbHelper {
publicclassRow_DocumentTableextendsObject {
publicint rwo_id;
public String dockey;
public String docid;
public String size;
public String status;
public String name;
public String product_discription;
public String type;
public String publisher;
public String version;
public String filepathurl;
public String basepage;
public String copypaste;
public String save;
public String print;
public String printablepage;
public String nonprintablepage;
public String search;
public String watermarkimageurl;
public String expiry;
public String versiondescription;
public String update_available;
public String localfilepath;
}
publicclassRow_CategoriesTableextendsObject {
public String dockey;
public String category_id;
public String category_name;
}
privatestaticfinalStringDROP_DOCUMENTDETAIL_TABLE_FROM_DATABASE="drop table if exists CONTENTRAVENDB.DOCUMENTDETAIL";
privatestaticfinalStringDROP_CATEGORIES_TABLE_FROM_DATABASE="drop table if exists CONTENTRAVENDB.CATEGORIES";
privatestaticfinalStringDATABASE_CREATE_DOCUMENTDETAIL="create table DOCUMENTDETAIL(row_id integer primary key autoincrement, dockey text , "
+ "docid text not null,"
+ "size text not null,"
+ "status text not null,"
+ "name text not null,"
+ "product_discription text not null,"
+ "type text not null,"
+ "publisher text not null,"
+ "version text not null,"
+ "filepathurl text not null,"
+ "basepage text not null,"
+ "copypaste text not null,"
+ "save text not null,"
+ "print text not null,"
+ "printablepage text not null,"
+ "nonprintablepage text not null,"
+ "search text ,"
+ "watermarkimageurl text not null,"
+ "expiry text not null,"
+ "versiondescription text not null,"
+ "update_available text not null,"
+ "localfilepath text not null"
+ ");";
privatestaticfinalStringDATABASE_CREATE_CATEGORIES="create table CATEGORIES(_id integer primary key autoincrement, "
+ "dockey text ,"
+ "category_id text ,"
+ "category_name text"
+ ");";
privatestaticfinalStringDATABASE_NAME="CONTENTRAVENDB";
privatestaticfinalStringDATABASE_TABLE1="CATEGORIES";
privatestaticfinalStringDATABASE_TABLE2="DOCUMENTDETAIL";
privatestaticfinalintDATABASE_VERSION=1;
private SQLiteDatabase db;
publicPersonDbHelper(Context ctx) {
db = ctx.openOrCreateDatabase(DATABASE_NAME, DATABASE_VERSION, null);
db.execSQL(DATABASE_CREATE_DOCUMENTDETAIL);
db.execSQL(DATABASE_CREATE_CATEGORIES);
}
publicvoiddropAllTable(Context ctx) {
db = ctx.openOrCreateDatabase(DATABASE_NAME, DATABASE_VERSION, null);
// db.execSQL(DROP_DOCUMENTDETAIL_TABLE_FROM_DATABASE);// db.execSQL(DROP_CATEGORIES_TABLE_FROM_DATABASE);
db.delete(DATABASE_TABLE1, null, null);
db.delete(DATABASE_TABLE2, null, null);
close();
}
publicPersonDbHelper(Context ctx, String abc) {
db = ctx.openOrCreateDatabase(DATABASE_NAME, DATABASE_VERSION, null);
}
publicPersonDbHelper() {
}
publicvoidclose() {
db.close();
}
publicvoidcreateRow_InDocumentDetailTable(String dockey, String docid,
String size, String status, String name,
String product_discription, String type, String publisher,
String version, String filepathurl, String basepage,
String copypaste, String save, String print, String printablepage,
String nonprintablepage, String search, String watermarkimageurl,
String expiry, String versiondescription, String update_available,
String localfilepath) {
ContentValuesinitialValues=newContentValues();
initialValues.put("dockey", dockey);
initialValues.put("docid", docid);
initialValues.put("size", size);
initialValues.put("status", status);
initialValues.put("name", name);
initialValues.put("product_discription", product_discription);
initialValues.put("type", type);
initialValues.put("publisher", publisher);
initialValues.put("version", version);
initialValues.put("filepathurl", filepathurl);
initialValues.put("basepage", basepage);
initialValues.put("copypaste", copypaste);
initialValues.put("save", save);
initialValues.put("print", print);
initialValues.put("printablepage", printablepage);
initialValues.put("nonprintablepage", nonprintablepage);
initialValues.put("search", search);
initialValues.put("watermarkimageurl", watermarkimageurl);
initialValues.put("expiry", expiry);
initialValues.put("versiondescription", versiondescription);
initialValues.put("update_available", update_available);
initialValues.put("localfilepath", localfilepath);
db.insert(DATABASE_TABLE2, null, initialValues);
}
publicvoidcreateRow_InCategorieTable(String dockey, String category_id,
String category_name) {
ContentValuesinitialValues=newContentValues();
initialValues.put("dockey", dockey);
initialValues.put("category_id", category_id);
initialValues.put("category_name", category_name);
db.insert(DATABASE_TABLE1, null, initialValues);
}
publicvoiddeleteRow_FromDocumentDetailTable(long rowId) {
db.delete(DATABASE_TABLE2, "_id=" + rowId, null);
}
public List<Row_DocumentTable> fetchAllRows_FromDocumentDetailTable() {
ArrayList<Row_DocumentTable> ret = newArrayList<Row_DocumentTable>();
try {
Stringsql="select * from DOCUMENTDETAIL";
Cursorc= db.rawQuery(sql, null);
// Cursor c =// db.query(DATABASE_TABLE2, new String[] {// "row_id","dockey","docid","size", "status", "name",// "product_discription",// "type", "publisher", "version", "filepathurl", "basepage"// , "copypaste", "save", "print", "printablepage",// "nonprintablepage"// , "search", "watermarkimageurl", "expiry",// "versiondescription","update_available","localfilepath"// }, null, null, null, null, null);intnumRows= c.getCount();
c.moveToFirst();
for (inti=0; i < numRows; ++i) {
Row_DocumentTablerow=newRow_DocumentTable();
row.rwo_id = c.getInt(0);
row.dockey = c.getString(1);
row.docid = c.getString(2);
row.size = c.getString(3);
row.status = c.getString(4);
row.name = c.getString(5);
row.product_discription = c.getString(6);
row.type = c.getString(7);
row.publisher = c.getString(8);
row.version = c.getString(9);
row.filepathurl = c.getString(10);
row.basepage = c.getString(11);
row.copypaste = c.getString(12);
row.save = c.getString(13);
row.print = c.getString(14);
row.printablepage = c.getString(15);
row.nonprintablepage = c.getString(16);
row.search = c.getString(17);
row.watermarkimageurl = c.getString(18);
row.expiry = c.getString(19);
row.versiondescription = c.getString(20);
row.update_available = c.getString(21);
row.localfilepath = c.getString(22);
ret.add(row);
c.moveToNext();
}
} catch (SQLException e) {
Log.e("Exception on query", e.toString());
}
return ret;
}
public List<Row_DocumentTable> fetchAllRows_Of_Single_Type(String argtype) {
ArrayList<Row_DocumentTable> ret = newArrayList<Row_DocumentTable>();
try {
Stringsql="select * from DOCUMENTDETAIL where type='" + argtype
+ "'";
Cursorc= db.rawQuery(sql, null);
// Cursor c=db.query(DATABASE_TABLE2, new String[] {// "dockey","docid", "status", "name", "product_discription",// "type", "publisher", "version", "filepathurl", "basepage"// , "copypaste", "save", "print", "printablepage",// "nonprintablepage"// , "search", "watermarkimageurl", "expiry", "versiondescription"// }, "type=PDF", null, null, null, null);intnumRows= c.getCount();
c.moveToFirst();
for (inti=0; i < numRows; ++i) {
Row_DocumentTablerow=newRow_DocumentTable();
row.rwo_id = c.getInt(0);
row.dockey = c.getString(1);
row.docid = c.getString(2);
row.size = c.getString(3);
row.status = c.getString(4);
row.name = c.getString(5);
row.product_discription = c.getString(6);
row.type = c.getString(7);
row.publisher = c.getString(8);
row.version = c.getString(9);
row.filepathurl = c.getString(10);
row.basepage = c.getString(11);
row.copypaste = c.getString(12);
row.save = c.getString(13);
row.print = c.getString(14);
row.printablepage = c.getString(15);
row.nonprintablepage = c.getString(16);
row.search = c.getString(17);
row.watermarkimageurl = c.getString(18);
row.expiry = c.getString(19);
row.versiondescription = c.getString(20);
row.update_available = c.getString(21);
ret.add(row);
c.moveToNext();
}
} catch (SQLException e) {
Log.e("Exception on query", e.toString());
}
return ret;
}
public List<Row_CategoriesTable> fetchAllRows_FromCategorieTable(
String argsql) {
ArrayList<Row_CategoriesTable> ret = newArrayList<Row_CategoriesTable>();
try {
Stringsql= argsql;
Cursorc= db.rawQuery(sql, null);
// Cursor c =// db.query(true,DATABASE_TABLE1, new String[] {// "dockey","category_id","category_name"// }, null,null,null, null, null,null);intnumRows= c.getCount();
c.moveToFirst();
for (inti=0; i < numRows; ++i) {
Row_CategoriesTablerow=newRow_CategoriesTable();
row.dockey = c.getString(0);
row.category_id = c.getString(0);
row.category_name = c.getString(0);
ret.add(row);
c.moveToNext();
}
} catch (SQLException e) {
Log.e("Exception on query", e.toString());
}
return ret;
}
public Row_DocumentTable fetchRow_FromDocumentDetailTableByDocKey(
String dockey) {
Row_DocumentTablerow=newRow_DocumentTable();
Stringsql="select * from DOCUMENTDETAIL where dockey='" + dockey
+ "'";
try {
Cursorc= db.rawQuery(sql, null);
// Cursor c =// db.query(DATABASE_TABLE2, new String[] {// "dockey","docid", "status", "name", "product_discription",// "type", "publisher", "version", "filepathurl", "basepage"// , "copypaste", "save", "print", "printablepage",// "nonprintablepage"// , "search", "watermarkimageurl", "expiry", "versiondescription"},// "dockey=" + dockey, null, null,// null,null,"name desc");if (c.getCount() > 0) {
c.moveToFirst();
row.rwo_id = c.getInt(0);
row.dockey = c.getString(1);
row.docid = c.getString(2);
row.size = c.getString(3);
row.status = c.getString(4);
row.name = c.getString(5);
row.product_discription = c.getString(6);
row.type = c.getString(7);
row.publisher = c.getString(8);
row.version = c.getString(9);
row.filepathurl = c.getString(10);
row.basepage = c.getString(11);
row.copypaste = c.getString(12);
row.save = c.getString(13);
row.print = c.getString(14);
row.printablepage = c.getString(15);
row.nonprintablepage = c.getString(16);
row.search = c.getString(17);
row.watermarkimageurl = c.getString(18);
row.expiry = c.getString(19);
row.versiondescription = c.getString(20);
row.update_available = c.getString(21);
row.localfilepath = c.getString(22);
return row;
} else {
row.docid = null;
row.dockey = row.name = null;
}
} catch (IllegalStateException e) {
e.printStackTrace();
}
return row;
}
publicvoidupdateRow_InDocumentDetailTableByDocKey(String dockey,
String docid, String status, String name,
String product_discription, String type, String publisher,
String version, String filepathurl, String basepage,
String copypaste, String save, String print, String printablepage,
String nonprintablepage, String search, String watermarkimageurl,
String expiry, String versiondescription, String update_available) {
ContentValuesargs=newContentValues();
args.put("dockey", dockey);
args.put("docid", docid);
args.put("status", status);
args.put("name", name);
args.put("product_discription", product_discription);
args.put("type", type);
args.put("publisher", publisher);
args.put("version", version);
args.put("filepathurl", filepathurl);
args.put("basepage", basepage);
args.put("copypaste", copypaste);
args.put("save", save);
args.put("print", print);
args.put("printablepage", printablepage);
args.put("nonprintablepage", nonprintablepage);
args.put("search", search);
args.put("watermarkimageurl", watermarkimageurl);
args.put("expiry", expiry);
args.put("versiondescription", versiondescription);
args.put("update_available", update_available);
db.update(DATABASE_TABLE2, args, "dockey='" + dockey + "'", null);
}
public Cursor GetAllRows() {
try {
return db.query(DATABASE_TABLE2, newString[] { "dockey", "docid",
"status", "name", "product_discription", "type",
"publisher", "version", "filepathurl", "basepage",
"copypaste", "save", "print", "printablepage",
"nonprintablepage", "search", "watermarkimageurl",
"expiry", "versiondescription" }, null, null, null, null,
null);
} catch (SQLException e) {
Log.e("Exception on query", e.toString());
returnnull;
}
}
publicvoidupdateRow_InDocumentDetailTableByDocKey_UpdateAvl(Context ctx,
String dockey, String update_available) {
db = ctx.openOrCreateDatabase(DATABASE_NAME, DATABASE_VERSION, null);
ContentValuesargs=newContentValues();
args.put("update_available", update_available);
db.update(DATABASE_TABLE2, args, "dockey='" + dockey + "'", null);
}
publicvoidupdateRow_InDocumentDetailTableByDocKey_LocalFilePath(
Context ctx, String dockey, String argLocalFilePath) {
db = ctx.openOrCreateDatabase(DATABASE_NAME, DATABASE_VERSION, null);
ContentValuesargs=newContentValues();
args.put("localfilepath", argLocalFilePath);
db.update(DATABASE_TABLE2, args, "dockey='" + dockey + "'", null);
}
}
I have two table and apply all the query insert update delete and createtable select using the code it is working fine. I hope this is help.
Solution 3:
First of all don't use tv.append instead use setText because if u use append then ur next joke will be append to the previous one and textView show the jokes as you click the button
Actually the answere is long enough so I have written a ansere in my blog please see the link the code will do exactly what you are lokking for but i just made one table you can add more table similarly.
You can visit HERE
Post a Comment for "How To Retrieve Data From Database In Android Creating Database On Sqlitebrowser"