Closing the database in a ContentProvider

This week I've been learning all about ContentProvider and using the SQLiteOpenHelper class to manage the creation and upgrading of the database inside of a provider. Specifically, I've been reading through the NotePad example from the sdk's samples directory.

Now, I can see that SQLiteOpenHelper has a close() method. I'm aware that leaving idle databases open is bad practice and can cause memory leaks and whatnot (unless this discussion is headed in the right direction). If I were using the class in an Activity, then I would simply call close() in the onDestroy() method, but as far as I know, ContentProvider does not have the same life cycle that activities do. The code for NotePad never seems to call close(), so I would like to assume that it is handled by SQLiteOpenHelper or some other piece of the puzzle, but I'd really like to know for sure. I don't really trust the sample code that much, either...

Question summary: When should we close the database in a provider, if at all?

According to Dianne Hackborn (Android framework engineer) there is no need to close the database in a content provider.

> A content provider is created when its hosting process is created, and > remains around for as long as the process does, so there is no need to > close the database -- it will get closed as part of the kernel > cleaning up the process's resources when the process is killed.

Thanks @bigstones for pointing this out.

This question is a bit old but is still quite relevant. Note that if you're doing things the 'modern' way (e.g. using LoaderManager and creating CursorLoaders to query a ContentProvider in a background thread), make sure that you do NOT call db.close() in your ContentProvider implementation. I was getting all sorts of crashes relating to CursorLoader/AsyncTaskLoader when it tried to access the ContentProvider in a background thread, which were resolved by removing the db.close() calls.

So if you're running into crashes that look like this (Jelly Bean 4.1.1):

Caused by: java.lang.IllegalStateException: Cannot perform this operation because the connection pool has been closed.
	at android.database.sqlite.SQLiteConnectionPool.throwIfClosedLocked(SQLiteConnectionPool.java:962)
	at android.database.sqlite.SQLiteConnectionPool.waitForConnection(SQLiteConnectionPool.java:677)
	at android.database.sqlite.SQLiteConnectionPool.acquireConnection(SQLiteConnectionPool.java:348)
	at android.database.sqlite.SQLiteSession.acquireConnection(SQLiteSession.java:894)
	at android.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:834)
	at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:62)
	at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:143)
	at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:133)
	at android.content.ContentResolver.query(ContentResolver.java:388)
	at android.content.ContentResolver.query(ContentResolver.java:313)
	at com.hindsightlabs.paprika.loaders.GroceryListLoader.loadInBackground(GroceryListLoader.java:147)
	at com.hindsightlabs.paprika.loaders.GroceryListLoader.loadInBackground(GroceryListLoader.java:1)
	at android.support.v4.content.AsyncTaskLoader.onLoadInBackground(AsyncTaskLoader.java:240)
	at android.support.v4.content.AsyncTaskLoader$LoadTask.doInBackground(AsyncTaskLoader.java:51)
	at android.support.v4.content.AsyncTaskLoader$LoadTask.doInBackground(AsyncTaskLoader.java:40)
	at android.support.v4.content.ModernAsyncTask$2.call(ModernAsyncTask.java:123)
	at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:305)
	... 4 more

Or this (ICS 4.0.4):

Caused by: java.lang.IllegalStateException: database /data/data/com.hindsightlabs.paprika/databases/Paprika.db (conn# 0) already closed
	at android.database.sqlite.SQLiteDatabase.verifyDbIsOpen(SQLiteDatabase.java:2215)
	at android.database.sqlite.SQLiteDatabase.lock(SQLiteDatabase.java:436)
	at android.database.sqlite.SQLiteDatabase.lock(SQLiteDatabase.java:422)
	at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:79)
	at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:164)
	at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:156)
	at android.content.ContentResolver.query(ContentResolver.java:318)
	at android.support.v4.content.CursorLoader.loadInBackground(CursorLoader.java:49)
	at android.support.v4.content.CursorLoader.loadInBackground(CursorLoader.java:35)
	at android.support.v4.content.AsyncTaskLoader.onLoadInBackground(AsyncTaskLoader.java:240)
	at android.support.v4.content.AsyncTaskLoader$LoadTask.doInBackground(AsyncTaskLoader.java:51)
	at android.support.v4.content.AsyncTaskLoader$LoadTask.doInBackground(AsyncTaskLoader.java:40)
	at android.support.v4.content.ModernAsyncTask$2.call(ModernAsyncTask.java:123)
	at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:305)
	... 4 more

Or if you're seeing error messages in LogCat that look like this:

Cursor: invalid statement in fillWindow()

Then check your ContentProvider implementation and make sure you're not closing the database prematurely. According to this, the ContentProvider will get cleaned up automatically when the process is killed anyway, so you don't need to close its database ahead of time.

That said, make sure you are still correctly:

1. Closing your Cursors that are returned from ContentProvider.query(). (CursorLoader/LoaderManager does this automatically for you, but if you're doing direct queries outside of the LoaderManager framework, or you've implemented a custom CursorLoader/AsyncTaskLoader subclass, you'll have to make sure you're cleaning up your cursors properly.)
2. Implementing your ContentProvider in a thread-safe way. (The easiest way to do this is to make sure your database access methods are wrapped in a synchronized block.)

Ive follow Mannaz's answer and saw that SQLiteCursor(database, driver, table, query); constructor is deprecated. Then I found getDatabase() method and used it instead of mDatabase pointer; and kept constructor for backward capability

public class MyOpenHelper extends SQLiteOpenHelper {
	public static final String TAG = "MyOpenHelper";
	
	public static final String DB_NAME = "myopenhelper.db";
	public static final int DB_VESRION = 1;

	public MyOpenHelper(Context context) {
		super(context, DB_NAME, new LeaklessCursorFactory(), DB_VESRION);
	}

    //...
}

public class LeaklessCursor extends SQLiteCursor {
	static final String TAG = "LeaklessCursor";
	
    public LeaklessCursor(SQLiteDatabase db, SQLiteCursorDriver driver,
			String editTable, SQLiteQuery query) {
		super(db, driver, editTable, query);
	}

    @Override
    public void close() {
        final SQLiteDatabase db = getDatabase();
        super.close();
        if (db != null) {
            Log.d(TAG, "Closing LeaklessCursor: " + db.getPath());
        	db.close();
        }
    }
}


public class LeaklessCursorFactory implements CursorFactory {
    @Override
    public Cursor newCursor(SQLiteDatabase db, SQLiteCursorDriver masterQuery,
        String editTable, SQLiteQuery query) {
        return new LeaklessCursor(db,masterQuery,editTable,query);
    }
}

If you want your Database to close automatically you can provide a CursorFactory when opening it:

mContext.openOrCreateDatabase(DB_NAME, SQLiteDatabase.OPEN_READWRITE, new LeaklessCursorFactory());

Here are the classes:

public class LeaklessCursorFactory implements CursorFactory {
    @Override
    public Cursor newCursor(SQLiteDatabase db, SQLiteCursorDriver masterQuery,
		String editTable, SQLiteQuery query) {
	    return new LeaklessCursor(db,masterQuery,editTable,query);
    }
}


public class LeaklessCursor extends SQLiteCursor {
	static final String TAG = "LeaklessCursor";
	final SQLiteDatabase mDatabase;

	public LeaklessCursor(SQLiteDatabase database, SQLiteCursorDriver driver, String table, SQLiteQuery query) {
		super(database, driver, table, query);
		mDatabase = database;
	}

	@Override
	public void close() {
		Log.d(TAG, "Closing LeaklessCursor: " + mDatabase.getPath());
		super.close();
		if (mDatabase != null) {
			mDatabase.close();
		}
	}
}

Close it when you are done with it, preferably in a finally block so you can ensure that it happens. I know that sounds a little trite and off-the-cuff, but it's really the only answer that I know of. If you open the database and perform an action, close it when you're done with that action unless you know for a fact it will be needed again (in which case be sure to close it once its no longer needed).

If you are using your content provider within a activity, then I do not believe that you have to maintain the connection of the content provider. You could just manage the cursor object returned using startManagingCursor. In the onPause method of activity, you can release the content provider. ( you can reload it in onResume). Assuming that the activity life cycle will usually be limited, this would suffice. (Atleast according to me ;))