C # How programmers learn the Android Development Series SQLite

C # How programmers learn the Android Development Series SQLite

As mentioned in the previous blog, SQLite is an embedded database, which is widely used in embedded devices due to its lightweight but powerful features. After the popularity of smartphones and tablets, as a file-type database, it became almost mandatory for the single-host database of smart devices, and can be packaged into the apk file with the Android app.

The official website of SQLite is ghost.

In Android development, we recommend that you create 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 "; private static final Integer version = 1; public DBHelper (Context context) {// constructor initializes member variables super (context, database, null, version );} @ Overridepublic void onCreate (SQLiteDatabase db) {// if the database connection is obtained through the subclass of SQLiteOpenHelper, if the database does not exist, call the onCreate method to create the database String SQL = "create table Score (id integer primary key autoi Ncrement, name varchar (20), point integer) "mongodb.exe cSQL (SQL) ;}@ Overridepublic void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {// when the database version number of the input instance is higher than the previous version number, the system will automatically call the onUpgrade method to update the database // update the database operation: Back up the database table data, re-create or modify tables and constraints, and then import the original data to the new table .}}

The above code has three points to note:

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

It will create the database under the data/package name/databases/directory by default. Of course, you can also specify the path where the database file exists. The version number is set to 1, if you want to upgrade, you can add version to the constructor parameters for initialization.

2. onCreate is executed when the database file is not created. If yes, It is not executed.

3. onUpgrade is executed when the new specified version number is higher than the old one. Generally, You need to perform operations during Database Upgrade.

Then we create a specific database operation class:

/*** Score operation class ** @ author guwei **/public class ScoreOp {// insert a score record public long insert (SQLiteDatabase db, String name, Integer 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 () ;}}// modify the 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}); db. setTransactionSuccessful (); return result;} finally {db. endTransaction () ;}}// delete a score record public long delete (SQLiteDatabase db, String name) {try {db. beginTransaction (); int result = db. delete ("Score", "name =? ", New String [] {name}); db. setTransactionSuccessful (); return result;} finally {db. endTransaction () ;}}// query the first 10 persons whose total scores are greater than the specified score in the forward order of name. 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 "," ");} // more flexible query. SQL can be concatenated with public Cursor query (SQLiteDatabase db, String SQL, string [] selectionArgs) {return db. rawQuery (SQL, selectionArgs );}}

The preceding CRUD operations are encapsulated and all contain transactions (required when multiple SQL statements are executed). The last two query methods are worth noting.

The first query specifies a complex SQL query statement.

In order, the parameter meanings are as follows:

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

Name of the table to be queried

2 ),ColumnsA list of which columns to return. Passing null will return all columns, which is discouraged to prevent reading data from storage that isn' t going to be used.

Column name of the returned query list

3 ),SelectionA filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table.

Where condition, excluding the where keyword

4 ),SelectionArgsYou may include? S in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings.

Parameter value specified by the where Condition

5 ),GroupByA filter declaring how to group rows, formatted as an SQL GROUP BY clause (excluding the GROUP BY itself). Passing null will cause the rows to not be grouped.

Column names followed by group

6 ),HavingA filter declare which row groups to include in the cursor, if row grouping is being used, formatted as an SQL HAVING clause (excluding the HAVING itself ). passing null will cause all row groups to be pinned ded, and is required when row grouping is not being used.
Having is followed by content further filtered based on the group

7 ),OrderByHow to order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself). Passing null will use the default sort order, which may be unordered.

Order by is followed by a Field

8 ),LimitLimits the number of rows returned by the query, formatted as LIMIT clause. Passing null denotes no LIMIT clause.
Limit keyword, used for paging Query

In fact, the above eight parameters correspond to the content following the keywords of the following SQL query statements. The principle is to concatenate the following SQL statements by specifying parameters.

Here, by the way, SQLite Expert is a very useful tool for managing SQLite databases. It can be downloaded at http://www.sqliteexpert.com/or directly searched and downloaded.

Return to the second query method. The parameter is very simple. There is only one SQL statement and one string array (the value of the SQL statement parameter is provided ). The significance of this method is that it is flexible and can directly throw the SQL statements that can be executed, and it is parameterized. It is a powerful supplement to the first query method.

Okay. Finally, we can write the test code to verify it. The interface is very simple. Just put a button.

Public class SQLiteActivity extends Activity {@ Overrideprotected void onCreate (Bundle savedInstanceState) {super. onCreate (savedInstanceState); setContentView (R. layout. activity_sqlite); Button btn = (Button) findViewById (R. id. btn); btn. setOnClickListener (new View. onClickListener () {@ Overridepublic void onClick (View v) {DBHelper dbHelper = new DBHelper (SQLiteActivity. this); // obtain 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); // modify the scoreOp record. update (wdb, "li4", 90); // Delete the scoreOp record. delete (wdb, "li4"); // obtain a readable database operation object SQLiteDatabase rdb = dbHelper. getReadableDatabase (); // 1. you can call the query method provided by the system The Cursor object is returned as a specified parameter // Cursor cursor = scoreOp. query (rdb, 192); // 2. you can directly execute the SQL query statement Cursor cursor = scoreOp. query (rdb, "select name, sum (point) as points from Score group by name having sum (point) >=192 order by name asc limit ?,? ", New String [] {" 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 + "; total score:" + points, Toast. LENGTH_SHORT ). show ();} cursor. close ();}});}}

Click the button to display the Qualified Data. If you are not at ease, you can switch to the DDMS interface, select the File Explorer tab, and find the test. db file, Pull (Pull) to the computer disk, open the verification with SQLite Expert and other tools.