Wednesday, 21 September 2011

SQLite database on Android

Note: It is not the purpose of this post to discuss about how to utilize SQL sentence or how to design a database. If you need such information, you might check some book as the one of Grant Allen and Mike Owens.

1. Introduction
There are two ways to save permanently data in Android: Shared preferences and Database.

The first one is indicated for small information such as variables or preferences in the settings, which could be independent between them. The second one, the database, is utilized to save big amount of data that the program in Android could need to record and obtain information even when it has been destroyed and restarted. This post is dedicated to the use of SQLite Database in Android, which is the default one.

SQLite Database is an ACID-compilant embedded relational database management system contained in a relatively small C programing library[1]. It is developed by D. Richard Hipp and released on August 2000. The actual stable version is 3.7.8, released on 19th of September 2011.

The SQLite version utilized in Android vary depends on the version of Android and on the different phones. For example, HTC Hero with Android 2.1 utilize the version 3.5.9, and the Nexus One with Android 2.2 could utilize the version 3.6.22. Here you can see the version of the SQLite utilized in you phone:

Since different the base version is 3, there won't be too much difference between different versions of SQLite.

One important characteristics that SQLite has, which is different regarding the "normal" SQL databases, is it is weakly typed. This means that in the column of type Integer, it could contains a variable of type String. This is because the typing is assigned to individual values, instead of a column. See here for more distintive features:

2. Packages utilized
Android contains its own packages which could be imported depending of the use of the SQLite database.
  • android.database.Cursor
All the query done with the database returns the the cursor Object, which is essential to communicate with database.
  • android.database.SQLException
The exception of the database. This is launched by the android program when something goes wrong.
  • android.database.sqlite.SQLiteDatabase
Contains methods to manage the SQLite database.
  • android.database.sqlite.SQLiteDatabase.CursorFactory
Part of the previous package/class. It is an interface used to extends the class SQLiteOpenHelper. (See below)
  • android.database.sqlite.SQLiteException
The exceptions of the SQLite database
  • android.database.sqlite.SQLiteOpenHelper
It is an helper class utilized for the database creation and version management.

3. SQLite database implementation
3.1 Data types
The default type of data that SQLite can storage are:
  • Null:
  • Integer
  • Real
  • Text
  • Blob: Array of binary data (bytes)
Notice that the type Boolean is not included in this list. This is because boolean is stored as integer (0 = false, 1 = true) in the database.

For the date and the time, SQLite doesn't give them directly support, instead, they can be stored as Text, Real or Integer.

Here you can find more information about date:
Android Cookbook:

My recommendation is if it will be handler by the computer, the best way is utilize the integer (time passed since "The epoch" in linux, because it is much more easier to save and retrieve.

3.2 Extend the SQLiteOpenHelper
The easier way to create a SQLite database is by extending the class SQLiteOpenHelper and implements the methods onCreate (SQLiteDatabase), onUpgrade (SQliteDatabase, int, int) and optionally onOpen(SQliteDatabase).

private static class myDBOpenHelper extends SQLiteOpenHelper {}

3.2.1 Constructor
The constructor of the class has four parameters:
  • Context context: The context in which the android program is running
  • String name: The name of the database. Normally it ends with ".db", which is the extension for a database
  • CursorFactory factory: The cursor factory for the SQLite database. This is useful when the user want implement specific methods and make the database returns a extended class of Cursor instead of the default Cursor class. For simple one, the value is null.
  • int version: The version of the actual database. If it is a new one, it could starts with 1.
It should give those parameters to the super class:

super(context, name, factory, version);

Additionally it could have other parameters, but it is depends on the database design.

3.2.2 onCreate (SQLiteDatabase _db)
This method is called when it detects that the database doesn't exists and it need to create a new one.
To create a new database, the method execSQL should be used. (Check the difference between the method execSQL and the method query below). In this case, the SQL sentence should include the keyword "CREATE TABLE", the fields and any additional information. As recommendation, it should has a field of identification which increases the value with each row.

Here is an example of the sentence:
private static final String DATABASE_CREATE = "CREATE TABLE " +
    todolist + "( _ id integer primary key autoincrement, " + 
                  "  task text not null, " + 
                  "  creation_date long );"

In this case, it creates a table called "todolist", which has a id which is auto incremented when a new field is inserted; a task, which is a text (String) and it cannot be null; and a creation_date, which is the type long.
For more information about the syntax to create a table, see the official manual:

3.2.3 onUpgrade (SQLiteDatabase _db, int _oldVersion, int _newVersion)
This method is called when a new version of the database has been detected. The easiest thing to do is drop all the tables of your previous version and create new tables. Be aware that in this case, you are going to lose all your information.

The code is:

_db.execSQL("DROP TABLE IF EXISTS " + Table_name);

Fore more information about the syntax to drop a table, see the official manual:

3.2.4 onOpen (SQLiteDatabase _db)
Optionally, the default action when a new database is opened could be set. Normally it is unnecessary. The recommendation is check the property isReadOnly() before.

3.3 Create the basic SQLite database adapter
Once the class SQLiteOpenHelper has been extended and customized, in the same java file, several public methods could be used as database adapter.

3.3.1 Constructor
The class constructor could be used to create the SQLiteOpenHelper, which has the variable context as the parameter.

3.3.2 Open() throws SQLException
This method is commonly used to access to the database. If the database hasn't been created, it could be created with this method also.

The common code for this method is:

private SQLiteDatabase db;
private myDBOpenHelper dbHelper;
public void open() throws SQLException {
    try {
        db = dbHelper.getWritableDatabase();
    } catch (SQLiteException ex) {
        db = dbHelper.getReadableDatabase();
Notices that the dbHelper should be created as new MyOpenHelper in the class constructor.

3.3.3 close()
Method used to close an database. The code is:

public void close() {

3.4 Consult and manipulate datas in the database
Until this point, several methods have been showed to create, open and close the SQLite database in Android. But the main point for the database is the possibility to consult and manipulate datas in the database. All the follow methods depends on the variable of the type SQLiteDatabase obtained before with the method open.

3.4.1 Inserting new data in the database
The class of SQLiteDatabase provides three different methods to insert a values into the database:
  • long insert (String table, String nullColumnHack, ContentValues values)
This is the simplest and most used method. It insert a series of values into a table and returns the row ID of the newly inserted row, or -1 if an error occurred.

The different parameters are:
    • String table: the name of the table
    • String nullColumnHack: It is optional. Normally it is set as null.
    • ContentValues values: The set of values that are inserted.
The nullColumnHack is a variable that indicates one of the columns of the table. It is used when the set of values to insert (ContentValues) are all null. In this case, the SQL sentence doesn't allow the insertion of the values all null without indicate any column.
Source: StackOverFlow

The ContentValues is a set of key-values where store the data to insert. It allows to create an empty constructor and then put the values with the method put.

For example:

ContentValues myContentValues = new ContentValues();
myContentValues.put("column2", "values2");
myContentValues.put("column3", "values3");

Notices that when the id (primary key) of the table has been set as autoincrement, there is not need to specify it.

For more information about the content values, check the Android's official manual:
  • long insertOrThrow (String table, String nullColumnHack, ContentValues values)
This method acts exactly as the previous method, with the same parameters. The difference is when something goes wrong, this methods returns -1 and throw a SQLException.
  • long insertWithOnConflict (String table, String nullColumnHack, ContentValues initialValues, int conflictAlgorithm)
This method is indicated specially when a conflict could occur, because it indicates the algorithm to solve it. The error for insertion could appears when two rows have exactly the same value (with the same id). In this case, the way to solve this conflict could be:
For more information about conflicts, some posts in StackOverFlow could help:

3.4.2 Delete the content of a raw
  • int delete (String table, String whereClause, String[] whereArgs)
This method removes a raw from the table. if null is set in whereClause, all the rows of the table will be removed.

It returns the number of rows removed.

An example of use of this method is:

db.delete("myTable", "id=? AND name=? AND surname=?", new String[] {Id.toString(), name, surname});

The symbol "?" in the whereClause will be replaced by each one of the strings in the whereArgs.

To remove all the rows of the table and left it empty,  a special input should be used.

In this case, whereClause, the special character "1" should be used.
For example, to remove all the rows of "myTable":

db.delete("myTable", "1", null);

Notices this way is different if we remove the whole table. After the execution of the above code, the table remains, but the content is empty. If we remove the whole the table, both the table and the content won't exist

3.4.3 Replacing the content of a raw
  • long replace (String table, String nullColumnHack, ContentValues initialValues)
In SQLite (Not Java), the sentence REPLACE is an alias for "INSERT OR REPLCE", which is an variant of INSERT command. I do not recommend the use of this method for beginners because the sentence REPLACE is a bit dangerous. If you need to update an raw, use the method update explained below. For more information about INSERT OR REPLACE, see StackOverFlow.
  • long replaceOrThrow (String table, String nullColumnHack, ContentValues initialValues)
The same method of above, but throw a SQLException when an error occurs.

3.4.4 Updating the content of a raw
  • int update (String table, ContentValues values, String whereClause, String[] whereArgs)
This is the method used to update one or more column in one or more rows in a specific table. The variables values, whereClause and whereArgs works exactly like the methods insert and delete explained above.

It returns the number of rows affected.

For example:
ContentValues updateItem = new ContentValues();
updateItem.put("item_name", "item1");
db.update("items", updateItem, "id=?", new String[] {Long.toString(itemId)});

This example updates column "item_name" in the raw with the id equal to "itemId" in the table "items" by "item1".
  • int updateWithOnConflict (String table, ContentValues values, String whereClause, String[] whereArgs, int conflictAlgorithm)
This method works like the normal update method. The unique difference is when a conflict appears, it set the action for the conflict. The list of the actions available are exactly the same as for the method insertWithOnConflict.

3.4.5 Make a query
Note: There are several methods to make a query. For simplicity, the author only has list two of them.

For all the queries, an expected information is expected to be returned. For Android, this information is the type Cursor. See information about Cursor in this post.
  • Cursor query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)
This is a simple query method. It contains several parameters that are explained below:
    • String table: The name of the table
    • String[] columns: The list of columns. If it is null, all the columns will be returned.
    • String selection: It works as the sentence SELECTION in SQL. Usually it includes the symbols "?", which are replaced by the selectionArgs. If it is set as null, all rows of an table will be returned
    • String[] selectionArgs: The list of the arguments that will replace the symbols "?" in selection
    • String groupBy: It utilizes the sentence GROUP BY of SQL which allow group the raws. If it is set as null, the raws won't be grouped. Normally it is set as null.
    • String having: It utilizes the sentence HAVING of SQL which evaluate all the rows with this expression. Normally it is set as null.
    • String orderBy: Specify how the result rows should be ordered. It utilizes the sentence ORDER BY of SQL. Normally it is set as Null
    • String limit: limited the number of rows returned by the query. Normally it is set as Null.
Several parameters matches with the options of the sentence SELECT in SQL. For more information about them, check:
  • Cursor rawQuery (String sql, String[] selectionArgs)
The rawQuery method execute a SQL sentence (SELECT) with its arguments and save the raws in the Cursor variable.

For example:
db.rawQuery("SELECT _id, name from table_1", null); 

Notice that the SQL sentence doesn't end with the symbol ";". This is an important point to remember.

3.4.6 Executing a SQL sentence which not returns data
  • void execSQL (String sql)
This method executes any single SQL sentence that is not SELECT, INSERT, UPDATE, DELETE, REPLACE or any other sentence which returns data. For example, CREATE TABLE to create a new table.

3.5 Other methods
Beside those basic methods, there are other methods that could be interesting. Here a list of them

3.5.1 Maximum size
  • long getMaximumSize()
  • long setMaximumSize(long numBytes)
Although the SQLite database can grow as much as the free space available, sometimes it could be needed to set a maximum size for it. The first method return the maximum size and the the second method limits the maximum size by number of Bytes.

3.5.2 Path
  • final String getPath()
Obtain the path to the database. Normally it is on /data/data/package_name/database

3.5.3 Version
  • int getVersion()
  • void setVersion(int version)
Set and get the actual version of the database.

3.5.4 Database's properties
  • boolean isDatabaseIntegrityOk()
Although the robustness of the SQLite database, sometimes the integrity of it could be corrupted. For example, when two rows have the same value (even for the primary key) or when some values that shouldn't be null contains null. This method checks the integrity of the database and all the attached databases.
  • boolean isReadOnly()
Check the permission of the database returned. For some reasons, no all the database returned when it is opened is writable.

4. Conclusion
Beside all the limitations that SQLite database has, it is the perfect database for Android because it is lightweight and it doesn't require any configuration. Although the use of the class SQLiteOpenHelper is optional, it is highly recommended for beginner because it make the management of the database much easier.

I hope with this small manual the use of the SQLite database is clear for the beginner. In the blog of Lars Vogel you can find a good example to play.

For more information
Android developers official guide:
Vogelia - Android SQLite Database:

Recommended book:
The definitive guide to SQLite - Grant Allen & Mike Owens:

[1] Wikipedia - SQLite:

No comments:

Post a Comment