Tuesday, 30 August 2011


1. SQLite in Android

SQLite is an Open Source Database which is embedded into Android. SQLight supports standard relational database features like SQL syntax, transactions and prepared statements. In addition it requires only little memory at runtime (approx. 250 KByte).

Using SQLite in Android does not require any database setup or administration. You specify the SQL for working with the database and the database is automatically managed for you.

Working with databases in Android can be slow due to the necessary I/O. Therefore is it recommended to perform this task in an AsyncTask . Please see Android Background Processing Tutorial for details.

SQLite supports the data types TEXT (similar to String in Java), INTEGER (similar to long in Java) and REAL (similar to double in Java). All other types must be converted into on of these fields before saving them in the database. SQLight itself does not validate if the types written to the columns are actually of the defined type, you can write an integer into a string column.

If your application creates an database this database is saved in the directory "DATA/data/APP_NAME/databases/FILENAME". "DATA" is the path which Environment.getDataDirectory() returns, "APP_NAME" is your application name and "FILENAME" is the name you give the database during creation. Environment.getDataDirectory() usually return the SD card as location.

A SQlite database is private to the application which creates it. If you want to share data with other applications you can use a Content Provider.

2. SQLiteOpenHelper

To create and upgrade a database in your Android application you usually subclass "SQLiteOpenHelper". In this class you need to override the methods onCreate() to create the database and onUpgrade() to upgrade the database in case of changes in the database schema. Both methods receive an "SQLiteDatabase" object.

SQLiteOpenHelper provides the methods getReadableDatabase() and getWriteableDatabase() to get access to an "SQLiteDatabase" object which allows database access either in read or write mode.

For the primary key of the database you should always use the identifier "_id" as some of Android functions rely on this standard.

3. SQLiteDatabase and Cursor

"SQLiteDatabase" provides the methods insert(), update() and delete() and execSQL() method which allows to execute directly SQL. The object "ContentValues" allow to define key/values for insert and update. The key is the column and the value is the value for this column.

Queries can be created via the method rawQuery() which accepts SQL or query() which provides an interface for specifying dynamic data or SQLiteQueryBuilder. SQLiteBuilder is similar to the interface of an content provider therefore it is typically used in ContentProviders. A query returns always a "Cursor".

The method query has the parameters String dbName, int[] columnNames, String whereClause, String[] valuesForWhereClause, String[] groupBy, String[] having, String[] orderBy. If all data should be selected you can pass "null" as the where clause. The where clause is specified without "where", for example "_id=19 and summary=?". If several values are required via ? you pass them in the valuesForWhereClause array to the query. In general if something is not required you can pass "null", e.g. for the group by clause.

A Cursor represents the result of a query. To get the number of elements use the method getCount(). To move between individual data rows, you can use the methods moveToFirst() and moveToNext(). Via the method isAfterLast() you can check if there is still some data.

No comments:

Post a Comment