Android data storage (7) SQLite usage notes and SQL statements

1. Data Type Problems

SQLite only supports the five data types: NULL, integer, real (floating point number), text (text), and blob (large binary object). However, SQLite can fully accept varchar (n), char (N), decimal (P, S) and other data types, but SQLite will convert them to the corresponding types of the above five data types during the original acid or storage.

In addition, SQLite also has a feature: it allows various types of data to be stored in any type of field, developers do not have to worry about declaring the data type used by this field. For example, a program can store string-type values in integer fields or Boolean fields .... However, in one case, fields defined as integer primary key can only store 64-bit integers. SQLite produces errors when saving data of other types except Integers to such fields.

In addition, strings of any length can be inserted into the string field, even if varchar (20) is declared to be limited.

2. Database shutdown issues

We use sqliteopenhelper to obtain a sqlitedatabase object, and then we can use this database. So when will this database be shut down. In general, we do not need to operate the database once, so we close it because it consumes a lot of resources. After all, this is not like a J2EE database. databases on the J2EE server generate many database connections based on the accessed users, so they need to be closed. However, on Android phones, only our current application has another database connection. Of course, you do not need to close it once.

Therefore, we only need to complete all database operations, or select an appropriate actual shutdown when exiting the program.

3. Obtain the sqlitedatabase object.

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 an exception throw new illegalstateexception ("getreadabledatabase called recursively");} // start to instantiate the database mdatabase try {// 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 wowould // fail waiting f Or the file 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 {misinitializ Ing = false; If (SUCCESS) {// 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.

In addition, only one sqlitedatabase instance is returned for the same database.

4. Database Operation statements and methods in Android

The SQL statements in SQLite are very similar to those in MySQL. You can refer to the SQL statements in MySQL.