8.1. Databases

Data is best stored in a relational database format if it can include many instances of the same type of thing. Take a contact list, for instance. There are many contacts, all of whom potentially have the same types of information (address, phone number, etc.). Each "row" of data stores information about a different person, while each "column" stores a specific attribute of each person: names in one column, addresses in another column, and home phone numbers in a third.

Android uses the SQLite database engine, a self-contained, transactional database engine that requires no separate server process. It is used by many applications and environments beyond Android, and is being actively developed by a large community.

The process that initiates a database operation, such as a SELECT or UPDATE, does the actual work of reading or writing the disk file that contains the database in order to fulfill the request. With SQLite, the database is a simple disk file. All of the data structures making up a relational database—tables, views, indexes, etc.—are within this file.

SQLite is not a Google project, although Google has contributed to it. SQLite has an international team of software developers who are dedicated to enhancing the software's capabilities and reliability. Some of those developers work full time on the project.

Reliability is a key feature of SQLite. More than half of the code in the project is devoted to testing the library. The library is designed to handle many kinds of system failures, such as low memory, disk errors, and power failures. In no case should the database be left in an unrecoverable state: this would be a showstopper on a mobile phone, where critical data is often stored in a database. If that database were susceptible to easy corruption, the mobile phone could become an expensive paperweight if the battery were to fail at an inopportune time.

This is not a book on SQL, so we will not go into much detail about the database commands themselves. Ample documentation about SQL in general and SQLite in particular can be found on the Web. But the SQL we use in our examples should be a good starting point for your own applications.

We'll use the MicroJobsDatabase.java file from our MicroJobs example application to discuss how to create and use a SQLite database using Android. This is the subject of the next section.

8.1.1. Basic Structure of the MicroJobsDatabase Class

In our example, the MicroJobsDatabase.java 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:

Constants

The MicroJobsDatabase class defines two important constants:

DATABASE_NAME

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

NOTE

Here is the full path to the MicroJobs file: /data/data/com.mi⁠cro⁠job⁠sinc.mjandroid/databases/MicroJobs. You can use the adb pull command line on your desktop (see the discussion of adb in Section 5.1) to pull the database from the emulator or developer device and then debug it using the SQLite3 executable on the desktop.

DATABASE_VERSION

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.

onCreate

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 MicroJobsDatabase.java:

String[] sql = 
 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');
...
"</string>      

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

NOTE

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 MicroJobsData⁠base_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
DROP TABLE IF EXISTS workers
DROP TABLE IF EXISTS status
"</string>      

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 MicroJobsDatabase.java follow. The custom Cursors and the public functions that return them are discussed next.

MicroJobsDatabase.java (structure):
package com.microjobsinc.mjandroid;

import ...

/**
 * 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;

    /** Constructor */
    public MicroJobsDatabase(Context context) {
        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();
        } catch (SQLException e) {
            Log.e("Error creating tables and debug data", e.toString());
            throw e;
        } finally {
            db.endTransaction();
        }
    }

    /** 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 {
            db.endTransaction();
        }

        // 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 )
            if (s.trim().length()>0)
                db.execSQL(s);
    }
}

Here are some of the highlights of the code:

8.1.2. Reading Data from the Database

There are many ways to read data from an SQL database, but they all come down to a basic sequence of operations:

This process can be very complex in the case of object-relational mapping software, or relatively simple when writing the queries directly into your application. The difference is fragility. Complex ORM tools shield your code from the complexities of database programming and object mapping by moving that complexity elsewhere. The result is that your code is more robust in the face of database changes, but at the cost of complex ORM setup and maintenance.

The simple approach of writing queries directly into your application works well only for very small projects that will not change much over time. Applications with database code in them are very fragile because as the database changes, any code that references those changes must be examined and potentially changed.

A common middle-ground approach is to sequester all of the database logic into a set of objects whose sole purpose is to translate application requests into database requests and deliver the results back to the application. This is the approach we have taken with the MicroJobs application; all of the database code is contained in a single class in the file MicroJobsDatabase.java.

Android gives us the ability to customize Cursors, and we use that ability to further reduce code dependencies by hiding all of the information about each specific database operation inside a custom cursor. Each custom cursor is a class within the MicroJobsDatabase class; the one that we'll look at in this chapter is the JobsCursor.

The interface to the caller in the getJobs method of MicroJobsDatabase appears first in the code that follows. The method's job is to return a JobsCursor filled with jobs from the database. The user can choose (through the single parameter passed to getJobs) to sort jobs by either the title column or the employer_name column:

public class MicroJobsDatabase extends SQLiteOpenHelper {
...
    /** Return a sorted JobsCursor
     * @param sortBy the sort criteria
     */
    public JobsCursor getJobs(JobsCursor.SortBy sortBy) {
        String sql = JobsCursor.QUERY + sortBy.toString();
        SQLiteDatabase d = getReadableDatabase();
        JobsCursor c = (JobsCursor) d.rawQueryWithFactory(
            new JobsCursor.Factory(),
            sql,
            null,
            null);
        c.moveToFirst();
        return c;
    }
...
    public static class JobsCursor extends SQLiteCursor{
        public static enum SortBy{
            title,
            employer_name
        }
        private static final String QUERY =
            "SELECT jobs._id, title, employer_name, latitude, longitude, status "+
            "FROM jobs, employers "+
            "WHERE jobs.employer_id = employers._id "+
            "ORDER BY ";
        private JobsCursor(SQLiteDatabase db, SQLiteCursorDriver driver,
                String editTable, SQLiteQuery query) {
            super(db, driver, editTable, query);
        }
        private static class Factory implements SQLiteDatabase.CursorFactory{
            @Override
            public Cursor newCursor(SQLiteDatabase db,
                    SQLiteCursorDriver driver, String editTable,
                    SQLiteQuery query) {
                return new JobsCursor(db, driver, editTable, query);
            }
        }
        public long getColJobsId()
         {return getLong(getColumnIndexOrThrow("jobs._id"));}
        public String getColTitle()
         {return getString(getColumnIndexOrThrow("title"));}
        public String 
          getColEmployerName()
           {return getString(getColumnIndexOrThrow("employer_name"));}
        public long getColLatitude()
           {return getLong(getColumnIndexOrThrow("latitude"));}
        public long getColLongitude()
           {return getLong(getColumnIndexOrThrow("longitude"));}
        public long getColStatus(){return getLong(getColumnIndexOrThrow("status"));}
    }

Here are some of the highlights of the code:

A sample use of the database follows. The code gets a cursor, sorted by title, through a call to getJobs. It then iterates through the jobs.

MicroJobsDatabase db = new MicroJobsDatabase(this);
JobsCursor cursor = db.getJobs(JobsCursor.SortBy.title);

for( int rowNum=0; rowNum<cursor.getCount(); rowNum++){
    cursor.moveToPosition(rowNum);
    doSomethingWith(cursor.getColTitle());
}

Here are some of the highlights of the code:

8.1.3. Modifying the Database

Android Cursors are great when you want to read data from the database, but the Cursors API does not provide methods for creating, updating, or deleting data. The SQLiteDatabase class provides two basic interfaces that you can use for both reading and writing:

• A set of four methods called simply insert, query, update, and delete

• A more general execSQL method that takes any SQL statement and runs it against the database

We recommend using the first method when your operations fit its capabilities. We'll show you both ways using the MJAndroid operations.

8.1.3.1. Inserting data into the database

The SQL INSERT statement is used whenever you want to insert data into an SQL database. The INSERT statement maps to the "create" operation of the CRUD methodology.

In the MJAndroid application, the user can add jobs to the list by clicking on the Add Job menu item when looking at the Jobs list. The user can then fill out a form to input the employer, job title, and description. After the user clicks on the Add Job button on the form, the following line of code is executed:

db.addJob(employer.id, txtTitle.getText().toString(), 
  txtDescription.getText().toString());

This code calls the addJob function, passing in the employer ID, the job title, and the job description. The addJob function does the actual work of writing the job out to the database.

Example 8-1 shows you how to use the insert method.

Example 8-1. Using the insert method

/** * Add a new job to the database. The job will have a status of open. * @param employer_id The employer offering the job * @param title The job title * @param description The job description */ public void addJob(long employer_id, String title, String description){ ContentValues map = new ContentValues(); map.put("employer_id", employer_id); map.put("title", title); map.put("description", description); try{ getWritableDatabase().insert("jobs", null, map); } catch (SQLException e) { Log.e("Error writing new job", e.toString()); } }

Here are some of the highlights of the code in Example 8-1:

Example 8-2 shows you how to use the execSQL method.

Example 8-2. Using the execSQL method

/** * Add a new job to the database. The job will have a status of open. * @param employer_id The employer offering the job * @param title The job title * @param description The job description */ public void addJob(long employer_id, String title, String description){ String sql = "INSERT INTO jobs (_id, employer_id, title, description, start_time, end_time, status) " + "VALUES ( NULL, ?, ?, ?, 0, 0, 3)"; Object[] bindArgs = new Object[]{employer_id, title, description}; try{ getWritableDatabase().execSQL(sql, bindArgs); } catch (SQLException e) { Log.e("Error writing new job", e.toString()); } }

Here are some of the highlights of the code in Example 8-2:

8.1.3.2. Updating data already in the database

The MicroJobs application enables the user to edit a job by clicking on the job in the Jobs list and choosing the Edit Job menu item. The user can then modify the strings for employer, job title, and description in the editJob form. After the user clicks on the Update button on the form, the following line of code is executed:

db.editJob((long)job_id, employer.id, txtTitle.getText().toString(), 
  txtDescription.getText().toString());

This code calls the editJob method, passing the job ID and the three items the user can change: employer ID, job title, and job description. The editJob method does the actual work of modifying the job in the database.

Example 8-3 shows you how to use the update method.

Example 8-3. Using the update method

/** * Update a job in the database. * @param job_id The job id of the existing job * @param employer_id The employer offering the job * @param title The job title * @param description The job description */ public void editJob(long job_id, long employer_id, String title, String description) { ContentValues map = new ContentValues(); map.put("employer_id", employer_id); map.put("title", title); map.put("description", description); String[] whereArgs = new String[]{Long.toString(job_id)}; try{ getWritableDatabase().update("jobs", map, "_id=?", whereArgs); } catch (SQLException e) { Log.e("Error writing new job", e.toString()); } }

Here are some of the highlights of the code in Example 8-3:

Example 8-4 shows you how to use the execSQL method.

Example 8-4. Using the execSQL method

/** * Update a job in the database. * @param job_id The job id of the existing job * @param employer_id The employer offering the job * @param title The job title * @param description The job description */ public void editJob(long job_id, long employer_id, String title, String description) { String sql = "UPDATE jobs " + "SET employer_id = ?, "+ " title = ?, "+ " description = ? "+ "WHERE _id = ? "; Object[] bindArgs = new Object[]{employer_id, title, description, job_id}; try{ getWritableDatabase().execSQL(sql, bindArgs); } catch (SQLException e) { Log.e("Error writing new job", e.toString()); } }

For the application in Example 8-4, we show the simplest possible function. This makes it easy to understand in a book, but is not enough for a real application. In a real application, you would want to check input strings for invalid characters, verify that the job exists before trying to update it, verify that the employer_id value is valid before using it, do a better job of catching errors, etc. You would also probably authenticate the user for any application that is shared by multiple people.

8.1.3.3. Deleting data in the database

The MicroJobs application enables the user to delete a job as well as create and change it. From the main application interface, the user clicks on the List Jobs button to get a list of jobs, and then clicks on a particular job to see the job detail. At this level, the user can click on the "Delete this job" menu item to delete the job. The application asks the user if he really wants to delete the job. When the user hits the "Delete" button in response, the following line of code in the MicroJobsDetail.java file is executed:

db.deleteJob(job_id);

This code calls the deleteJob method of the MicroJobsDatabase class, passing it the job ID to delete. The code is similar to the functions we've already seen and lacks the same real-world features.

Example 8-5 shows you how to use the delete method.

Example 8-5. Using the delete method

/** * Delete a job from the database. * @param job_id The job id of the job to delete */ public void deleteJob(long job_id) { String[] whereArgs = new String[]{Long.toString(job_id)}; try{ getWritableDatabase().delete("jobs", "_id=?", whereArgs); } catch (SQLException e) { Log.e("Error deleteing job", e.toString()); } }

Example 8-6 shows you how to use the execSQL method.

Example 8-6. Using the execSQL method

/** * Delete a job from the database. * @param job_id The job id of the job to delete */ public void deleteJob(long job_id) { String sql = String.format( "DELETE FROM jobs " + "WHERE _id = '%d' ", job_id); try{ getWritableDatabase().execSQL(sql); } catch (SQLException e) { Log.e("Error deleteing job", e.toString()); } }