1. databasehelper class, used to create and update database tables
Package com. dB;
Import Android. content. context;
Import Android. database. SQLite. sqlitedatabase;
Import Android. database. SQLite. sqlitedatabase. cursorfactory;
Import Android. database. SQLite. sqliteopenhelper;
Import Android. util. log;
Public class databasehelper extends sqliteopenhelper {
Public databasehelper (context, string name, cursorfactory factory,
Int version ){
Super (context, name, null, version );
}
@ Override
Public void oncreate (sqlitedatabase dB ){
Db.exe csql (sqlstatement. create_table_test_allen );
}
@ Override
Public void onupgrade (sqlitedatabase dB, int oldversion, int newversion ){
Log. D ("======= database update ====== ",
"Database updated from old version" + oldversion
+ "New version" + newversion );
Db.exe csql ("Drop table if exists" + sqlstatement. table_test_db_allen );
Oncreate (db );
}
}
2. Databaseprocesser, used for adding, deleting, modifying, and querying databases
Package com. dB;
Import java. util. arraylist;
Import java. util. List;
Import Android. content. contentvalues;
Import Android. content. context;
Import Android. database. sqlexception;
Import Android. database. SQLite. sqlitedatabase;
Import com. DB. model. Allen;
Public class databaseprocesser {
Private Static final string db_name = "allen_test_database.db ";
Private Static final int db_version = 2;
Private Static databaseprocesser instance = NULL;
Private databasehelper dbhelper = NULL;
Private sqlitedatabase MDB = NULL;
Private Static Context context;
/**
*
* @ Param Context
*/
Public databaseprocesser (context ){
Instance = this;
}
Public static databaseprocesser getinstance (){
If (instance = NULL ){
New databaseprocesser (context );
}
Return instance;
}
/**
* Open Database
*
* @ Throws sqlexception
*/
Private void opendatabase () throws sqlexception {
If (dbhelper = NULL ){
Dbhelper = new databasehelper (context, db_name, null, db_version );
}
MDB = dbhelper. getwritabledatabase ();
}
/**
* Close database
*/
Private void closedatabase (){
If (MDB! = NULL ){
MDB. Close ();
}
}
/**
*
* @ Param name
* @ Param time
* @ Return
*/
Public Boolean inserttestdata (string name, string time ){
Boolean ret = true;
Contentvalues CV = new contentvalues ();
Cv. Put (sqlstatement. column_name, name );
Cv. Put (sqlstatement. column_time, time );
Try {
Opendatabase ();
MDB. insertorthrow (sqlstatement. table_test_db_allen, null, CV );
} Catch (exception e ){
Ret = false;
E. printstacktrace ();
} Finally {
Closedatabase ();
}
Return ret;
}
/**
*
* @ Param ID
* @ Return
*/
Public Boolean deletetestdata (int id ){
Boolean ret = true;
String SQL = "delete * From table_allen where id =" + ID;
Try {
Opendatabase ();
Mdb.exe csql (SQL );
} Catch (sqlexception e ){
Ret = false;
E. printstacktrace ();
} Finally {
Closedatabase ();
}
Return ret;
}
/**
*
* @ Param ID
* @ Param name
* @ Param time
* @ Return
*/
Public Boolean updatetestdata (int id, string name, string time ){
Return false;
}
/**
*
* @ Param ID
* @ Return
*/
Public Allen gettestdata (int id ){
Return NULL;
}
/**
*
* @ Return
*/
Public list <Allen> gettestdata (){
List <Allen> Allens = new arraylist <Allen> ();
Return Allens;
}
}
3. add, delete, modify, and query methods:
/**
*
* @ Param categoryname
* @ Param categoryimagelevel
* @ Return true when inserted, else return false
*/
Public Boolean insertcategory (string categoryname, int categoryimagelevel ){
Boolean ret = true;
Cursor cur = NULL;
Try {
Opendatabase ();
// Category exist or not
Cur = MDB. rawquery (sqlstatement. SQL _get_category_by_name,
New String [] {categoryname });
If (cur. movetonext ()){
// Category exists in Database
Ret = false;
} Else {
// Insert category
Contentvalues CV = new contentvalues ();
Cv. Put (sqlstatement. column_category_name, categoryname );
Cv. Put (sqlstatement. column_category_image_level,
Categoryimagelevel );
MDB. insertorthrow (sqlstatement. db_table_category, null, CV );
}
} Catch (sqlexception e ){
Ret = false;
E. printstacktrace ();
} Finally {
If (cur! = NULL ){
Cur. Close ();
}
Closedatabase ();
}
Return ret;
}
/**
*
* @ Return a list of category
*/
Public list <categoryinfo> getcategory (){
List <categoryinfo> ret = new arraylist <categoryinfo> ();
Cursor cur = NULL;
Try {
Opendatabase ();
Cur = MDB. rawquery (sqlstatement. SQL _get_category, null );
While (cur. movetonext ()){
Categoryinfo Category = new categoryinfo ();
Category. setid (cur. getint (cur
. Getcolumnindex (sqlstatement. column_id )));
Category. setcategoryname (cur. getstring (cur
. Getcolumnindex (sqlstatement. column_category_name )));
Category. setcategoryimagelevel (cur. getint (cur
. Getcolumnindex (sqlstatement. column_category_image_level )));
Category. setcategoryclickcount (cur. getint (cur
. Getcolumnindex (sqlstatement. column_category_click_count )));
Ret. Add (category );
}
} Catch (sqlexception e ){
E. printstacktrace ();
} Finally {
If (cur! = NULL ){
Cur. Close ();
}
Closedatabase ();
}
Return ret;
}
/**
*
* @ Param ID
* @ Return
*/
Public Boolean deletecategory (string categoryname ){
Boolean ret = true;
Try {
Opendatabase ();
Mdb.exe csql ("delete from" + sqlstatement. db_table_category
+ "Where" + sqlstatement. column_category_name + "= '"
+ Categoryname + "'");
} Catch (sqlexception e ){
Ret = false;
E. printstacktrace ();
} Finally {
Closedatabase ();
}
Return ret;
}
4. sqlstatement, used to encapsulate SQL statements
Package com. dB;
Public class sqlstatement {
// Table name
Public static final string table_test_db_allen = "table_allen ";
// Column name
Public static final string column_id = "column_id ";
Public static final string column_name = "column_name ";
Public static final string column_time = "column_time ";
// SQL statement to Create Table
Public static final string create_table_test_allen = "create table if not exists"
+ Table_test_db_allen
+ "("
+ Column_id
+ "Integer primary key autoincrement ,"
+ Column_name
+ "Varchar ,"
+ Column_time
+ "Varchar );";
}