如何正确地在其他线程上执行数据库调用?

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.

我真搞不懂如何在Android应用程序中使用线程进行数据库交互。资源太多了，我不知道该选哪个。所以我希望能从我的具体情况中得到更具体、更集中的建议，这样我就有了一个起点。

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).

因此，每当我想访问数据库时，我都会做mDatabaseHelper = DatabaseHelper.getInstance(context);我很乐意去。我不会对open()或close()或类似的任何东西进行任何显式调用。但是现在我正在UI线程上进行所有的数据库调用(在onCreate或onCreateView方法或不调用任何新线程的独立方法中)。

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

如何正确地将这个线程化，从而避免在UI线程上执行数据库操作?

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.

   首先对我的数据库类进行必要的编辑，以确保在多个线程同时尝试执行操作的情况下，它能够正常工作。我已经尝试过将我的类变成单例类(我认为它是单例类，不管怎样?)并使用“volatile”和“synchronized”这样的关键字，但可能我在某些地方漏掉了什么。

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?

能听懂我说的吗?这样做对吗?有没有一个简单的例子可以告诉我如何正确地做一些事情，比如mThingy = mDatabaseHelper.getThingyById(id);或mDatabaseHelper.addNewThingy(someName);使用正确的线程从示例活动/片段/等等中获取吗?

2 个解决方案

#1

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

#2

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

如何正确地将这个线程化，从而避免在UI线程上执行数据库操作?

Simply perform any database operations off the UI thread. A common technique involves an AsyncTask. For example:

只需在UI线程上执行任何数据库操作。一种常见的技术涉及到异步任务。例如:

public class GetThingyTask extends AsyncTask<Long, Void, Thingy> {

    private Context context;

    public AddTask(Context context){
        this.context = context;
    }

    @Override
    protected Thingy doInBackground(Long... ids) {

        final long id = ids[0];

        Cursor cursor = DatabaseHelper.getInstance(context).query(
            DatabaseHelper.THINGY_TABLE,
            new String[]{
                DatabaseHelper.THINGY_COLUMN_ID,
                DatabaseHelper.THINGY_COLUMN_NAME
            },
            DatabaseHelper.THINGY_COLUMN_ID + "=?",
            new String[]{String.valueOf(id)},
            null, null, null);

        String name = null;

        if (cursor.moveToFirst() && (cursor.getCount() > 0)) {
            name = getStringFromColumnName(cursor, DatabaseHelper.THINGY_COLUMN_NAME);
        }

        cursor.close();

        return new Thingy(id, name);

    }

    @Override
    protected void onPostExecute(Thingy thingy) {
        //Broadcast the Thingy somehow. EventBus is a good choice.
    }

}

And to use it (inside, for example, an Activity):

并使用它(例如，在活动内部):

new GetThingyTask(this).execute(id);