Often used Android database base class, Android database base class
// Create a database public class DBCreate {public static void CreateDatabase (SQLiteDatabase db) {db. beginTransaction (); try {create_NetTaskBuffer (db); insert_ SQL (db); db. setTransactionSuccessful ();} catch (Exception e) {e. printStackTrace ();} finally {db. endTransaction () ;}}// cache private static void create_NetTaskBuffer (SQLiteDatabase db) {db.exe cSQL ("drop table if exists NetTaskBuffer"); StringBuilde R SQL = new StringBuilder (); SQL. append ("create table if not exists NetTaskBuffer ("); SQL. append ("id integer primary key autoincrement,"); // This column cannot be modified. append ("label text collate nocase,"); // SQL. append ("param text collate nocase,"); // SQL. append ("result text collate nocase,"); // SQL. append ("remark text collate nocase,"); SQL. append ("time LONG)"); // db.exe cSQL (SQL. toString ();} // insert The initialization database uses private static void insert_ SQL (SQLiteDatabase db) {}}// ---------------------- database operation base class ------------------------------------ // *** basic operation class of the database, database creation, update operations are all performed here ** @ author yizhe * @ date 2014-9-11 */public abstract class DBHelper extends SQLiteOpenHelper {static String name = "hk. db "; // database name static CursorFactory cursorFactory = null; static int version = 1000; // initial version: 1000 Context Context; protected ContentValues contentValues; // cursor object forwarding intermediary, use protected String tableName for the subclass; protected DBHelper (Context context, String tableName) {super (context, name, cursorFactory, version); this. context = context; this. tableName = tableName;}/*** is called when the software is installed for the first time. Overwrite installation does not call */public void onCreate (SQLiteDatabase db) {// DBCreate is performed here for the creation process of all tables. createDatabase (db);}/*** overwrite the installation. When the version number is released When a change occurs, this method is called and only once */public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {onCreate (db ); // if (oldVersion <109) {// onCreate (db ); ///} else {//}/*** run */public void onOpen (SQLiteDatabase db) {super. onOpen (db); // It is first executed after the database is successfully opened} public void finalize () {close ();} // -------------------------- SQL method ----------------------------------- // *** execute sq L, no */public void execSQL (String SQL) {System. out. println ("= execSQL =" + SQL); SQLiteDatabase db = getWritableDatabase (); db.exe cSQL (SQL); db. close ();}/*** Execute SQL statements in batches, enable internal transactions ** @ param list * SQL list * @ return whether execution is successful */public boolean execSQLBatch (ArrayList <String> list) {SQLiteDatabase db = getWritableDatabase (); db. beginTransaction (); try {for (String SQL: list) {db.exe cSQL (SQL);} db. se TTransactionSuccessful ();} catch (Exception e) {e. printStackTrace (); return false;} finally {db. endTransaction (); db. close ();} return true ;} the/*** Delete record by id ** @ param id * Table must contain the "id" field * @ return the number of rows affected if a whereClause is passed in, 0 * otherwise. to remove all rows and get a count pass "1" as the * whereClause. */public int delete (int id) {SQLiteDatabase db = getWritab LeDatabase (); int result = db. delete (tableName, "id =? ", New String [] {id +" "}); db. close (); return result;}/*** Delete: you only need to write the where condition (without where) and the parameter ** @ param whereStr * For example: "id =? "* @ Param arr * example: new String [] {" 123 "} * @ return Affected row */public int delete (String whereStr, String [] arr) {SQLiteDatabase db = getWritableDatabase (); int result = db. delete (tableName, whereStr, arr); db. close (); return result;}/*** clear table */public void clearTable () {SQLiteDatabase db = getWritableDatabase (); db.exe cSQL ("delete from" + tableName ); db. close ();}/*** common query, mostly used in transactions */public Static Cursor Query (SQLiteDatabase db, String SQL) {System. out. println ("= Query =" + SQL); return db. rawQuery (SQL, new String [] {});}/*** common execution SQL, mostly used in transactions **/public static void execSQL (SQLiteDatabase db, String SQL) {System. out. println ("= execSQL =" + SQL); db.exe cSQL (SQL );}} // ------------------------- The following is a specific DEMO --------------------------------- // *** dao class to implement basic database operations <br/> * You need to save Object Attributes cannot use the basic type. We recommend that you use only Integer and String <br/> * as common objects, modify tableName when creating a demo ** @ author yizhe * @ date 2012-5-18 */public class NetTaskBufferDao extends DBHelper {int expiryDays = 10; // The data cache validity period is public NetTaskBufferDao (Context context) {super (context, "NetTaskBuffer");} // All pojo integers must be of the Long or Integer type. We recommend that you use Long public void iniContentValues (NetTaskBuffer pojo) {contentValues = new ContentValues (); ContentValues. put ("id", pojo. id); contentValues. put ("label", pojo. label); contentValues. put ("param", pojo. param); contentValues. put ("result", pojo. result); contentValues. put ("remark", pojo. remark); contentValues. put ("time", pojo. time);} public NetTaskBuffer setBaseItem (Cursor cursor) {NetTaskBuffer pojo = new NetTaskBuffer (); pojo. id = cursor. getInt (cursor. getColumnIndex ("id"); pojo. label = cu Rsor. getString (cursor. getColumnIndex ("label"); pojo. param = cursor. getString (cursor. getColumnIndex ("param"); pojo. result = cursor. getString (cursor. getColumnIndex ("result"); pojo. remark = cursor. getString (cursor. getColumnIndex ("remark"); pojo. time = cursor. getLong (cursor. getColumnIndex ("time"); return pojo;} // ---------------------- custom method ----------------------------------/public String getBuffe R (String label, String param) {String SQL = "select * from" + tableName + "where label =? And param =? "; NetTaskBuffer obj = getOneAsSQL (SQL, new String [] {label, param}); if (null = obj) {return null;} Date time = new Date (obj. time); Calendar c = Calendar. getInstance (); c. add (Calendar. DAY_OF_MONTH,-expiryDays); if (time. compareTo (c. getTime () <0) {delete (obj. id); return null;} return obj. result;} public String getBuffer (String label, String param, String remark) {String SQL = "select * From "+ tableName +" where label =? And param =? And remark =? "; NetTaskBuffer obj = getOneAsSQL (SQL, new String [] {label, param, remark}); if (null = obj) {return null ;} date time = new Date (obj. time); Calendar c = Calendar. getInstance (); c. add (Calendar. DAY_OF_MONTH,-expiryDays); if (time. compareTo (c. getTime () <0) {delete (obj. id); return null;} return obj. result;} public void deleteBuffer (String label) {String whereSql = "label =? "; Delete (whereSql, new String [] {label});} public void deleteBuffer (String label, String param) {String whereSql =" label =? And param =? "; Delete (whereSql, new String [] {label, param});} public void deleteBuffer (String label, String param, String remark) {String whereSql =" label =? And param =? And remark =? "; Delete (whereSql, new String [] {label, param, remark });} // -------------------- basic method --------------------------------- // *** obtain list */public ArrayList based on SQL <NetTaskBuffer> getListAsSQL (String SQL) {SQLiteDatabase db = getReadableDatabase (); cursor cursor = db. rawQuery (SQL, new String [] {}); ArrayList <NetTaskBuffer> itemList = new ArrayList <NetTaskBuffer> (); for (cursor. moveToFirst ();! (Cursor. isAfterLast (); cursor. moveToNext () {itemList. add (setBaseItem (cursor);} cursor. close (); db. close (); return itemList;}/*** get a record by ID */public NetTaskBuffer getById (int id) {String SQL = "select * from" + tableName + "where id =" + id; return getOneAsSQL (SQL, null );} /*** extract a record in the returned result */public NetTaskBuffer getOneAsSQL (String SQL, String [] arr) {SQLiteDatabase db = getReadableDa Tabase (); Cursor cursor = db. rawQuery (SQL, arr); try {if (null! = Cursor & cursor. getCount ()> 0) {cursor. moveToFirst (); return setBaseItem (cursor) ;}} finally {cursor. close (); db. close ();} return null;}/*** Save the object to the database and automatically determine whether the insert is more like ** @ return returns the number of updated records or the id of the inserted data, if the returned value is <= 0, it indicates failure */public long save (NetTaskBuffer pojo) {if (null = pojo. time | 0 = pojo. time) {pojo. time = new Date (). getTime () ;}long idortrows = 0l; if (null = pojo. id | pojo. id <1 ){ IdOrEffectRows = insert (pojo);} else {idOrEffectRows = (long) update (pojo);} return idOrEffectRows;}/*** add data, automatically insert id ** @ return the row ID of the newly inserted row, or-1 if an error occurred */public long insert (NetTaskBuffer pojo) {SQLiteDatabase db = getWritableDatabase (); // obtain the writable SQLiteDatabase object iniContentValues (pojo); // The ContentValues is similar to map and stores the key-Value Pair long result = db. insert (tableName, Null, contentValues); if (result! =-1) {pojo. id = (int) result;} db. close (); return result;}/*** update the record by ID, it is similar to inserting ** @ return the number of rows affected **/public int update (NetTaskBuffer pojo) {SQLiteDatabase db = getWritableDatabase (); iniContentValues (pojo ); // initialize the key-Value Pair int result = db. update (tableName, contentValues, "id =? ", New String [] {pojo. id +" "}); db. close (); return result ;}}