时间戳

首页 > 解决方案 > 如何在sqlite java android上删除1行列后重置自动增量值？

java - 如何在sqlite java android上删除1行列后重置自动增量值？

问题描述

在 MYSQLI 上，这个方法当时对我有用ALTER TABLE tablename AUTO_INCREMENT = 0，这将在最后一个自动增量位置重置

但我不知道如何在 SQLITE 上执行此操作，所以我的想法是，我想删除 1 行列表，同时我想将自动增量值重置为最后一个位置，这是我的代码

listView.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
        @Override
        public boolean onItemLongClick(AdapterView<?> parent, View view, int position, long id) {
            getName = parent.getItemAtPosition(position).toString();
            getID = myDB.getID(getName);
            getIDtoString = String.valueOf(getID);

            AlertDialog.Builder builder = new AlertDialog.Builder(Notepad.this);
            builder.setMessage("You Want To Delete "+getName+" From Notepad?").setPositiveButton("Yes Please!", new DialogInterface.OnClickListener() {
                @Override
                public void onClick(DialogInterface dialog, int which) {
                    boolean isDeleted = myDB.deleteLol(getIDtoString,getName);
                    if (isDeleted) {
                        toastMessage("Delete Success!");
                        refresh();
                    } else {
                        toastMessage("Delete Failed!");
                    }
                }
            }).setNegativeButton("No, Don't Do That!", null);

            AlertDialog alertDialog = builder.create();
            alertDialog.show();

        return true;
        }
    });

这是我的 DatabaseHelper 类

public void onCreate(SQLiteDatabase db) {
    db.execSQL("create table usersData(id integer primary key autoincrement, username text, email text, password text)");
    db.execSQL("create table notepadData(id integer primary key autoincrement, notepad text)");
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("drop table if exists usersData");
    db.execSQL("drop table if exists notepadData");
    onCreate(db);
}

public boolean deleteLol(String id,String notepad) {
    SQLiteDatabase myDB = this.getWritableDatabase();
    myDB.delete("notepadData", "id = ?",new String[] {id});
    myDB.execSQL("UPDATE SQLITE_SEQUENCE SET SEQ=0 WHERE notepad = '"+notepad+"'");// i found this on internet recently and doesn't work
    return true;
}

标签： javaandroidandroid-studio

解决方案

SQLite将最后ROW-ID一个存储在 table 中SQLITE_SEQUENCE，由 SQLite 自动管理。即使您删除或清空其他表，此表中的值也会保持保存。

有两种方法可以重置自增计数器。

  1. Delete your entire table and then recreate it. (maybe use a dummy temporary table to save the current data). Delete the information about your table from the SQLITE_SEQUENCE meta table. 
DELETE from table;
DELETE from sqlite_sequence where name='table';
  1. Update the sequence in the sqlite_sequence using update query
update sqlite_sequence set seq=5 where name='table';

推荐阅读