to to implement a SQLite Manager for thread-safe read/write access?

Question

I'm planning on improving my SQLite implementation - currently, activities are opening the SQLite DB whenever they need some relevant data, then closing it. Closing the DB is important to avoid exceptions.

my design goals:

1. thread-safe access to an applications' SQLite DB

2. synchronous operation

What I thought I would do is implement some kind of "Manager" class instead of my basic "SQLhelper" class. I want synchronous operation so that rules out implementing it as a service with messages.

I think the best way to implement this "SQLiteManager" is as a singleton.

Are there any better implementations ?

Answer 1

Step 1 - extend the Application class

import android.app.Application;
import android.content.Context;

/**
 * This class is created automatically when the app launches.
 * It is used to provide an application-level context for the SQLiteOpenHelper
 */
public class ApplicationContext extends Application
{

    private static ApplicationContext instance;

    public ApplicationContext()
    {
        instance = this;
    }

    public static Context getContext()
    {
        return instance;
    }

}

Step 2 - update the manifest so that this application class is used

<application android:name="ApplicationContext"
             android:icon="@drawable/icon" 
             android:label="@string/app_name"
             android:debuggable="true">

Step 3 - build the singleton SQLdataHelper into your app

public class SQLdataHelper
{
    //for logging
    private final String TAG = this.getClass().getSimpleName();

    //DATABASE
    private static final String DATABASE_NAME = "my.db";
    private static final int DATABASE_VERSION = 1;//initial version

    //TABLE NAMES
    private static final String TABLE_NAME_A = "exampleOneTable";

    //MEMBER VARIABLES
    private DatabaseHelper mDBhelper;
    private SQLiteDatabase mDB;

    //SINGLETON
    private static final SQLdataHelper instance = new SQLdataHelper();


    private SQLdataHelper()
    {
        final DatabaseHelper dbHelper = new DatabaseHelper(ApplicationContext.getContext());

        //open the DB for read and write
        mDB = dbHelper.getWritableDatabase();
    }


    public static SQLdataHelper getInstance()
    {
        return instance;
    }

    /**
     *  INSERT FUNCTIONS consisting of "synchronized" methods 
     */
    public synchronized long insertTableA(String myName, int myAge)
    {
        Long lValueToReturn;

        //organize the data to store as key/value pairs
        ContentValues kvPairs = new ContentValues();
        kvPairs.put("ColumnOne", myName);
        kvPairs.put("ColumnTwo", myAge);

        lValueToReturn = mDB.insert(TABLE_NAME_A, null, kvPairs);

        return lValueToReturn;
    }


    private static class DatabaseHelper extends SQLiteOpenHelper
    {

        DatabaseHelper(Context context)
        {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }


        //this is called for first time db is created.
        // put all CREATE TABLE here
        @Override
        public void onCreate(SQLiteDatabase db)
        {
            db.execSQL( "CREATE TABLE "
                       + TABLE_NAME_A 
                       + " ("
                       + "_id INTEGER PRIMARY KEY AUTOINCREMENT,"
                       + "ColumnOne TEXT,"
                       + "ColumnTwo TEXT"
                       + ")" );
        }

        //this is called when an existing user updates to a newer version of the app
        // add CREATE TABLE and ALTER TABLE here
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
        {

            //update SQL DB with new columns depending on old version
            // also add new tables
            //NOTE: whatever is done here must also go into onCreate() so that new users get the correct db created
            switch(oldVersion)
            {
            case 1:
//EXAMPLE         db.execSQL("ALTER TABLE " + TABLE_NAME_A + " ADD COLUMN ColumnThree INTEGER;");

                 //don't use a break. for next case simply let them run together to update all the way to latest version
                 //This way, the case just represents a starting point to start updating.

            case 2:
//EXAMPLE         db.execSQL("ALTER TABLE " + TABLE_NAME_A + " ADD COLUMN ColumnFour INTEGER;");

            }

//this code drops the table and will create a fresh one. Note all data lost!
//          db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_C);
//          onCreate(db);
        }

    }

}

I've only included one example insert operation. Add more as you need them and simply make sure they are 'synchronized' methods.

Step 4 - use the SQLdataHelper in your activity

    SQLdataHelper mDataHelper = SQLdataHelper.getInstance();
    mDataHelper.insertTableA("Someone", 100);