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.

Grannie

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.

Sher

Ok, so I have to remove the line db = getWritableDatabase(), and adding in each method getWritableDatabase() or getReadableDatabase(). Then, in the writing method, like updateEntry(Object) for example, I should make something like : getWritableDatabase(); db.rawQuery(myRequestString, null); db.close() ? – Grannie

if myRequestString is an insert statment - yes. look at this example I'm not suggesting you copy exactly but you can see how the close works within the transaction. The way I do it is to have a method in the db handler class that performs each write function - e.g. add entry to the table. At the start of that method I get a writeable db instance and when the transaction is done I close it at the end of the method. – Sher

Ok. It seems to be a really good option. I worried about the performances making a lot of open/close calls, but apparently it's not really an issue :) – Grannie

While I don't have any documentation for the performance of calling open/close I don't think it will be an issue. The taxing action is not opening/closing the connection itself but the reading/writnig of data - depending on its size. I'd imagine the call to open/close to be negligable. – Sher

"You should call close anytime you are done writing to your databse" no you shouldn't – Tablet

You'd leave the connection open indefinitely? – Sher

That's totally wrong. Please make another search, even google docs says it's better to leave it open and let GC to close it than reopen it on every action. #6608998 – Peggypegma

Granted it was 4 years ago but it's still not clear then why the documentation says this: "Once opened successfully, the database is cached, so you can call this method every time you need to write to the database. (Make sure to call close() when you no longer need the database.)"developer.android.com/reference/android/database/sqlite/… – Sher