SQLite in Android

Posted: July 9, 2011 in Android

1.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.

1.2. SQLightOpenHelper

To create and upgrade a database in your Android application you usually subclass “SQLightOpenHelper”. 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 “SQLightDatabase” object.

“SQLightDatabase” provides the execSQL() method which allows to execute SQL.

It also provides the methods getReadableDatabase() and getWriteableDatabase() to get access to the database either in read or write mode. The database is represented by an “SQLightDatabase” object.

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

1.3. SQLightDatabase and Cursor

“SQLightDatabase” provides the methods insert(), update() and delete().

Queries can be created via the method rawQuery() which accepts SQL, query() which provides an interface for specifying dynamic data or SQLightQueryBuilder. SQLightBuilder is similar to the interface of an content provider therefore it is typically used for them. A query returns always a “Cursor”.

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.

A Cursor can be directly used via the “SimpleCursorAdapter” in ListViews. Please see the ListView Tutorial for details.






Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s