SQLite:消除后创建新项目时ID错误 [英] SQLite : Wrong ID when i create a new item after an elimination

问题描述

我制作了字段 id autoincrement 并且当我创建一个项目时没有问题.当我删除一个项目时，问题就开始了:只有当数据库中所有项目的 id 都低于我要删除的项目时，才会正确删除该项目.

I made the field id autoincrement and when I create an item there are no problems. The problem starts when i delete an item: the item is correctly deleted only if there are in the database all the items with a lower id compared to the one I want to delete.

这是一个例子:

id       item
-----------------
1        swim
2        tennis
3        football
4        baseball

当我删除棒球时一切正常，但如果我在删除棒球后创建一个新项目，它的 id = 5

When I delete baseball is all ok, but if I create a new item after the elimination of baseball it has the id = 5

例如:

id       item
------------------
1        swim
2        tennis
3        football
5        rugby

所以，现在消除橄榄球需要2次点击(而不是1次)在我将可点击的图像上点击到可点击的回收视图中以删除该项目.发生这种情况是因为 rugby 下的第一个元素的 id = 3 而不是 4.

So, now the elimination of rugby needs 2 clicks (instead of one) on the image I made clickable into a clickable recyclerview in order to delete the item. This happens because the first element under rugby has id = 3 and not 4.

另一个例子:在数据库中，我只有一个 id = 18 的项目，要删除它，我必须点击 18 次，因为 id 在变为 1 时递减，然后被删除.

Another example: in the database I have only a item with id = 18 , for delete it I have to click 18 times because the id is decremented while it become 1 and then it is deleted.

我不知道如何让自增列id从最高id重新开始自增到数据库中，或者如果没有项就从1重新开始.

I don't know how to make the autoincrement column id restart its autoincrementation from the highest id into the database, or if there are no items make it restart from 1.

这是delete(我使用变量j，因为位置从0开始):

This is the delete (I use the variable j because position starts from 0):

public void deleteEntry(int position) {
        SQLiteDatabase sqLiteDatabase = getWritableDatabase();
        int j = position + 1;
        sqLiteDatabase.execSQL("DELETE FROM " + SportEntry.TABLE_NAME + " WHERE " +
                SportEntry._ID + " = " + j + ";");
        sqLiteDatabase.execSQL("UPDATE " + SportEntry.TABLE_NAME + " SET " + SportEntry._ID + " = " +
                SportEntry._ID + " -1 " + " WHERE " + SportEntry._ID + " > " + j + ";");
    }

这是 dbHelper 类中的 onCreate:

This is the onCreate in the dbHelper class:

public void onCreate(SQLiteDatabase db) {
        String SQL_CREATE_SPORT_TABLE = "CREATE TABLE " + TABLE_NAME + "("
            + SportEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + SportEntry.COLUMN_ATTIVITA + " TEXT NOT NULL);"; 
        db.execSQL(SQL_CREATE_SPORT_TABLE);
    }

这是合同类:

public final class SportContract {

    private SportContract(){}

    public static final class SportEntry implements BaseColumns {
        public static final String TABLE_NAME = "sport";
        public static final String _ID = BaseColumns._ID;
        public static final String COLUMN_ATTIVITA = "Attivita";
    }
}

推荐答案

这段代码让我可以获取所选项目的正确 ID:
进入DbHelper，这是delete的函数:

This code allow me to take the corrrect ID of the selected item:
Into the DbHelper, this is the funcition delete:

public void deleteEntry(int position) {
        SQLiteDatabase sqLiteDatabase = getWritableDatabase();
        sqLiteDatabase.execSQL("DELETE FROM " + SportEntry.TABLE_NAME + " WHERE " +
                SportEntry._ID + " = " + position + ";");
        sqLiteDatabase.execSQL("UPDATE " + SportEntry.TABLE_NAME + " SET " + SportEntry._ID + " = " +
                SportEntry._ID + " -1 " + " WHERE " + SportEntry._ID + " > " + position + ";");
}

OnDeleteClick 函数进入主活动(当我点击项目对应的图像进入 recyclerview 允许我删除项目时):

OnDeleteClick function into the main activity ( when i click the image corresponding to the item into the recyclerview allow me to delete the item):

@Override
public void onDeleteClick (int position) {
       int x = ReadID(position);
       removeItem(x);
}

removeItem 函数进入主活动:

removeItem function into the main activity:

public void removeItem(int position) {
       mDbHelper.deleteEntry(position);
       mAdapter.swapCursor(mDbHelper.getAllItems());
}

这是主类中的函数，允许我获取正确的 ID:

This is function into the main class that allow me to take the correct ID:

    public int ReadID(int position){
         mDbHelper = new SportDbHelper(this);
         SQLiteDatabase db = mDbHelper.getReadableDatabase();
         String[] projection = {BaseColumns._ID,
            };

         String sortOrder = SportEntry._ID + " ASC";

         Cursor cursor = db.query(
                 SportEntry.TABLE_NAME,   // The table to query
                 projection,             // The array of columns to return (null to get all)
                 null,              // The columns for the WHERE clause
                 null,              // The values for the WHERE clause
                 null,              // null if you don't want to group the rows
                 null,              // null if you don't want filter by row groups
                 sortOrder          // the order
         );
         try {
         int idColumnIndex = cursor.getColumnIndex(SportEntry._ID);
             int currentID  = 0;
             int cg=0;
         while ((cursor.moveToNext() == true) && (cg != (position + 1))) {
             currentID = cursor.getInt(idColumnIndex);
             cg ++;

            }
         return currentID;
         } finally {
        cursor.close();
    }

}

为完整起见，这是 DbHelper 类中的 getAllItems() 函数:

For completeness, this is the getAllItems() function into the DbHelper class:

public Cursor getAllItems() {
        SQLiteDatabase db = getReadableDatabase();

        return db.query(
                SportEntry.TABLE_NAME,
                null,
                null,
                null,
                null,
                null,
                null
        );
}

这篇关于SQLite:消除后创建新项目时ID错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！