How to correctly and appropriately access the SQLite database form another thread - Android

Question

Correct approach that should be taken to access a database.

So I have read numerous forums on how to access the SQLite database (via SQLiteOpenHelper) - with different answers. Essentially, I have two problems (questions):

• 1. Access write / or read data from the database on a thread that is not the UI thread. Do I use AsyncTask, Background service, or what? I am merely trying to read or write from the database, and display the result in the UI.

     • 2. Should simple read / write operations be executed within another thread? If so, does the other thread type used differ from the one used in question 1?

          public class ReadUsers extends AsyncTask<Void, Void, List<User>> {
              public void doInBackground(Void aVoid) {
                  // Query the database here.
              }
          
              public void onPostExecute(List<User> users) {
                  // Display the data obtained here.
              }
          }
          

Answer 1

1) I think an AsyncTask would work, but I don't have a sample of that available. For SQLite DB read/writing, I almost always use a Runnable (see example below).

2) Any database operation should always be done in another thread, because there is a very real possibility that your query will take long enough to execute that your app will fail on being unresponsive on the UI thread (like 3 seconds?). The key is just to not block the UI thread. But, then you have to have a plan for when the background task finishes, how do you update the UI? See the runOnUIThread instruction in the example below.

Here is an example, first how to call your method:

        final Runnable searchWords = new Runnable() {
             public void run() {
                 readWords();
                }
           };

        Thread thread = new Thread(null, searchWords, "MagentoBackground");
        thread.start(); 

Then the method:

public void readWords() {

    Edit.listOfWords = new ArrayList<Word>();

    try {

        myDbHelper.openDataBase();

    }catch(SQLException sqle){

        throw sqle;

    }

    Cursor c = null;

    try{

        boolean containsPct = mSearch.contains("%");

        if (containsPct) {
            c = myDbHelper.searchEnableWords(mSearch);  
        } else {
            String sLength = "" + mSearch.length();
            c = myDbHelper.getEnableWords(mSearch, sLength);                    
        }

        if (c.moveToFirst())
        {

            do { 

                Word word = new Word();

                word.word = c.getString(0);
                word.length = word.word.length();
                word.value = wordValue(word.word);
                Edit.listOfWords.add(word);

            } while (c.moveToNext());
        }

        if (c != null) {
            if (!c.isClosed()) {
                c.close();
            }
        }


    } catch (Throwable t){
        Log.e("Word Assist",t.getMessage(),t);

        if (c != null) {
            if (!c.isClosed()) {
                c.close();
            }
        }

    }

    try {

        myDbHelper.close();

    }catch(SQLException sqle){

        throw sqle;

    }

    Collections.sort(Edit.listOfWords, new WordComparator());
    adapter = new WordAdapter(this, Edit.listOfWords);


    runOnUiThread(returnRes);

}

Then do what you need to on the UI Thread:

private Runnable returnRes = new Runnable() {
    @Override
    public void run() {

        mProgressDialog.dismiss();
        list.setAdapter(adapter);

        if (Edit.listOfWords.size() == 0) {
            empty.setVisibility(View.VISIBLE);
        } else {
            empty.setVisibility(View.GONE);
        }


    }

};

Here is an AsyncTask example:

private class GetPetDetails extends AsyncTask<String, Void, String> {
    ProgressDialog dialog;    

    @Override     
    protected void onPreExecute() {   

        dialog = new ProgressDialog(thisContext);         
        dialog.setMessage("Loading, please wait...");         
        dialog.setIndeterminate(true);         
        dialog.setCancelable(false);         
        dialog.show();     


        }

    protected String doInBackground(String... urls) {

        String animal_breed = urls[0];
        String results = "";

        try{

            results = myDbHelper.getBreedDetails(animal_breed);

        } catch (Throwable t){
            Log.e("VCAS",t.getMessage(),t);
        }

        return results;
    }

    protected void onPostExecute(String results) {
        dialog.dismiss(); 

        breedDetails = results;
        displayDetail();



    }

}//