SQLite INSERT OR REPLACE through ContentProvider

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? :-)

About the author

Jesper Borgstrup Jesper is a Masters student of computer science at the University of Copenhagen with many years of experience in writing applications for Android.

13 thoughts on “SQLite INSERT OR REPLACE through ContentProvider

  1. Sergiandreplace

    Hi!

    I’ve also use the insertWithOnConflict instruction. It allows to set a flag on what to perform when constraints are violated. In this case, the right one will be SQLiteDatabase.CONFLICT_REPLACE. What it does is insert the row, and, if it previously exists, it’s deleted before the insert.

    Maybe this solution is not usable in all cases, but also very powerful and result looks much cleaner.

    Reply
  2. santosh

    Sounds a bit absurd. How is db.replace going to identify which row to replace when there is no selection criteria?

    Reply
    1. Jesper Borgstrup Post author

      As you can see in the official documentation (SQL As Understood By SQLite), the command does an INSERT OR REPLACE. That is, do an INSERT, and if that INSERT fails because of a conflict, delete the conflicting rows before INSERTing again.

      Reply
  3. Pingback: differentiate between update and inset in SQLite : Android Community - For Application Development

  4. Brandon

    Wouldn’t it be easier (and cleaner) to just to create your table using “on conflict replace” and let the database handle the conflicts?

    Reply
    1. Brandon

      …or use SQLiteDatabase.insertWithOnConflict() with CONFLICT_REPLACE as the conflictAlgorithm? Am I missing a nuance of what your code is trying to accomplish?

      Reply
      1. Jesper Borgstrup Post author

        It would maybe be easier, but that would solve a slightly different problem. If you create the table or always insert with a specific conflict resolution algorithm, you cannot (easily) choose runtime whether or not to use REPLACE instead of INSERT. This solution (although the use cases are probably limited) allows you to choose outside of the ContentProvider at runtime.

        Reply
      2. Pablo

        if you have autoincremental pk and use the “on conflict replace” strategy, this will change as it deletes the old row and insert a new one. It would be a problem if you have a foraign key to this table.

        Reply
  5. Pingback: How to do a insert or replace when the primary key will not be used | Ask & Answers

  6. Ramesh

    Hi,
    Thanks for this post and I have query that how to replace query dynamically if inserts violets through content provider logic.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>