Introduction

This tip aims to illustrate the best practices for accessing an Android database in a thread safe manner. The WorxForUs framework used here helps to do many common tasks: serialize access to the database, perform per-table upgrades, and more advanced synchronization features to keep offline application databases in sync with a remote database. This also provides an alternative to writing a complicated ContentProvider for your app.

Background

I wrote this library because my app was based on the Android examples and although they worked most of the time, I frequently got errors:

android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5)
java.lang.IllegalStateException: Cannot perform this operation because 
                 the connection pool has been closed.
java.lang.IllegalStateException: database not open
android.database.sqlite.SQLiteException: database is locked
Failed to setLocale() when constructing, closing the database

The trick that I was failing to account for was to keep the access to my database serialized. This works fine on single-threaded devices, but multi-core devices choked frequently. To prevent this, I used a singleton to access the database and use semaphores to provide a lock on the database so that only one thread can access it at a time.

Using the Code

First, download the WorxForUs framework and sample project shown above.

Import the projects into your Eclipse or Android Studio.

Make sure the sample project is linked to the library project by adding a reference to the worxforus_library project from Properties / Android.

The Main Application

See below for a sample of how the main program uses the database access. Be careful that each access to the table object is sandwiched between the acquireConnection(..) and releaseConnection(..) methods. Those methods are what serializes the access to it. Keep your accesses short and sweet.

//create the database table - note a TablePool could be used here
NuggetTable nuggetTable = new NuggetTable(this);

//lock the database access for use by this thread
TableManager.acquireConnection(this, NuggetTable.DATABASE_NAME, table);
//insert your table action here

Nugget nugget = new Nugget();
nugget.setType((int)Math.round(Math.random()*3));
//add item to the table - here I am ignoring the returned results which includes the 
//insert id so it can be retrieved
table.insert(nugget);
//get the list of all objects in the table
ArrayList<Nugget> list = table.getAllEntries();

//release the database access so other threads can no access it.
TableManager.releaseConnection(nuggetTable);

The Model

Take a look at the data model we are storing. In this case, it is simple details of an ore nugget.

package com.example.worxforusdbsample;

public class Nugget {
    String type="";
    int id =0;
  
    public static final String IRON = "Iron";
    public static final String GOLD = "Gold";
    public static final String DIAMOND = "Diamond";

    public String getType() {
        return type;
    }
    public void setType(String type) {
        this.type = type;
    }
    public void setType(int type) {
        if (type == 1)
            setType(GOLD);
        else if (type == 2)
            setType(DIAMOND);
        else //set remaining to Iron
            setType(IRON);
    }
  
    public int getId() {
        return id;
    }
  
    public void setId(int id) {
        this.id = id;
    }
  
    public String getDescription() {
        return type+" nugget";
    }
}

The Table - How the Base Database Table is Defined

The NuggetTable.java file shows how we are creating our link to the database. This class extends from TableInterface<T> which provides an interface that allows us to do all the neat things to the database such as providing a standard way to access, create, and update tables. The primary point here is that since we are using an interface we can access everything with a TableManager. The benefit of doing this is that the TableManager already knows how to check if the database was already created or not, whether it needs to be updated, and how to perform the update.

The first thing you'll see in this code is a bunch of static final Strings and ints that define the table data fields and SQL code to create the table in the database.

Overridden methods are defined for the open and closing of the database helper which is the standard SQLiteOpenHelper found in Android sqlite database projects.

In addition to the insert method, I include an insertOrUpdate method which just uses the sqlite replace method in case you do not want (or need) to check if a database row with the same primary key already exists. Although this has a slight performance hit since replace actually does a delete and an insert behind the scenes.

The other item worth noting is the inclusion of comments in the onUpgrade helper function which show how to make changes to your database and the system will automatically process them the next time the database table is accessed. Just increment the TABLE_VERSION field in your code each time you change it and add a database command as a public static final String to be run the next time the change is detected.

NuggetTable Code

package com.example.worxforusdbsample;

import java.util.ArrayList;

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.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

//Result is a convenience class to capture errors and pass objects back to the caller
import com.worxforus.Result;
import com.worxforus.db.TableInterface;

public class NuggetTable extends TableInterface<Nugget> {
    public static final String DATABASE_NAME = "sample_db"; //Instead of a text string, 
                            // this should be a static constant for your app
    public static final String TABLE_NAME = "nugget_table";
    public static final int TABLE_VERSION = 1;
    // 1 - Initial version

    static int i = 0;                         // counter for field index
    public static final String NUGGET_ID = "nugget_id";     // int
    public static final int NUGGET_ID_COL = i++;
    public static final String NUGGET_TYPE = "nugget_type";     // String
    public static final int NUGGET_TYPE_COL = i++;

    private static final String DATABASE_CREATE = "CREATE TABLE " + TABLE_NAME + " ( " 
            + NUGGET_ID + "     INTEGER PRIMARY KEY AUTOINCREMENT,"
            + NUGGET_TYPE + "   TEXT" 
            + ")";

    private SQLiteDatabase db;
    private NuggetDbHelper dbHelper;

    public NuggetTable(Context _context) {
        dbHelper = new NuggetDbHelper(_context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public Result openDb() {
        Result r = new Result();
        try {
            db = dbHelper.getWritableDatabase();
        } catch (SQLException e) {
            Log.e(this.getClass().getName(), r.error);
            throw(new RuntimeException(e));
        }
        return r;
    }

    @Override
    public void closeDb() {
        if (db != null)
            db.close();
    }

    @Override
    public void createTable() {
        dbHelper.onCreate(db);
    }

    @Override
    public void dropTable() {
        db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME);
        invalidateTable();
    }

    public void wipeTable() {
        synchronized (TABLE_NAME) {
            db.delete(TABLE_NAME, null, null);
        }
    }
    
    @Override
    public void updateTable(int last_version) {
        dbHelper.onUpgrade(db, last_version, TABLE_VERSION);
    }

    @Override
    public String getTableName() {
        return TABLE_NAME;
    }

    @Override
    public int getTableCodeVersion() {
        return TABLE_VERSION;
    }

    /**
     * For ease of use, not efficiency, I combined insert and update as a single statement. 
     * Note that if the item exists,
     * that two operations are performed, a delete and insert.
     */
    @Override
    public Result insertOrUpdate(Nugget t) {
        synchronized (TABLE_NAME) {
            Result r = new Result();
            try {
                ContentValues cv = getContentValues(t);
                r.last_insert_id = (int) db.replace(TABLE_NAME, null, cv);
            } catch( Exception e ) {
                Log.e(this.getClass().getName(), e.getMessage());
                r.error = e.getMessage();
                r.success = false;
            }
            return r;
        }
    }
    
    public Result insert(Nugget t) {
        synchronized (TABLE_NAME) {
            Result r = new Result();
            try {
                ContentValues vals = new ContentValues();
                if (t.getId() > 0)
                    vals.put(NUGGET_ID, t.getId());
                vals.put(NUGGET_TYPE, t.getType());
                r.last_insert_id = (int) db.insert(TABLE_NAME, null, vals);
            } catch( Exception e ) {
                Log.e(this.getClass().getName(), e.getMessage());
                r.error = e.getMessage();
                r.success = false;
            }
            return r;
        }
    }

    @Override
    public Result insertOrUpdateArrayList(ArrayList<Nugget> t) {
        return null; //not implemented in this sample
    }

    public Result insertArrayList(ArrayList<Nugget> list) {
        Result r = new Result();
        db.beginTransaction();
        for (Nugget item : list) {
            try {
                insert(item);
            } catch(SQLException e ) {
                Log.e(this.getClass().getName(), e.getMessage());
                r.error = e.getMessage();
                r.success = false;
            }
        }
        db.setTransactionSuccessful();
        db.endTransaction();
        return r;
    }
    
    @Override
    public ArrayList<Nugget> getUploadItems() {
        return null; //not implemented in this sample
    }

    public ArrayList<Nugget> getAllEntries() {
        ArrayList<Nugget> al = new ArrayList<Nugget>();
        Cursor list = getAllEntriesCursor();
        if (list.moveToFirst()){
            do {
                al.add(getFromCursor(list));
            } while(list.moveToNext());
        }
        list.close();
        return al;
    }
    
    protected Cursor getAllEntriesCursor() {
        return db.query(TABLE_NAME, null, null, null, null, null, NUGGET_ID);
    }
    
    // ================------------> helpers <-----------==============\\
 
    /** returns a ContentValues object for database insertion
     * @return
     */
    public ContentValues getContentValues(Nugget item) {
        ContentValues vals = new ContentValues();
        //prepare info for db insert/update
        vals.put(NUGGET_ID, item.getId());
        vals.put(NUGGET_TYPE, item.getType());
        return vals;
    }
    
    /**
     * Get the data for the item currently pointed at by the database
     * @param record
     * @return
     */
    public Nugget getFromCursor(Cursor record) {
        Nugget c= new Nugget();
        c.setId(record.getInt(NUGGET_ID_COL));
        c.setType(record.getString(NUGGET_TYPE_COL));
        return c;
    }
    
    // ================------------> db helper class <-----------==============\\
    private static class NuggetDbHelper extends SQLiteOpenHelper {
        public NuggetDbHelper(Context context, String name,
                CursorFactory factory, int version) {
            super(context, name, factory, version);
        }

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

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // called when the version of the existing db is less than the current
            Log.w(this.getClass().getName(), 
        "Upgrading table from " + oldVersion + " to " + newVersion);
//            if (oldVersion < 1) { //EXAMPLE: if old version was V1, just add field
//                // create new table
//                db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
//                onCreate(db);
//                Log.d(this.getClass().getName(), "Creating new "+ DATABASE_TABLE + " Table");
//            }
//            if (oldVersion < 2) {
//                //EXAMPLE: add field and change the index
//                db.execSQL("ALTER TABLE "+TABLE_NAME+
//                " ADD COLUMN "+NEW_COLUMN+" "+NEW_COLUMN_TYPE);
//                db.execSQL("DROP INDEX IF EXISTS "+INDEX_1_NAME); //remove old index
//                db.execSQL(INDEX_1); //add a new index
//                Log.d(this.getClass().getName(), "Adding new field and new index to "    + 
//                DATABASE_TABLE + " Table");
//            }
        }
    }    
}

Points of Interest

The Android framework used in this example application can be found on github.

The Checklists ToGo app also uses this framework as a basis for database and network access. For a complete example, check out the Checklists ToGo API which uses the more complicated features of the WorxForUs framework. Thanks for reading!

More by RightHandedMonkey

Android Database Example Project (Thread Safe Version)