Introduction
This tip aims to illustrate the best practices for accessing an Android database in a thread safe manner. The WorxForUs framework used here helps to do many common tasks: serialize access to the database, perform per-table upgrades, and more advanced synchronization features to keep offline application databases in sync with a remote database. This also provides an alternative to writing a complicated ContentProvider
for your app.
Background
I wrote this library because my app was based on the Android examples and although they worked most of the time, I frequently got errors:
android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5)
java.lang.IllegalStateException: Cannot perform this operation because
the connection pool has been closed.
java.lang.IllegalStateException: database not open
android.database.sqlite.SQLiteException: database is locked
Failed to setLocale() when constructing, closing the database
The trick that I was failing to account for was to keep the access to my database serialized. This works fine on single-threaded devices, but multi-core devices choked frequently. To prevent this, I used a singleton to access the database and use semaphores to provide a lock on the database so that only one thread can access it at a time.
Using the Code
First, download the WorxForUs
framework and sample project shown above.
Import the projects into your Eclipse or Android Studio.
Make sure the sample project is linked to the library project by adding a reference to the worxforus_library
project from Properties / Android.
The Main Application
See below for a sample of how the main program uses the database access. Be careful that each access to the table object is sandwiched between the acquireConnection(..)
and releaseConnection(..)
methods. Those methods are what serializes the access to it. Keep your accesses short and sweet.
NuggetTable nuggetTable = new NuggetTable(this);
TableManager.acquireConnection(this, NuggetTable.DATABASE_NAME, table);
Nugget nugget = new Nugget();
nugget.setType((int)Math.round(Math.random()*3));
table.insert(nugget);
ArrayList<Nugget> list = table.getAllEntries();
TableManager.releaseConnection(nuggetTable);
The Model
Take a look at the data model we are storing. In this case, it is simple details of an ore nugget.
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
setType(IRON);
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getDescription() {
return type+" nugget";
}
}
The Table - How the Base Database Table is Defined
The NuggetTable.java file shows how we are creating our link to the database. This class extends from TableInterface<T>
which provides an interface that allows us to do all the neat things to the database such as providing a standard way to access, create, and update tables. The primary point here is that since we are using an interface we can access everything with a TableManager
. The benefit of doing this is that the TableManager
already knows how to check if the database was already created or not, whether it needs to be updated, and how to perform the update.
The first thing you'll see in this code is a bunch of static final String
s and int
s that define the table data fields and SQL code to create the table in the database.
Overridden methods are defined for the open and closing of the database helper which is the standard SQLiteOpenHelper
found in Android sqlite database projects.
In addition to the insert
method, I include an insertOrUpdate
method which just uses the sqlite replace
method in case you do not want (or need) to check if a database row with the same primary key already exists. Although this has a slight performance hit since replace actually does a delete
and an insert
behind the scenes.
The other item worth noting is the inclusion of comments in the onUpgrade
helper function which show how to make changes to your database and the system will automatically process them the next time the database table is accessed. Just increment the TABLE_VERSION
field in your code each time you change it and add a database command as a public static final String
to be run the next time the change is detected.
NuggetTable Code
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;
import com.worxforus.Result;
import com.worxforus.db.TableInterface;
public class NuggetTable extends TableInterface<Nugget> {
public static final String DATABASE_NAME = "sample_db";
public static final String TABLE_NAME = "nugget_table";
public static final int TABLE_VERSION = 1;
static int i = 0;
public static final String NUGGET_ID = "nugget_id";
public static final int NUGGET_ID_COL = i++;
public static final String NUGGET_TYPE = "nugget_type";
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;
}
@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;
}
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;
}
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);
}
public ContentValues getContentValues(Nugget item) {
ContentValues vals = new ContentValues();
vals.put(NUGGET_ID, item.getId());
vals.put(NUGGET_TYPE, item.getType());
return vals;
}
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;
}
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) {
Log.w(this.getClass().getName(),
"Upgrading table from " + oldVersion + " to " + newVersion);
}
}
}
Points of Interest
The Android framework used in this example application can be found on github.
The Checklists ToGo app also 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 WorxForUs framework. Thanks for reading!
More by RightHandedMonkey