Correctly open/close a database with Singleton design pattern

Question

I am creating an application which makes a lot of interactions with a database (both read and write operations).

To avoid open/close operations at each request, I created a class extending SQLiteOpenHelper with a Singleton design pattern. This way, I am sure only one instance of the SQLiteOpenHelper and only one connection to the database is made during all the application lifecycle (and not only activity lifecycle).

I also read some articles about ContentProvider, but I am not sure it's a better way.

So, this is the main logic of my Singleton class (onCreate and onUpgrade removed) :

public final class BaseSQLite extends SQLiteOpenHelper {

    private static BaseSQLite mInstance = null;

    private SQLiteDatabase db = null;

    public static BaseSQLite getInstance(Context context) {
        if (mInstance == null) {
            mInstance = new BaseSQLite(context.getApplicationContext(),
                    DBNAME, DBVERSION);
        }
        return mInstance;
    }

    private BaseSQLite(final Context context, final String name,
            final int version) {
        super(context, name, null, version);
        db = getWritableDatabase();
    }

    @Override
    public synchronized void close() {
        if (mInstance != null)
            db.close();
    }

    public Cursor getAllData() {
        String buildSQL = "SELECT * FROM myTable";

        return db.rawQuery(buildSQL, null);
    }

}

So, to access my database, I made this :

BaseSQLite baseSQLite = BaseSQLite.getInstance(context);
baseSQLite.getAllData();

It works perfectly for now. But my question is about the close() method. I really don't know when to call it. Actually, my database instance is the same for every Activies of my application, so I think it's a bad idea to call close() in an onPause() method, because the instance will be potentially (and it will often happens) recreated in the onStart() method of the next Activity. Also, I can't detect the end of my application, i.e. when no activity is visible on the screen anymore.

Can somebody give me some help about this issue ? I found some answer when the database is linked to ONE activity, but no really hint is given for my case.

Answer 1

You should call close anytime you are done writing to your database. For example when you insert data, you will have an open connection to the database that should be closed when it is done.

Reading is different. When you create a SQLite database on your phone, the data is persistent. The database exists and the handler you create provides a convenient way to access that information. Reading the database usually takes place by getting a readable instance of the database and using a Cursor to extract values. In that case you close the cursor when you're done, not the database itself.

You're right that you should not be closing the database connection during separate activities' lifecycle methods. Instead, as suggested above, close the database connection in your handler's methods that write to the database when you are done performing that transaction.