Friday, March 21, 2014

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). 


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 / AndroidCreate 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)
        else if (type == 2)
        else //set remaining to Iron
    public int getId() {
        return id;
    public void setId(int 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_TYPE + "   TEXT"
            + ")";

    private SQLiteDatabase db;
    private NuggetDbHelper dbHelper;

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

    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;

    public void closeDb() {
        if (db != null)

    public void createTable() {

    public void dropTable() {

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

    public String getTableName() {
        return TABLE_NAME;

    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.
    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;

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

    public Result insertArrayList(ArrayList<Nugget> list) {
        Result r = new Result();
        for (Nugget item : list) {
            try {
            } catch(SQLException e ) {
                Log.e(this.getClass().getName(), e.getMessage());
                r.error = e.getMessage();
                r.success = false;
        return r;
    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 {
            } while(list.moveToNext());
        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();
        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);

        public void onCreate(SQLiteDatabase db) {

        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;
    protected void onCreate(Bundle savedInstanceState) {

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

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:
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("DROP INDEX IF EXISTS "+INDEX_1_NAME); //remove old index
        db.execSQL(INDEX_1); //add a new index
"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.

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.

Friday, March 14, 2014

Introducing the WorxForUs Open Source Database & Network Helper for Android

WorxForUs Android Framework (Database and Network helper tools)

This article is an introductory guide to the Android helper library by WorxForUs.  The Android SDK has a lot of built in functionality, but there are certain things that it does not take care of for you.  The API guides only get you so far.  This framework is an attempt to pick up where the Android Samples leave off and address some of the trickier issues.  Hopefully you will find features in this library to help your code become more robust.

Major Features of the WorxForUs Android Library:
  • Database management - com.worxforus.db
    • Serialized access framework for writing thread safe (multi-threaded) applications
    • Allows upgrade on a per table level instead of per database
    • Supports methods to keep track of data synchronization to a remote server
  • Obscuring Shared Preferences Data - com.worxforus.ObscuredSharedPreferences
    • Shared data is normally stored in plaintext.  This tool easily encrypts your SharedPreferences data.
  • Network Tools -
    • Allows easy detection and handling of network errors
    • Built in support for cookies
    • Allows easy use of network retry mode (ie. will attempt 5 times before returning an error)
    • Deals with authenticated and non-authenticated network requests
  • Object Pools - com.worxforus.Pool 
    • Put objects into a pool to keep memory usage low and minimize object creation



There are two ways to use the library.
1. Clone the source code at the github WorxForUs Library and import into your IDE.  Link the WorxForUs Library project to your project.
2. Download the jar file from github and include in your project/lib files.

If you are using the Network Tools, you may want to get the latest Apache HttpClient package from their download page here.  You will want the httpcore-4.x.x.jar, httpclient-4.x.x.jar, httpclient-cache-4.x.x.jar, and httpmime-4.x.x.jar.  Import these into your referenced jar library.  The latest binary release as of this writing is the 4.3.3 package.

Why was this Framework created? 

Aside from the typical utilities one generally collects or creates when writing software for a long time, I had a few specific needs for robust database access and network handling.

Database Management

A typical example of using a SQLite database in Android shows that you extend the SQLiteOpenHelper.  This is good, but what if you have several tables each with their own version?  Also, the sample projects show doing a dbHelper.getWritableDatabase(), but do not say anything about how to handle multiple threads contending for the same resource.  This may work fine on single core CPU devices, but suddenly your app may crash on a multi-Core Nexus 7 because your database access is not serialized. This package use a Singleton to provide access to the SQLite database and Semaphores to provide the locking which by default serializes the database access and prevents many common database exceptions from occurring.

Network Tools

I needed to handle cookies and detect when network connections were failing and identify what type of failure occurred.  Was the problem a server failure or a bad connection on the user device?  In a mobile network errors are the norm and it is important to handle them correctly.  I also wanted a way to easily retry a connection several times until it was successful.  This package is based on the Apache HttpClient because it easily supported the things I needed such as cookies and error detection.  Future versions are planned to use the Velocity framework.

*Additional Source of code, including but not limited to:
The Android Open Source Project - for the Base64Support code.
Obscuring Shared Preferences - much of the code for this came from help by emmby at

Wednesday, September 4, 2013

How to Run the Android Support Library Samples (Eclipse Version)

 How to Run the Android Support Library Samples (such as ActionBar) in Eclipse

What are the Android support libraries?  They are .jar files or eclipse projects that let you access the latest features in the Android SDK on older devices such as Fragments (v4) or ActionBar (v7) support.  The v4 library requires a minimum SDK of 4 or Android 1.6, the v7 library requires a minimum SDK of 7 or Android 2.1, and so on.

So you want to support the older Android phones and start by running the support library samples (v4, v7, v13, AppNavigation, ActionBar, etc), but can't find the instructions from Google?  You'll need to download the Android Support Library, create a blank library project, import the existing android code (for v7 support), fix up the created library projects, create a blank project, import the sample demo code, and finally link the support library to your demo.

Also, there is a gotcha to watch out for when using the ActionBar that I experienced where the android:showAsAction values in your menu.xml are ignored and must be set at runtime.  See below for details.

Get the Support Library

Load the support package (and at least API 17) through the Android SDK Manager

After the download is finished, this will be in:{android_sdk}\extras\android\support\
The {android_sdk}\extras\android\support\samples\ folder contains the samples that we will import.
The v4, v7, v13 include the support library for whichever minimum SDK you are willing to support.  i.e. when using the v7 library, the min SDK should be set to 7.  The min SDK should be 13 when using the v13 library.

To install the Android Support v4 Sample Demo

Follow this excellent guide by Tek Eye.  The guide by Tek Eye allowed me to install the samples and get them going, but it did not have an entry for the Android Support v7 Demos, which are a bit trickier to get going.

To install the Android Support v7  Samples Demo

Create a New Eclipse Project to Store the v7 Support Library

You'll be creating a blank project and using this to import the support library as an android library project.  Choose File / New / Android Application Project

Uncheck the 'Create custom launcher' and the 'Create activity' icon, but
Check the 'Mark this project as a Library'

Now, load the support library.  Select File / Import... and choose 'Existing Android Code into Workspace'
Browse to the {android-sdk}\extras\android\support\v7 folder and select Copy projects into workspace if you want.

That will give you 4 new projects in your Eclipse workspace.  But they are not quite ready to use yet.  Also the android-support-v7-mediarouter will have an error that you will need to resolve.

To Fix Up the Android Support Library v7 Dependancies

You should now have 4 new projects:

Expand the libs folder of each library, right-click on each jar and select Build Path / Add to Build Path

Now you will also need to export the jars when projects are linked against this library

Go to Build Path / Configure Build Path and on the 'Order and Export' tab, check the support jars and uncheck Android Dependencies.  Do this for each library: android-support-v7-appcompat, android-support-v7-gridlayout, & android-support-v7-mediarouter.

Now, at this point, android-support-v7-mediarouter will still have a red mark on it with the error: error: Error retrieving parent for item: No resource found that matches the given name 'Widget.AppCompat.ActionButton'.

Link the android-support-v7-appcompat library to the android-support-v7-mediarouter

The android-support-v7-mediarouter has some dependencies on the android-support-v7-appcompat library, so we will need to link the projects.

Click on properties of the android-support-v7-mediarouter project. 
Select the Android page
Add... the android-support-v7-appcompat library

Now do a Project / Clean / All and the Support Library v7 should be clean of errors and your support library is ready to go.

NOTE:  The support library install instructions were adapted from the Android developer page here.

Create a New Eclipse Project to Store the v7 Demo Sample

Choose File / New / Android Application Project

Uncheck the 'Create custom launcher' and the 'Create activity' icon.

Right Click on the project and select Import...  
Pick General / File System

Select the {android-sdk}\extras\android\support\samples\Support7Demos
Check the res, src, and manifest files.

Link the project to the Android Support Library v7:
Click on properties of the SupportV7Demo project. 
Select the Android page
Add... the android-support-v7-appcompat library
Add... the android-support-v7-gridlayout library
Add... the android-support-v7-mediarouter library

Wow, now that all those steps are completed you should be able to compile and install the Android Support Samples to your Android device.

That's it!  The same technique can be used to install the Android Support v4 Library and Samples or the App Navigation Samples.

Note: As one observant reader noticed below, this leaves you with an extra AndroidSupportV7 library project that Eclipse created during the import process that is not used.  You can delete it, or add the libraries above (appcompat/gridlayout/mediarouter) into it so that you only need to import one combined project instead of three separate ones.

General Support Library Notes:

One anonymous comment noted if you are having troubles with a project, check that you have the Target SDK Version in the manifest set to at least 17.  This is found in Eclipse under Project properties / Android / Project Build Target to API 17/Android 4.2.2.

To use the v7 files, copy these to your project's /libs folder.
  •     android-support-v4.jar
  •     android-support-v7-appcompat.jar
  •     android-support-v7-gridlayout.jar (*if needed)
  •     android-support-v7-mediarouter.jar (*if needed)

Typical gotchas for people:
  •     Make sure the min SDK is at least API 7
  •     Make sure the project target build is at least API 17

What to do next:

To learn more about database and network usage with android, check out the WorxForUs Android Database and Networking framework tutorial that assists database access and network access and addresses several common pitfalls.

Hope you found this guide useful, please drop a note, funny internet cat picture, or +1 if it helped.

ActionBar Gotcha Notes:

If you have trouble getting the ActionBar to display icons instead of just overflowing, check where you are setting the showAsAction values.  In the menu_layout.xml, the showAsAction attribute is ignored when using the support library and won't work.    <item
        android:showAsAction="ifRoom" //this will not work

To fix this, add the show as action items in the options menu creation function
    public void onCreateOptionsMenu(Menu menu, MenuInflater inflater) {
        inflater.inflate(, menu);
        MenuItem add = menu.getItem(0);

        //Set the show as action value here at runtime
        MenuItemCompat.setShowAsAction(add, MenuItemCompat.SHOW_AS_ACTION_IF_ROOM);