user6419910 user6419910 - 6 months ago 14
Android Question

How do I properly execute database calls on other threads?

I am really confused how I should be using threads in my Android applications for database interaction. There are too many resources and I don't know which to choose from. So I'm hoping to get more specific and focused advice on my particular situation so I have a starting point.

This is my database class structure, which works great so far:

public class DatabaseHelper extends SQLiteOpenHelper {
private static volatile SQLiteDatabase mDatabase;
private static DatabaseHelper mInstance = null;
private static Context mContext;

private static final String DB_NAME = "database.db";
private static final int DB_VERSION = 1;

private static final DB_CREATE_THINGY_TABLE = "CREATE TABLE blahblahblah...";
//other various fields here, omitted

public static synchronized DatabaseHelper getInstance(Context context) {
if (mInstance == null) {
mInstance = new DatabaseHelper(context.getApplicationContext());
try {
mInstance.open();
}
catch (SQLException e) {
e.printStackTrace();
}
}
return mInstance;
}

private DatabaseHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
mContext = context;
}

@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(DB_CREATE_THINGY_TABLE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}

@Override
public void onConfigure(SQLiteDatabase db){
super.onConfigure(db);
db.setForeignKeyConstraintsEnabled(true);
}

public void open() throws SQLException {
mDatabase = getWritableDatabase();
}

public void close() {
mDatabase.close();
}

public long addNewThingy(String name) {
ContentValues values = new ContentValues();
values.put(DatabaseHelper.THINGY_COLUMN_NAME, name);
return mDatabase.insertWithOnConflict(DatabaseHelper.THINGY_TABLE, null, values, SQLiteDatabase.CONFLICT_IGNORE);
}

public Thingy getThingyById(long id) {
Cursor cursor = mDatabase.query(
DatabaseHelper.THINGY_TABLE, // table
new String[]{DatabaseHelper.THINGY_COLUMN_ID, DatabaseHelper.THINGY_COLUMN_NAME}, // column names
DatabaseHelper.THINGY_COLUMN_ID + " = ?", // where clause
new String[]{id + ""}, // where params
null, // groupby
null, // having
null); // orderby
cursor.moveToFirst();
Thingy thingy = null;
if (!cursor.isAfterLast()) {
String name = getStringFromColumnName(cursor, DatabaseHelper.THINGY_COLUMN_NAME);
thingy = new Thingy(id, name);
cursor.moveToNext();
}
cursor.close();
return thingy;
}

}


So any time I want access to the database I do
mDatabaseHelper = DatabaseHelper.getInstance(context);
and I am good to go. I don't make any explicit calls to
open()
or
close()
or anything like that. However right now I am making all my database calls on the UI thread, I believe (either in my onCreate or onCreateView methods or separate methods which don't invoke any new threads or anything).

How would I correctly make this threaded so that I am not performing database operations on the UI thread?

I figure I have to change all my database calls to basically do this:


  1. Make any necessary edits to my database class first to ensure it will work properly in the event that multiple threads are trying to perform operations at the same time. I already tried by making my class a singleton (I think it's a singleton, anyway?) and using keywords like "volatile" and "synchronized" but maybe I am missing something somewhere.

  2. Perform database operation in its own thread.

  3. Somehow trigger additional code back in the appropriate function/activity/fragment that will execute once the database operation has completed.

  4. Make this whole process versatile enough to where I can do it anywhere.



Am I making sense? Is this the right way to be going about all this? Is there a simple example you can make that can show me how to, for example, correctly do something like
mThingy = mDatabaseHelper.getThingyById(id);
or
mDatabaseHelper.addNewThingy(someName);
from a sample activity/fragment/etc using proper threading?

Answer

Simple solution using Threads

public class DatabaseHelper extends SQLiteOpenHelper {
    //...

    public void addNewThingyAsync(final String name, final Callback<Long> cb) {
        new Thread(new Runnable(){
            @Override
            public void run(){
                cb.callback(addNewThingy(name));
            }
        }).start();
    }

    private synchronized long addNewThingy(String name){
        //implementation...
    }

    public void getThingyByIdAsync(final long id, final Callback<Thingy> cb) {
        new Thread(new Runnable(){
            @Override
            public void run(){
                cb.callback(getThingyById(id));
            }
        }).start();
    }

    private synchronized Thingy getThingyById(long id) {
        //implementation...
    }

    public interface Callback<T> {
        public void callback(T t);
    }
}

Solution using AsyncTasks

Same as above with the following changes:

public class DatabaseHelper extends SQLiteOpenHelper {
    //...

    public void addNewThingyAsync(final String name, final Callback<Long> cb) {
        new AsyncTask<Void, Void, Long>(){
            @Override
            protected Long doInBackground(Void... ignoredParams) {
                return addNewThingy(name);
            }

            @Override
            protected void onPostExecute(Long result) {
                cb.callback(result);
            }
        }.execute();
    }

    //...

    public void getThingyByIdAsync(final long id, final Callback<Thingy> cb) {
        new AsyncTask<Void, Void, Thingy>(){
            @Override
            protected Thingy doInBackground(Void... ignoredParams) {
                return getThingyById(id);
            }

            @Override
            protected void onPostExecute(Thingy result) {
                cb.callback(result);
            }
        }.execute();
    }
    //...
}

Calling (works with both approaches)

long mId = ...; 
mDatabaseHelper = DatabaseHelper.getInstance(context);
mDatabaseHelper.getThingyByIdAsync(mId, new Callback<Thingy>{
    @Override
    public void callback(Thingy thingy){
        //do whatever you want to do with thingy
    }
});