structured way to describe the operation of a database
This way, even if we are unfamiliar with SQL statements, we can use the most familiar object-oriented approach to database operations.
Structured way to query the data of cursor: Db.query ("Contacts", new string[]{"id", "name"},null,null,null,null,null);// Structured way to insert data contentvalue values = new Contentvalues () values.put ("id", 2), Values.put ("name", "CPACM");d b.insert ("table" , null,values);
/*** Parameter Description * Table: Data table name, columns: The column name that needs to be displayed, if NULL is equivalent to * * selection: The Where condition of the SQL statement; The Selectionargs array puts the where condition to replace? Number * Groupby:sql Statement Group, ORDER by: Sort, default asc**/public Cursor query (string table, string[] columns, string selection, string[ ] Selectionargs, String groupBy, String having, string-by-clause) {
}
For example, the SQL statement I want to query is
SELECT CustomerName, SUM (orderprice) from Orders WHERE country=? GROUP by CustomerName have SUM (orderprice) >500 ORDER by CustomerName
So I wrote the following code
Data table name string table = "Orders"; Column name to display string[] columns = new string[] {"CustomerName", "SUM (Orderprice)"}; Select the condition string selection = "country=?"; The variables in the corresponding conditions of the question mark, the number of times please one by one seated. string[] Selectionargs = new string[]{"China"}; Group name string groupBy = "CustomerName"; The conditions for grouping string having = "SUM (orderprice) >500"; Sort by field string by order = "CustomerName"; Cursor c = db.query (table, columns, selection, Selectionargs, GroupBy, having, by-and-by);
This will enable the database to be queried. The other statement parameters are similar, here is not introduced.
Public long Insert (string table, String nullcolumnhack, contentvalues values)
public int Delete (string table, String Whereclause, string[] whereargs)
public int update (string table, contentvalues values, String whereclause, string[] whereargs)
Extracurricular knowledge : About GroupBy and having the use of
Group by the name is grouped by XXX, it must have an aggregate function to work with, and at least one group Identification field is required for use. Aggregate functions are: sum (), COUNT (), AVG (), and so on, using group by to summarize the data in groups. For example the above SQL statement CustomerName, if it has four rows {"Zhang San", "John Doe", "Zhang San", "John Doe"}, then this will be divided into two groups, respectively, Zhang San Group and Li four groups, and then the sum of the orderprice they use. The
having the function is to specify a condition for each group, like where to specify the condition, that is, you can select rows based on criteria you specify. If you want to use the HAVING clause, it must be behind the GROUP BY clause. or the above SQL statement, if Zhang San's sum (orderprice) does not exceed 500, then the three groups will not be displayed.
Pre-compiling of SQL statements
In practice, some SQL statements need to be reused, in order to avoid the overhead of repeatedly parsing SQL statements, you can precompile the SQL statements that need to be reused to improve the execution efficiency of database operations.
Compile complex SQL statements sqlitestatement Compiledsql = db.compilestatement (asql);//execute Sqlcompiledsql.execute ();
In addition, Android offers a rich range of advanced database features, such as support for triggers, support for composite indexes, and support for processing of database transactions.
try{ db.begintransaction (); Perform related database operations, and if there are exceptions, go directly to the finally section. }finally{ //success is called Endtransaction to end transaction db.endtransaction (); }
Extracurricular knowledge : The so-called transaction is a user-defined sequence of database operations, these operations are either done or not done, is an inseparable unit of work. For example, in a relational database, a transaction can be an SQL statement, a set of SQL statements, or an entire program. For example, if you want to modify two different tables in the database at the same time, if they are not a transaction, when the first table is modified, but the second table modification has an exception and cannot be modified, only the second table is returned to the unmodified state, and the first table has been modified. And when you set them up as a transaction, when the first table is modified, but the second table modification has an exception and cannot be modified, the first table and the second table are going back to the unmodified state! This is called a transaction rollback.
Sqliteopenhelper
In Sqliteopenhelper, a Sqlitedatabase object is encapsulated with the ability to perform operations on the database by using this class.
Package Com.example.notebook;import Android.content.context;import Android.database.sqlite.sqlitedatabase;import Android.database.sqlite.sqliteopenhelper;import Android.database.sqlite.sqlitedatabase.cursorfactory;public Class DBHelper extends sqliteopenhelper{private static final int version=1; /** * In Sqliteopenhelper subclasses, you must have this constructor * @param context Context Object * @param name Database name * @param facto RY * @param version of the current database, the value must be an integer and an incremented state */public DBHelper (Context context,string name,cursorfactory FA Ctory,int version) {super (context,name,factory,version); } public DBHelper (context context, String name, int. version) {this (context,name,null,version); } public DBHelper (context context, String name) {this (context,name,version); @Override public void OnCreate (Sqlitedatabase db) {////database is first constructed, this function is called, where you can construct tables, indexes, and so on SYSTEM.OUT.P Rintln ("Create a Database"); The execsql is used to perform sqL Statement db.execsql ("CREATE Table notebook (_id integer primary key autoincrement,pic varchar (), title varchar (), con Tent text,time varchar) "); } @Override public void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {//If the given current database version is higher than the existing database version, call the function System.out.println ("Upgrade a Database"); } }
Sqliteopenhelperthe application
Create a new database management class Dbmanager
Package Com.example.notebook;import Android.content.contentvalues;import Android.content.context;import Android.database.cursor;import Android.database.sqlite.sqlitedatabase;import Android.database.sqlite.sqliteexception;import Android.util.log;public class DBManager {private Context Mcontext = nul L Private Sqlitedatabase Msqlitedatabase = null;//Object used to manipulate the database private dbhelper DH = null;//Object used to create the database private Strin G DbName = "note.db";//database name private int dbversion = 1;//database Version public DBManager (context context) {Mcontext = Context; public void Open () {try{DH = new DBHelper (mcontext, dbName, NULL, dbversion);//Establish database if (DH = = null) {LOG.V ("msg", "is null"); return; } msqlitedatabase = Dh.getwritabledatabase ();//Open Database//dh.onopen (msqlitedatabase) in writable mode; }catch (sqliteexception se) {se.printstacktrace (); }}public void Close () { Msqlitedatabase.close ();//Close Database Dh.close (); }public cursor SelectAll () {cursor cursor = NULL; try{//sql statement operation String sql = "SELECT * from Notebook"; cursor = msqlitedatabase.rawquery (sql, NULL); }catch (Exception ex) {ex.printstacktrace (); cursor = NULL; } return cursor; Public Cursor Selectbyid (int id) {//string result[] = {}; cursor cursor = NULL; try{//sql statement action String sql = "SELECT * from notebook where _id= '" + ID + "'"; cursor = msqlitedatabase.rawquery (sql, NULL); }catch (Exception ex) {ex.printstacktrace (); cursor = NULL; } return cursor; Public long Insert (string title, String content,string pic) {Long datetime = System.currenttimemillis (); Long L =-1; try{//Structured mode operation contentvalues CV = new Contentvalues (); Cv.put ("title", title); Cv.put ("content", content); Cv.put ("Time", DateTime); Cv.put ("Pic", pic); L = Msqlitedatabase.insert ("notebook", NULL, CV); LOG.V ("datetime", datetime+ "" +l); }catch (Exception ex) {ex.printstacktrace (); L =-1; } return L; }public int delete (int id) {int affect = 0; try{//structured mode operation affect = Msqlitedatabase.delete ("Notebook", "_id=?", new String[]{string.valueof (ID)}); }catch (Exception ex) {ex.printstacktrace (); affect =-1; } return affect;} public int update (int ID, string title, String content,string pic) {int affect = 0; try{//Structured mode operation contentvalues CV = new Contentvalues (); Cv.put ("title", title); Cv.put ("content", content); Cv.put ("Pic", pic); String w[] = {string.valueof (id)}; affect = Msqlitedatabase.update ("Notebook", CV, "_id=?", W); }catch (Exception ex) {ex.printstacktrace (); affect =-1; } return affect;}}
Get Data sample
Private DBManager DM = null;//Database Management Object
private cursor cursor = NULL;
DM = new DBManager (this);//Database Operation object Dm.open ();//Open Database operation object cursor = Dm.selectall ();//Get All data Cursor.movetofirst ();//move the cursor to the first data, you must call int count = Cursor.getcount () before use,//number ArrayList <String> contents = new arraylist<string> ();//All collections of the picture arraylist<string> IMGs = new arraylist& Lt String> ();//All collections of the picture arraylist<string> items = new arraylist<string> ();//All collections of the title Array List<string> times = new arraylist<string> ();//All sets of time for (int i= 0; i < count; i++) { Contents.add (Cursor.getstring (Cursor.getcolumnindex ("content")); Imgs.add (Cursor.getstring (Cursor.getcolumnindex ("pic")); Items.Add (Cursor.getstring (Cursor.getcolumnindex ("title")); Times.add (Cursor.getstring (Cursor.getcolumnindex ("Time"));
Cursor.getint (Cursor.getcolumnindex ("_id")) Cursor.movetonext ();//pointing the cursor to the next} Dm.clo SE ();//Close Data manipulation object