Android Database Connector - Beginner Usage & Common Pitfalls

When you are first building your application, you learn to connect to a database using a SQLiteOpenHelper.  You can access the database by creating a wrapper class that has a database helper object and then access it using the code below in your Activity:

public BaseTableVersionDBAdapter baseTableDb;

//connect the db to a valid context

OnCreate(Bundle b) {

     BaseTableVersionDBAdapter baseTableDb = new  BaseTableVersionDBAdapter(this);

}

//now access the database

protected void someFunction() {

     baseTableDb.open();

     baseTableDb.insertOrUpdate("Table1", 3); //add value

     baseTableDb.removeEntry("Table1"); //remote it

     //...do stuff

     baseTableDb.close();

}

 This works well until you need to have background threads access the database or start running your application on multicore processors.  This implementation allows for multiple connections to the database which will cause exceptions and crash your app.
Depending on how your app is written, you may not even notice a problem until you begin to use your application on a multi-core processor where proper thread-safe access to the database is a must.  Your app may work fine on some devices, but not on others.

See the Android Database Connector - Singleton Usage for the improvement to connecting to the database and for how to handle multi-threaded and multi-core application usage.

Common Pitfalls:

1. java.lang.IllegalStateException: attempt to re-open an already-closed object.  - This error crops up if you have closed a database and then try to access it.
   For example:
        baseTableDb.close();
        baseTableDb.insertOrUpdate("table name", 2);
   Despite the indication on the error, this does not mean you closed and reopened a database, because this is perfectly acceptable:
        baseTableDb.close();
        baseTableDb.open();
2. android.database.sqlite.SQLiteDatabaseLockedException (or android.database.sqlite.SQLException in pre-Honeycomb) : database is locked (code 5). This error can be caused by different things.  A few are listed below.
1. One is if you've started a transaction with beginTransaction() but didn't call endTransaction() before trying to open the database.  The transaction locks the database and the SQLiteHelper will keep trying to acquire a lock to the database before it eventually times out with the message: database is locked (code 5).
2. You have added Threads to your application and two threads are trying to access the database at the same time
3. You have a multi-core device that has at least two threads that are trying to access the database at the same time.

Sample Database wrapper class that extends a SQLiteOpenHelper:

public class BaseTableVersionDBAdapter {

    public static final String DATABASE_NAME = com.cxworx.Utils.DATABASE_NAME;
    public static final String DATABASE_TABLE = "table_meta_data";

    public static final String  KEY_TABLE_NAME="name";
    public static final int      KEY_TABLE_NAME_COL=i++;
    public static final String  KEY_TABLE_VERSION="version";
    public static final int     KEY_TABLE_VERSION_COL=i++;

    private static final String DATABASE_CREATE = "CREATE TABLE "+DATABASE_TABLE+" ( " +
    KEY_TABLE_NAME+"            TEXT PRIMARY KEY,"+
    KEY_TABLE_VERSION+"            INTEGER NOT NULL"+
    ")";

    //hold the active db connection
    private SQLiteDatabase db;
    //holds the db helper object
    private TableVersionDbHelper dbHelper;

    public BaseTableVersionDBAdapter(Context _context) {
        dbHelper = new TableVersionDbHelper(context, DATABASE_NAME, null, LT_DATABASE_VERSION);
    }
   
    /**
     *
     * @return result object with SQLiteDatabase in result.object if successful
     */
    public com.cxworx.Result open() {
        com.cxworx.Result r = new com.cxworx.Result();
        try {
            db=dbHelper.getWritableDatabase();
            if (!check_if_table_exists())
                dbHelper.onCreate(db);
        } catch (SQLException e) {
            r.error = "Could not open database. "+e.getMessage();
            r.success = false;
        }
        return r;
    }
   
    public synchronized void close() { db.close(); }
   
    /**
     * Used to merge data from web to local database
     * @param c
     * @return
     */
    public Result insertOrUpdate(String table_name, int table_ver) {
        com.cxworx.Result r = new com.cxworx.Result();
        try {
            int index = (int)db.replaceOrThrow(DATABASE_TABLE, null, get_content_values(table_name, table_ver));
            r.last_insert_id = index;
        } catch (SQLException s) {
            r.error = s.getMessage();
            r.success = false;
        }
        return r;
    }
   
   
    public boolean removeEntry(String name) {
        return db.delete(DATABASE_TABLE,
                KEY_TABLE_NAME+" = ? ", new String[] {name})> 0;
    }


    /** returns a ContentValues object for database insertion
     *
     * @return
     */
    public ContentValues get_content_values(String name, int version) {
        ContentValues vals = new ContentValues();
        //prepare info for db insert/update
        vals.put(KEY_TABLE_NAME, name);
        vals.put(KEY_TABLE_VERSION, version);
       
        return vals;
    }
   
   
    private boolean check_if_table_exists() {
        String query = "SELECT name FROM sqlite_master WHERE type='table' AND name='"+DATABASE_TABLE+"'";
        Cursor result = db.rawQuery(query, null);
        int count = result.getCount();
        result.close();
        return count > 0;
    }
   
   
    //================------------> helper class <-----------==============\\
    private static class TableVersionDbHelper extends SQLiteOpenHelper {

        public TableVersionDbHelper(Context context, String name,
                CursorFactory factory, int version) {
            super(context, name, factory, version);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DATABASE_CREATE);
           
            //Add itself to the entry
            ContentValues vals = new ContentValues();
            vals.put(KEY_TABLE_NAME, BaseTableVersionDBAdapter.DATABASE_TABLE);
            vals.put(KEY_TABLE_VERSION, BaseTableVersionDBAdapter.TABLE_VERSION);

            db.insert(DATABASE_TABLE, null, vals);
        }

        @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 db from "+oldVersion+" to "+newVersion);
            db.execSQL("DROP TABLE IF EXISTS "+DATABASE_TABLE);
            onCreate(db);
        }
       
    }
  }