Basic Structure of the Micro JobsDatabase Class

In our example, the file completely encapsulates all of the SQL logic necessary to work with the database. All of the other Java classes in the MicroJobs application work with standard Java classes or Cursors and are unaware of how the data is actually stored. This is good programming practice and should be emulated in all of your Android applications that use databases.

Before we delve too deeply into the guts of creating a database and selecting data from it, it's important to understand the general layout of the MicroJobsDatabase class.

MicroJobsDatabase inherits from the abstract SQLiteOpenHelper class, and therefore must override the onCreate and onUpgrade methods. The onCreate method is automatically called when the application starts for the first time; its job is to create the database.

As newer versions of the application are shipped, the database on the phone tends to be updated, a task that falls to the onUpgrade method. When you ship a new version of a database, you must also increment the version number, as we'll explain.

The general elements in MicroJobsDatabase code are:


The MicroJobsDatabase class defines two important constants: DATABASE_NAME

This holds the filename of the database, "MicroJobs" in this case.

* . Here is the full path to the MicroJobs file: /data/data/

com.microjobsinc.mjandroid/databases/MicroJobs. You can ' v t v use the adb pull command line on your desktop (see the discussion of adb in "The Tools" on page 57) to pull the database from the emulator or developer device and then debug it using the SQLite3 executable on the desktop.


This defines the database version understood by the software that defines the constant. If the version of the database on the machine is less than DATABASE_VERSION, the application should run onUpgrade to upgrade the database to the current level. Constructor

The constructor for the database in this program, MicroJobsDatabase, uses the super function to call its parent's constructor. The parent does most of the work of creating the database object. One thing our MicroJobsDatabase constructor has to do is store the Context object. This step is not required in applications whose database code is encapsulated within an enclosing content provider class, because the ContentProvider class has a getContext call that will provide the Context object when necessary. Since MicroJobs is a standalone database class, it has to keep the Context object around in its own private variable. In the case of MicroJobs, the Context object is really the Activity object that opens the database. An Activity is a Context. The Context object is the interface to application-global resources and classes as well as application-level operations, such as broadcasting Intents and launching activities.


When an Android application attempts to read or write data to a database that does not exist, the framework executes the onCreate method. The onCreate method in the MicroJobsDatabase class shows one way to create the database. Because so much SQL code is required to create the database and populate it with sample data, we've chosen to segregate all of the SQL code invoked by onCreate into the strings.xml resource file; this makes the Java code much more readable but forces the developer to look in two separate files to see what's really going on. When we look at the custom Cursor classes later in this chapter, we'll see that SQL can be embedded into the application source code as well. It's really a matter of style. To actually create the database, the first line of the onCreate method loads the SQL string referenced by the MicroJobsDatabase_onCreate resource identifier into a String array named sql. Note the following code snippets from

mContext.getString(R.string.MicroJobsDatabase_onCreate).split("\n"); and from strings.xml:

<string name="MicroJobsDatabase_onCreate">"

CREATE TABLE jobs (_id INTEGER PRIMARY KEY AUTOINCREMENT, employer_id INTEGER, title TEXT, description TEXT, start_time INTEGER, end_time INTEGER, status INTEGER); CREATE TABLE employers( _id INTEGER, employer_name TEXT, ... CREATE TABLE workers( _id INTEGER PRIMARY KEY AUTOINCREMENT, ... CREATE TABLE status( _id INTEGER PRIMARY KEY AUTOINCREMENT, ... INSERT INTO status (_id , status) VALUES (NULL, 'Filled'); INSERT INTO status (_id , status) VALUES (NULL, 'Applied For'); INSERT INTO status (_id , status) VALUES (NULL, 'Open');

The single getString line of Java code loads the SQL required to create the database, along with a reasonable amount of test data.

One crucial piece of information mentioned only briefly in the Android documentation is that you must either escape all single quotes and double quotes with a backslash (\" or \' ) within a resources string or enclose the entire string in either single or double quotes. If single and double quotes are mixed in a resource string, they must be escaped. In the case of the MicroJobs Database_onCreate string just shown, notice that the entire thing is surrounded with double quotes.

The rest of the onCreate method runs each line of SQL. The entire process runs under a transaction so that it will either execute completely or be rolled back and have no effect at all on the database. onUpdate

In the MicroJobs application, the onUpdate method is very similar in structure to the onCreate method. However, the contents of the strings.xml resource file are quite different:

<string name="MicroJobsDatabase_onUpgrade">" DROP TABLE IF EXISTS jobs DROP TABLE IF EXISTS employers


The opening <string> tag is followed by a double quotation mark to start a string, and a closing quotation mark ends the strings before the </string> tag. Within the string are four rather drastic SQL commands. To support the demonstration code in this book, we cheat a little. The "upgrade" code removes the old database and re-creates it with whatever is in the current version of the code. Although this is nice for a book, it won't work very well in real life. Your customers won't be very happy if they have to re-key their information each time they upgrade software versions! A real application would have several upgrade scripts, one for each version that might be out in the wild. We would execute each upgrade script, one at a time, until the phone's database is completely up-to-date.

The structural parts of follow. The custom Cursors and the public functions that return them are discussed next. (structure): package com.microjobsinc.mjandroid;

* Provides access to the MicroJobs database. Since this is not a Content Provider,

* no other applications will have access to the database.

public class MicroJobsDatabase extends SQLiteOpenHelper { /** The name of the database file on the file system */ private static final String DATABASE_NAME = "MicroJobs"; /** The version of the database that this class understands. */ private static final int DATABASE_VERSION = 1;

/** Keep track of context so that we can load SQL from string resources */ private final Context mContext;

public MicroJobsDatabase(Context context) {O

super(context, DATABASE_NAME, null, DATABASE_VERSION); this.mContext = context;

/** Called when it is time to create the database */ ^Override public void onCreate(SQLiteDatabase db) { String[] sql =

mContext.getString(R.string.MicroJobsDatabase_onCreate).split("\n");© db.beginTransaction();© try {

// Create tables and test data execMultipleSQL(db, sql); db.setTransactionSuccessful();0 } catch (SQLException e) {

Log.e("Error creating tables and debug data", e.toString());


/** Called when the database must be upgraded */ @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {© Log.w(MicroJobs.LOG_TAG, "Upgrading database from version " + oldVersion + " to " +

newVersion + ", which will destroy all old data"); String[] sql =

mContext.getString(R.string.MicroJobsDatabase_onUpgrade).split("\n"); db.beginTransaction(); try {

execMultipleSQL(db, sql); db.setTransactionSuccessful(); } catch (SQLException e) {

Log.e("Error upgrading tables and debug data", e.toString()); throw e; } finally {


// This is cheating. In the real world, you'll need to add columns, not rebuild from scratch. onCreate(db);

* Execute all of the SQL statements in the String[] array

* @param db The database on which to execute the statements

* @param sql An array of SQL statements to execute */

private void execMultipleSQL(SQLiteDatabase db, String[] sql){ for( String s : sql )

Here are some of the highlights of the code:

O Constructs the MicroJobsDatabase object. We pass the parent class the database name and version, and it keeps track of when to simply open the database and when to upgrade the version. The database itself is not opened here—that happens in response to a getReadableDatabase or getWritableDatabase call. We also keep a private reference to the Context object in the constructor.

© Retrieves strings containing SQL code, which we have chosen to store in a resource file for easier readability and maintenance.

© Begins the transaction within which all the SQL statements will execute to create the database.

O Ends the transaction, creating the database.

© Function to call in order to upgrade the database.

© Function that executes each SQL statement retrieved by item 2.

0 0

Post a comment