Android provides full relational database capabilities through the SQLite library, without imposing any additional limitations. Using SQLite, you can create independent, relational databases for each application. Use them to store

and manage complex, structured application data. All Android databases are stored in the /data/data/<package_name>/databases folder on your device (or emulator). By default, all databases are private, accessible only by the application that created them. To share a database across applications, use Content Providers, as shown later in this chapter.

Database design is a vast topic that deserves more thorough coverage than is possible within this book. However, it’s worth highlighting that standard database best practices still apply. In particular, when creating databases for resource-constrained devices, it’s important to reduce data redundancy using normalization.

In this post, we focus on the practicalities of creating and managing SQLite databases in Android.

Introducing SQLite

SQLite is a relational database management system (RDBMS). It is well regarded, being:

  • Open source
  • Standards-compliant
  • Lightweight
  • Single-tier

It has been implemented as a compact C library that’s included as part of the Android software stack. By providing functionality through a library, rather than as a separate process, each database becomes an integrated part of the application that created it. This reduces external dependencies, minimizes latency, and simplifies transaction locking and synchronization.

SQLite has a reputation of being extremely reliable and is the database system of choice for many consumer electronic devices, including several MP3 players, the iPhone, and the iPod Touch.

Lightweight and powerful, SQLite differs from many conventional database engines by using a loosely typed approach to column definitions. Rather than requiring column values to conform to a single type, the values in each row for each column are individually typed. As a result, there’s no strict type checking when assigning or extracting values from each column within a row.

For more comprehensive coverage of SQLite, including its particular strengths and limitations, check out the official site at

Cursors and Content Values

ContentValues objects are used to insert new rows into database tables (and Content Providers). Each Content Values object represents a single row, as a map of column names to values. Queries in Android are returned as Cursor objects. Rather than extracting and returning a copy of the result values, Cursors act as pointers to a subset of the underlying data. Cursors are a managed way of controlling your position (row) in the result set of a database query.

The Cursor class includes several functions to navigate query results including, but not limited to, the following:

  • moveToFirst: Moves the cursor to the first row in the query result.
  • moveToNext: Moves the cursor to the next row.
  • moveToPrevious: Moves the cursor to the previous row.
  • getCount: Returns the number of rows in the result set.
  • getColumnIndexOrThrow: Returns an index for the column with the specified name (throwing an exception if no column exists with that name).
  • getColumnName: Returns the name of the specified column index.
  • getColumnNames: Returns a String array of all the column names in the current cursor.
  • moveToPosition: Moves the cursor to the specified row.
  • getPosition: Returns the current cursor position.

Android provides a mechanism to manage Cursor resources within your Activities. The startManagingCursor method integrates the Cursor’s lifetime into the parent Activity’s lifetime management. When you’ve fi nished with the Cursor, call stopManagingCursor to do just that.

Later in this post, we’ll learn how to query a database and how to extract specific row/column values from the resulting Cursor objects.

Working with Android Databases

It’s good practice to create a helper class to simplify your database interactions. Consider creating a database adapter, which adds an abstraction layer that encapsulates database interactions. It should provide intuitive, strongly typed methods for adding, removing, and updating items.

A database adapter should also handle queries and wrap creating, opening, and closing the database. It’s often also used as a convenient location from which to publish static database constants, including table names, column names, and column indexes.

The following snippet shows the skeleton code for a standard database adapter class. It includes an extension of the SQLiteOpenHelper class, used to simplify opening, creating, and upgrading the database.

import android.content.Context;
import android.database.*;
import android.database.sqlite.*;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;
public class MyDBAdapter {
private static final String DATABASE_NAME = "myDatabase.db";
private static final String DATABASE_TABLE = "mainTable";
private static final int DATABASE_VERSION = 1;
// The index (key) column name for use in where clauses.
public static final String KEY_ID="_id";
// The name and column index of each column in your database.
public static final String KEY_NAME="name";
public static final int NAME_COLUMN = 1;
// TODO: Create public field for each column in your table.
// SQL Statement to create a new database.
private static final String DATABASE_CREATE = "create table " +
" integer primary key autoincrement, " +
KEY_NAME + " text not null);";
// Variable to hold the database instance
private SQLiteDatabase db;
// Context of the application using the database.
private final Context context;
// Database open/upgrade helper
private myDbHelper dbHelper;
public MyDBAdapter(Context _context) {
context = _context;
dbHelper = new myDbHelper(context, DATABASE_NAME, null,DATABASE_VERSION);
public MyDBAdapter open() throws SQLException {
db = dbHelper.getWritableDatabase();
return this;
public void close() {
public long insertEntry(MyObject _myObject) {
ContentValues contentValues = new ContentValues();
// TODO fill in ContentValues to represent the new row
return db.insert(DATABASE_TABLE, null, contentValues);
public boolean removeEntry(long _rowIndex) {
return db.delete(DATABASE_TABLE, KEY_ID +
"=" + _rowIndex, null) > 0;
public Cursor getAllEntries () {
return db.query(DATABASE_TABLE, new String[] {KEY_ID, KEY_NAME},
null, null, null, null, null);
public MyObject getEntry(long _rowIndex) {
MyObject objectInstance = new MyObject();
// TODO Return a cursor to a row from the database and
// use the values to populate an instance of MyObject
return objectInstance;
public int updateEntry(long _rowIndex, MyObject _myObject) {
String where = KEY_ID + "=" + _rowIndex;
ContentValues contentValues = new ContentValues();
// TODO fill in the ContentValue based on the new object
return db.update(DATABASE_TABLE, contentValues, where, null);
private static class myDbHelper extends SQLiteOpenHelper {
public myDbHelper(Context context, String name,
CursorFactory factory, int version) {
super(context, name, factory, version);
// Called when no database exists in
// disk and the helper class needs
// to create a new one.
public void onCreate(SQLiteDatabase _db) {
// Called when there is a database version mismatch meaning that
// the version of the database on disk needs to be upgraded to
// the current version.
public void onUpgrade(SQLiteDatabase _db, int _oldVersion,
int _newVersion) {
// Log the version upgrade.
Log.w("TaskDBAdapter", "Upgrading from version " +
_oldVersion + " to " +
_newVersion +
", which will destroy all old data");
// Upgrade the existing database to conform to the new version.
// Multiple previous versions can be handled by comparing
// _oldVersion and _newVersion values.
// The simplest case is to drop the old table and create a
// new one.
// Create a new one.

Using the SQLiteOpenHelper

SQLiteOpenHelper is an abstract class that wraps up the best practice pattern for creating, opening, and upgrading databases. By implementing and using an SQLiteOpenHelper, you hide the logic used to decide if a database needs to be created or upgraded before it’s opened.

The code snippet above shows how to extend the SQLiteOpenHelper class by overriding the constructor, onCreate, and onUpgrade methods to handle the creation of a new database and upgrading to a new version, respectively.

In the previous example, onUpgrade simply drops the existing table and replaces it with the new definition. In practice, a better solution is to migrate existing data into the new table.

To use an implementation of the helper class, create a new instance, passing in the context, database name, current version, and a CursorFactory (if you’re using one).

Call getReadableDatabase or getWriteableDatabase to open and return a readable/writable instance of the database.

A call to getWriteableDatabase can fail because of disk space or permission issues, so it’s good practice to provide fallback to the getReadableDatabase method as shown below:

dbHelper = new myDbHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
SQLiteDatabase db;
try {
db = dbHelper.getWritableDatabase();
catch (SQLiteException ex){
db = dbHelper.getReadableDatabase();

Behind the scenes, if the database doesn’t exist, the helper executes its onCreate handler. If the database version has changed, the onUpgrade handler will fire. In both cases, the get<read/write>ableDatabase call will return the existing, newly created, or upgraded database as appropriate.

Opening and Creating Databases without the SQLiteHelper

You can create and open databases without using the SQLiteHelper class with the openOrCreateDatabase method on the application Context.

Setting up a database is a two-step process. First, call openOrCreateDatabase to create the new database. Then, call execSQL on the resulting database instance to run the SQL commands that will create your tables and their relationships. The general process is shown in the snippet below:

private static final String DATABASE_NAME = “myDatabase.db”;
private static final String DATABASE_TABLE = “mainTable”;
private static final String DATABASE_CREATE =
“create table “ + DATABASE_TABLE +
“ ( _id integer primary key autoincrement,” +
“column_one text not null);”;
SQLiteDatabase myDatabase;
private void createDatabase() {
myDatabase = openOrCreateDatabase(DATABASE_NAME,
Context.MODE_PRIVATE, null);

Querying Your Database

All database queries are returned as a Cursor to a result set. This lets Android manage resources more efficiently by retrieving and releasing row and column values on demand.

To execute a query on a database, use the query method on the database object, passing in:

  • An optional Boolean that specifies if the result set should contain only unique values
  • The name of the table to query
  • A projection, as an array of Strings, that lists the columns to include in the result set
  • A “where” clause that defines the rows to be returned. You can include ? wildcards that will be replaced by the values stored in the selection argument parameter.
  • An array of selection argument strings that will replace the ?’s in the “where” clause
  • A “group by” clause that defines how the resulting rows will be grouped
  • A “having” filter that defines which row groups to include if you specified a “group by” clause
  • A String that describes the order of the returned rows
  • An optional String that defines a limit to the returned rows

The following skeleton code shows snippets for returning some, and all, of the rows in a particular table:

// Return all rows for columns one and three, no duplicates
String[] result_columns = new String[] {KEY_ID, KEY_COL1, KEY_COL3};
Cursor allRows = myDatabase.query(true, DATABASE_TABLE, result_columns,
null, null, null, null, null, null);
// Return all columns for rows where column 3 equals a set value
// and the rows are ordered by column 5.
String where = KEY_COL3 + “=” + requiredValue;
String order = KEY_COL5;
Cursor myResult = myDatabase.query(DATABASE_TABLE, null, where,
null, null, null, order);

Extracting Results from a Cursor

To extract actual values from a result Cursor, first use the moveTo<location> methods described previously to position the Cursor at the correct row of the result set.

With the Cursor at the desired row, use the type-safe get methods (passing in a column index) to return the value stored at the current row for the specified column, as shown in the following snippet:

String columnValue = myResult.getString(columnIndex);

The following example shows how to iterate over a result cursor, extracting and summing a column of floats:

Cursor myGold = myDatabase.query(“GoldHoards”, null, null, null, null,
null, null);
float totalHoard = 0f;
// Make sure there is at least one row.
if (myGold.moveToFirst()) {
// Iterate over each cursor.
do {
float hoard = myGold.getFloat(GOLD_HOARDED_COLUMN);
totalHoard += hoard;
} while(myGold.moveToNext());
float averageHoard = totalHoard / myGold.getCount();

Because SQLite database columns are loosely typed, you can cast individual values into valid types as required. For example, values stored as floats can be read back as Strings

Adding, Updating, and Removing Rows

The SQLiteDatabase class exposes specialized insert, delete, and update methods to encapsulate the SQL statements required to perform these actions. Nonetheless, the execSQL method lets you execute any valid SQL on your database tables should you want to execute these operations manually.

Any time you modify the underlying database values, you should call refreshQuery on any Cursors that currently have a view on the table.

Inserting New Rows

To create a new row, construct a ContentValues object, and use its put methods to supply values for each column. Insert the new row by passing the Content Values object into the insert method called on the target database object — along with the table name — as shown in the snippet below:

// Create a new row of values to insert.
ContentValues newValues = new ContentValues();
// Assign values for each row.
newValues.put(COLUMN_NAME, newValue);
[ ... Repeat for each column ... ]
// Insert the row into your table
myDatabase.insert(DATABASE_TABLE, null, newValues);

Updating a Row on the Database

Updating rows is also done using Content Values. Create a new ContentValues object, using the put methods to assign new values to each column you want to update. Call update on the database object, passing in the table name, the updated Content Values object, and a where statement that returns the row(s) to update.

The update process is demonstrated in the snippet below:

// Define the updated row content.
ContentValues updatedValues = new ContentValues();
// Assign values for each row.
updatedValues.put(COLUMN_NAME, newValue);
[ ... Repeat for each column ... ]
String where = KEY_ID + “=” + rowId;
// Update the row with the specified index with the new values.
myDatabase.update(DATABASE_TABLE, updatedValues, where, null);

Deleting Rows

To delete a row, simply call delete on your database object, specifying the table name and a where clause that returns the rows you want to delete, as shown in the code below:

myDatabase.delete(DATABASE_TABLE, KEY_ID + “=” + rowId, null);
Tagged with: Android

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!

Related News Feeds

Set your Twitter account name in your settings to use the TwitterBar Section.