Android Database Example and Tutorial (thread safe version)

This post explains how to use the WorxForUs SQLite Database framework to have an Android app that works well in multi-threaded applications and supports table level upgrades (instead of the entire database). 

Background:

I wrote this library because I my app was based on the Android examples and worked on the device I had at the time (a Galaxy S), but suddenly was having problems when working on newer devices.  After much searching, I found out that the new devices were multi-core and was accessing the database in multiple places in the program.  I didn't have that problem with the earlier devices because they were single-threaded and so the database access was naturally serialized.  The Android samples were good, but didn't go far enough to address the problems I was seeing.

Typical Classic Style Database access exceptions:

This framework corrects some of the following issues that are commonly seen once your app starts getting more complicated and calls the database from multiple locations or background threads.

Android 4
java.lang.IllegalStateException: Cannot perform this operation because the connection pool has been closed.
android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5)

Android 2.3.3
java.lang.IllegalStateException: database not open
android.database.sqlite.SQLiteException: database is locked
Failed to setLocale() when constructing, closing the database

Accessing a SQLite database in a thread-safe manner:

First, download the WorxForUs framework from the github page (or clone here).
Import the project into your Eclipse or Android Studio.

Create your new project: right-click and select 'New / Android Application Project'.

I'm going to name it: 'WorxforusDbSample' and create the project using defaults for the remaining options. 

Once the project is loaded, you will need to add a reference to the worxforus_library project from Properties / Android.  Create a new class in com.example.worxforusdbsample called Nugget and enter the following code.

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";
    }
}

Creating your object is the easy part, now create the association with the database.  Create a new class called NuggetTable and extend from the abstract class TableInterface<Nugget>.   Extending from TableInterface allows us to us the TableManager which is what serializes access to the database and prevents from multiple threads colliding when accessing the data.  Get the sample app code here.

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);        }
    }
   
}

Code for the main activity

To connect to the database, first establish a NuggetTable object in your app or activity onCreate method.  You may want to store this connection in a singleton for easy access by any other activity (and also to reduce memory usage and connection time).

public class NuggetDbActivity extends ActionBarActivity {
    NuggetTable nuggetTable;
    static final int NUM_ITEMS_TO_CREATE = 5;
    static final int NUM_THREADS_TO_RUN = 10;
   
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        nuggetTable = new NuggetTable(this);
        //Your activities setup code...
    }

Once the NuggetTable object is ready, then you use the TableManager to serialize access to the database.

    public void addRandomDataWorxForUs(NuggetTable table) {
        TableManager.acquireConnection(this, NuggetTable.DATABASE_NAME, table);
        Nugget nugget = new Nugget();
        Result r = table.insert(nugget);
        TableManager.releaseConnection(nuggetTable);
    }

The TableManager.acquireConnection does a number of things, it checks to see if your table has already been created or if you have marked it for upgrade (ie. increased the TABLE_VERSION and if so, will run your onUpgrade code).  Otherwise it just creates a new table and locks its use by only the current thread.

In addition to the table object we want to lock for our use, a context is passed that is needed to initialize the database connection.  The database name is also passed to the acquire method so that the method knows which database to use for storing the table meta information such as the current version of the table and sync information.

Finally when all the data operations are completed, you will need to release the connection held by the TableManager to allow other methods to access the database.  If you forget to release the connection, you will quickly realize it because your app will hang the next time you try to access the database.

That's all there is to it for simple database access that works in a thread safe manner.  Ok, it was a lot of code, but you get the point!

Upgrading Tables

Let's say you have released an app to the Google Play store and now you realize you need to add an index to speed up database access or maybe you need too add another field to store more data.

Add a new field to the existing database, modify your NuggetDbHelper.onUpgrade code:
@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("SampleW4UsDb", "Upgrading table from "+oldVersion+" to "+newVersion);
    if (oldVersion < 2) { //this means the current version is 1 or less
        //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("SampleW4UsDb", "Adding new field and new index to "    + DATABASE_TABLE + " Table");
    }
}

And change the line for TABLE_VERSION to 2
    public static final int TABLE_VERSION = 2;
 
Now the next time TableManager.acquireConnection is run on this table, it will see that the existing table is version 1 (if it hasn't been updated yet) and proceed to run the update code which will change it to version 2.  In this case it will modify the table to include the new field and also add a new index into the table automatically.  You never have to worry about checking that a table was already created or checking the version, the framework handles all of that for you.

If you've found this post helpful, please take a moment to add a comment, +1, or a funny iguana picture.  Thanks!

Related Work

The source code to this example application can be found on github.
https://github.com/RightHandedMonkey/WorxForUsDb_Samples

The Checklists ToGo app 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 framework.
https://github.com/RightHandedMonkey/CTG_API