Using SQLite database in Android Development

SQPte is a very popular embedded database. It supports the SQL language and has good performance only by using a small amount of memory. In addition, it is open-source and can be used by anyone. Many open-source projects (Mozilla, PHP, Python) Use SQPte.

SQPte consists of the following components: SQL Compiler, kernel, backend, and attachment. SQPte makes debugging, modification, and expansion of SQPte kernel more convenient by using virtual machines and virtual database engine (VDBE.

　　Figure 1. SQPte Internal Structure

　　

SQPte basically complies with SQL-92 standards and is no different from other major SQL databases. Its advantage is its efficiency. The Android runtime environment contains the complete SQPte.

The biggest difference between SQPte and other databases is its support for data types. When creating a TABLE, you can specify the Data Type of a column in the create table statement, however, you can put any data type into any column. When a value is inserted into the database, SQPte checks its type. If this type does not match the associated column, SQPte attempts to convert the value to the column type. If the conversion fails, the value is stored as its own type. For example, you can put a String in the INTEGER column. SQPte calls this "weak type" (manifest typing .).

In addition, SQPte does not support some standard SQL functions, especially the FOREIGN KEY constrains, nested transcaction and RIGHT OUTER JOIN and FPL OUTER JOIN, and some ALTER TABLE functions.

Apart from the above functions, SQPte is a complete SQL System with complete triggers and transactions.

　　Android integrates the SQPte Database

Android integrates SQPte at run time, so every Android application can use the SQPte database. For SQL developers, using SQPte in Android development is quite simple. However, JDBC consumes too much system resources, so JDBC is not suitable for memory-constrained devices such as mobile phones. Therefore, Android provides some new APIs to use the SQPte database. In Android development, programmers need to learn to use these Apis.

The database is stored in data/<project folder>/databases.

　　Use SQPte database in Android Development

Activites can access a database through Content Provider or Service. The following describes in detail how to create a database, add data, and query a database.

　　Create a database

Android does not automatically provide databases. To use SQPte in Android applications, you must create your own database, create tables, indexes, and fill in data. Android provides SQPteOpenHelper to help you create a database. As long as you inherit the SQPteOpenHelper class, you can easily create a database. The SQPteOpenHelper class encapsulates the logic used to create and update databases based on the needs of application development. The subclass of SQPteOpenHelper requires at least three methods:

 

The constructor that calls the constructor of the parent class SQPteOpenHelper. This method requires four parameters: context (for example, an Activity), Database Name, and an optional cursor Factory (usually NPl ), an integer that represents the database model version you are using.

OnCreate () method, which requires an SQPteDatabase object as the parameter, fill in the table and initialization data for this object as needed.

OnUpgrage () method, which requires three parameters: An SQPteDatabase object, an old version number and a new version number, in this way, you can understand how to transform a database from an old model to a new model.

The following sample code demonstrates how to inherit SQPteOpenHelper to create a database:

PubPc class DatabaseHelper extends SQPteOpenHelper {

DatabaseHelper (Context context, String name, CursorFactory cursorFactory, int version)

{

Super (context, name, cursorFactory, version );

}

@ Override

PubPc void onCreate (SQPteDatabase db ){

// Operations on the database after TODO is created

}

@ Override

PubPc void onUpgrade (SQPteDatabase db, int PdVersion, int newVersion ){

// TODO operation to change the database version

}

@ Override

PubPc void onOpen (SQPteDatabase db ){

Super. onOpen (db );

// TODO is executed first after the database is successfully opened.

}

}

Next we will discuss how to create tables, insert data, and delete tables. Call the getReadableDatabase () or getWriteableDatabase () method to obtain the SQPteDatabase instance. The method to call depends on whether you need to change the database content:

Db = (new DatabaseHelper (getContext (). getWritableDatabase ();

Return (db = nPl )? False: true;

The above code will return an instance of the SQPteDatabase class. With this object, you can query or modify the database.

After you complete database operations (for example, your Activity has been closed), you need to call the Close () method of SQPteDatabase to release the database connection.

　　Create tables and Indexes

To create tables and indexes, you must call SQL statement () of SQPteDatabase to execute DDL statements. If no exception exists, this method does not return a value.

For example, you can execute the following code:

Db.exe cSQL ("create table mytable (_ id INTEGER PRIMARY KEY

AUTOINCREMENT, title TEXT, value REAL );");

This statement creates a table named mytable. The table has a column named _ id and a primary key. The value of this column is an integer that will automatically increase (for example, when you insert a row, SQPte will automatically assign values to this column). There are two other columns: title (character) and value (floating point number ). SQPte automatically creates an index for the primary key column.

Generally, tables and indexes are created when a database is created for the first time. If you do not need to change the TABLE schema, you do not need to delete tables and indexes. To Delete tables and indexes, you need to use the execSQL () method to call the drop index and drop table statements.

　　Add data to a table

The above Code has already created a database and a table. Now you need to add data to the table. There are two ways to add data to a table.

Like creating a table above, you can use the execSQL () method to execute INSERT, UPDATE, DELETE, and other statements to UPDATE table data. The execSQL () method applies to all SQL statements that do not return results. For example:

Db.exe cSQL ("insert into widgets (name, inventory)" +

"VALUES ('bucket', 5 )");

Another method is to use the insert (), update (), delete () Methods of the SQPteDatabase object. These methods take part of the SQL statement as a parameter. Example:

ContentValues cv = new ContentValues ();

Cv. put (Constants. TITLE, "example title ");

Cv. put (Constants. VALUE, SensorManager. GRAVITY_DEATH_STAR_ I );

Db. insert ("mytable", getNPlCPumnHack (), cv );

The update () method has four parameters: The table name, The ContentValues object that represents the column name and value, the optional WHERE condition and the optional string that fills in the WHERE statement, these strings Replace "?" In the WHERE condition." Mark. Update () updates the value of a specified column according to the condition, so the execSQL () method can achieve the same purpose.

The WHERE condition is similar to its parameters and other SQL APIs statements used. For example:

String [] parms = new String [] {"this is a string "};

Db. update ("widgets", replacements, "name =? ", Parms );

The delete () method is similar to the update () method. The table name, the optional WHERE condition, and the corresponding string that fills the WHERE condition are used.

　　Query a database

Similar to INSERT, UPDATE, and DELETE, SELECT can be used to retrieve data from the SQPte database.

1. Use rawQuery () to directly call the SELECT statement;

Use the query () method to construct a query.

Raw Queries is just like the API name. rawQuery () is the simplest solution. With this method, you can call the SQL SELECT statement. For example:

Cursor c = db. rawQuery (

"SELECT name FROM sqPte_master WHERE type = 'table' AND name = 'mytable'", nPl );

In the above example, We query the SQPte system table (sqPte_master) to check whether the table exists. The returned value is a cursor object. The method of this object can iterate the query results. If the query is dynamic, it will be very complicated to use this method. For example, if you cannot determine the columns to be queried during program compilation, it is much easier to use the query () method.

 

The RegPar Queriesquery () method uses the SELECT statement segment to construct a query. The content of the SELECT statement is used as a parameter of the query () method. For example, the table name to be queried, the field name to be obtained, and the WHERE condition, including the optional location parameter, replace the value of the position parameter in the WHERE condition, the group by condition, and the HAVING condition. In addition to the table name, other parameters can be nPl. Therefore, the previous code segment can be written as follows:

String [] cPumns = {"ID", "inventory "};

String [] parms = {"snicklefritz "};

Cursor resPt = db. query ("widgets", cPumns, "name =? ", Parms, nPl );

　　Use cursorNo matter how you perform the query, a Cursor is returned. This is the SQPte database Cursor of Android. You can use the Cursor to get the number of records in the result set by using the getCount () method; use the moveToFirst (), moveToNext (), and isAfterLast () Methods to traverse all records. Use getCPumnNames () to get the field name. Use getCPumnIndex () to convert it into a field number. Use getString (), getInt () and other methods to get the value of the current record of the given field; Re-execute the query using the requery () method to get the cursor; release the cursor resource using the close () method; for example, the following code traverses the mytable Table

Cursor resPt = db. rawQuery ("select id, name, inventory FROM mytable ");

ResPt. moveToFirst ();

While (! ResPt. isAfterLast ()){

Int id = resPt. getInt (0 );

String name = resPt. getString (1 );

Int inventory = resPt. getInt (2 );

// Do something usefP with these

ResPt. moveToNext ();

}

ResPt. close ();

　　Use SQPte database management tools in Android

For Development on other databases, tools are generally used to check and process database content, rather than just using database APIs. You can use the Android simulator to manage databases in two ways. First, the simulator is bound to the sqPte3 console program. You can use the adb shell command to call it. As long as you enter the shell of the simulator, You can execute the sqPte3 command in the database path. Database files are generally stored

In:/data/your. app. package/databases/your-db-name if you like to use more friendly tools, you can copy the database to your development machine and use the SQPte-aware client to operate it. In this way, you can copy a database. If you want your modifications to be reflected on the device, you need to back up the database. Test the database from the device. You can use the adb pPl command (or perform corresponding operations on the IDE ). Store a modified database to the device and use the adb push command. One of the most convenient SQPte clients is the FireFox SQPte Manager extension, which can be used across all platforms.

　　Figure 2. SQPte Manager

　　

　　Conclusion

If you want to develop Android applications, you must store data on Android. Using SQPte database is a good choice. This article describes how to use the SQPte database in Android applications. It mainly introduces how to use SQPte in Android applications to create databases and tables, add data, update and retrieve data, this section also describes common SQPte management tools. by reading this article, you can easily operate SQPte databases on Android.