Android Room-如何在每次运行应用程序时重置自动生成的表主键 [英] Android Room - How to reset auto generated table primary key on each app run
问题描述
我正在使用Room来保留数据. 我有一个实体，该实体具有一个自动生成的(自动生成)主键，该主键模仿票证系统. 在每次运行应用程序时，我都需要此键从0开始.
I'm using Room in order to persist data. I have a Entity that has a automatically generated (autoGenerate) primary key that mimics a ticket system. On every application run I need this key to start from 0.
实体:
@Entity
public class SequenceAction {
@PrimaryKey(autoGenerate = true)
private Integer sequenceId;
private String actionType;
private String extraInfo;
//getters&setters
}
初始化:
sequenceAction = new SequenceAction();
sequenceAction.setActionType(COLLECT_ALL);
sequenceAction.setExtraInfo("id = " + ids.get(i));
//run this line with executer(sequenceId is automatically set on insert to table):
AppDatabase.getInstance(getContext()).sequenceActionDao().save(sequenceAction);
我尝试过的事情:
我使用AppDatabase.getInstance(getApplicationContext()).clearAllTables();清除退出时的表，但这不会重置密钥起始索引，而是从上次运行中断的地方开始.
I use AppDatabase.getInstance(getApplicationContext()).clearAllTables(); to clear the tables on exit but this does not reset the key starting index, instead it starts where it left off on the last run.
我还没有找到使用Room来执行此操作的方法，因此我正在尝试将SimpleSQLiteQuery传递给我的Dao中的RawQuery方法:
I have not found a way to do this using Room so I'm trying with a SimpleSQLiteQuery passed to a RawQuery method in my Dao:
//Dao
@RawQuery()
Integer init(SimpleSQLiteQuery query);
//Passed query
new SimpleSQLiteQuery("...query...");
我已经尝试了下一个查询:
I've tried the next queries:
-
"ALTER TABLE SequenceAction AUTO_INCREMENT = 0"
我得到一个错误(我尝试使用"AUTOINCREMENT"进行同样的错误):
I get an error(I tried this with 'AUTOINCREMENT', same error):
android.database.sqlite.SQLiteException:在"AUTO_INCREMENT"附近:语法错误(代码1):，在编译时:ALTER TABLE SequenceAction AUTO_INCREMENT = 0
android.database.sqlite.SQLiteException: near "AUTO_INCREMENT": syntax error (code 1): , while compiling: ALTER TABLE SequenceAction AUTO_INCREMENT = 0
可能是因为，如此问题/答案所述，SQLite中没有autoincrement关键字，而是声明为INTEGER PRIMARY KEY的列将自动进行自动递增.
Probably because, as this question/answer states, there is no autoincrement keyword in SQLite but rather a column declared INTEGER PRIMARY KEY will automatically autoincrement.
-
"delete from sqlite_sequence where name='SequenceAction'"
没有错误，但是索引也未重置.
No error but, the index is not reset either.
-
根据建议此处:
"UPDATE SQLITE_SEQUENCE SET seq = -1 WHERE name = 'SequenceAction'"
没有错误，但是没有效果.
No error but, no effect.
-
"TRUNCATE TABLE 'SequenceAction';"
错误(可能是因为 SQLite不支持TRUNCATE命令):
android.database.sqlite.SQLiteException:靠近"TRUNCATE":语法错误(代码1):，同时编译:TRUNCATE TABLE'SequenceAction';
android.database.sqlite.SQLiteException: near "TRUNCATE": syntax error (code 1): , while compiling: TRUNCATE TABLE 'SequenceAction';
- 所以...最后一次尝试:
DELETE FROM SequenceAction
没有错误，没有效果.
推荐答案
为了在退出时清除表，但这不会重置密钥 起始索引，而是从上次运行中断处开始.
In order to clear the tables on exit but, this does not reset the key starting index, instead it starts where it left off on the last run.
....
从sqlite_sequence中删除，其中name ='Sequence Action'"没有错误 但是，索引也不会重置.
"delete from sqlite_sequence where name='Sequence Action'" No error but, the index is not reset either.
您必须同时删除 SequenceAction 表中的所有行，并从sqlite_sequence中删除相应的行.
You have to both delete all rows in the SequenceAction table AND delete the respective row from sqlite_sequence.
也就是说，当使用AUTOINCREMENT关键字时，将使用其他算法.这符合以下原则:-
That is when the AUTOINCREMENT keyword is used then a different algorithm is used. This is along the lines of:-
找出其中一个的最大值 -a)在sqlite_sequence编号中的表的值存储，以及 -b)最高rowid值
Find the highest value of either - a) the value store for the table in the sqlite_sequence number and - b) the highest rowid value
另一种选择是不使用AUTOINCREMENT关键字，而只使用?? INTEGER PRIMARY KEY(其中??表示列名).
An alternative would be to not use the AUTOINCREMENT keyword, rather to just have ?? INTEGER PRIMARY KEY (where ?? represents the column name).
您仍然会有一个唯一的ID，它是rowid库伦的别名，但不能保证它会一直增加. AUTOINCREMENT确实保证了唯一ID的增加，但并不能保证唯一的ID不断增加.
You would still have a unique id that is an alias of the rowid coulmn, but there is no guarantee that it would always increase. AUTOINCREMENT does guarantee an increasing unique id, but it does not guarantee a monotonically increasing unique rowid.
在运行每个应用程序时，我都需要此键从0开始.
但是，SQLite会将第一个值设置为1而不是0.
以下内容确实可以正常工作，就像您使用AUTOINCREMENT所看到的(尽管有点hack):-
The following does work, and as you see with AUTOINCREMENT (albeit a bit of a hack) :-
DROP TABLE IF EXISTS SequenceAction;
DROP TRIGGER IF EXISTS use_zero_as_first_sequence;
CREATE TABLE IF NOT EXISTS SequenceAction (id INTEGER PRIMARY KEY AUTOINCREMENT, otherdata TEXT);
CREATE TRIGGER IF NOT EXISTS use_zero_as_first_sequence AFTER INSERT ON SequenceAction
BEGIN
UPDATE SequenceAction SET id = id - 1 WHERE id = new.id;
END
;
INSERT INTO SequenceAction VALUES(null,'TEST1'),(null,'TEST2'),(null,'TEST3');
SELECT * FROM SequenceAction;
-- RESET and RESTART FROM 0
DELETE FROM SequenceAction;
DELETE FROM sqlite_sequence WHERE name = 'SequenceAction';
INSERT INTO SequenceAction VALUES(null,'TEST4'),(null,'TEST5'),(null,'TEST6');
SELECT * FROM SequenceAction
- 仅需测试删除和重新定义2条DROP语句即可.
结果为:-
第一个查询返回:-
和第二个返回:-
所以本质上您想要:-
DELETE FROM SequenceAction;
DELETE FROM sqlite_sequence WHERE name = 'SequenceAction';
，如果您希望编号从0开始而不是从1开始，还可以触发.
或者，如果您取消了AUTOINCREMENT，则可以使用稍有变化的Trigger:-
Alternately if you did away with AUTOINCREMENT then you could use a slightly changed Trigger :-
CREATE TRIGGER IF NOT EXISTS use_zero_as_first_sequence
AFTER INSERT ON SequenceAction
WHEN (SELECT count() FROM SequenceAction) = 1
BEGIN
UPDATE SequenceAction SET id = 0;
END
;
- 这只是对第一行插入的行进行重新编号(该算法然后在随后的插入中添加1)
然后只需从SequenceAction表中删除所有行即可重置编号.
And then simply delete all rows from just the SequenceAction table, to reset the numbering.
根据您的代码以及上面的示例，以下方法似乎有效:-
Based upon your code along with the example above, the following method, appears to work :-
private void resetSequenceAction() {
SQLiteDatabase dbx;
String sqlite_sequence_table = "sqlite_sequence";
long initial_sacount;
long post_sacount;
long initial_ssn =0;
long post_ssn = 0;
Cursor csr;
/*
Need to Create Database and table if it doesn't exist
*/
File f = this.getDatabasePath(TestDatabase.DBNAME);
if (!f.exists()) {
File d = new File(this.getDatabasePath(TestDatabase.DBNAME).getParent());
d.mkdirs();
dbx = SQLiteDatabase.openOrCreateDatabase(f,null);
String crtsql = "CREATE TABLE IF NOT EXISTS " + SequenceAction.tablename + "(" +
SequenceAction.id_column + " INTEGER PRIMARY KEY AUTOINCREMENT," +
SequenceAction.actionType_column + " TEXT," +
SequenceAction.extraInfo_column + " TEXT" +
")";
dbx.execSQL(crtsql);
/*
Might as well create the Trigger as well
*/
String triggerSql = "CREATE TRIGGER IF NOT EXISTS user_zero_as_first_rowid AFTER INSERT ON " +
SequenceAction.tablename +
" BEGIN " +
" UPDATE " + SequenceAction.tablename +
" SET " +
SequenceAction.id_column + " = " + SequenceAction.id_column + " - 1 " +
" WHERE " + SequenceAction.id_column + " = new." + SequenceAction.id_column + ";" +
" END ";
dbx.execSQL(triggerSql);
} else {
dbx = SQLiteDatabase.openDatabase(this.getDatabasePath(TestDatabase.DBNAME).getPath(),null, Context.MODE_PRIVATE);
}
/*
Add trigger to set id's to 1 less than they were set to
*/
initial_sacount = DatabaseUtils.queryNumEntries(dbx,SequenceAction.tablename);
/*
Delete all the rows at startup
*/
String deleteAllSequenceIdRowsSql = "DELETE FROM " + SequenceAction.tablename;
dbx.execSQL(deleteAllSequenceIdRowsSql);
post_sacount = DatabaseUtils.queryNumEntries(dbx,SequenceAction.tablename);
/*
delete the sequence row from the sqlite_sequence table
*/
csr = dbx.query(sqlite_sequence_table,
new String[]{"seq"},"name=?",
new String[]{SequenceAction.tablename},
null,null,null
);
if (csr.moveToFirst()) {
initial_ssn = csr.getLong(csr.getColumnIndex("seq"));
}
String deleteSqlLiteSequenceRow = "DELETE FROM " +
sqlite_sequence_table +
" WHERE name = '" + SequenceAction.tablename + "'";
dbx.execSQL(deleteSqlLiteSequenceRow);
csr = dbx.query(
sqlite_sequence_table,
new String[]{"seq"},
"name=?",
new String[]{SequenceAction.tablename},
null,null,null
);
if (csr.moveToFirst()) {
post_ssn = csr.getLong(csr.getColumnIndex("seq"));
}
csr.close();
Log.d("SEQACTSTATS",
"Initial Rowcount=" + String.valueOf(initial_sacount) +
" Initial Seq#=" + String.valueOf(initial_ssn) +
" Post Delete Rowcount =" + String.valueOf(post_sacount) +
" Post Delete Seq#=" + String.valueOf(post_ssn)
);
dbx.close();
}
初始运行的结果(即不存在数据库):-
Result from an initial run (i.e. no DB exists) :-
D/SEQACTSTATS: Initial Rowcount=0 Initial Seq#=0 Post Delete Rowcount =0 Post Delete Seq#=0
在随后的运行(添加40行之后):-
From a subsequent run (after 40 rows have been added) :-
D/SEQACTSTATS: Initial Rowcount=40 Initial Seq#=40 Post Delete Rowcount =0 Post Delete Seq#=0
添加一种方法来列出所有行，如:-
Adding a method to list all the rows, as per :-
private void listAllRows() {
new Thread(new Runnable() {
@Override
public void run() {
salist = mTestDB.SequenceActionDaoAccess().getAll();
getSequenceActionList(salist);
}
}).start();
}
伴随:-
@Override
public void getSequenceActionList(List<SequenceAction> sequenceActionList) {
for (SequenceAction sa: sequenceActionList) {
Log.d("SA","ID=" + String.valueOf(sa.getSequenceId()) + " AT=" + sa.getActionType() + " EI=" + sa.getExtraInfo());
}
}
结果(第一行为ID=0 AT=X0 EI=Y0，即第一行的 ID 列为 0 ):-
Results in (first row is ID=0 AT=X0 EI=Y0 i.e. the ID column of the first row is 0):-
06-17 02:56:47.867 5526-5554/rt_mjt.roomtest D/SA: ID=0 AT=X0 EI=Y0
ID=1 AT=X0 EI=Y0
ID=2 AT=X0 EI=Y0
ID=3 AT=X0 EI=Y0
ID=4 AT=X1 EI=Y1
ID=5 AT=X1 EI=Y1
ID=6 AT=X1 EI=Y1
ID=7 AT=X1 EI=Y1
06-17 02:56:47.868 5526-5554/rt_mjt.roomtest D/SA: ID=8 AT=X2 EI=Y2
ID=9 AT=X2 EI=Y2
ID=10 AT=X2 EI=Y2
ID=11 AT=X2 EI=Y2
ID=12 AT=X3 EI=Y3
ID=13 AT=X3 EI=Y3
ID=14 AT=X3 EI=Y3
ID=15 AT=X3 EI=Y3
ID=16 AT=X4 EI=Y4
06-17 02:56:47.869 5526-5554/rt_mjt.roomtest D/SA: ID=17 AT=X4 EI=Y4
ID=18 AT=X4 EI=Y4
ID=19 AT=X4 EI=Y4
ID=20 AT=X5 EI=Y5
ID=21 AT=X5 EI=Y5
ID=22 AT=X5 EI=Y5
ID=23 AT=X5 EI=Y5
ID=24 AT=X6 EI=Y6
ID=25 AT=X6 EI=Y6
ID=26 AT=X6 EI=Y6
ID=27 AT=X6 EI=Y6
06-17 02:56:47.870 5526-5554/rt_mjt.roomtest D/SA: ID=28 AT=X7 EI=Y7
ID=29 AT=X7 EI=Y7
ID=30 AT=X7 EI=Y7
ID=31 AT=X7 EI=Y7
ID=32 AT=X8 EI=Y8
ID=33 AT=X8 EI=Y8
ID=34 AT=X8 EI=Y8
ID=35 AT=X8 EI=Y8
ID=36 AT=X9 EI=Y9
ID=37 AT=X9 EI=Y9
ID=38 AT=X9 EI=Y9
ID=39 AT=X9 EI=Y9
- 由于多个线程在没有控制/排序的情况下运行，因此注释结果可能很奇怪.
addSomeData方法曾经是:-
private void addSomeData() {
new Thread(new Runnable() {
@Override
public void run() {
SequenceAction sa = new SequenceAction();
for (int i=0; i < 10; i++) {
sa.setSequenceId(0);
sa.setActionType("X" + String.valueOf(i));
sa.setExtraInfo("Y" + String.valueOf(i));
mTestDB.SequenceActionDaoAccess().insertSingleRow(sa);
}
}
}) .start();
}
补充评论:-
我相信您必须在房间之前进..."-您的意思是执行 在实例化Room之前清除运行索引的SQL 数据库? -ho
"I believe you have to get in before Room..." - do you mean execute the SQL that clears the running index before instantiating the Room database? - ghosh
不一定，但在房间之前 打开数据库，您尝试对其进行任何操作之前. 添加了调用代码(在Overridden活动的onStart()方法中) 有了Room Db之后，将立即调用addSomeData. – MikeT
not necessarily but before Room opens the database which is before you try to do anything with it. Have added invoking code (in Overidden activities onStart() method ) with some Room Db access to addSomeData is called immediately after. – MikeT
以下是在实例化RoomDatabase之后但在用于访问/打开数据库之前调用resetSequenceAction方法的示例(addSomeData打开已实例化的数据库并插入10行):-
Here's an example of calling the resetSequenceAction method after the RoomDatabase has been instantiated, but before it is used to access/open the database (addSomeData opens the already instantiated Database and inserts 10 rows) :-
@Override
protected void onStart() {
super.onStart();
mTestDB = Room.databaseBuilder(this,TestDatabase.class,TestDatabase.DBNAME).build(); //<<<< Room DB instantiated
resetSequenceAction(); //<<<< reset the sequence (adding trigger if needed)
addSomeData(); // This will be the first access open
addSomeData();
addSomeData();
addSomeData();
listAllRows();
这篇关于Android Room-如何在每次运行应用程序时重置自动生成的表主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
