Android: How do I effectively update the SQLite database when dragging and dropping items in the RecyclerView?

Issue

I have this to-do-list app where "Tasks" can contain their own list of "SubTasks". The problem is whenever these "Tasks" get rearranged via drag and drop, it's sometimes giving me odd behavior like crashing when clicking on them to bring up their sublist.

This here is the code for the Drag and Drop listener:

private void attachItemTouchHelperToAdapter() {
    ItemTouchHelper.SimpleCallback touchCallback =
            new ItemTouchHelper.SimpleCallback(ItemTouchHelper.UP | ItemTouchHelper.DOWN,
                    ItemTouchHelper.LEFT | ItemTouchHelper.RIGHT) {
                @Override
                public boolean onMove(RecyclerView recyclerView,
                                      RecyclerView.ViewHolder viewHolder,
                                      RecyclerView.ViewHolder target) {
                    final int fromPosition = viewHolder.getAdapterPosition();
                    final int toPosition = target.getAdapterPosition();
                    rearrangeTasks(fromPosition, toPosition);

                    // Call background thread to update database.
                    new UpdateDatabaseTask(mTaskList).execute();
                    return true;
                }

                @Override
                public void onSwiped(RecyclerView.ViewHolder viewHolder, int direction) {
                    int position = viewHolder.getAdapterPosition();
                    Task taskToDelete = mTaskAdapter.mTasks.get(position);
                    TaskLab.get(getActivity()).deleteTask(taskToDelete);
                    updateUI(position);
                }
            };

    ItemTouchHelper itemTouchHelper = new ItemTouchHelper(touchCallback);
    itemTouchHelper.attachToRecyclerView(mTaskRecyclerView);
}

Code to rearrange the list that is called within onMove:

private void rearrangeTasks(int fromPosition, int toPosition) {
    if (fromPosition < toPosition) {
        for (int i = fromPosition; i < toPosition; i++) {
            Collections.swap(mTaskList, i, i + 1);
        }
    } else {
        for (int i = fromPosition; i > toPosition; i--) {
            Collections.swap(mTaskList, i, i - 1);
        }
    }
    mTaskAdapter.notifyItemMoved(fromPosition, toPosition);
}

AsyncTask that is called to update the sqlite database:

 private class UpdateDatabaseTask extends AsyncTask<Void, Void, Void> {
    private List<Task> mTaskList;

    public UpdateDatabaseTask(List<Task> taskList) {
        mTaskList = taskList;
    }

    @Override
    protected Void doInBackground(Void... voids) {
        TaskLab.get(getActivity()).updateDatabase(mTaskList);
        return null;
    }

    @Override
    protected void onPostExecute(Void aVoid) {

    }
}

Database helper function that gets called to update the whole table with the rearranged list:

public void updateDatabase(List<Task> tasks) {
    mDatabase.delete(TaskTable.NAME, null, null);
    for (Task task : tasks) {
        addTask(task);
    }
}

The problem here is that sometimes, when rapidly dragging and dropping the "Task", the AsyncTask isn't updating the database fast enough, thus giving me odd behaviors when I try to perform functions on it before it finishes updating.

What is a better way to update the SQLite database while the RecyclerView is dragging and dropping?

Solution

This question is too broad to answer as there are several solutions that you can consider in solving your problem. I can think of two now and here I am going with a brief description of them.

Solution #1

You might consider call the database method of updating the table directly without having it called via an AsyncTask. Register a content observer with your database table so that each time you update the table the RecyclerView gets updated along with it without having notifyDataSetChanged() called. The sample implementation should look like the following.

Declare the URI of your database table like this.

public static final Uri DB_TABLE_TASKS_URI = Uri
            .parse("sqlite://" + Constants.ApplicationPackage + "/" + DB_TABLE_TASKS);

Now in your updateDatabase function you need to call this notify function to notify the observers listening to this content when something is your tasks table has changed or updated.

public static void updateDatabase(List<Task> taskList) {
    // .. Your other implementation goes here 
    context.getContentResolver().notifyChange(DBConstants.DB_TABLE_TASKS_URI, null);
}

Now use the LoaderCallbacks to show the tasks from your sqlite table in your RecyclerView.

public class TasksActivity extends Activity implements LoaderManager.LoaderCallbacks<Cursor> {
    private final TAKS_QUERY_LOADER = 0;

    protected void onCreate(Bundle savedInstanceState) {
        // Other code. 

        // Get your cursor loader initialized here. 
        getLoaderManager().initLoader(TASKS_QUERY_LOADER, null, this).forceLoad();
    }
}

Now you need to implement the functions of your LoaderCallbacks in your TasksActivity. One sample implementation should look like this.

@Override
public Loader<Cursor> onCreateLoader(int id, Bundle args) {

    return new SQLiteCursorLoader(getActivity()) {
        @Override
        public Cursor loadInBackground() {

            Cursor cursor;
            cursor = TasksLab.getTasks();

            // Register the cursor with the content observer
            this.registerContentObserver(cursor, DBConstants.DB_TABLE_TASKS_URI);
            return cursor;
        }
    };
}

@Override
public void onLoadFinished(Loader<Cursor> loader, Cursor data) {
    // Set the cursor in the adapter of your RecyclerView here. 
}

@Override
public void onLoaderReset(Loader<Cursor> loader) {

}

// Do not forget to destroy the loader when you are destroying the view.
@Override
public void onDestroy() {
    getLoaderManager().destroyLoader(TASKS_QUERY_LOADER);
    super.onDestroyView();
}

This is it. You are now capable of updating your task instantly in your sqlite table and get the reflection in your RecyclerView instantly. I would highly recommend you update a single task at a time. As I could see, you are updating the database table using updateDatabase function passing the whole list of tasks here. I would recommend passing only the task id which one was dragged or updated, so that the table gets updated instantly.

Solution #2

This solution is simpler than the previous one. The idea is to update your sqlite database on refreshing the list or on destroying the view.

When a task is dragged in some place (i.e. updated its position) or deleted, you need to keep the track in a ArrayList somewhere in your TasksActivity. Do not update the sqlite table in your database instantly, or using your AsyncTask. Save this task for later when you are destroying your view in your onDestroy or onPause method. In this process you need to refresh your RecyclerView each time when your Activity gets resumed (i.e. in onResume function). So here's the pseudo implementation goes.

@Override
public boolean onMove(RecyclerView recyclerView,
                      RecyclerView.ViewHolder viewHolder,
                      RecyclerView.ViewHolder target) {
    final int fromPosition = viewHolder.getAdapterPosition();
    final int toPosition = target.getAdapterPosition();
    rearrangeTasks(fromPosition, toPosition);

    // Do not call background thread to update database.
    // new UpdateDatabaseTask(mTaskList).execute();

    // Keep a local ArrayList and keep the track of dragging the item. 
    updateLocalArrayListWhichIsBindToRecyclerView(mTaskList);
    return true;
}

@Override
public void onSwiped(RecyclerView.ViewHolder viewHolder, int direction) {
    int position = viewHolder.getAdapterPosition();
    Task taskToDelete = mTaskAdapter.mTasks.get(position);
    // TaskLab.get(getActivity()).deleteTask(taskToDelete);
    updateLocalArrayListWhichIsBindedToRecyclerView(taskToDelete);
    updateUI(position);
}

Now when you are leaving the TaskActivity you need to save the changes in your database finally.

@Override
public void onDestroy() {
    updateDatabaseFromTheList();
    super.onDestroyView();
}

@Override
public void onPause() {
    updateDatabaseFromTheList();
    super.onDestroyView();
}

And load the tasks each time from your database inside the onResume function.

@Override
public void onResume() {
    super.onResume();
    updateRecyclerViewFromDatabase();
}

Update

Based on clarification required for the Solution #1 in the comment that said.

"I would recommend passing only the task id which one was dragged or updated, so that the table gets updated instantly. ". How exactly do I do this?

I am not sure about how you are updating your tasks table right now. However, I can think of some implementation of my own. Just pass the task that you want to be updated its position in the tasks table. So the pseudo implementation might look like the following.

public class Task {
    public int taskId;
    public int taskPosition; 
}

public void updateDatabase(Task taskToBeUpdated, int newPosition) {

     // You need two update queries. 
     // Update task_table set task_position += task_position + 1 where task_position >= newPosition;
     // Update task_table set task_position = newPosition where taskId = taskTobeUpdated.taskId
}

Hope that helps.

Answered By - Reaz Murshed