Save reordered RecyclerView in SQLite

Question

So I'm trying to implement Drag-and-Drop within my To Do List app, but I am having trouble saving the order of the rows once moved - i.e. I moved the items, exited the app and it reverted back to its original position. I thought of a solution.

My Solution:

Create a column in my table: specified_position. Every time a row is moved, the onItemMove() method returns a fromPosition and toPosition. Move the row's position to toPosition, then increment/update all values there and above. Every time the table is read, it checks the positions and orders them according to it.

My Question:

Is my solution correct? Is there a better, easier even method instead to do this? Thanks a lot!

Answer 1

I had same problem on my To Do List app. Here I share my solution with you.

First I have an updateSortID() function on my Database Helper

public void updateSortID(Integer newID,int rowid)
{
     SQLiteDatabase db = this.getWritableDatabase();
        String strSQL = "UPDATE " +  TABLE_NAME_todos +  " SET " + ITEM_SORT_ID +  "=" + newID + " WHERE " + ID +" = "+ rowid;

        db.execSQL(strSQL);
}

Then on activity or adapter, where you should listen your position change of items, you need to have an function like this. It will update all sort ID's of all items that affected from this position changes.

@Override
public void drop(int from, int to) {
    // TODO Auto-generated method stub

    Log.d("drop", String.valueOf(from));
    Log.d("drop", String.valueOf(to));

    ArrayList<Items> temp = db.getToDos();

    int tempstart = from;
    int tempend = to;

    if (from < to) {
        Log.d("up to down", "yes");
        db.updateSortID(temp.get(tempend).getSortID(), temp.get(tempstart).getID());
        for (int i = from; i <= to - 1; i++) {
            db.updateSortID(temp.get(i).getSortID(), temp.get(i+1).getID());
        }
    } else if (from > to) {
        Log.d("up to down", "no");
        db.updateSortID(temp.get(tempend).getSortID(), temp.get(tempstart).getID());            
        for (int i = from; i >= to + 1; i--) {
            db.updateSortID(temp.get(i).getSortID(), temp.get(i-1).getID());                
        }
    }

    listChanged(); // this is the method I call `notifyDataSetChanged()` method and other related functions
}

Answer 2

As alternative way of answer @Emin Ayar

clearView method of ItemTouchHelper.Callback Class is called only once not exactly same with onMove and onSwiped methods...

onMove and onSwiped are called multiple times...Because of it move or swipe every elements that is affected...

But clearView methods is called only one times...

so in this method, delete every elements on Sqlite and add new list, can be another solution...

 @Override
    public void clearView(RecyclerView recyclerView, RecyclerView.ViewHolder viewHolder) {
        super.clearView(recyclerView, viewHolder);

        db.deleteAllFavoriteNewspapers();

        for(NewspaperClass nc : mAdapter.list){
            db.insertToFavoriteNewspapers(nc);
        }
    }

Tutorial for Drag & Swipe