Using SQLite Transactions with your ContentProvider

In the world of databases, a transaction is a unit of work (including insertions, deletions, updates) that is Atomic, Consistent, Isolated, and Durable. By default in SQLite, each insertion is a transaction. And to preserve data integrity, SQLite will wait until data is stored on the disk before completing the transaction. So if you have a large data set that you’re trying to insert into your database, inserting each piece individually is going to seem extremely slow.

You want to use transactions, not just because they will increase the performance of your database operations. Because transactions are atomic, they will help you ensure your database is consistent. For example, if you need to process a large batch of instructions, then either everything happened correctly, or if something went wrong then that whole transaction failed (so it’s all or nothing).

By default the ContentResolver API provides a bulkInsert() method, but its not atomic and its slow as hell, so let’s override the bulkInsert() method in our ContentProvider.

public class YourProvider extends ContentProvider {
//..... define constants for the UriMatcher
public static final int EVENTS = 1;
public static final int FESTIVITIES = 2;
//....

private static final UriMatcher sUriMatcher = buildUriMatcher();
private YourDatabase mOpenHelper;


/**
 * Creates the Uri matcher
 */
private static UriMatcher buildUriMatcher(){
	final UriMatcher matcher = new UriMatcher(UriMatcher.NO_MATCH);
	final String authority = YOUR_CONTENT_AUTHORITY;

   //let the matcher match URIs with your defined constants
	matcher.addURI(authority, "events", EVENTS);
	matcher.addURI(authority, "festivities", FESTIVITIES);
   //.....

	return matcher;
}

@Override
public boolean onCreate() {
	mOpenHelper = new YourDatabase(getContext());
	return true;
}

@Override
public String getType(Uri uri) {
	final int match = sUriMatcher.match(uri);
	switch(match){
	case EVENTS:
		return EVENTS.CONTENT_TYPE;
	case FESTIVITIES:
		return FESTIVITIES.CONTENT_TYPE;
    //......
	default:
		throw new UnsupportedOperationException("unknown: uri " + uri);
	}
}

@Override
public int bulkInsert(Uri uri, ContentValues[] values) {
	final SQLiteDatabase db = mOpenHelper.getWritableDatabase();
	final int match = sUriMatcher.match(uri);
	switch(match){
	case EVENTS:
                int numInserted= 0;
		db.beginTransaction();
		try {
            //standard SQL insert statement, that can be reused
			SQLiteStatement insert = 
				db.compileStatement("insert into " + YOUR TABLE
						+ "(" + COLUMN1 + "," + COLUMN2
						+ "," + COLUMN3 + ")"
						+" values " + "(?,?,?");
			
			for (ContentValues value : values){
                //bind the 1-indexed ?'s to the values specified
				insert.bindString(1, value.getAsString(COLUMN1));
				insert.bindString(2, value.getAsLong(COLUMN2));
				insert.bindString(3, value.getAsString(COLUMN3));
				insert.execute();
			}
			db.setTransactionSuccessful();
            numInserted = values.length
		} finally {
			db.endTransaction();
		}
		return numInserted;
    //.... 
	default:
		throw new UnsupportedOperationException("unsupported uri: " + uri);
	}}

}

So, for each ContentURI you are suppporting (each table in your db), write its respective bulkInsert case as above. And now you will witness an absolutely HUGE increase in performance (for me it cut the bulkInsert() time from 20 seconds to 1), and the return value of the bulkInsert() will now let you know if the transaction was successful or not. Also look here to see the transaction API.

Advertisements
This entry was posted in Android and tagged , , , , , . Bookmark the permalink.

2 Responses to Using SQLite Transactions with your ContentProvider

  1. Erdal says:

    I think you are missing a closing “)” after your question marks “?” line 58

  2. Great stuff. Helped me out. Curious though I’m getting an Android error now I never saw:

    “Application did not close the cursor or database object that was opened here”

    Does the above code need a db.close() at line 72 ? Added one and the error seems to have gone

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s