Using Singleton design pattern for SQLiteDatabase

Question

I'm rather newbie on Android, and I'm working on a simple application to get some basic experience. My app is pretty simple and consists among other things of a broadcast receiver and some activities. Both components make use of a single database, so in theory it could happen that both try to access the db concurrently.

Currently I'm simply instantiating the db object (which is-a SQLite db helper class) each time I need it, and performing the needed operations: query, insert, etc.

From what I've been reading here and in some other documents, this has the problem of getting a "db locked" exception in case the db is accessed concurrently, so a better approach would be having a single instance of this db object so all components use the same db connection at all times.

Is the above reasoning correct? Would a singleton then be a good-enough solution for this? I know some purists may argue against it, but please note that this is a rather simple application so I can afford doing things I wouldn't in other cases.

Otherwise, what would a better option be? I've read about using content provider but it would be too much for this, besides that I'm not interested to share the data with other activities. I have indeed read this post and found it rather helpful.

Teodor

Answer 1

Click here to see my blog post on this subject.

---

Here is some sample code that illustrates three possible approaches. These will allow access to the database throughout the application.

Approach #1: have `SQLiteOpenHelper` be a static data member

This isn't the complete implementation, but it should give you a good idea on how to go about designing the DatabaseHelper class correctly. The static factory method ensures that there exists only one DatabaseHelper instance at any time.

/**
 * create custom DatabaseHelper class that extends SQLiteOpenHelper
 */
public class DatabaseHelper extends SQLiteOpenHelper { 
    private static DatabaseHelper mInstance = null;

    private static final String DATABASE_NAME = "databaseName";
    private static final String DATABASE_TABLE = "tableName";
    private static final int DATABASE_VERSION = 1;

    private Context mCxt;

    public static DatabaseHelper getInstance(Context ctx) {
        /** 
         * use the application context as suggested by CommonsWare.
         * this will ensure that you dont accidentally leak an Activitys
         * context (see this article for more information: 
         * http://android-developers.blogspot.nl/2009/01/avoiding-memory-leaks.html)
         */
        if (mInstance == null) {
            mInstance = new DatabaseHelper(ctx.getApplicationContext());
        }
        return mInstance;
    }
    
    /**
     * constructor should be private to prevent direct instantiation.
     * make call to static factory method "getInstance()" instead.
     */
    private DatabaseHelper(Context ctx) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.mCtx = ctx;
    }
}

Approach #2: abstract the SQLite database with a `ContentProvider`

This is the approach I would suggest. For one, the new CursorLoader class requires ContentProviders, so if you want an Activity or Fragment to implement LoaderManager.LoaderCallbacks<Cursor> with a CursorLoader (which I suggest you take advantage of, it is magical!), you'll need to implement a ContentProvider for your application. Further, you don't need to worry about making a Singleton database helper with ContentProviders. Simply call getContentResolver() from the Activity and the system will take care of everything for you (in other words, there is no need for designing a Singleton pattern to prevent multiple instances from being created).

Mayfly

Alex, I use your approach (#2) and I love its simplicity. But recently I noticed a problem. Wonder if you can help me with that issue: #10973219 Btw, just noticed that I use static mCxt (forgot why). Wonder if it has anything to do with my problem – Luzluzader

The above link is broken... here's an updated one: android-developers.blogspot.com/2009/01/… – Mayfly

Here is a wrapper I wrote/use which makes it slightly easier to use SQLite well in the context of android - SqlDb for Android – Bowleg

Thanks @AlexLockwood, while the approach #1 makes perfect sense to me, I thought one should only use ContentProvider when sharing the database access with other application? – Rozanneroze

Wouldn't it be a good idea to make getInstance synchronized? – Entomologize

@AlexLockwood I am setting up a SQLite database for the first time. It will hold data that will then populate a RecyclerView item list. SQLite is being used for data persistence. Would you recommend your Approach #1 or Approach #2? – Bastard

Here the mInstance contains a Context object in it. Which is a memory leak and a blocker for new instant run feature, – Meerschaum

I'm getting a warning about the class instance mInstance: "Do not place Android context classes in static fields (static reference to DatabaseHelper which has field contenxt pointing to Context); this is a memoryleak (and also breaks Instant Run)". Is there a work around? – Rebirth

nvm, the warning disappeared after I removed the Context variable mCxt – Rebirth

Answer 2

I never read about using a singleton to access a db on android. Would you mind to provide a link about that.

In my apps, I use simple dbhelper objects, not singletons, I was thinking this is more the job of the sql engine to ensure db is not locked, not the job of your android classes, and it works pretty well for my biggest app that is medium sized.

Update #1: looking at the reference you gave, it looks like the problem is not at all about using different instances of a dbhelper. Even a single instance could encounter problems accessing the databases : the problem comes from a concurrent accesses. So the only way to ensure a proper access to the database by different threads is to use simple thread synchronization mechanisms (synchronized methods or blocks), and it almost nothing to do with using a singleton.

Update #2 : the second link you provide clearly shows that their is a need for singleton dbhelper objects in the case of multiple threads writing concurrently in a db. This can happen if you do you sql operations (inserts/updates/deletes) from AsyncTasks for instance. In that case a singleton object dbhelper would simply put all sql operations in some sort of pipeline and execute them in order.

This solution could be easier to implement than using proper thread synchronization using synchronized methods in java. Actually I think there should be more emphasize somewhere in android docs about this problem and the use of a singleton db helper could be encouraged.

Thanks for this nice question and the follow ups.

Barlow

Thanks Stéphane. The articles that I used as a base are these: #2648042 #4302786 Regards, Dan – Teodor

The problem to what you comment is that (AFAIK) using different connections to the same database is recipe for failure. Thus the use of a singleton. – Teodor

See this thread, which seems to support my theory: #2493831 – Teodor