Android Tool Class database management

Database Tools 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 class * This class provides database operations commonly used additions and deletions, as well as various complex conditions matching, paging, sorting and other operations * * @see Sqlitedatabase */public class Sqlitetemplate {/** * Default Primary Key */ protected String Mprimarykey = "_id";/** * DBManager */private DBManager dbmanager;/** * is a transaction */private Boolean Istrans ACTION = false;/** * DB connection */private sqlitedatabase database = Null;private sqlitetemplate () {}private sqlitetemplate (DBMa Nager DBManager, Boolean istransaction) {This.dbmanager = Dbmanager;this.istransaction = istransaction;} /** * Istransaction Whether it belongs to a transaction note: Once the istransaction is set to True * all sqlitetemplate methods do not automatically close the resource and need to be closed manually after the transaction is successful * * @return */public Stat IC sqlitetemplate getinstance (DBManager Dbmanager,boolean istransaction) {return new sqlitetemplate (DBManager, istransaction);} /** * Executes an SQL statement * * @param name * @param tel */public void execSQL (String sql) {try {dataBase = dbmanager.opendatabase ();d atabase.execsql (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 = Dbmana Ger.opendatabase ();d atabase.execsql (SQL, Bindargs);} catch (Exception e) {e.printstacktrace ();} finally {if (!istransaction) {closeDatabase (null);}}} /** * Insert a data into a database table * * @param table * Name * @param content * Field value */public long insert (String table, Contentvalues content) {try {database = Dbmanager.opendatabase ();//Insert method first parameter: Database table name, second parameter inserts a NULL into the table if content is empty , 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;} /** * Bulk deletion of specified primary key data * * @param ids */public void Deletebyids (String table, Object ... primarykeys) {try {if (Primarykeys.leng Th > 0) {StringBuilder sb = new StringBuilder (); for (@SuppressWarnings ("unused") Object Id:primarykeys) {sb.append ("?"). Append (",");} Sb.deletecharat (Sb.length ()-1);d atabase = Dbmanager.opendatabase ();d atabase.execsql ("Delete from" + table + "where" + Mprimarykey + "in (" + SB + ")", (object[]) primarykeys);}} catch (Exception e) {e.printstacktrace ();} finally {if (!istransaction) {closeDatabase (null);}}} /** * Deletes a row of data based on a field and value, such as Name= "Jack" * @param table * @param field * @param value * @return The return value greater than 0 means the deletion succeeds */public int d Eletebyfield (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 condition * * @param table * Name * @param whereclause * Query statement parameters adopted? * @param Whereargs * Parameter value * @return The return value greater than 0 means the deletion succeeds */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 according to the primary key * * @param table * @param ID * @return The return value greater than 0 indicates successful deletion */public int Deletebyid (string table, string id) {TR y {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 according to the primary key * * @param table * @param ID * @param values * @return The return value greater than 0 indicates a successful update */public int Updatebyid (String table , String ID, contentvalues values) {try {dataBase = Dbmanager.opendatabase (); Return database.update (table, values, Mprima Rykey + "=?", 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 return value greater than 0 indicates update succeeded */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 ();} finally {if (!istransaction) {closeDatabase (null);}} return 0;} /** * see if a piece of data exists based on 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;} /** * see if a piece of data exists based on a field/value * @param status * @return */public Boolean Isexistsbyfield (String table, String field, String V alue) {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 SQL statements to see if 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, Selectio Nargs), 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 data * * @param rowmapper * @param SQL * @param args * @return */public <T> T queryforobject (rowmapper<t > 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 * Stride length * @return */public <T> list<t> queryforlist (rowmapper<t> rowmapper, String sql,string[] Selecti Onargs) {cursor cursor = NULL; List<t> list = null;try {dataBase = Dbmanager.opendatabase (); cursor = database.rawquery (sql, selectionargs); list = New Arraylist<t> (); while (Cursor.movetonext ()) {List.add (Rowmapper.maprow (cursor, cursor.getposition ()));}} finally {if (!istransaction) {closeDatabase (cursor);}} return list;}            /** * Paging Query * * @param rowmapper * @param SQL * @param startresult * Start index Note: The first record index is 0 * @param maxresult * Stride length * @return */public <T> list<t> queryforlist (rowmapper<t> rowmapper, String sql,int startresult , int maxresult) {cursor cursor = NULL; List<t> list = null;try {dataBase = Dbmanager.opendatabase (); cursor = database.rawquery (sql + "Limit?,?", new Stri Ng[] {String.valueoF (Startresult), string.valueof (Maxresult)}), List = new arraylist<t> (), while (Cursor.movetonext ()) {List.add ( Rowmapper.maprow (cursor, cursor.getposition ()));}} finally {if (!istransaction) {closeDatabase (cursor);}} return list;} /** * Get Record Count * * @return */public Integer getcount (String sql, string[] args) {cursor cursor = null;try {dataBase = Dbmana  Ger.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;} /** * Paging Query * * @param rowmapper * @param table * Retrieved tables * @param columns * is an array of strings consisting of column names that need to return columns, passing in NULL Returns all the columns. * @param selection * query conditional clauses, which are equivalent to the part after the WHERE keyword in the SELECT statement, where the placeholder "?" * @param Selectionargs * Corresponds to the SEL in the conditional clause The value of the placeholder in the ection statement, the position of the value in the array must match the position of the placeholder in the statement, otherwise there will be an exception * @param groupBy * GROUP BY statement that groups the result set (excluding the group by keyword). Passing in NULL will not group the result set * @param havinG * Filters The result set after the query, passing NULL does not filter * @param ORDER BY statement (excluding the order by keyword) that sorts the result set. Passing NULL will use the default sort for the result set * @param limit * Specifies the offset and the number of records fetched, which is equivalent to the part after the SELECT statement limit keyword, and if NULL returns all rows * @return */public < T> list<t> queryForList (rowmapper<t> rowmapper, string table,string[] columns, string selection, string[ ] Selectionargs,string groupBy, string having, string by-clause, string limit) {list<t> List = null; Cursor cursor = null;try {dataBase = Dbmanager.opendatabase (); cursor = database.query (table, columns, selection, selectio Nargs,groupby, having, as-is, limit); list = new arraylist<t> (); 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 <T> */public interface rowmapper<t> {/** * * @param cursor * Cursor * @param index * subscript * @return */public T maprow (cursor cursor, int index);} /** * Close database */public void closeDatabase (cursor cursor) {if (null! = database) {database.close ();} if (null! = cursor) {cursor.close ();}}}