Detailed description of SQLite applications in Android

Last time I introduced you to the basic information and usage process of SQLite. I believe my friends have some knowledge about SQLite. Today, I will share with you how to use SQLite in Android.

Currently, mainstream mobile devices such as Android and iPhone all use SQLite as the storage engine for complex data. When we develop applications for mobile devices, we may need to use SQLite to store a large amount of data, so we need to master the SQLite development skills on mobile devices. For the Android platform, the system has built-in rich APIs for developers to operate SQLite. We can easily access data.

The following describes the common SQLite operation methods. For convenience, I wrote the code in oncreate of the activity:

@ Overrideprotected void oncreate (bundle savedinstancestate) {super. oncreate (savedinstancestate); // open or create test. DB database sqlitedatabase DB = openorcreatedatabase ("test. DB ", context. mode_private, null1_mongodb.exe csql ("Drop table if exists person"); // create a personable table db.exe csql ("create table person (_ id integer primary key autoincrement, name varchar, age smallint )"); person = new person (); person. name = "John"; Per Son. Age = 30; // insert data db.exe csql ("insert into person values (null ,?, ?) ", New object [] {person. name, person. age}); person. name = "David"; person. age = 33; // contentvalues stores data in the form of key-value pairs contentvalues CV = new contentvalues (); cv. put ("name", person. name); cv. put ("Age", person. age); // Insert the data dB in contentvalues. insert ("person", null, CV); cv = new contentvalues (); cv. put ("Age", 35); // update the data dB. update ("person", CV, "name =? ", New string [] {" John "}); cursor c = dB. rawquery (" select * From person where age> =? ", New string [] {" 33 "}); While (C. movetonext () {int _ id = C. getint (C. getcolumnindex ("_ id"); string name = C. getstring (C. getcolumnindex ("name"); int age = C. getint (C. getcolumnindex ("Age"); log. I ("DB", "_ id =>" + _ ID + ", name =>" + name + ", age =>" + age);} C. close (); // Delete the database. delete ("person", "age <? ", New string [] {" 35 "}); // closes the current database. close (); // delete test. DB database // deletedatabase ("test. DB ");}

After the above code is executed, the system will generate a database file named "test. DB" in the/data/[package_name]/databases directory,

The above code basically covers most database operations. For addition, update, and deletion, we can use

Db.exe cutesql (string sql1_mongodb.exe cutesql (string SQL, object [] bindargs); // use placeholders in SQL statements, and the second parameter is the actual parameter set

In addition to the unified form, they also have their own operation methods:

db.insert(String table, String nullColumnHack, ContentValues values);db.update(String table, Contentvalues values, String whereClause, String whereArgs);db.delete(String table, String whereClause, String whereArgs);

The first parameter of the preceding three methods indicates the name of the table to be operated. The second parameter in insert indicates that if each column of the inserted data is empty, you must specify the name of a column in this row. If this column is set to null, no errors will occur. The third parameter in insert is a variable of the contentvalues type and a map composed of key-value pairs, key indicates the column name, and value indicates the value to be inserted in the column. The second parameter of update is similar, except that it updates the key of the field to the latest value, the third parameter whereclause indicates the where expression, for example, "age>? And Age
<?" The final whereargs parameter is the actual parameter value of the placeholder. The parameters of the delete method are also the same.

The following describes the query operation. The query operation is more complex than the preceding operations. Because we often face a variety of query conditions, the system also takes this complexity into account and provides us with a richer Query format:

db.rawQuery(String sql, String[] selectionArgs);db.query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy);db.query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit);db.query(String distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit);

The preceding methods are common query methods. The first method is the simplest. All SQL statements are organized into a string and Placeholders are used to replace actual parameters. selectionargs is the actual parameter set of placeholders; the following parameters are similar. Columns indicates all the names of the columns to be queried, and selection indicates the condition statement after where. You can use placeholders to specify the column names of groups, having specifies the grouping condition, used with groupby, orderby specifies the column name for sorting, limit specifies the paging parameter, and distinct can specify "true" or "false" to indicate whether to filter duplicate values. Note that the parameters selection, groupby, having, orderby, and limit do not include "where" and "group ".
By, having, order by, limit, and other SQL keywords.
Finally, they return a cursor object at the same time, representing the cursor of the dataset, a bit similar to the resultset in javase.

The following are common methods for cursor objects:

C. move (INT offset); // move to the specified row C. movetofirst (); // move to the first line C. movetolast (); // move to the last row C. movetoposition (INT position); // move to the specified row C. movetoprevious (); // move to the previous Row C. movetonext (); // move to the next row C. isfirst (); // specifies whether to point to the first C. islast (); // indicates whether to point to the last C. isbeforefirst (); // specifies whether to point to C. isafterlast (); // whether to point to C. isnull (INT columnindex); // specifies whether the column is null (the column base is 0) C. isclosed (); // whether the cursor has been closed c. getcount (); // The total number of data items c. getposition (); // returns the number of rows pointed to by the current cursor C. getcolumnindex (string columnname); // returns the column index value C for a column name. getstring (INT columnindex); // return the value of the specified column in the current row

Some of the common methods have been used in the above code examples. For more information, refer to the instructions in the official documentation.

Finally, after we complete database operations, remember to call the close () method of sqlitedatabase to release the database connection. Otherwise, sqliteexception may occur.

The above is the basic application of SQLite, but in actual development, in order to better manage and maintain the database, we will encapsulate a database operation class inherited from the sqliteopenhelper class, then, we encapsulate our business logic methods based on this class.

Next, we will use an instance to explain the specific usage. We will create a project named dB with the following structure:

Dbhelper inherits sqliteopenhelper and serves as the base class for maintaining and managing databases. dbmanager is built on dbhelper and encapsulates common business methods. person is the JavaBean corresponding to our person table, mainactivity is the display interface.

Next let's take a look at dbhelper:

Package COM. scott. DB; import android. content. context; import android. database. SQLite. sqlitedatabase; import android. database. SQLite. sqliteopenhelper; public class dbhelper extends sqliteopenhelper {Private Static final string database_name = "test. DB "; Private Static final int database_version = 1; Public dbhelper (context) {// set cursorfactory to null and use the default value super (context, database_name, null, database_version );} // when the database is created for the first time, oncreate will be called @ overridepublic void oncreate (sqlitedatabase dB) mongodb.exe csql ("create table if not exists person" + "(_ id integer primary key autoincrement, name varchar, age integer, info text) ");} // if the value of database_version is changed to 2, the system finds that the current database version is different, onupgrade @ overridepublic void onupgrade (sqlitedatabase dB, int oldversion, int newversion) mongodb.exe csql ("alter table person add column other string ");}}

As described above, the oncreate method will be called when the database is created for the first time. We can execute the statement for creating a table. After the system detects a version change, it will call the onupgrade method, we can execute statements such as modifying the table structure.

To facilitate the use of Object-oriented Data, we create a person class that corresponds to the fields in the person table, as shown below:

package com.scott.db;public class Person {public int _id;public String name;public int age;public String info;public Person() {}public Person(String name, int age, String info) {this.name = name;this.age = age;this.info = info;}}

Then, we need a dbmanager to encapsulate all our business methods. The Code is as follows:

Package COM. scott. DB; import Java. util. arraylist; import Java. util. list; import android. content. contentvalues; import android. content. context; import android. database. cursor; import android. database. SQLite. sqlitedatabase; public class dbmanager {private dbhelper helper; private sqlitedatabase dB; Public dbmanager (context) {helper = new dbhelper (context); // because getwritabledatabase internally calls mcontext. openorcres Atedatabase (mname, 0, mfactory); // to ensure that context has been initialized, we can put the steps for instantiating dbmanager in oncreate of activity DB = helper. getwritabledatabase ();}/*** add persons * @ Param persons */Public void add (list <person> Persons) {dB. begintransaction (); // start the transaction try {for (person: Persons) {db.exe csql ("insert into person values (null ,?, ?, ?) ", New object [] {person. name, person. age, person.info});} dB. settransactionsuccessful (); // The transaction is successfully set} finally {dB. endtransaction (); // end transaction}/*** update person's age * @ Param person */Public void updateage (person) {contentvalues CV = new contentvalues (); cv. put ("Age", person. age); dB. update ("person", CV, "name =? ", New string [] {person. name});}/*** delete old person * @ Param person */Public void deleteoldperson (person) {dB. delete ("person", "Age >=? ", New string [] {string. valueof (person. age)});}/*** query all persons, return list * @ return list <person> */public list <person> query () {arraylist <person> Persons = new arraylist <person> (); cursor c = querythecursor (); While (C. movetonext () {person = new person (); person. _ id = C. getint (C. getcolumnindex ("_ id"); person. name = C. getstring (C. getcolumnindex ("name"); person. age = C. getint (C. getcolumnindex ("Age"); person.info = C. getstring (C. getcolumnindex ("info"); persons. add (person);} C. close (); return persons;}/*** query all persons, return cursor * @ returncursor */Public cursor querythecursor () {cursor c = dB. rawquery ("select * From person", null); Return C;}/*** close database */Public void closedb () {dB. close ();}}

We instantiate dbhelper in the dbmanager constructor and obtain a sqlitedatabase object as the database instance of the entire application. When adding multiple person information, we adopt transaction processing to ensure data integrity; finally, we provide a closedb method to release database resources. This step is executed when the entire application is closed. This step is easy to forget, so pay attention to it.

The getwritabledatabase () method is used to obtain database instances. Some friends may have doubts. In getwritabledatabase () and getreadabledatabase (), why do you choose the former as the database instance of the entire application? Here I want to focus on this point.

Let's take a look at the getreadabledatabase () method in sqliteopenhelper:

Public synchronized sqlitedatabase getreadabledatabase () {If (mdatabase! = NULL & mdatabase. isopen () {// If mdatabase is not empty and has been opened, return mdatabase;} If (misinitializing) {// throw the exception throw new illegalstateexception ("getreadabledatabase called recursively");} // start to instantiate the database mdatabasetry {// note that getwritabledatabase () is called () method return getwritabledatabase ();} catch (sqliteexception e) {If (mname = NULL) Throw E; // can't open a temp database read-only! Log. E (TAG, "couldn't open" + mname + "for writing (will try read-only):", e );} // if the database cannot be opened in read/write mode, enable sqlitedatabase DB = NULL in read-only mode; try {misinitializing = true; string Path = mcontext. getdatabasepath (mname ). getpath (); // obtain the database path // open the database DB = sqlitedatabase in read-only mode. opendatabase (path, mfactory, sqlitedatabase. open_readonly); If (dB. getversion ()! = Mnewversion) {Throw new sqliteexception ("can't upgrade read-only database from version" + dB. getversion () + "to" + mnewversion + ":" + path);} onopen (db); log. W (TAG, "opened" + mname + "in read-only mode"); mdatabase = dB; // specify the new database return mdatabase for the mdatabase; // return the opened database} finally {misinitializing = false; If (DB! = NULL & dB! = Mdatabase) dB. Close ();}}

In the getreadabledatabase () method, first determine whether a database instance already exists and is in the open state. If yes, the instance is returned directly. Otherwise, an attempt is made to obtain a database instance in read/write mode, if the retrieval fails when the disk space is full, open the database in read-only mode, obtain the database instance, return the result, and assign the mdatabase value to the latest opened database instance. Since it is possible to call the getwritabledatabase () method, let's take a look:

Public synchronized sqlitedatabase getwritabledatabase () {If (mdatabase! = NULL & mdatabase. isopen ()&&! Mdatabase. isreadonly () {// If the mdatabase is not empty and is not in read-only mode, return the instance return mdatabase;} If (misinitializing) {Throw new illegalstateexception ("getwritabledatabase called recursively");} // if we have a read-only database open, someone cocould be using it // (though they shouldn't ), which wowould cause a lock to be held on // the file, and our attempts to open the database read-write wocould // fail waiting for the fil E lock. to prevent that, we acquire the // lock on the read-only database, which shuts out other users. boolean success = false; sqlitedatabase DB = NULL; // If the mdatabase is not empty, lock and block other operations if (mdatabase! = NULL) mdatabase. lock (); try {misinitializing = true; If (mname = NULL) {DB = sqlitedatabase. create (null);} else {// open or create database DB = mcontext. openorcreatedatabase (mname, 0, mfactory);} // obtain the database version (if the database is just created, the version is 0) int version = dB. getversion (); // compare the version (the mnewversion in our code is 1) if (version! = Mnewversion) {dB. begintransaction (); // start the transaction try {If (version = 0) {// execute our oncreate method oncreate (db );} else {// if our application has upgraded mnewversion to 2, and the original version is 1, The onupgrade method onupgrade (dB, version, mnewversion) will be executed;} dB. setversion (mnewversion); // you can specify the latest DB version. settransactionsuccessful (); // SET transaction success} finally {dB. endtransaction (); // end transaction} onopen (db); success = true; return dB; // return the database instance in read/write mode} finally {misinitializing = false; if (succ ESS) {// If (mdatabase! = NULL) {// If mdatabase has a value, disable try {mdatabase first. close ();} catch (exception e) {} mdatabase. unlock (); // unlock} mdatabase = dB; // value to mdatabase} else {// unlock failure: Unlock or close if (mdatabase! = NULL) mdatabase. Unlock (); If (DB! = NULL) dB. Close ();}}}

As you can see, the key steps are to first determine that mdatabase will return directly if it is not empty and is not in read-only mode. Otherwise, if mdatabase is not empty, it will be locked, start to open or create a database, compare the version, call the corresponding method based on the version number, set a new version number for the database, and finally release the old non-empty mdatabase and unlock it, assign the newly opened database instance to mdatabase and return to the latest instance.

After reading the above process, you may know a lot about it. If the disk space is not full, getreadabledatabase () will generally return the same database instance as getwritabledatabase, therefore, it is feasible to use getwritabledatabase () in the dbmanager constructor to obtain the database instance used by the entire application. Of course, if you are really worried about this situation, you can first use getwritabledatabase () to obtain the data instance. If an exception occurs, try to use getreadabledatabase () to obtain the instance, of course, the instance you get can only be read and cannot be written.

Finally, let's take a look at how to use these data operations to display data. The following is the layout file and code of mainactivity. Java:

<?xml version="1.0" encoding="utf-8"?><LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"    android:orientation="vertical"    android:layout_width="fill_parent"    android:layout_height="fill_parent"><Buttonandroid:layout_width="fill_parent"android:layout_height="wrap_content"android:text="add"android:onClick="add"/><Buttonandroid:layout_width="fill_parent"android:layout_height="wrap_content"android:text="update"android:onClick="update"/><Buttonandroid:layout_width="fill_parent"android:layout_height="wrap_content"android:text="delete"android:onClick="delete"/><Buttonandroid:layout_width="fill_parent"android:layout_height="wrap_content"android:text="query"android:onClick="query"/><Buttonandroid:layout_width="fill_parent"android:layout_height="wrap_content"android:text="queryTheCursor"android:onClick="queryTheCursor"/><ListViewandroid:id="@+id/listView"android:layout_width="fill_parent"android:layout_height="wrap_content"/></LinearLayout>

Package COM. scott. DB; import Java. util. arraylist; import Java. util. hashmap; import Java. util. list; import Java. util. map; import android. app. activity; import android. database. cursor; import android. database. cursorwrapper; import android. OS. bundle; import android. view. view; import android. widget. listview; import android. widget. simpleadapter; import android. widget. simplecursoradapter; public class mainactivity extends activity {private dbmanager Mgr; private listview; @ override public void oncreate (bundle savedinstancestate) {super. oncreate (savedinstancestate); setcontentview (R. layout. main); listview = (listview) findviewbyid (R. id. listview); // initialize dbmanager Mgr = new dbmanager (this) ;}@ override protected void ondestroy () {super. ondestroy (); // the DB Mgr. closedb ();} public void add (view) {arraylist <person> Persons = new arraylist <person> (); person person1 = new person ("Ella", 22, "lively girl"); person person2 = new person ("Jenny", 22, "Beautiful Girl"); person person3 = new person ("Jessica", 23, "Sexy girl"); person person4 = new person ("Kelly", 23, "Hot baby"); person person5 = new person ("Jane", 25, "A Pretty Woman"); persons. add (person1); persons. add (person2); persons. add (person3); persons. add (person4); persons. add (person5); Mgr. add (persons);} public void Update (view) {person = new person (); person. name = "Jane"; person. age = 30; Mgr. updateage (person);} public void Delete (view) {person = new person (); person. age = 30; Mgr. deleteoldperson (person);} public void query (view) {list <person> Persons = Mgr. query (); arraylist <Map <string, string> List = new arraylist <Map <string, string> (); For (person: Persons) {hashmap <string, string> map = new hashmap <string, string> (); map. put ("name", person. name); map. put ("info", person. age + "years old," + person.info); list. add (MAP);} simpleadapter adapter = new simpleadapter (this, list, android. r. layout. simple_list_item_2, new string [] {"name", "info"}, new int [] {android. r. id. text1, android. r. id. text2}); listview. setadapter (adapter);} public void querythecursor (view) {cursor c = Mgr. querythecursor (); startmanagingcursor (c); // entrust the activity to manage the life cycle of the cursor according to its own life cycle. cursorwrapper = new cursorwrapper (c) {@ override Public String getstring (INT columnindex) {// Add the age if (getcolumnname (columnindex) before the introduction ). equals ("info") {int age = getint (getcolumnindex ("Age"); Return age + "years old," + super. getstring (columnindex);} return Super. getstring (columnindex) ;}}; // make sure that the "_ id" column simplecursoradapter adapter = new simplecursoradapter (this, android. r. layout. simple_list_item_2, cursorwrapper, new string [] {"name", "info"}, new int [] {android. r. id. text1, android. r. id. text2}); listview = (listview) findviewbyid (R. id. listview); listview. setadapter (adapter );}}

Note the simplecursoradapter application. When using this adapter, we must first get a cursor object. There are several problems: how to manage the life cycle of cursor, if you wrap cursor, you need to pay attention to the cursor result set.

If you manage cursor manually, it will be very troublesome and risky. If you handle cursor improperly, an exception will occur during running. Fortunately, activity provides the startmanagingcursor (cursor) method, it manages the current cursor object according to the activity lifecycle. The following describes the method:

/**     * This method allows the activity to take care of managing the given     * {@link Cursor}'s lifecycle for you based on the activity's lifecycle.     * That is, when the activity is stopped it will automatically call     * {@link Cursor#deactivate} on the given Cursor, and when it is later restarted     * it will call {@link Cursor#requery} for you.  When the activity is     * destroyed, all managed Cursors will be closed automatically.     *      * @param c The Cursor to be managed.     *      * @see #managedQuery(android.net.Uri , String[], String, String[], String)     * @see #stopManagingCursor     */

As mentioned in this Article, the startmanagingcursor method manages the lifecycle of the current cursor object based on the activity lifecycle. That is to say, when the activity stops, it automatically calls the Deactivate method of cursor to disable the cursor, when the activity returns to the screen, it will call the requery method of cursor to query again. When the activity is destroyed, the managed cursor will be automatically disabled and released.

How to wrap cursor: we will use the cursorwrapper object to wrap our cursor object to implement the data conversion work we need. This cursorwrapper actually implements the cursor interface. The obtained cursor is actually a reference of cursor, and the system actually returns to us an object instance of the implementation class of the cursor interface. We use cursorwrapper to wrap this instance, then, use simplecursoradapter to display the result to the list.

What do you need to pay attention to in the cursor result set? One of the most important thing is that we must include a "_ id" column in our result set; otherwise, simplecursoradapter will face it and face it, why? This is because of the SQLite specification. The primary key is based on "_ id. Solution 3: first, create a table according to the specification; second, use an alias for the query, for example, select ID as _ id from person; third, write an article in cursorwrapper:

    CursorWrapper cursorWrapper = new CursorWrapper(c) {    @Override    public int getColumnIndexOrThrow(String columnName) throws IllegalArgumentException {    if (columnName.equals("_id")) {    return super.getColumnIndex("id");    }    return super.getColumnIndexOrThrow(columnName);    }    };

If you try to obtain the column index corresponding to "_ id" from cursorwrapper, we will return the column index corresponding to "ID" in the query result.

Finally, let's take a look at the results: