Introduction to Android SQLite Database


1.SQLite and Android

a) What is SQLite ?
SQLite is an Open Source Database which is embedded into Android. SQLite supports standard relational database features like SQL syntax, transactions and prepared statements.

In addition it requires only little memory at runtime (approx. 250 KByte).

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 one of these fields before saving them in the database. SQLite 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.

b) SQLite in Android
SQLite is available on every Android device. Using an SQLite database 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 can be slow. Therefore is it recommended to perform these task in the background, for example via an AsyncTask.

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.

c) Content Provider and sharing data
A SQLite database is private to the application which creates it. If you want to share data with other applications you can use a ContentProvider. If data is not shared it typically easier to work directly with the database.

2. Android Architecture

a) Packages
The package android.database contains all general classes for working with databases. android.database.sqlite contains the SQLite specific classes.

b) 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 which represents the database.

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 tables you should always use the identifier _id as some of Android functions rely on this standard.

A best practice is to create per table a separate class which define static onCreate() and onUpdate() methods. These methods are then called in the corresponding methods of SQLiteOpenHelper . This way your implementation of SQLiteOpenHelper will not get to large even if you have several tables.

c) SQLiteDatabase
SQLiteDatabase is the base class for working with an SQLite database in Android and provides methods to open, query, update and close the database. More specifically SQLiteDatabase provides the insert(), update() and delete() methods. The execSQL() method 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.
For example to run a rawQuery() you can do the following:
[sourcecode language=”java”]
Cursor cursor = getReadableDatabase().rawQuery(“select * from todo where _id = ?”, new String[] { id });

The method query() has the following parameters.

    • String dbName – The table name to compile the query against


    • int[] columnNames – A list of which columns to return. Passing null will return all columns.


    • String whereClause – Filter for the selection of data without the “WHERE” clause, null will select all


    • selectionArgs You may include ?’s in the whereClause, which will be replaced by the values from selectionArgs.


    • String[] groupBy – A filter declaring how to group rows, null will cause the rows to not be grouped.


    • String[] having – Filter for the groups, null means no filter


    • String[] orderBy – row which will be used to order the data, null means no ordering


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.

For example to run a query() you can do the following:
[sourcecode language=”java”]
return database.query(DATABASE_TABLE,
null, null, null, null, null);

d) Cursor
A query returns always a Cursor. A Cursor represents the result of a query and basically points always to one row of the database. This way Android can buffer the results efficiently as it does not have to load all data into memory.

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.

To access data Cursor provides typed get methods, e.g. getLong(columnIndex), getString(columnIndex) whereby the columnIndex is the number of the column you are accessing.

A Cursor can be directly used via the SimpleCursorAdapter in ListViews

3. Command line interface for SQLite

It is possible to access an SQLite database on the emulator or a rooted device via the command line. For this use adb shell to connect to the device and the command “sqlite3” to connect to an database.