Android tool-type Database Management

Database tool class, elegant Management of sqlite in android

Package csdn. shimiso. eim. db; import java. util. arrayList; import java. util. list; import android. content. contentValues; import android. database. cursor; import android. database. sqlite. SQLiteDatabase;/*** SQLite database template tool ** provides common addition, deletion, modification, and query operations for database operations, as well as complex condition matching and paging, sorting and other operations ** @ see SQLiteDatabase */public class SQLiteTemplate {/*** Default Primary key */protected String mPrimaryKey = "_ id "; /*** DBManager */private D BManager dBManager;/*** whether it is a transaction */private boolean isTransaction = false;/*** dataBase connection */private SQLiteDatabase dataBase = null; private SQLiteTemplate () {} private SQLiteTemplate (DBManager dBManager, boolean isTransaction) {this. dBManager = dBManager; this. isTransaction = isTransaction;}/*** does isTransaction belong to a transaction? Note: Once isTransaction is set to true * All SQLiteTemplate methods do not automatically close resources, you must manually close ** @ return */public after the transaction is successful. Static SQLiteTemplate getInstance (DBManager dBManager, boolean isTransaction) {return new SQLiteTemplate (dBManager, isTransaction );} /*** execute an SQL statement ** @ param name * @ param tel */public void execSQL (String SQL) {try {dataBase = dBManager.openDatabase();dataBase.exe cSQL (SQL );} catch (Exception e) {e. printStackTrace ();} finally {if (! IsTransaction) {closeDatabase (null) ;}}/*** executes an SQL statement ** @ param name * @ param tel */public void execSQL (String SQL, object [] bindArgs) {try {dataBase = dbmanager.opendatabase(;;database.exe cSQL (SQL, bindArgs);} catch (Exception e) {e. printStackTrace ();} finally {if (! IsTransaction) {closeDatabase (null );}}} /*** insert a piece of data into the database table ** @ param table * table name * @ param content * field value */public long insert (String table, ContentValues content) {try {dataBase = dBManager. openDatabase (); // The first parameter of the insert method: dataBase table name. If the second parameter is NULL, a NULL parameter is inserted into the table, and the third parameter is the inserted CONTENT return dataBase. insert (table, null, content);} catch (Exception e) {e. printStackTrace ();} finally {if (! IsTransaction) {closeDatabase (null) ;}} return 0 ;}/ *** Delete specified primary key data in batches ** @ param ids */public void deleteByIds (String table, object... primaryKeys) {try {if (primaryKeys. length> 0) {StringBuilder sb = new StringBuilder (); for (@ SuppressWarnings ("unused") Object id: primaryKeys) {sb. append ("? "). Append (",");} sb. deleteCharAt (sb. length ()-1); dataBase = dbmanager.opendatabase(;;database.exe cSQL ("delete from" + table + "where" + mPrimaryKey + "in (" + sb + ")", (Object []) primaryKeys) ;}} catch (Exception e) {e. printStackTrace ();} finally {if (! IsTransaction) {closeDatabase (null) ;}}/ *** delete a row of data based on a field and value, for example, name = "jack" ** @ param table * @ param field * @ param value * @ return the return value is greater than 0, indicating that the deletion is successful */public int deleteByField (String table, String field, string value) {try {dataBase = dBManager. openDatabase (); return dataBase. delete (table, field + "=? ", New String [] {value});} catch (Exception e) {e. printStackTrace ();} finally {if (! IsTransaction) {closeDatabase (null) ;}} return 0 ;}/ *** delete data based on conditions ** @ param table * table name * @ param whereClause * query statement parameters? * @ Param whereArgs * parameter value * @ return value greater than 0 indicates successful deletion */public int deleteByCondition (String table, String whereClause, String [] whereArgs) {try {dataBase = dBManager. openDatabase (); return dataBase. delete (table, whereClause, whereArgs);} catch (Exception e) {e. printStackTrace ();} finally {if (! IsTransaction) {closeDatabase (null) ;}} return 0 ;} /*** delete a row of data based on the primary key ** @ param table * @ param id * @ return the returned value is greater than 0, indicating that the deletion is successful */public int deleteById (String table, String id) {try {dataBase = dBManager. openDatabase (); return deleteByField (table, mPrimaryKey, id);} catch (Exception e) {e. printStackTrace ();} finally {if (! IsTransaction) {closeDatabase (null) ;}} return 0 ;} /*** update a row of data based on the primary key ** @ param table * @ param id * @ param values * @ return the returned value is greater than 0, indicating that the update is successful */public int updateById (String table, string id, ContentValues values) {try {dataBase = dBManager. openDatabase (); return dataBase. update (table, values, mPrimaryKey + "=? ", New String [] {id});} catch (Exception e) {e. printStackTrace ();} finally {if (! IsTransaction) {closeDatabase (null) ;}} return 0 ;} /***** update data ** @ param table * @ param values * @ param whereClause * @ param whereArgs * @ return value greater than 0 indicates successful update */public int update (String table, contentValues values, String whereClause, String [] whereArgs) {try {dataBase = dBManager. openDatabase (); return dataBase. update (table, values, whereClause, whereArgs);} catch (Exception e) {e. printStackTrace ();} fi Nally {if (! IsTransaction) {closeDatabase (null) ;}} return 0 ;} /*** check whether a data entry exists based on the primary key ** @ param table * @ param id * @ return */public Boolean isExistsById (String table, String id) {try {dataBase = dBManager. openDatabase (); return isExistsByField (table, mPrimaryKey, id);} catch (Exception e) {e. printStackTrace ();} finally {if (! IsTransaction) {closeDatabase (null) ;}} return null ;} /*** check whether a data entry exists based on a field/value ** @ param status * @ return */public Boolean isExistsByField (String table, String field, String value) {StringBuilder SQL = new StringBuilder (); SQL. append ("select count (*) FROM "). append (table ). append ("WHERE "). append (field ). append ("=? "); Try {dataBase = dBManager. openDatabase (); return isExistsBySQL (SQL. toString (), new String [] {value});} catch (Exception e) {e. printStackTrace ();} finally {if (! IsTransaction) {closeDatabase (null) ;}} return null ;} /*** use an SQL statement to check whether a piece of data exists ** @ param SQL * @ param selectionArgs * @ return */public Boolean isExistsBySQL (String SQL, String [] selectionArgs) {Cursor cursor = null; try {dataBase = dBManager. openDatabase (); cursor = dataBase. rawQuery (SQL, selectionArgs); if (cursor. moveToFirst () {return (cursor. getInt (0)> 0);} else {return false;} catch (Exception e) {E. printStackTrace ();} finally {if (! IsTransaction) {closeDatabase (cursor) ;}} return null;}/*** query a piece of data ** @ param rowMapper * @ param SQL * @ param args * @ return */public
 
  
T queryForObject (RowMapper
  
   
RowMapper, String SQL, String [] args) {Cursor cursor = null; T object = null; try {dataBase = dBManager. openDatabase (); cursor = dataBase. rawQuery (SQL, args); if (cursor. moveToFirst () {object = rowMapper. mapRow (cursor, cursor. getCount () ;}} finally {if (! IsTransaction) {closeDatabase (cursor) ;}} return object ;}/ *** query ** @ param rowMapper * @ param SQL * @ param startResult * Start index note: the first record index is 0 * @ param maxResult * Step * @ return */public
   
    
List
    
     
QueryForList (RowMapper
     
      
RowMapper, String SQL, String [] selectionArgs) {Cursor cursor = null; List
      
        List = null; try {dataBase = dBManager. openDatabase (); cursor = dataBase. rawQuery (SQL, selectionArgs); list = new ArrayList
       
         (); While (cursor. moveToNext () {list. add (rowMapper. mapRow (cursor, cursor. getPosition () ;}} finally {if (! IsTransaction) {closeDatabase (cursor) ;}} return list ;}/ *** query by PAGE ** @ param rowMapper * @ param SQL * @ param startResult * Start index note: the first record index is 0 * @ param maxResult * Step * @ return */public
        
          List
         
           QueryForList (RowMapper
          
            RowMapper, String SQL, int startResult, int maxResult) {Cursor cursor = null; List
           
             List = null; try {dataBase = dBManager. openDatabase (); cursor = dataBase. rawQuery (SQL + "limit ?,? ", New String [] {String. valueOf (startResult), String. valueOf (maxResult)}); list = new ArrayList
            
              (); While (cursor. moveToNext () {list. add (rowMapper. mapRow (cursor, cursor. getPosition () ;}} finally {if (! IsTransaction) {closeDatabase (cursor) ;}} return list ;}/ *** obtain the number of records ** @ return */public Integer getCount (String SQL, String [] args) {Cursor cursor = null; try {dataBase = dBManager. openDatabase (); cursor = dataBase. rawQuery ("select count (*) from (" + SQL + ")", args); if (cursor. moveToNext () {return cursor. getInt (0) ;}} catch (Exception e) {e. printStackTrace ();} finally {if (! IsTransaction) {closeDatabase (cursor) ;}} return 0 ;} /*** query by PAGE ** @ param rowMapper * @ param table * The retrieved table * @ param columns * is an array of strings consisting of the names of the columns to be returned, if null is input, all columns are returned. * @ Param selection * the query Condition Clause is equivalent to the part after the where keyword of the select statement. The placeholder "? "* @ Param selectionArgs * Corresponds to the placeholder value in the selection statement. The position in the array must be the same as that in the statement, otherwise, an exception * @ param groupBy * indicates the group by statement that groups the result set (excluding the group by keyword ). If null is input, no result set is grouped * @ param having * to filter the queried result set, if null is input, the order by statement * @ param orderBy * to sort the result set (excluding the order by keyword) is not filtered ). If null is input, the default sorting * @ param limit * is used for the result set to specify the offset and the number of retrieved records, which is equivalent to the part following the limit keyword of the select statement, if it is null, all rows are returned * @ return */public
             
               List
              
                QueryForList (RowMapper
               
                 RowMapper, String table, String [] columns, String selection, String [] selectionArgs, String groupBy, String having, String orderBy, String limit) {List
                
                  List = null; Cursor cursor = null; try {dataBase = dBManager. openDatabase (); cursor = dataBase. query (table, columns, selection, selectionArgs, groupBy, having, orderBy, limit); list = new ArrayList
                 
                   (); While (cursor. moveToNext () {list. add (rowMapper. mapRow (cursor, cursor. getPosition () ;}} finally {if (! IsTransaction) {closeDatabase (cursor) ;}} return list ;}/ *** Get Primary Key *** @ return */public String getPrimaryKey () {return mPrimaryKey ;} /*** Set Primary Key ** @ param primaryKey */public void setPrimaryKey (String primaryKey) {this. mPrimaryKey = primaryKey;}/***** @ author shimiso ** @ param
                  
                    */Public interface RowMapper
                   
                     {/***** @ Param cursor * Cursor * @ param index * subscript index * @ return */public T mapRow (cursor, int index );} /*** close database */public void closeDatabase (Cursor cursor) {if (null! = DataBase) {dataBase. close () ;}if (null! = Cursor) {cursor. close ();}}}