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 
{
 
}

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

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

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

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

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

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.

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);
        <b>DBAdapter db = new DBAdapter(this);</b> 
    }
}

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);
 
        <b>//---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();</b> 
    }

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

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:

<b> 
    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();        
    } 
</b>

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
    <b>public void</b> onCreate(Bundle savedInstanceState) {
        <b>super</b>.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        DBAdapter db = new DBAdapter(this);
 
        <b>//---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();</b> 
    }

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

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);
 
        <b>//---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();
    }</b>

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";
 
    <b>//---change this to a higher value---
    private static final int DATABASE_VERSION = 2;</b> 
 
    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.