Using SQLite database in Android Development

SQLite Introduction

SQLite is a very popular embedded database. It supports the SQL language and has good performance only with 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 SQLite.

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

Figure 1. SQLite Internal Structure



 

SQLite basically conforms to SQL-92 standards, and other major SQL database is no difference. Its advantage is its efficiency. The Android runtime environment contains the complete SQLite.

The biggest difference between SQLite 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, SQLite checks its type. If this type does not match the associated column, SQLite will try 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. SQLite calls this "weak type" (manifest typing .).

In addition, SQLite does not support some standard SQL functions, especially the FOREIGN KEY constraints (FOREIGN KEY constrains), nested transcaction and RIGHT OUTER JOIN and FULL OUTER JOIN, and some ALTER TABLE functions.

In addition to the above functions, SQLite is a complete SQL System with complete triggers and transactions.

Android integrates SQLite Database

Android integrates SQLite at runtime, so every Android application can use the SQLite database. For developers familiar with SQL, using SQLite 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 SQLite database. In Android development, programmers need to learn to use these Apis.

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

Using SQLite 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 SQLite in Android applications, you must create your own database, create tables, indexes, and fill in data. Android provides SQLiteOpenHelper to help you create a database. As long as you inherit the SQLiteOpenHelper class, you can easily create a database. The SQLiteOpenHelper class encapsulates the logic used to create and update databases based on the needs of application development. SQLiteOpenHelper subclasses must implement at least three methods:

The constructor that calls the constructor of the parent class SQLiteOpenHelper. This method requires four parameters: context (for example, an Activity), Database Name, and an optional cursor Factory (usually Null ), an integer that represents the database model version you are using.
OnCreate () method, which requires a SQLiteDatabase object as the parameter, fill in the table and initialization data for this object as needed.
OnUpgrage () method, which requires three parameters: A SQLiteDatabase 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 from SQLiteOpenHelper to create a database:

Public class DatabaseHelper extends SQLiteOpenHelper {DatabaseHelper (Context context, String name, CursorFactory cursorFactory, int version) {super (context, name, cursorFactory, version );} @ Override public void onCreate (SQLiteDatabase db) {// operations on the database after TODO is created} @ Override public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {// operation on changing the database version of TODO} @ Override public void onOpen (SQLiteDatabase 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 SQLiteDatabase instance. The method to call depends on whether you need to change the database content:

Db = (new DatabaseHelper (getContext (). getWritableDatabase (); return (db = null )? False: true;
 

 

The above code will return an instance of the SQLiteDatabase 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 SQLiteDatabase to release the database connection.

Create tables and Indexes

To create tables and indexes, you must call the execSQL () method of SQLiteDatabase 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, SQLite will automatically assign values to the column). There are also two columns: title (character) and value (floating point number ). SQLite 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 SQLiteDatabase object. These methods take part of the SQL statement as a parameter. Example:

Cv. put (Constants. TITLE, "example title"); cv. put (Constants. VALUE, SensorManager. GRAVITY_DEATH_STAR_ I); db. insert ("mytable", getNullColumnHack (), 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 SQLite database.

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

Use the query () method to construct a query.

Raw Queries
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 sqlite_master WHERE type = 'table' AND name = 'mytable'", null );
 

In the above example, We query the SQLite system table (sqlite_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.

Regular Queries
The query () 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 null. Therefore, the previous code segment can be written as follows:

String [] columns = {"ID", "inventory"}; String [] parms = {"snicklefritz"}; Cursor result = db. query ("widgets", columns, "name =? ", Parms, null );
 

Use cursor

No matter how you perform the query, a Cursor is returned. This is the SQLite database Cursor of Android. You can use the Cursor:

Use the getCount () method to obtain the number of records in the result set;

Use the moveToFirst (), moveToNext (), and isAfterLast () Methods to traverse all records;

Get the field name through getColumnNames;

Use getColumnIndex () to convert it into a field number;

Use getString (), getInt (), and other methods to obtain the value of the current record of the given field;

Query the cursor through requery () method;

Use the close () method to release cursor resources;

For example, the following code traverses the mytable table

Cursor result = db. rawQuery ("select id, name, inventory FROM mytable"); result. moveToFirst (); while (! Result. isAfterLast () {int id = result. getInt (0); String name = result. getString (1); int inventory = result. getInt (2); // do something useful with these result. moveToNext ();} result. close ();
Use SQLite 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 sqlite3 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 sqlite3 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 SQLite-aware client to operate on 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 pull 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 SQLite clients is the FireFox SQLite Manager extension, which can be used across all platforms.

Figure 2. SQLite Manager

 

 

Conclusion

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

 

From chenghai2011