SQLite更改数据库中项目的排序顺序-SQLiteChangeSortOrderforitemsondatabase

So imagine this is a quick mockup of my database:

所以想象一下这是我的数据库的快速模型：

The items from the database are presented to the user per list, each list being displayed on a new fragment, which are displayed on a viewpager. So let's say in this hypotetical case, there would be two fragments on the viewpager, first fragment would display first_list and second fragment would display second_list. Here's the code for that query:

来自数据库的项目按列表呈现给用户，每个列表显示在新片段上，该片段显示在viewpager上。所以让我们说在这个hypotetical案例中，viewpager上会有两个片段，第一个片段显示first_list，第二个片段显示second_list。这是该查询的代码：

 public static Cursor getListItems (final Context context, String listName) {
    if (mDatabase == null || !mDatabase.isOpen())
        open(context);  //This gets the writable db.

    String where = LIST_NAME + " = '" + listName + "'";
    return mDatabase.query(TABLE_LIST_ITEMS, PROJECTION_LIST_ITEMS, 
    where, null, null, null, SORT_ORDER);
}

Where SORT_ORDER is order_in_list, this works well, to begin with.

在SORT_ORDER是order_in_list的地方，这很有效，首先。

Now, the listviews are re-arrangeable using a public library, which attempts to allow the user to control the order of the items in each list. Here's where I am having issues, there is no add(int index, Object object) for the cursor, or some other easy way to manage the sorting. I first thought I could simply call mDatabase.update() to change the value for order_in_list but that works, but the results are not as intended. For example, user drags item two to position zero, remeber: zero-index values, we would now have two items with order_in_list as zero. And although I can call mDatabase.update() on item one to update his position to one, imagine how much work that'd be to handle several items on a well-formed database.

现在，可以使用公共库重新排列列表视图，该公共库试图允许用户控制每个列表中项目的顺序。这是我遇到问题的地方，没有为游标添加（int索引，Object对象），或者其他一些简单的方法来管理排序。我首先想到我可以简单地调用mDatabase.update（）来更改order_in_list的值，但这样可行，但结果并非如预期的那样。例如，用户将第二项拖到零位，记住：零指数值，我们现在有两个项目，order_in_list为零。虽然我可以在第一项上调用mDatabase.update（）来将其位置更新为一，但想象一下在一个格式良好的数据库上处理多个项目需要做多少工作。

Does anyone have any good suggestions on how I could work this out? I thought I had been clever by adding the extra col for sorting purposes :(

有没有人对如何解决这个问题有任何好的建议？我认为通过添加额外的col用于分类目的我很聪明:(

INB4:

INB4：

Yes, I Know arrays handle this well. But the database doesn't only store 4 cols, it has many more fields. Populating arrays each time from the database would be a waste of time and effort. And I would, anyways, have to write back to the database when the app is closed.

是的，我知道数组处理得很好。但是数据库不仅存储4个cols，还有更多的字段。每次从数据库填充数组都是浪费时间和精力。无论如何，当应用程序关闭时，我会写回数据库。

EDIT So I changed the listview to only display one String of text, and further columns upon actual clicking on the item (and therefore displaying a new fragment with the specified list item data). This allowed me to simply keep an ArrayAdapter which easily handles the drag and drop. During onStop, I update the reference only if there was a change that required to be saved:

编辑因此我将listview更改为仅显示一个文本字符串，并在实际单击该项目时显示更多列（因此显示具有指定列表项数据的新片段）。这让我可以简单地保留一个可以轻松处理拖放的ArrayAdapter。在onStop期间，只有在需要保存更改时才更新引用：

@Override
public void onStop() {
    if (updateDbOnExit) {
        //Update rows on database.
        for (int i = 0; i 

 public static void moveTaskItemTo (final Context context, String item, int to) {
    if (mDatabase == null || !mDatabase.isOpen())
        open(context);  

    String where = COL_CONTENT + " = '" + item+ "'";

    ContentValues mCOntentValues= new ContentValues();
    mContentValues.put(ORDER_IN_LIST, to);  
    int rows = mDatabase.update(TABLE_LIST_ITEMS, mContentValues, where, null);

    Constants.LogMessage(rows + " row updated. Item moved to position: " + to);

    close();
}

0.00 cat
1.00 aardvark
2.00 wolf
3.00 dog