The Android development series of SQLite

The previous blog mentions SQLite. It is an embedded database because it is lightweight but powerful and 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.

SQLite's official site is http://www.sqlite.org/, can be downloaded freely, the above also have detailed documentation to participate, this blog focus on how to use SQLite in Android development.

In Android development. It is recommended to establish a class that inherits from Sqliteopenhelper to create a database operation class, for example:

public class DBHelper extends Sqliteopenhelper {private static final String database = "Test.db";p rivate static final Inte GER Version = 1;public DBHelper (context context) {//constructor initializes each member variable super (context, database, NULL, version);} @Overridepublic void OnCreate (Sqlitedatabase db) {//When a database connection is obtained from a subclass of Sqliteopenhelper. Assume that the database does not exist. Call the OnCreate method to create the database string sql = "CREATE TABLE score (ID integer primary key autoincrement,name varchar (), point Integer)" ;d b.execsql (SQL);} @Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {//when the database version number of the incoming instance is higher than the previous version number, The system will voluntarily invoke the Onupgrade method to update the database//update the database: Back up the database table data, create or ALTER tables, constraints, and so on. The original data is then imported 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 to create this database under the data/data/package name/databases/folder. Of course you can also specify the path to the database file. The version number is set to 1. If you want to upgrade, you can add version to the constructor's parameters in order to initialize it.

2. OnCreate is run when the database file is not created, assuming that it does not run

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

Then we build a detailed database operation class:

/** * Score Operation class * * @author Guwei * */public class Scoreop {//Insert a transcript public long insert (Sqlitedatabase db, String name, in Teger point) {try {db.begintransaction (); Contentvalues values = new Contentvalues (), Values.put ("name", name), Values.put ("point", point), long result = Db.insert ( "Score", null, Values), if (Result! =-1) {db.settransactionsuccessful ();} return result;} finally {db.endtransaction ();}} Change a transcript public int update (Sqlitedatabase db, String name, Integer point) {try {db.begintransaction (); Contentvalues values = new Contentvalues (), Values.put ("name", name), Values.put ("point", point), int result = Db.update (" Score ", values," name =?
", new string[] {name});d b.settransactionsuccessful (); return result;} finally {db.endtransaction ();}}  Delete a transcript public long delete (Sqlitedatabase db, String name) {try {db.begintransaction (); int result = Db.delete ("Score", "name =?", new string[] {name});d b.settransactionsuccessful (); return result;} finally {db.endtransaction ();}} Query the first 10 total points greater than the specified score for the name of a forward order public Cursor query (sqlitedatabase db, Integer point) {return db.query ("Score", new String[] {"Name", "sum (point) as points"}, NULL, NULL, "name", "sum (point) >=" + Point, "name ASC", "0,10");} More flexible queries, SQL random stitching public Cursor query (sqlitedatabase db, String sql, string[] selectionargs) {return db.rawquery (SQL, Sele Ctionargs);}}

This encapsulates the crud operation. And all with transactions (required to run multiple SQL), the last two query methods are worth paying attention to.

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

According to the order, the meaning of the parameters is as follows:

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, does not contain Wherekeyword

4),Selectionargs may include?

s in selection, which is replaced by the values from Selectionargs, in order that they appear in the selection. The values would be bound as Strings.

The number of parameters 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.
Limitkeyword. Use when paging queries

In fact, the corresponding 8 parameters above, such as the following SQL query statement after each keyword, the principle is by specifying the number of parameters such as the following SQL statement.

By the way, SQLite expert is a very useful tool for managing SQLite databases and can be downloaded to http://www.sqliteexpert.com/. You can also search directly to download the corresponding cracked version number.

Go back to the second query method. The parameters are very easy, there is only one SQL statement and a string array (the value that provides the SQL statement parameters). The point of this approach is that it is very flexible and can throw SQL that can run directly into the run. And it is a number of references. is a very powerful addition to the first query method.

Good. Finally, we can write test code to verify. The interface is very easy to put a button directly.

public class Sqliteactivity extends Activity {@Override protected void onCreate (Bundle savedinstancestate) {          Super.oncreate (savedinstancestate);            Setcontentview (R.layout.activity_sqlite);          Button btn = (button) Findviewbyid (R.ID.BTN);                  Btn.setonclicklistener (New View.onclicklistener () {@Override public void OnClick (View v) {                                    DBHelper dbhelper = new DBHelper (sqliteactivity.this);                                    Get a writable database operation object Sqlitedatabase wdb = Dbhelper.getwritabledatabase ();                  Add record Scoreop Scoreop = new Scoreop ();                  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); Change Record scoreop.updAte (wdb, "Li4", 90);                    Delete Record Scoreop.delete (wdb, "li4");                                    Get a readable database operation object Sqlitedatabase RDB = Dbhelper.getreadabledatabase ();                    1. Ability to invoke the system-supplied query method to return the cursor object in the form of a specified number of parameters//CURSOR cursor = Scoreop.query (RDB, 192);                                  2. Ability to run SQL query statements directly cursor cursor = scoreop. Query (RDB, "Select Name,sum (point) as points from score group by name have sum (point) >=192 ORDER by name ASC limit?,?"                  , new string[] {"0", "10"}); while (Cursor.movetonext ()) {String name = cursor.getstring (cursor. get                      ColumnIndex ("name"));                        Integer points = cursor.getint (cursor. Getcolumnindex ("points"));     Toast.maketext (Sqliteactivity.this,                         "Name:" + name + ".                  Total Score: "+ points, Toast.length_short)." Show ();              } cursor.close ();        }          });   }    }

When you click button to run, you can eject the qualifying data. Assuming that you can switch to the DDMS interface, select the File Explorer tab, locate the Test.db file that we created under the path, pull (draw) to the computer disk, and open the validation with tools such as SQLite expert.

The Android development series of SQLite