Creating and Using Databases in Android

Database support is the lifeline of every application, big or small. Unless your application deals only with simple data, you need a database system to store your structured data. Android uses the SQLite database system, which is an open-source, stand-alone SQL database, widely used by many popular applications. For example, Mozilla Firefox uses SQLite to store configuration data and iPhone also uses SQLite for database storage.

In Android, the database that you create for an application is only accessible to itself; other applications will not be able to access it. Once created, the SQLite database is stored in the /data/data/<package_name>/databases folder of an Android device. In this article, you will learn how to create and use a SQLite database in Android.

SQLite Database

Create an Android project using Eclipse and name it Database (see Figure 1).

Figure 1.
Database: Your new Android project, created using Eclipse.

Creating the DBAdapter Helper Class

A good practice for dealing with databases is to create a helper class to encapsulate all the complexities of accessing the database so that it's transparent to the calling code. So, create a helper class called DBAdapter that creates, opens, closes, and uses a SQLite database.

First, add a DBAdapter.java file to the src/<package_name> folder (in this case it is src/net.learn2develop.Database).

In the DBAdapter.java file, import all the various namespaces that you will need:

package net.learn2develop.Databases;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter
  {
  }

Next, create a database named bookstitles with the fields shown in Figure 2.

Figure 2.
The Database Fields: This shows the titles table you will be building in this article.

In the DBAdapter.java file, define the following constants shown in Listing 1.

Listing 1. 

package net.learn2develop.Database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter
{
  public static final String KEY_ROWID = "_id";
  public static final String KEY_ISBN = "isbn";
  public static final String KEY_TITLE = "title";
  public static final String KEY_PUBLISHER = "publisher";
  private static final String TAG = "DBAdapter";

  private static final String DATABASE_NAME = "books";
  private static final String DATABASE_TABLE = "titles";
  private static final int DATABASE_VERSION = 1;

  private static final String DATABASE_CREATE =
     "create table titles (_id integer primary key autoincrement, "
     + "isbn text not null, title text not null, "
      + "publisher text not null);";

  private final Context context;
}

The DATABASE_CREATE constant contains the SQL statement for creating the titles table within the books database.

Within the DBAdapter class, you extend the SQLiteOpenHelper class—an Android helper class for database creation and versioning management. In particular, you override the onCreate() and onUpgrade() methods (as shown in Listing 2).

Listing 2.
In the DBAdapter class, extend the SQLiteOpenHelper class and override the onCreate() and onUpgrade() methods.

package net.learn2develop.Database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter
{
  public static final String KEY_ROWID = "_id";
  public static final String KEY_ISBN = "isbn";
  public static final String KEY_TITLE = "title";
  public static final String KEY_PUBLISHER = "publisher";
  private static final String TAG = "DBAdapter";

  private static final String DATABASE_NAME = "books";
  private static final String DATABASE_TABLE = "titles";
  private static final int DATABASE_VERSION = 1;

  private static final String DATABASE_CREATE =
    "create table titles (_id integer primary key autoincrement, "
    + "isbn text not null, title text not null, "
    + "publisher text not null);";

  private final Context context;
  private DatabaseHelper DBHelper;
  private SQLiteDatabase db;

public DBAdapter(Context ctx)
{
  this.context = ctx;
  DBHelper = new DatabaseHelper(context);
}

private static class DatabaseHelper extends SQLiteOpenHelper
{
  DatabaseHelper(Context context)
  {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }

  @Override
  public void onCreate(SQLiteDatabase db)
  {
    db.execSQL(DATABASE_CREATE);
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
  {
    Log.w(TAG, "Upgrading database from version " + oldVersion
      + " to "
      + newVersion + ", which will destroy all old data");
      db.execSQL("DROP TABLE IF EXISTS titles");
      onCreate(db);
   }
  }
}

The onCreate() method creates a new database if the required database is not present. The onUpgrade() method is called when the database needs to be upgraded. This is achieved by checking the value defined in the DATABASE_VERSION constant. For this implementation of the onUpgrade() method, you will simply drop the table and create the table again.

You can now define the various methods for opening and closing the database, as well as the methods for adding/editing/deleting rows in the table (see Listing 3).

Listing 3.
Defining the various methods for opening and closing the database, as well as the methods for adding/editing/deleting rows in the table.

public class DBAdapter
{
  //...
  //...

  //---opens the database---
  public DBAdapter open() throws SQLException
  {
    db = DBHelper.getWritableDatabase();
    return this;
  }

  //---closes the database---
  public void close()
  {
    DBHelper.close();
  }

  //---insert a title into the database---
  public long insertTitle(String isbn, String title, String publisher)
  {
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_ISBN, isbn);
    initialValues.put(KEY_TITLE, title);
    initialValues.put(KEY_PUBLISHER, publisher);
    return db.insert(DATABASE_TABLE, null, initialValues);
  }

  //---deletes a particular title---
  public boolean deleteTitle(long rowId)
  {
    return db.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
  }

  //---retrieves all the titles---
  public Cursor getAllTitles()
  {
    return db.query(DATABASE_TABLE, new String[] {
    KEY_ROWID,
    KEY_ISBN,
    KEY_TITLE,
    KEY_PUBLISHER},
    null,
    null,
    null,
    null,
    null);
  }

  //---retrieves a particular title---
  public Cursor getTitle(long rowId) throws SQLException
  {
    Cursor mCursor =
    db.query(true, DATABASE_TABLE, new String[] {
    KEY_ROWID,
    KEY_ISBN,
    KEY_TITLE,
    KEY_PUBLISHER
    },
    KEY_ROWID + "=" + rowId,
    null,
    null,
    null,
    null,
    null);
    if (mCursor != null) {
       mCursor.moveToFirst();
       }
    return mCursor;
    }

  //---updates a title---
  public boolean updateTitle(long rowId, String isbn, String title,

  String publisher)
  {
    ContentValues args = new ContentValues();
    args.put(KEY_ISBN, isbn);
    args.put(KEY_TITLE, title);
    args.put(KEY_PUBLISHER, publisher);
    return db.update(DATABASE_TABLE, args,
    KEY_ROWID + "=" + rowId, null) > 0;
    }
 }

Notice that Android uses the Cursor class as a return value for queries. Think of the Cursor as a pointer to the result set from a database query. Using Cursor allows Android to more efficiently manage rows and columns as and when needed. You use a ContentValues object to store key/value pairs. Its put() method allows you to insert keys with values of different data types.

The full source listing of DBAdapter.java is shown in Listing 4.

Listing 4.
The full source listing of DBAdapter.java class.

package net.learn2develop.Database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter
{
  public static final String KEY_ROWID = "_id";
  public static final String KEY_ISBN = "isbn";
  public static final String KEY_TITLE = "title";
  public static final String KEY_PUBLISHER = "publisher";
  private static final String TAG = "DBAdapter";

  private static final String DATABASE_NAME = "books";
  private static final String DATABASE_TABLE = "titles";
  private static final int DATABASE_VERSION = 1;

  private static final String DATABASE_CREATE =
    "create table titles (_id integer primary key autoincrement, "
    + "isbn text not null, title text not null, "
    + "publisher text not null);";

  private final Context context;

  private DatabaseHelper DBHelper;
  private SQLiteDatabase db;

  public DBAdapter(Context ctx)
  {
    this.context = ctx;
    DBHelper = new DatabaseHelper(context);
  }

  private static class DatabaseHelper extends SQLiteOpenHelper
  {
    DatabaseHelper(Context context)
    {
       super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db)
    {
       db.execSQL(DATABASE_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion,
    int newVersion)
    {
       Log.w(TAG, "Upgrading database from version " + oldVersion
       + " to "
       + newVersion + ", which will destroy all old data");
       db.execSQL("DROP TABLE IF EXISTS titles");
       onCreate(db);
    }
  }

  //---opens the database---
  public DBAdapter open() throws SQLException
  {
    db = DBHelper.getWritableDatabase();
    return this;
  }

  //---closes the database---
  public void close()
  {
    DBHelper.close();
  }

  //---insert a title into the database---
  public long insertTitle(String isbn, String title, String publisher)
  {
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_ISBN, isbn);
    initialValues.put(KEY_TITLE, title);
    initialValues.put(KEY_PUBLISHER, publisher);
    return db.insert(DATABASE_TABLE, null, initialValues);
  }

  //---deletes a particular title---
  public boolean deleteTitle(long rowId)
  {
    return db.delete(DATABASE_TABLE, KEY_ROWID +
    "=" + rowId, null) > 0;
  }

  //---retrieves all the titles---
  public Cursor getAllTitles()
  {
    return db.query(DATABASE_TABLE, new String[] {
    KEY_ROWID,
    KEY_ISBN,
    KEY_TITLE,
    KEY_PUBLISHER},
    null,
    null,
    null,
    null,
    null);
  }

  //---retrieves a particular title---
  public Cursor getTitle(long rowId) throws SQLException
  {
    Cursor mCursor =
    db.query(true, DATABASE_TABLE, new String[] {
    KEY_ROWID,
    KEY_ISBN,
    KEY_TITLE,
    KEY_PUBLISHER
    },
    KEY_ROWID + "=" + rowId,
    null,
    null,
    null,
    null,
    null);
    if (mCursor != null) {
       mCursor.moveToFirst();
    }
    return mCursor;
  }

  //---updates a title---
  public boolean updateTitle(long rowId, String isbn,
  String title, String publisher)
  {
    ContentValues args = new ContentValues();
    args.put(KEY_ISBN, isbn);
    args.put(KEY_TITLE, title);
    args.put(KEY_PUBLISHER, publisher);
    return db.update(DATABASE_TABLE, args,
    KEY_ROWID + "=" + rowId, null) > 0;
  }
}

Using the Database

You are now ready to use the database along with the helper class you've created. In the DatabaseActivity.java file, create an instance of the DBAdapter class:

package net.learn2develop.Database;

import android.app.Activity;
import android.os.Bundle;

public class DatabaseActivity extends Activity {
  /** Called when the activity is first created. */
  @Override
  public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
    DBAdapter db = new DBAdapter(this);
  }
}

Adding a Title
To add a title into the titles table, use the insertTitle() method of the DBAdapter class:

@Override
public void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.main);

  DBAdapter db = new DBAdapter(this);

  //---add 2 titles---
  db.open();
  long id;
  id = db.insertTitle( "0470285818", "C# 2008 Programmer's Reference", "Wrox");
  id = db.insertTitle( "047017661X", "Professional Windows Vista

       Gadgets Programming", "Wrox");
  db.close();
}

The insertTitle() method returns the ID of the inserted row. If an error occurs during the adding, it returns -1.

If you examine the file system of the Android device/emulator, you can observe that the books database is created under the databases folder (see Figure 3).

Figure 3.
The Database Folder: The books database is created in the databases folder.

Retrieving All the Titles

To retrieve all the titles in the titles table, use the DBAdapter class' getAllTitles() method (see Listing 5).

Listing 5.
Retrieving all the titles in the titles table with the DBAdapter class' getAllTitles() method:

package net.learn2develop.Database;

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.widget.Toast;

public class DatabaseActivity extends Activity {
  /** Called when the activity is first created. */
  @Override
  public void onCreate(Bundle savedInstanceState)
  {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
    DBAdapter db = new DBAdapter(this);
    //---get all titles---
    db.open();
    Cursor c = db.getAllTitles();
    if (c.moveToFirst())
  {
  do {
       DisplayTitle(c);
     } while (c.moveToNext());
  }
  db.close();
}
}

The result is returned as a Cursor object. To display all the titles, you first need to call the Cursor object's moveToFirst() method. If it succeeds (which means there is at least one row available), display the details of the title using the DisplayTitle() method (defined below). To move to the next title, call the Cursor object's moveToNext() method:

public void DisplayTitle(Cursor c)
{
   Toast.makeText(this,
   "id: " + c.getString(0) + "\n" +
   "ISBN: " + c.getString(1) + "\n" +
   "TITLE: " + c.getString(2) + "\n" +
   "PUBLISHER: " + c.getString(3),
   Toast.LENGTH_LONG).show();
}

Figure 4 shows the Toast class displaying one of the titles retrieved from the database.

Figure 4.
The Toast Class: The device displays each individual title using the Toast class.

Retrieving a Single Title

To retrieve a single title using its ID, call the getTitle() method of the DBAdapter class with the ID of the title:

@Override
public void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.main);
  DBAdapter db = new DBAdapter(this);

  //---get a title---
  db.open();
  Cursor c = db.getTitle(2);
  if (c.moveToFirst())
  DisplayTitle(c);
  else
  Toast.makeText(this, "No title found",
  Toast.LENGTH_LONG).show();
  db.close();
}

The result is returned as a Cursor object. If a row is returned, display the details of the title using the DisplayTitle() method, else display an error message using the Toast class.
Updating a Title

To update a particular title, call DBAdapter's updateTitle() method by passing the ID of the title you want to update as well as the values of the new fields (Listing 6).

Listing 6.

Update a particular title by calling the DBAdapter class' updateTitle() method and passing the ID of the title you want to update as well as the values of the new fields.

@Override
public void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.main);
  DBAdapter db = new DBAdapter(this);

  //---update title---
  db.open();
  if (db.updateTitle(1, "0470285818", "C# 2008 Programmer's Reference",

  "Wrox Press"))
  Toast.makeText(this, "Update successful.",
  Toast.LENGTH_LONG).show();
  else
  Toast.makeText(this, "Update failed.",
  Toast.LENGTH_LONG).show();
  //-------------------
  //---retrieve the same title to verify---
  Cursor c = db.getTitle(1);
  if (c.moveToFirst())
  DisplayTitle(c);
  else
  Toast.makeText(this, "No title found",
  Toast.LENGTH_LONG).show();
  //-------------------
  db.close();
}

A message is displayed to indicate if the update is successful. At the same time, you retrieve the title that you have just updated to verify that the update is indeed correct.

Deleting a Title

To delete a title, use the deleteTitle() method in the DBAdapter class by passing the ID of the title you want to delete:

@Override
public void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.main);
  DBAdapter db = new DBAdapter(this);

  //---delete a title---
  db.open();
  if (db.deleteTitle(1))
  Toast.makeText(this, "Delete successful.",
  Toast.LENGTH_LONG).show();
  else
  Toast.makeText(this, "Delete failed.",
  Toast.LENGTH_LONG).show();
  db.close();
}

A message is displayed to indicate if the deletion is successful.

Upgrading the Database

To upgrade the database, change the DATABASE_VERSION constant in the DBAdapter class to a value higher than the previous one. For example, if its previous value was 1, change it to 2:

Figure 5.
The LogCat Window: The message shows that the database has been upgraded.

public class DBAdapter
{
  public static final String KEY_ROWID = "_id";
  public static final String KEY_ISBN = "isbn";
  public static final String KEY_TITLE = "title";
  public static final String KEY_PUBLISHER = "publisher";
  private static final String TAG = "DBAdapter";

  private static final String DATABASE_NAME = "books";
  private static final String DATABASE_TABLE = "titles";

  //---change this to a higher value---
  private static final int DATABASE_VERSION = 2;

  private static final String DATABASE_CREATE =
    "create table titles (_id integer primary key autoincrement, "
    + "isbn text not null, title text not null, "
    + "publisher text not null);";

When you run the application one more time, you will see the message in Eclipse's LogCat window (see Figure 5) indicating that the database has been upgraded.

Easy Database Access

Using the DBAdapter class pattern defined in this article, you can easily access database records for your own Android applications. One important thing to note is that all SQLite databases created in Android are visible only to the application that created it. If you need to share common data, you need to use a content provider, a topic that will be covered in a future article.