Android AsyncTask and SQLite DB instance

Question

I have a problem and I am not sure how to approach it. An activity in my app has multiple AsyncTasks which access single SQLiteOpenHelper. I initialize and open the helper in onCreate() and I am closing it in onStop(). I also check if it has been initialized in onResume().

Since I have published my app I received number of errors with Null Exception in doInBackground where I try to access the DB helper. I know that this is happens because the DB is closed ( onStop() ) just before the doInBackground is called, fair enough.

My question is, where should I close the DB connection? Is it right to use a single instance of the DB helper in the Activity and access it from multiple threads(AsyncTasks)? Or I should use separate DB helper instance for each AsyncTask?

This is a simplified skeleton of my activity:

public class MyActivity extends Activity{
    private DbHelper mDbHelper;
    private ArrayList<ExampleObject> objects;

    @Override
    public void onStop(){
        super.onStop();
        if(mDbHelper != null){
            mDbHelper.close();
            mDbHelper = null;
        }
    }

    @Override
    public void onResume(){
        super.onResume();
        if(mDbHelper == null){
            mDbHelper = new DbHelper(this);
            mDbHelper.open();
        }
    }

    @Override 
    public void onCreate(Bundle icicle) { 
        super.onCreate(icicle); 
        DbHelper mDbHelper = new DbHelper(this);
        mDbHelper.open();
    }

    private class DoSomething extends AsyncTask<String, Void, Void> {

        @Override
        protected Void doInBackground(String... arg0) {
            objects = mDbHelper.getMyExampleObjects();
            return null;
        }

        @Override
        protected void onPostExecute(final Void unused){
            //update UI with my objects
        }
    }

    private class DoSomethingElse extends AsyncTask<String, Void, Void> {

        @Override
        protected Void doInBackground(String... arg0) {
            objects = mDbHelper.getSortedObjects();
            return null;
        }

        @Override
        protected void onPostExecute(final Void unused){
            //update UI with my objects
        }
    }
}

Answer 1

You don't need to manage db connection for each activity. You can do it in an instance of android.app.Application and access db using this instance. Something like this:

public class MyApplication extends Application {

    // Synchronized because it's possible to get a race condition here
    // if db is accessed from different threads. This synchronization can be optimized
    // thought I wander if it's necessary
    public synchronized static SQLiteDatabase db() {
        if(self().mDbOpenHelper == null) {
            self().mDbOpenHelper = new MyDbOpenHelper();
        }
        return self().mDbOpenHelper.getWritableDatabase();
    }

    public static Context context() {
        return self();
    }

    @Override
    public void onCreate() {
        super.onCreate();
        mSelf = this;
    }

    private static MyApplication self() {
        if (self == null) throw new IllegalStateException();
        return mSelf;
    }

    private MyDbOpenHelper mDbOpenHelper;

    private static MyApplication mSelf;
}

This way you can be sure you Db is always accessible.

And yes, having one instance of Db helper is a good practice. Thread syncronisation is made for you by default.