The Android development series of SQLite

The previous blog mentioned SQLite, which is an embedded database, because of its lightweight but powerful, is widely used in embedded devices. Later, after the popularity of smartphones and tablets, as a file-based database, it almost became a must-have for the smart Device standalone database, which can be packaged into the APK file with the Android app.

The official website of SQLite is http://www.sqlite.org/, can be downloaded arbitrarily, the above also has the detailed document to refer to, this blog focuses on how SQLite is used in Android development.

In Android development, it is recommended to establish a class that inherits from Sqliteopenhelper to create database operation classes, such as:

 Public classDBHelperextendsSqliteopenhelper {Private Static FinalString database = "Test.db"; Private Static FinalInteger Version = 1;  PublicDBHelper (Context context) {//constructors Initialize each member variable        Super(Context, database,NULL, version); } @Override Public voidonCreate (Sqlitedatabase db) {//When a database connection is obtained through a subclass of Sqliteopenhelper, if the database does not exist, the OnCreate method is called to create the databaseString sql = "CREATE TABLE score (ID integer primary key autoincrement,name varchar (), point Integer)";    Db.execsql (SQL); } @Override Public voidOnupgrade (Sqlitedatabase db,intOldversion,intnewversion) {        //The system automatically calls the Onupgrade method to update the database when the database version number of the incoming instance is higher than the previous version number//Update database operations: Back up the database table data, recreate or modify tables, constraints, and so on, and then import the original data into the newly created table.     }}

The above code has 3 points to note:

1. The name and version number of the database are specified in the constructor (method)

It will default in the data/data/package name/databases/directory to create this database, of course, you can also specify the path of the database file exists, the version number is set to 1, if you want to upgrade, you can add version to the parameters of the constructor method in order to initialize.

2, OnCreate is executed when the database file is not created, if any, do not execute

3, Onupgrade is in the new specified version number is higher than the old specified version number of time to execute, generally in the database upgrade needs to operate

Then we build a specific database operation class:

/*** Score Operation class * *@authorGuwei **/ Public classScoreop {//insert a score record     Public LongInsert (Sqlitedatabase db, String name, Integer point) {Try{db.begintransaction (); Contentvalues Values=Newcontentvalues (); Values.put ("Name", name); Values.put ("Point", point); Longresult = Db.insert ("Score",NULL, values); if(Result! =-1) {db.settransactionsuccessful (); }            returnresult; } finally{db.endtransaction (); }    }    //Modify a record of a score     Public intUpdate (Sqlitedatabase db, String name, Integer point) {Try{db.begintransaction (); Contentvalues Values=Newcontentvalues (); Values.put ("Name", name); Values.put ("Point", point); intresult = Db.update ("Score", values, "name =?"),                    Newstring[] {name});            Db.settransactionsuccessful (); returnresult; } finally{db.endtransaction (); }    }    //Delete a track record     Public LongDelete (sqlitedatabase db, String name) {Try{db.begintransaction (); intresult = Db.delete ("Score", "name =?",Newstring[] {name});            Db.settransactionsuccessful (); returnresult; } finally{db.endtransaction (); }    }    //Query the first 10 total points of the person that are greater than the specified score according to the name forward order     PublicCursor Query (sqlitedatabase db, Integer point) {returnDb.query ("Score",                NewString[] {"Name", "sum (point) as points"},NULL,NULL,                "Name", "sum (point) >=" + Point, "name ASC", "0,10"); }    //more flexible query, SQL arbitrary splicing     PublicCursor Query (sqlitedatabase db, String sql, string[] selectionargs) {returndb.rawquery (SQL, Selectionargs); }}

This encapsulates the CRUD operations, all with transactions (required to execute multiple SQL), and the last two query methods are worth paying attention to.

The first query is a case where a complex SQL query statement is specified.

In order, the parameters have the following meanings:

1),table the table name to compile the query against.

The table name of the specified query

2),columns A list of which columns to return. Passing NULL would return all columns, which are discouraged to prevent reading data from storage This isn ' t going to being use D.

Column name of the returned query list

3),selection A filter declaring which rows to return, formatted as a SQL WHERE clause (excluding the Where Itsel f). Passing null would return all rows for the given table.

Where condition, not including where keyword

4),Selectionargs You may include? s in selection, which'll be replaced by the values from Selectionargs, in Ord Er that they appear in the selection. The values would be bound as Strings.

The parameter value specified by the Where condition

5),groupBy A Filter declaring how to group rows, formatted as a SQL GROUP BY clause (excluding the group by Itse LF). Passing null would cause the rows to is grouped.

Column names followed by the group by group

6), have A filter declare which row groups to include in the cursor, if row grouping is being used, formatted as An SQL has clause (excluding the having itself). Passing null would cause all row groups to be included, and was required when row grouping was not being used.
Having to be followed in the next section on a group basis

7), order the rows, formatted as a SQL ORDER BY clause (excluding the order by itself). Passing NULL would use the default sort order, which could be unordered.

Order BY is followed by a field

8),limit Limits The number of rows returned by the query, formatted as limit clause. Passing null denotes no LIMIT clause.
The Limit keyword, used when paging queries

In fact, the corresponding 8 parameters, which corresponds to the following SQL query statement after the keyword of the content, the principle is to use the specified parameters to join the following SQL statement.

Here by the way, SQLite expert is a very useful tool to manage the SQLite database, can be downloaded to the http://www.sqliteexpert.com/, or directly search to download the corresponding cracked version.

Back to the second query method, the arguments are simple, with only one SQL statement and a string array (providing the value of the SQL statement parameter). The significance of this method is that it is flexible, can be executed directly into the SQL execution, and is parameterized, is the first query method is a strong complement.

Well, finally we can write the test code to verify. The interface is simple, just place a button.

 Public classSqliteactivityextendsActivity {@Overrideprotected voidonCreate (Bundle savedinstancestate) {Super. OnCreate (savedinstancestate);            Setcontentview (R.layout.activity_sqlite); Button btn=(Button) Findviewbyid (R.ID.BTN); Btn.setonclicklistener (NewView.onclicklistener () {@Override Public voidOnClick (View v) {dbhelper DBHelper=NewDBHelper (sqliteactivity. This); //get a writable database operandSqlitedatabase wdb =dbhelper.getwritabledatabase (); //Add a recordScoreop Scoreop =NewScoreop (); Scoreop.insert (WDB,"Zhang3", 98); Scoreop.insert (WDB,"Zhang3", 94); Scoreop.insert (WDB,"Li4", 92); Scoreop.insert (WDB,"Wang5", 89); Scoreop.insert (WDB,"Wang5", 82); //Modify a recordScoreop.update (wdb, "Li4", 90); //Deleting RecordsScoreop.delete (wdb, "Li4"); //get a readable database operation objectSqlitedatabase RDB =dbhelper.getreadabledatabase (); //1. You can call the system-supplied query method to return the cursor object in the form of a specified parameter//cursor cursor = scoreop.query (RDB, 192); //2. SQL query statements can be executed directlycursor cursor =scoreop. Query (RDB,"Select Name,sum (point) as points from score group by name have sum (point) >=192 ORDER by name ASC limit?,?",                                  NewString[] {"0", "10" });  while(Cursor.movetonext ()) {String name=cursor.getstring (cursor. Getcolumnindex ("Name")); Integer points=cursor.getint (cursor. Getcolumnindex ("Points")); Toast.maketext (sqliteactivity. This,                              "Name:" + name + "; Score:" +points, Toast.length_short). Show ();              } cursor.close ();        }          }); }    }  

After clicking on the button, you can eject the qualifying data. If you are not at ease, you can switch to the DDMS interface, select the File Explorer tab, locate the Test.db file we created under the path, pull (draw) to the computer disk, and open the validation with a tool such as SQLite expert.

The Android development series of SQLite