Shulou(Shulou.com)06/02 Report--

This article shows you the internal structure of Android SQLite and how to use SQLite. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

SQLite introduction

SQLite is a very popular embedded database, which supports the SQL language and has good performance with very little memory. Because JDBC is not suitable for memory-constrained devices such as mobile phones, Android developers need to learn new API to use SQLite. In addition, it is open source, and anyone can use it. Many open source projects (Mozilla, PHP, Python) use SQLite.

SQLite consists of the following components: SQL compiler, kernel, backend, and attachments. SQLite makes it easier to debug, modify, and extend the kernel of SQLite through the use of virtual machines and virtual database engine (VDBE).

Figure 1. SQLite internal architecture

SQLite basically conforms to the SQL-92 standard and is no different from other major SQL databases. Its advantage is that it is efficient, and the Android runtime environment contains a complete SQLite.

The difference between SQLite and other databases is the support for data types. When creating a table, you can specify the data type of a column in the CREATE TABLE statement, but you can put any data type in any column. When a value is inserted into the database, SQLite checks its type. If the type does not match the associated column, SQLite attempts to convert the value to the type of the column. If it cannot be converted, the value is stored as its own type. For example, you can put a string (String) in the INTEGER column. SQLite calls this "weak type" (manifest typing.).

In addition, SQLite does not support some standard SQL features, especially 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, transactions, and so on.

Android integrates SQLite database

Android integrates with SQLite at run-time, so every Android application can use the SQLite database. For developers familiar with SQL, using SQLite in Android development is fairly simple. However, because JDBC consumes too many system resources, JDBC is not suitable for memory-constrained devices like mobile phones. As a result, Android provides some new API to use SQLite databases, and programmers need to learn to use these API in Android development.

The database is stored in data/

/ databases/.

Using SQLite database in Android development

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

Create a database

Android does not automatically provide the database. To use SQLite in an Android application, you must create your own database, then create tables, indexes, and populate the 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 according to the needs of developing applications. A subclass of SQLiteOpenHelper, you need to implement at least three methods:

Constructor that calls the constructor of the parent class SQLiteOpenHelper. This method requires four parameters: the context (for example, an Activity), the database name, an optional cursor factory (usually Null), and an integer representing the version of the database model you are using.

The onCreate () method, which requires a SQLiteDatabase object as a parameter, populates the table and initializes data on this object as needed.

The onUpgrage () method, which requires three parameters, a SQLiteDatabase object, an old version number, and a new version number, so you can figure out how to transform a database from the old model to the new model.

The following sample code shows how to inherit 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) {/ / TODO after creating the database Operation on the database} @ Override public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {/ / TODO operation to change the database version} @ Override public void onOpen (SQLiteDatabase db) {super.onOpen (db) / / TODO is executed first after each successful opening of the database}}

Next, we will discuss how to create tables, insert data, delete tables, and so on. Call the getReadableDatabase () or getWriteableDatabase () method, and you can get a SQLiteDatabase instance, depending on whether you need to change the contents of the database:

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

The above code returns an instance of the SQLiteDatabase class, which you can query or modify the database.

When you have finished working on the database (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 need to call the execSQL () method of SQLiteDatabase to execute the DDL statement. If there is no exception, this method does not return a value.

For example, you can execute the following code:

Db.execSQL ("CREATE TABLE mytable (_ id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, value REAL);")

This statement creates a table called mytable, which has a column named _ id and is the primary key. The value of this column is an automatically growing integer (for example, SQLite automatically assigns a value to this column when you insert a row), and there are two other columns: title (character) and value (floating point). SQLite automatically creates an index for the primary key column.

Typically, tables and indexes are created when the database is created for the first time. If you do not need to change the schema of the table, you do not need to delete the table and index. To delete tables and indexes, you need to call the DROP INDEX and DROP TABLE statements using the execSQL () method.

Add data to a table

The above code, which has created the database and table, now needs to add data to the table. There are two ways to add data to a table.

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

Db.execSQL ("INSERT INTO widgets (name, inventory)" + VALUES ('Sprocket', 5) ")

Another method is to use the insert (), update (), delete () methods of the SQLiteDatabase object. These methods take part of the SQL statement as parameters. Examples are as follows:

ContentValues cv=new ContentValues (); cv.put (Constants.TITLE, "example title"); cv.put (Constants.VALUE, SensorManager.GRAVITY_DEATH_STAR_I); db.insert ("mytable", getNullColumnHack (), cv)

The update () method takes four parameters, namely the table name, the ContentValues object representing the column name and value, the optional WHERE condition, and the optional string that populates the WHERE statement, which replaces the "?" in the WHERE condition. Mark. Update () updates the value of the specified column based on the condition, so you can do the same with the execSQL () method.

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

String [] parms=new String [] {"this is a string"}; db.update ("widgets", replacements, "name=?", parms)

The use of the delete () method is similar to update (), using the table name, optional WHERE condition, and the corresponding string that populates the WHERE condition.

Query database

Similar to INSERT, UPDATE, and DELETE, there are two ways to retrieve data from an SQLite database using SELECT.

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

Use the query () method to build a query.

Raw Queries

Like the API name, rawQuery () is the easiest 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 see if the table table exists. The return value is a cursor object whose methods can iterate over the query results.

If the query is dynamic, using this method can be very complex. For example, it is much more convenient to use the query () method when you need to query columns that are not sure when the program is compiled.

Regular Queries

The query () method builds the query with the SELECT statement segment. The contents of the SELECT statement are used as parameters of the query () method, such as the name of the table to be queried, the field name to be obtained, the WHERE condition, including optional position parameters, to replace the value of the position parameter in the WHERE condition, the GROUP BY condition, and the HAVING condition.

Except for the table name, the other parameter can be null. Therefore, the previous code snippet can be written as:

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

Use cursors

No matter how you execute the query, you will return a Cursor, which is Android's SQLite database cursor. With cursors, you can:

Get how many records are in the result set by using the getCount () method

Traverse all records through the moveToFirst (), moveToNext (), and isAfterLast () methods

Get the field name through getColumnNames ()

Convert to a field number by getColumnIndex ()

Get the current record value of a given field by getString (), getInt () and other methods

The cursor is obtained by re-executing the query by the requery () method

Release cursor resources through the close () method

For example, the following code iterates through 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 ()

Using SQLite database management tools in Android

When developing on other databases, tools are generally used to check and process the contents of the database, rather than just using the API of the database. Using the Android simulator, there are two alternative ways to manage the database.

First, the simulator binds the sqlite3 console program, which can be called using the adb shell command. As long as you enter the shell of the simulator, execute the sqlite3 command in the path to the database. Database files are generally stored in:

/ data/data/your.app.package/databases/your-db-name

If you prefer to use more friendly tools, you can copy the database to your development machine and use the SQLite-aware client to manipulate it. In this way, you operate on a copy of the database, and if you want your changes to be reflected on the device, you need to back up the database.

To test the database off the device, you can use the adb pull command (or do it on IDE). Store a modified database on the device, using the adb push command.

One of the most convenient SQLite clients is the FireFox SQLite Manager extension, which is available across all platforms.

Figure 2. SQLite Manager

If you want to develop Android applications, you must store data on Android, and using SQLite databases is a very good choice.

The above is the internal structure of Android SQLite and how to use SQLite. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.