Creating Your Application's SQLite Database

The Task Reminder application needs a place to store and retrieve the user's tasks, and the best place for this kind of information is inside an SQLite database. Your application needs to read, create, update, and delete tasks from the database.

Understanding how the SQLite database works

The two activities in the Task Reminder application need to perform various duties on the database to operate. ReminderEditActivity needs to do the following:

• Create a new record.
• Read a record so that it can display the details for editing.
• Update the existing record.

The ReminderListActivity needs to perform these duties:

• Read all the tasks to display them on the screen.
• Delete a task by responding to the click event from the context menu after a user has long-pressed an item.

To work with an SQLite database, you must communicate with SQLite through classes in the android.database package. Because common practice is to abstract as much of the database communication away from the Activity objects as possible, the database mechanisms are placed into another Java file (and usually a package if the database portion is quite large) to help separate the application into layers of functionality. Therefore, if you ever need to alter code that affects the database, you know that you need to change the code in only one location. You follow this approach in the sections that follow.

Creating a Java file to hold the database code

Your first task is to create a Java file in your Android project that will house all the database-centric code. I named my file RemindersDbAdapter.java — I chose this name because this is a simple implementation of the adapter software engineering pattern.

The adapter pattern is simply a wrapper class that allows incompatible classes to communicate with each other. Think of the adapter pattern as the wires and ports behind your television and DVD player. The cables plug into ports that are essentially adapters that allow devices to communicate that normally couldn't. They share a common interface. By creating an adapter to handle the database communication, you can program with Java. At run time, the adapter class does the translation and adapts certain Java requests into SQLite-specific commands.

Defining the key elements

Before you open and create your database, I need to define a few key fields. Type the code from Listing 14-1 into your RemindersDbAdapter class.

Listing 14-1: The Constants, Fields, and Constructors of the RemindersDbAdapter Class

private static final String DATABASE_NAME = “data”;              →1
private static final String DATABASE_TABLE = “reminders”;        →2
private static final int DATABASE_VERSION = 1;                   →3

public static final String KEY_TITLE = “title”;                  →5
public static final String KEY_BODY = “body”;
public static final String KEY_DATE_TIME = “reminder_date_time”;
public static final String KEY_ROWID = “_id”;                    →8


private DatabaseHelper mDbHelper;                               →11
private SQLiteDatabase mDb;                                     →12

private static final String DATABASE_CREATE =                   →14
        “create table “ + DATABASE_TABLE + ” (”
                      + KEY_ROWID + “ integer primary key autoincrement, ”
                      + KEY_TITLE + “ text not null, ”
                      + KEY_BODY + “ text not null, ”
                      + KEY_DATE_TIME + “ text not null);”;

private final Context mCtx;                                     →21

public RemindersDbAdapter(Context ctx) {                        →23
        this.mCtx = ctx;
    }

Each line is explained in detail here:

The SQL database is now ready to be created with the DATABASE_CREATE script as defined previously.

Visualizing the SQL table

The table object in SQL is the construct that holds the data that you decide to manage. Visualize tables in SQLite as similar to spreadsheets. Each row consists of data, and each column represents the data inside the row. In Listing 14-1 on lines 5–8, you defined the column names for the database. These column names equate to the header values in a spreadsheet, as shown in Figure 14-1. Each row contains a value for each column, which is how your data is stored in SQLite (conceptually, anyway — the real data is stored as 1s and 0s).

Figure 14-1: Visualizing data in the Task Reminder application.

Starting on line 14, you assemble the database create script. This script concatenates various constants from within the file to create a database create script. When you run this script in SQLite, SQLite creates a table by the name of reminders in a database called data. The columns and how they're built in the create script are described as follows:

• create table DATABASE_TABLE: This portion of the script informs SQLite that you want to create a database table with the name of reminders.
• ROW_ID: This property acts as the identifier for the task. This column has the integer primary key autoincrement attributes applied to it. The integer attribute specifies that the row is an integer. The primary key attribute states that the ROW_ID is the primary identifier for a task. The autoincrement attribute informs SQLite that each time a new task is inserted, simply automatically increment the ROW_ID to the next available integer. For example, if rows 1, 2, and 3 existed and you inserted another record, the value of the ROW_ID column in the next row would be 4.
• KEY_TITLE: This column is the title of the task that the user provides, such as “Schedule Vacation.” The text attribute informs SQLite that you are working with a text column. The not null attribute states that the value of this column cannot be null — you must provide a value.
• KEY_BODY: This portion is the body or description of the task. The attributes for this column are the same as for KEY_TITLE.
• KEY_DATE_TIME: This column is where the date and time of the reminder are stored. The attributes are the same as the prior two columns. Wait! You're probably thinking, “This is a date field; why is he storing it as text?” This is because SQLite does not have a storage class associated with storing dates or times.

Creating the database table

You're now ready to create your first table. To do so, you provide an implementation of SQLiteOpenHelper. In the RemindersDbAdapter class, type the code as shown in Listing 14-2. This listing creates a nested Java class inside the RemindersDbAdapter class. You can type this at the end of the class.

Listing 14-2: Creating Your First Database Table

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

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

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion,
                    int newVersion) {                               →12
            // Not used, but you could upgrade the database with ALTER
           // Scripts
        }
}

The code lines are explained here:

You now create the database by calling the getReadableDatabase() or getWritableDatabase() method on the DatabaseHelper object. To do this, type the following code anywhere into your RemindersDbAdapter class:

public RemindersDbAdapter open() throws android.database.SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
}

The open() method opens (and creates if necessary) the database using the DatabaseHelper() class that was just created. This class then returns itself through the this Java keyword. The reason that the class is returning itself is because the caller (ReminderEditActivity or ReminderListActivity) needs to access data from this class and this method returns an instance of the RemindersDbAdapter.

Closing the database

A database is an expensive resource and should be closed when not in use. To close the database, type the following method anywhere into your RemindersDbAdapter class:

public void close() {
      mDbHelper.close();
}

This method closes the database when called. You call this from within the ReminderEditActivity when the user cancels the activity by using the Back button on the device.