Managing database connections in an Android Activity

Question

I have an application with a ListActivity that uses a CursorAdapter as its adapter. The ListActivity opens the database and does the querying for the CursorAdapter, which is all well and good, but I am having issues with figuring out when to close both the Cursor and the SQLiteDatabase.

The way things are handled right now, if the user finishes the activity, I close the database and the cursor. However, this still ends up with the DalvikVM warning me that I've left a database open - for example, if the user hits the "home" button (leaving the activity in the task's stack), rather than the "back" button.

If I close them during pause and then re-query during resume, then I don't get any errors, but then a user cannot return to the list without it requerying (and thus losing the user's place in the list). By this I mean, the user can click on any item in the list and open a new activity based on it, but will often want to hit "back" afterwards and return to the same place on the list. If I requery, then I cannot return the user back to the correct spot.

What is the proper way to handle this issue? I want the list to remain scrolled properly, but I don't want the VM to keep complaining about unclosed databases.

Edit: Here's a general outline of how I handle the code at the moment:

public class MyListActivity extends ListActivity {
    private Cursor mCursor;
    private CursorAdapter mAdapter;

    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        mAdapter = new MyCursorAdapter(this);
        setListAdapter(mAdapter);
    }

    protected void onPause() {
        super.onPause();
        if (isFinishing()) {
            mCursor.close();
        }
    }

    protected void onDestroy() {
        super.onDestroy();
        mCursor.close();
    }

    private void updateQuery() {
        // If we had a cursor open before, close it.
        if (mCursor != null) {
            mCursor.close();
        }
        MyDbHelper dbHelper = new MyDbHelper(this);
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        mCursor = db.query(...);
        mAdapter.changeCursor(mCursor);
        db.close();
    }
}

updateQuery() can be called multiple times because the user can filter the results via menu items (I left this part out of the code, as the problem still occurs even if the user does no filtering).

Again, the issue is that when I hit home I get leak errors. Yet, after going home, I can go back to the app and find my list again - cursor fully intact.

Answer 1

Close the Cursor and SQLiteDatabase in onDestroy(), for those things not already closed, and see if that helps. There may be scenarios where isFinishing() is false in onPause(), yet onDestroy() still winds up being called.

Answer 2

I usually have one SQLiteOpenHelper per app:

public class MyApp extends Application {

 private static MyDbHelper dbHelper;

 @Override
 public void onCreate() {
  super.onCreate();
  dbHelper = new MyDbHelper(this);
 }

 @Override
 public void onTerminate() {
  super.onTerminate();
  dbHelper.close();
 }

 public static SQLiteDatabase getDB() {
  return dbHelper.getWritableDatabase();
 }

}

Haven't had any problems with this approach so far.
And you can use startManagingCursor(..) available in Activity subclasses (though I let Adapters manage Cursors):

public class SomeAdapter extends CursorAdapter {

    public void setNewFilter(CharSequense query){
        // in fact, I use a DAO here
        Cursor c = MyApp.getDB().query(..);
        changeCursor(c);
    }

}