Android SQLite database operations

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 );";
}