清除表并重置自动增量主键

问题是ROOM防止使用SQLite表（即以sqlite_开头的表），因此您无法直接在ROOM中重置sqlite_sequence。

下面是在ROOM中避免这种情况并实现重置序列目标的两种方法。第三个选项（未显示）是关闭ROOM数据库，然后使用SQLiteDatabase实例访问数据库以重置序列。

选项A 您可以通过删除表，然后使用SupportSQLiteDatabase实例重新创建表来执行此操作。

以下是选项A的演示（但还包括不必要地关闭Room数据库以及在对象外部使用Room以外的数据库，但是已通过从sqlite_sequence中提取数据来添加该文件作为该技术的证明）。

注意 上面的代码只是该技术的演示，上面的代码在房间和非房间之间交换存在一些问题，该解决方案不是必需的。

核心测试代码：

//Stage 1 load some data
getRoomDB();
mRowIdTestDao = mRTDB.rowIdTestDao();
mRowIdTestDao.insertManyRowIdTests(
        new RowIdTest("A"),
        new RowIdTest("B"),
        new RowIdTest("C")
);

// Stage 2 close to room and dump sqlite_sequence
//mRTDB.close();
getNonRoomDB();
DatabaseUtils.dumpCursor(mNotRoomdb.query("sqlite_sequence",null,null,null,null,null,null));
mNotRoomdb.close();

//Stage 3 clear sqlite_sequence by dropping the table using SupportSQLiteDatabase
getRoomDB();
mSuppDB.execSQL("DROP TABLE RowIdTest"); //<<<<<<<<<< DROP THE TABLE
mSuppDB.execSQL("CREATE TABLE IF NOT EXISTS RowIdTest (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT)"); //<<<<<<<<<< CREATE THE TABLE

// Stage 4 close to room and dump sqlite_sequence
mRTDB.close();
getNonRoomDB();
DatabaseUtils.dumpCursor(mNotRoomdb.query("sqlite_sequence",null,null,null,null,null,null));
mNotRoomdb.close();

// Stage 5 check that all is OK again (add some rows)
getRoomDB();
mRowIdTestDao.insertManyRowIdTests(
        new RowIdTest("A"),
        new RowIdTest("B"),
        new RowIdTest("C")
);

DatabaseUtils.dumpCursor(mSuppDB.query("SELECT * FROM RowIdTest"));

调用方法：

RoomDatabase.Callback getSuppDb = new RoomDatabase.Callback() {

@Override
public void onOpen(@NonNull SupportSQLiteDatabase db) {
    super.onOpen(db);
    mSuppDB = db;
}

};

private void getRoomDB() { mRTDB = Room.databaseBuilder(this,RoomTestingDatabase.class,"rtdb.db") .allowMainThreadQueries() .addCallback(getSuppDb) .build(); }

private void getNonRoomDB() { mNotRoomdb = SQLiteDatabase.openDatabase( this.getDatabasePath("rtdb.db").getPath(), null, SQLiteDatabase.OPEN_READWRITE ); } 日志结果 2019-10-03 17:13:25.506 W/SQLiteConnection: Could not change the database journal mode of '/data/user/0/art.roomtesting/databases/rtdb.db' from 'wal' to 'TRUNCATE' because the database is locked. This usually means that there are other open connections to the database which prevents the database from enabling or disabling write-ahead logging mode. Proceeding without changing the journal mode. 2019-10-03 17:13:25.508 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@add2875 2019-10-03 17:13:25.508 I/System.out: 0 { 2019-10-03 17:13:25.508 I/System.out: name=RowIdTest 2019-10-03 17:13:25.508 I/System.out: seq=6 2019-10-03 17:13:25.508 I/System.out: } 2019-10-03 17:13:25.509 I/System.out: <<<<< 2019-10-03 17:13:25.511 E/SQLiteLog: (5) statement aborts at 1: [PRAGMA journal_mode=TRUNCATE] database is locked 2019-10-03 17:13:25.511 W/SQLiteConnection: Could not change the database journal mode of '/data/user/0/art.roomtesting/databases/rtdb.db' from 'wal' to 'TRUNCATE' because the database is locked. This usually means that there are other open connections to the database which prevents the database from enabling or disabling write-ahead logging mode. Proceeding without changing the journal mode. 2019-10-03 17:13:25.512 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@38ae98 2019-10-03 17:13:25.513 I/System.out: <<<<< 2019-10-03 17:13:25.518 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@f873957 2019-10-03 17:13:25.518 I/System.out: 0 { 2019-10-03 17:13:25.518 I/System.out: id=1 2019-10-03 17:13:25.518 I/System.out: name=A 2019-10-03 17:13:25.518 I/System.out: } 2019-10-03 17:13:25.518 I/System.out: 1 { 2019-10-03 17:13:25.518 I/System.out: id=2 2019-10-03 17:13:25.518 I/System.out: name=B 2019-10-03 17:13:25.518 I/System.out: } 2019-10-03 17:13:25.518 I/System.out: 2 { 2019-10-03 17:13:25.518 I/System.out: id=3 2019-10-03 17:13:25.519 I/System.out: name=C 2019-10-03 17:13:25.519 I/System.out: } 2019-10-03 17:13:25.519 I/System.out: <<<<< 选项B 另一种选择是使用AUTOINCREMENT创建表，从而仅根据表中的行生成rowid。我相信关于不使用AUTOINCREMENT的方式有些困惑，（或一种）方式是不对autoGenerate = true进行编码，并使用Long not long（或Integer not int for type，不建议使用）。

考虑上面使用的RowTestId表/实体，它是：-

@Entity public class RowIdTest {

@PrimaryKey(autoGenerate = true)
private long id;
private String name;


public RowIdTest() {};

@Ignore
public RowIdTest(String name){
    this.name = name;
}

public long getId() {
    return id;
}

public void setId(long id) {
    this.id = id;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

} 等效的非AUTOINCREMENT版本为：-

@Entity public class AltRowIdTest {

@PrimaryKey
private Long id; //<<<<<<<<<< Long not long
private String name;


public AltRowIdTest() {};

@Ignore
public AltRowIdTest(String name){
    this.name = name;
}

public Long getId() { //<<<<<<<<<< Long not long
    return id;
}

public void setId(Long id) { //<<<<<<<<<< Long not long
    this.id = id;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

} 另外考虑：-

@Query("DELETE FROM AltRowIdTest")
int clearAllAltRowIdTestRows();

然后是以下代码：-

//Option B
mAltRowIdTestDao.insertManyRowIdTests(new AltRowIdTest("X"),new AltRowIdTest("Y"),new AltRowIdTest("Z"));
DatabaseUtils.dumpCursor(mSuppDB.query("SELECT * FROM AltRowIdTest"));
mAltRowIdTestDao.clearAllAltRowIdTestRows();
mAltRowIdTestDao.insertManyRowIdTests(new AltRowIdTest("X"),new AltRowIdTest("Y"),new AltRowIdTest("Z"));
DatabaseUtils.dumpCursor(mSuppDB.query("SELECT * FROM AltRowIdTest"));

日志中的结果是：

2019-10-03 17:49:31.783 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@561c44 2019-10-03 17:49:31.785 I/System.out: 0 { 2019-10-03 17:49:31.785 I/System.out: id=1 2019-10-03 17:49:31.785 I/System.out: name=A 2019-10-03 17:49:31.785 I/System.out: } 2019-10-03 17:49:31.785 I/System.out: 1 { 2019-10-03 17:49:31.785 I/System.out: id=2 2019-10-03 17:49:31.785 I/System.out: name=B 2019-10-03 17:49:31.785 I/System.out: } 2019-10-03 17:49:31.785 I/System.out: 2 { 2019-10-03 17:49:31.785 I/System.out: id=3 2019-10-03 17:49:31.785 I/System.out: name=C 2019-10-03 17:49:31.786 I/System.out: } 2019-10-03 17:49:31.786 I/System.out: <<<<<

2019-10-03 17:49:31.793 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@f369662 2019-10-03 17:49:31.794 I/System.out: 0 { 2019-10-03 17:49:31.794 I/System.out: id=1 2019-10-03 17:49:31.794 I/System.out: name=X 2019-10-03 17:49:31.794 I/System.out: } 2019-10-03 17:49:31.794 I/System.out: 1 { 2019-10-03 17:49:31.794 I/System.out: id=2 2019-10-03 17:49:31.794 I/System.out: name=Y 2019-10-03 17:49:31.794 I/System.out: } 2019-10-03 17:49:31.794 I/System.out: 2 { 2019-10-03 17:49:31.794 I/System.out: id=3 2019-10-03 17:49:31.794 I/System.out: name=Z 2019-10-03 17:49:31.794 I/System.out: } 2019-10-03 17:49:31.795 I/System.out: <<<<< 即id列在删除所有行后从1重新开始，或者与Option A不同，如果删除末尾的行，它将重新使用释放的id（如果未删除末行，则bot不使用）。此外，与AUTOINCREMENT不同，如果达到了最高ID值（9223372036854754775）（原因ID应始终为long或Long，而不是int或Integer），则SQLITE_FULL异常可能不会导致，因为SQLite会尝试使用随机未使用的数字（使用负值将可用范围增加一倍）。