Wednesday, August 22, 2012

Android Database Singleton Connector - Preferred Practice

Working with databases in a multi-threaded environment:

NOTE: This database access tutorial has been updated.  Use the new WorxForUs framework and check out the post Android Database Example and Tutorial for the updated version of this article.

Now that your database system sort of works, it's time to make it work in multiple thread situations and on a multi-core processor.  Since Android uses file level locking on the database you'll want to create a single access point for database connections.  This way all database access will be serialized and stop resulting in locked database exceptions.

First, extend your SQLOpenHelper object and wrap in your accessor class as before (see Android Database Connector - Beginner Usage for the SQLOpenHelper example).

Now create your access manager as a singleton using the Application context.  We'll use this class to prevent the database locking and state exceptions mentioned in the previous post.

/* * - Database helper singleton style
 * DBConnector.Connect(Context c);
 * DBConnector.getDbX();
 * DBConnector.Release();

public class DBConnector {
    private Application application;
    private static DBConnector instance = new DBConnector();

    // databases - these don't need to be static because they will be called from the static instance

    //each unique adapter (database table) should be added here along with its get method below
    private BaseTableVersionDBAdapter baseTableVersionDb;

    public DBConnector() {

    public static DBConnector self() {
        return instance;

    public static void ConnectApp(Application app) { 

        //make sure this is set to application context
        if (self().application != null) {
        self().application = app;

    public static boolean isConnected() {
        return (self().application != null);
    public static void Release() {
        // close all the database connections

        self().baseTableVersionDb = null;

        // set context to null
        self().application = null;

    //A separate get function will be needed for each database added
    public static BaseTableVersionDBAdapter getBaseTableVersionDB() {
        if (self().baseTableVersionDb == null) {
            self().baseTableVersionDb = new BaseTableVersionDBAdapter(self().application);
        return self().baseTableVersionDb;

Note that to open the initial connection, we do so like this in the first (or each) Activity:

    public void onCreate(Bundle savedInstanceState) {
        //Any other create code goes here
        if (!DBConnector.isConnected())

The Application object is passed here instead of a Context to ensure that we only passing the Application's Context and not an Activity Context.  We want this DBConnector to last the life of the Application not the Activity.


Since the DBConnector is now active, we can access this anywhere in our application by using:
DBConnector.getBaseTableVersionDB().insertOrReplace("string1", 111);
or use whatever functions you have built into your database helper.

Notice that we are not using db.close() or anymore since we don't know when any other threads will be trying to access the database.  Instead the DBConnector opens the database on the first usage of it.  If we leave in the db.close() then another thread may be trying to access it a closed database object and we'll get an exception.

Though not necessary, to close the database when the Application is finished without relying on the garbage collector, use the following:

Please note that you may want to add your own update and create handler code, or have a way to test that necessary tables have been created.  This DBConnector class is a good place to put all that code, because it can be tested once upon opening the application instead of each time you are opening the database.

I'll post the full sourcecode for this type of access if enough people express interest in it.  Thanks for reading.

*Please comment - your comments are welcome and appreciated!