Single SQLite connection

If you look back at my earlier post, android sqlite locking, I demonstrated that more than one sqlite connection was really bad. Bad-bad. OK, so now what? How do you keep one connection and manage it?

Back when I worked on some earlier versions of ORMLite, I coded this big, complex framework that would do reference counting, and close the connection when nobody had a reference to it, and that worked pretty good.  The downside?  You had to extend my base classes for Activity, Service, etc, and if you wanted to integrate my code with either your class hierarchy, or worse, a 3rd party, it was a huge pain.

It kind of dawned on me.  The way SQLite works, it would be basically impossible to corrupt your database, unless there’s a bug in the SQLite code, or a hardware issue.  What I’m going to say is controversial, but I’ve done some snooping and testing, and I’m 99.99% sure its the way to go.

Keep a single SQLiteOpenHelper instance in a static context.  Do lazy initialization, and synchronize that method.  When do you close it?  You don’t.  When the app shuts down, it’ll let go of the file reference, if its even holding on to it.

What?!?!?!?!?!?! You have to close your databases!!!!!!!

Settle down.  It works.  Chances are, if you have a db in your app, you’re probably using it in most/all of your Activities and Services.  The “connection” is just a file handle.  When you do writes, they are flushed to disk immediately.

“But Kevin, what about that ‘close() was never explicitly called on database’ exception?”

If you pay attention, you don’t get that exception when the connection is just “hanging out”.  You get it when you ALREADY have a connection that was opened, and you try to open another one.  If you’re doing it right, you only open the connection once.  Presumably, that error triggers when the system notices somebody else has a lock on the file (I’d guess a read lock, because even though you get the exception, you can still see the DB).

What would it look like?

public class DatabaseHelper extends OrmLiteSqliteOpenHelper
{
    private static DatabaseHelper instance;

    public static synchronized DatabaseHelper getHelper(Context context)
    {
        if (instance == null)
            instance = new DatabaseHelper(context);

        return instance;
    }
//Other stuff... 
}

Boom. That’s it. It’ll work.

-Kevin