Android database connections and cursors oh my

Question

I have read plenty of blogs and tutorials on how to create and use database connections when working with android. Although I have plenty of working examples, different implementations result in different issues.

Example, I use a datasource class, Datasource and a database helper class, DBManagement.

DataSource

public class DataSource {
    // Database fields
    private SQLiteDatabase database;
    private DBManagement dbHelper;

    public SMSDataSource(Context context) {
        dbHelper = new DBManagement(context);
    }

    public void open() throws SQLException {
        if(database == null){
             database = dbHelper.getWritableDatabase();
        }
    }

public Cursor exampleCursor(long constraint){
    Cursor cur = database.query(DBManagement.TABLE_NAME,
            new String[] {DBManagement.Column}, "constraint="+constraint, null, null, null, null);
    return cur;
}
    //.. other methods down here which do rawQuery, QueryBuilder, etc.. 

DBManagement

public class DBManagement extends SQLiteOpenHelper{

    // .. table definitions and columns etc ..//

    public DBManagement(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);      
    }

In my onCreate methods within activity, I will call datasource.open() and the SQL connection is open. After that I will do:

DataSource datasource = new DataSource();

Cursor cursor = datasource.exampleCursor(1);
startManagingCursor(cursor);

If I navigate to a new activity, I get the following error:

 06-27 21:59:14.812: E/Database(13396): close() was never explicitly called on database '/data/data/com.example.package/databases/db.db' 

If i add datasource.close(); to the end of onCreate, none of the simple cursor adapters work, or I get errors that the db is not open if perform an action on a conextual menu.

What is the best way to handle the above?

So I did the following, and I am still getting the database issue:

@Override
public void onBackPressed() {
    // do something on back.
    //Log.i(getClass().toString(), "onBackPressed");

    datasource.close();

    finish();
    return;
}

@Override
protected void onResume(){
    super.onResume();
    onCreate(null);
}


@Override
protected void onRestart(){
    datasource = new DataSource(this);
    datasource.open();

    filters = datasource.getFilterCursor();
    startManagingCursor(filters);

    super.onRestart();
}

@Override
protected void onPause(){
    //Log.i(getClass().toString(), "onPause");
    ((CursorAdapter) adapter).getCursor().close();
    datasource.close();
    super.onPause();
}   
@Override
protected void onStop(){
    //Log.i(getClass().toString(), "onStop");
    datasource.close();
    super.onStop();
}

My Datasource.java class has the following:

public Datasource(Context context){
     dbHelper = new DBManagement(context);
}

public void open() throws SQLException {
    if( database == null ){
           database = dbHelper.getWritableDatabase();
    }
}

public void close(){
    if(dbHelper != null){
         dbHelper.close();
    }
}

Answer 1

This is actually very simple:

• explicitly close all your cursors when done (using finally), etc.
• don't use startManagingCursor()
• make your database wrapper/helper/manager/whatever class a singleton
• don't explicitly call close() on the database helper. I will be automatically closed when your process dies.

Additionally: frequent opening and closing is definitely a bad idea, since it carries quite a lot of overhead.

Using loaders is also an option. You definitely don't need to use a content provider to use a loader however it is not as straightforward as it could be. Using an content provider involves IPC which is usually overkill if you don't plan to export your data to other apps.

Answer 2

I would insist to close the DB connection as soon as you complete your fetching of data from your Database. Also the best would be to open and close again inside onResume() and onPause().

Answer 3

I remember having a similar issue and I just recreated errors whenever I call getWritableDatabase() in a new Activity without closing the old reference. When I close the SQLiteDatabase object database and SQLiteOpenHelper object dbHelper I no longer receiver these errors.

public void close() {
    // Check against the database being created but not opened, close writable db
    if(database != null) {
        database.close();
        database = null;
    }

    // In case someone calls DataSource.close() more than once...
    if(dbHelper != null) {
        dbHelper.close();
        dbHelper = null;
    }
}

Hope that helps!

Answer 4

The "ideal" solution to your problem is to transition to Content Providers and Loaders, and use the v4 compability library for backward compatibility. Doing so, solves this problem, since you no longer care about opening and closing the database connection, and you get benefit of doing the database operations in the background and not on the UI thread.

It also future proofs your application, since startManagingCursor is deprecated. It can still be used for now, even in 4.1, but it will be removed at some point.

I have another post on using Content Providers here, that goes into other reasons to use provides, and provided a link to a tutorial.

I think the biggest failure, on Google's part for ContentProvider, is that that don't provide an easy and intuitive way to construct them.

Answer 5

If you open your database in onCreate then you can close on onDestroy.