When should I close an SQLiteDatabase object?

Question

I kept getting this annoying runtime error for hours, which crashed my app:

java.lang.RuntimeException: An error occured while executing doInBackground().

Caused by: java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase

After some debugging I found that it happens because I close my SQLiteDatabse object, in the onDestory() method. It happens when I call SQLiteOpenHelper.close() as well.

@Override
protected void onDestroy() {
    super.onDestroy();
    _cursor.close(); //is fine
    _db.close(); //causes error
    _databaseHelper.close(); //causes error too (probably calls db.close() internally..?)
    SharedFunctions.d("closed!"); //ignore this ugly thing
}

This brings up the two questions

1. Am I doing it right? (probably not)
2. When do I need to close a SQLiteDatabase object, if not in the onDestroy method?

Edit: The DB and the Helper classes are static:

public class MainActivity extends Activity {

    private Cursor _cursor = null;
    private MyCursorAdapter _myCursorAdapter = null;
    private ListView _listView = null;

    private static SalaryDatabaseHelper _databaseHelper = null;
    public static SQLiteDatabase db = null;

    ...

I initialize _databaseHelper in the onCreate() method:

//get database helper
        if(_databaseHelper == null)
            _databaseHelper = SalaryDatabaseHelper.getInstance(this);

db is initialized in an AsyncTask.doInBackground():

protected Boolean doInBackground(Integer... data)
{
    try {
        //get writable database
        if(db == null)
            db = SalaryDatabaseHelper.getDbInstance();

I use singletons for the helper class and the database class: (both accessed via the helper class)

class MyDatabaseHelper extends SQLiteOpenHelper{

    private static SalaryDatabaseHelper _instance = null;
    private static SQLiteDatabase _dbInstance = null;

    //singletons
    public static synchronized SalaryDatabaseHelper getInstance(Context context)
    {
        // Use the application context, which will ensure that you
        // don't accidentally leak an Activity's context.
        if (_instance == null)
            _instance = new SalaryDatabaseHelper(context.getApplicationContext());

        return _instance;
    }

    public static synchronized SQLiteDatabase getDbInstance() {
        if(_dbInstance == null)
            _dbInstance = _instance.getWritableDatabase();

        return _dbInstance;
    }

    ...

Answer 1

Your SQLiteOpenHelper instance is static, and therefore global in scope. With that in mind:

Am I doing it right? (probably not)

No.

When do I need to close a SQLiteDatabase object, if not in the onDestroy method?

Never. SQLite is transactional. There is no risk from failing to close the database.

_{Yes, this irks me too, but I have gone through the seven stages of grief over this, and I am on to "acceptance"}

In simpler scenarios, where there is a single component with access to the database, you might close it when that component is destroyed. In your case, your whole app, including background threads, have access to the database. In that case, you simply never close it.