Introduction
A lot of times in an app, you need to update some locally cached data stored in a database with newer data fetched from e.g. a webservice. A normal way (or so it was for me) to do this is with the “if-not-update-then-insert” pattern, like this:
if ( getContentProvider().update( uri, values, selection, selectionArgs ) == 0 ) {
getContentProvider().insert( uri, values );
}
Or with many rows, using bulkInsert():
List<ContentValues> valuesToInsert = new ArrayList<ContentValues>();
for ( ContentValues values: myValues ) {
if ( getContentProvider().update( uri, values, selection, selectionArgs ) == 0 ) {
valuesToInsert.add( values );
}
}
getContentProvider.bulkInsert( uri, valuesToInsert.toArray( new ContentValues[0] ) );
While this might seem like a solid way to do it, there are a few problems:
First of all, some of the code is boilerplate and could be reduced a bit, but the major issue here is…
Thread safety
If multiple threads are running this code simultaneously, it is possible to violate your database constraints:
For example if you have a unique key in your table that the update() tries to match with, and two threads executing the code, the following could happen:
Both threads have run all the update() calls without updating anything and are about to run the insert()/bulkInsert() method. This means that both threads think that it is safe to insert the values, because they have already checked that no constraints are violated when no updates took place. The threads are, however, not aware of eachother’s plans to insert data with the same unique key, which undoubtedly will result in a constraint violation, and possibly an exception like the following:
android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed
at android.database.sqlite.SQLiteStatement.native_execute(Native Method)
at android.database.sqlite.SQLiteStatement.execute(SQLiteStatement.java:61)
at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1677)
at android.database.sqlite.SQLiteDatabase.insertOrThrow(SQLiteDatabase.java:1547)
Why not just synchronize it?
An easy solution for this thread safety problem is simply to wrap the code in a synchronized block, like this:
synchronized( this ) {
List<ContentValues> valuesToInsert = new ArrayList<ContentValues>();
for ( ContentValues values: myValues ) {
if ( getContentProvider().update( uri, values, selection, selectionArgs ) == 0 ) {
valuesToInsert.add( values );
}
}
getContentProvider.bulkInsert( uri, valuesToInsert.toArray( new ContentValues[0] ) );
}
This works, and no constraint violations occur anymore. But now we added more code and slowed down the execution of the code by locking it to one thread at a time.
Better solution: Using SQLite’s INSERT OR REPLACE statement
The better solution to this is to use one of SQLite’s already builtin features, the INSERT OR REPLACE statement. However, the ContentProvider only has a insert() method and not a replace method, so we’ll have to work around this in some clever way:
Fortunately, the ContentValues objects can hold an arbitrary amount of parameters to the ContentProvider, so we’ll use that to indicate that we want to do an INSERT OR REPLACE instead of just a normal INSERT.
First we’ll define some static string constant that we would never use as column name:
public static final String SQL_INSERT_OR_REPLACE = "__sql_insert_or_replace__";
Next, we’ll pass that along with the other values in our ContentValues object:
ContentValues values = new ContentValues();
values.put( SQL_INSERT_OR_REPLACE, true );
// ... fill the ContentValues with the actual values ...
// ... notice we only need one line to insert/replace
getContentProvider().insert( uri, values );
Now we have passed the parameter on, so we’ll modify our ContentProvider to handle this:
public Uri insert(Uri uri, ContentValues values) {
// ...
boolean replace = false;
if ( values.containsKey( SQL_INSERT_OR_REPLACE ) {
replace = values.getAsBoolean( SQL_INSERT_OR_REPLACE );
// Clone the values object, so we don't modify the original.
// This is not strictly necessary, but depends on your needs
values = new ContentValues( values );
// Remove the key, so we don't pass that on to db.insert() or db.replace()
values.remove( SQL_INSERT_OR_REPLACE );
}
long rowId;
if ( replace ) {
rowId = db.replace(tableName, null, values);
} else {
rowId = db.insert(tableName, null, values);
}
// ...
}
Conclusion
There you have it. We can now call SQLite’s INSERT OR REPLACE function through our ContentProvider,
and even takes even less code, since we only have to call insert(), and not update(). Neat huh? 
