Android SQLite Query, Insert, Update, Delete, Always Need to be On Background Thread?

Question

I currently use Loaders to grab data from my ContentProvider (to enable auto-updating of my Cursors). This approach is straight-forward for Querying the database, though, it seems ill suited for any other DB operation (such as Insert, Update, Delete).

My questions are:

Do all SQLite operations need to be on a background thread, or is it safe to do simple operations like Inserting, Updating, or Deleting a single row on the UI thread?
What is a nice design patter to ensure all queries go through a background thread? I would like to implement AsyncTask, should I create a SuperTask so to speak that extends AsyncTask and Executes each SQLite operation? (Bonus: Can you provide bare-bones example?)

We should use background thread for bulk operations..... Inserting, Updating, or Deleting a single row can be done on UI thread. — dd619, May 07 '16 at 08:11

score 6 · Answer 1 · answered Mar 12 '14 at 02:27

6

I have done SQLite operations on my UI Thread. I guess the question really becomes whether your queries will ever take a long time or not. I've never had my application crash from taking too long to execute SQL calls on my SQLite database.

With that said, if you plan on writing complex queries that can take time to load you would want to run it as an AsyncTask or Thread and use callbacks to update your UI if need be.

This is a great tutorial on SQLite on Android (It also addresses some of the complex sql timing issues you were talking about): http://www.vogella.com/tutorials/AndroidSQLite/article.html

answered Mar 12 '14 at 02:27

Thank you for the reply. I guess, this could get messy pretty quickly because you need to either define a new AsyncTask for each type of query, or you need to add switch statements to a master AsyncTask to handle everything, is that typically how this works when doing background threading in AsyncTask? – AutoM8R Mar 13 '14 at 00:09
You don't need a specific Task for each query but you can create a specific Task to handle all database requests. Therefore it runs in the background of your activity that makes a bunch of queries and schedules queries and callbacks on the actual AsyncTask. In the background you can continuously take a hook into your database using BlockingQueue to provide a call to your DB with a command. On your Thread UI you could just add a command to the queue – Mar 13 '14 at 00:42

score 5 · Answer 2 · edited May 23 '17 at 12:08

All SQLite operations do not need to be on a background, but should be. Even simple row updates can impact the UI thread and therefore application responsiveness.
Android includes the AsyncQueryHandler abstract class:

A helper class to help make handling asynchronous ContentResolver queries easier.

Here are two example implementations from Using AsyncQueryHandler to Access Content Providers Asynchronously in Android. A member class:

class MyQueryHandler extends AsyncQueryHandler {

    public MyQueryHandler(ContentResolver cr) {
        super(cr);
    }

    @Override
    protected void onQueryComplete(int token, Object cookie, Cursor cursor) {
        // query() completed
    }

    @Override
    protected void onInsertComplete(int token, Object cookie, Uri uri) {
        // insert() completed
    }

    @Override
    protected void onUpdateComplete(int token, Object cookie, int result) {
        // update() completed
    }

    @Override
    protected void onDeleteComplete(int token, Object cookie, int result) {
        // delete() completed
    }
}

An anonymous class:

AsyncQueryHandler queryHandler = new AsyncQueryHandler(getContentResolver()) {
    @Override
    protected void onQueryComplete(int token, Object cookie, Cursor cursor) {

        if (cursor == null) {
            // Some providers return null if an error occurs whereas others throw an exception
        }
        else if (cursor.getCount() < 1) {
            // No matches found
        }
        else {

            while (cursor.moveToNext()) {
                // Use cursor
            }

        }
    }
};

Further details:

Android SQLite Query, Insert, Update, Delete, Always Need to be On Background Thread?

2 Answers2

Linked