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
        }
    }
}

Serriform

Answer 1

You mentioned that you cancel the AsyncTask before closing the DB. But you should keep in mind that cancelling the AsyncTask just signals the task to be cancelled and you need to check isCancelled() in doInBackground() and do the needful to stop DB operations.

Before closing the DB, you then need to check getStatus() to be sure that the AsyncTask has stopped.

Haroun

So basically I should use while(!isCancelled){} in doInBackground and perform all calculation inside the loop? – Serriform

Yes, and check getStatus() in your main thread to be sure that the AsyncTask has ended. – Haroun

OK but what if I check it in onStop() and it hasn't ended? Where would I close DB connection then? – Serriform

For an AsyncTask, it is recommended that you should exit doInBackground as early as possible when a cancel signal has been raised. So, within the while loop in doInBackground, whenever you are about to execute a DB statement, check for isCancelled() and break from there if it is true. In the onStop() on the main thread, you should loop with sleep and wait for the AsyncTask to complete before closing the DB connection. – Haroun

Answer 2

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.

Characterize

One more question...where would you close DB connection? – Serriform

I don't close it. Wonder if it's really necessary. If you can find more details on this please share, it's interesting. Thought I never hand any problems with this approach. – Characterize

Thanks for getting beck to me. I will try to investigate this bit more – Serriform

If you are still interested, I have seen the connection being closed in the onTerminate method of Application. – Wilheminawilhide

"This method is for use in emulated process environments. It will never be called on a production Android device, where processes are removed by simply killing them; no user code (including this callback) is executed when doing so." from API documentation – Characterize

if some want this example with full code just go here... ashwinrayaprolu.wordpress.com/2011/03/15/… – Benedetto

Answer 3

It is good to use a single DB Helper. The problem is that when the user leaves the Activity the DB is closed but the AsyncTask may still run. So you should check that the DB is not null when you are trying to access it, and if it is null this may mean that your Activity was destroyed and cancel that task.

Bombay

Thanks for the fast response. I forgot to mention that I have also got few Exceptions saying 'unable to close due to unfinalised statements'. That probably means that I try to close the DB connection in onStop() while the query is executing in doInBackground(). I cancel the AsyncTask before clsoing the DB but I still get this error. Any ideas why it's happening? – Serriform

@Ovidiu Where do you usually put the DB initialization and cleanup code? (Suppousing there's a DBHelper being accesed by several views). – Quinque

as @boulder said it may be a good practice to keep it in Application – Bombay