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.
The Create, Read, Update, and Delete actions are known as CRUD operations — each letter standing for its respective action.
The two activities in the Task Reminder application need to perform various duties on the database to operate. ReminderEditActivity needs to do the following:
The ReminderListActivity needs to perform these duties:
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.
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.
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.
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.
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).
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:
For more information on dates and times in SQLite, view the documentation at www.sqlite.org/datatype3.html#datetime.
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.
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:
→1 | This line is the implementation of your SQLiteOpenHelper. |
→3 | The call made to the base SQLiteOpenHelper constructor. This call creates, opens, or manages a database. The database is not actually created or opened until getReadableDatabase() or getWriteableDatabase() is called on the SQLiteOpenHelper instance — in this case, it would be the mDbHelper variable. |
→7 | The onCreate() method, which is called when the database is created for the first time. |
→8 | This line is where all the magic happens — it creates your database and your database table. The execSQL() method accepts an SQL script string as a parameter. This is the SQL that the SQLite database executes to create the database table. |
→12 | The onUpgrade() method is used when you need to upgrade an existing database. |
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.
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.
Upgrading your database
When would you upgrade your database? Consider the following situation: You have released your application and 10,000 users have installed it and are actively using it. Not only are they using it, but they also love it! Some users are even sending in feature requests. You decide that you want to implement one of these feature requests, but to do so, you must change the database schema. To update your database, you must perform SQL ALTER statements inside the onUpgrade() call. Some developers upgrade their databases by dropping the existing database and then creating a new one. However, don't do this — it deletes all the user's data! Imagine updating your favorite Task Reminder application and then finding out that the upgrade erased all your preexisting tasks! That would be a major bug.
3.16.217.187