Android Room - 如何在每次运行应用程序时重置自动生成的表主键 [英] Android Room - How to reset auto generated table primary key on each app run

查看：27
本文介绍了Android Room - 如何在每次运行应用程序时重置自动生成的表主键的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在使用 Room 来保存数据.
我有一个实体，它有一个模拟票务系统的自动生成(自动生成)主键.在每次运行应用程序时，我都需要此密钥从 0 开始.

实体:

@Entity公共类 SequenceAction {@PrimaryKey(autoGenerate = true)私有整数序列ID；私人字符串动作类型；私人字符串 extraInfo;//getter &二传手}

初始化:

//初始化 sequenceAction 对象//与 executor 一起运行(sequenceId 在插入到表时自动设置):AppDatabase.getInstance(getContext()).sequenceActionDao().save(sequenceAction);

我尝试过的事情:

我使用 AppDatabase.getInstance(getApplicationContext()).clearAllTables(); 在退出时清除表，但这不会重置键起始索引，而是它从上次运行停止的地方开始.

我还没有找到使用 Room 来做到这一点的方法，所以我正在尝试将 SimpleSQLiteQuery 传递给我的 Dao 中的 RawQuery 方法:

//道@RawQuery()整数初始化(SimpleSQLiteQuery 查询)；//通过查询new SimpleSQLiteQuery("...查询...");

我已经尝试了下一个查询:

  1. ALTER TABLE SequenceAction AUTO_INCREMENT = 0"

我收到一个错误(我用AUTOINCREMENT"试过了，同样的错误):

<块引用>

android.database.sqlite.SQLiteException:AUTO_INCREMENT"附近:语法错误(代码 1):，编译时:ALTER TABLE SequenceAction AUTO_INCREMENT = 0

可能是因为，正如

第二次返回:-

本质上你想要 :-

DELETE FROM SequenceAction;从 sqlite_sequence 中删除名称 = 'SequenceAction';

如果您希望编号从 0 而不是 1 开始，也可以使用触发器.

或者，如果您取消了 AUTOINCREMENT，那么您可以使用稍微更改的触发器:-

如果不存在则创建触发器 use_zero_as_first_sequence插入序列后操作WHEN (SELECT count() FROM SequenceAction) = 1开始更新 SequenceAction SET id = 0;结尾;

  • 这只是对第一个插入的行重新编号(算法然后为后续插入添加 1)

然后简单地从 SequenceAction 表中删除所有行，以重置编号.

<小时>

使用房间的例子:-

根据您的代码以及上面的示例，以下方法似乎有效:-

private void resetSequenceAction() {SQLite 数据库 dbx；String sqlite_sequence_table = "sqlite_sequence";长initial_sacount;长 post_sacount;long initial_ssn =0;长 post_ssn = 0;光标 csr;/*如果不存在，则需要创建数据库和表*/文件 f = this.getDatabasePath(TestDatabase.DBNAME);如果 (!f.exists()) {File d = new File(this.getDatabasePath(TestDatabase.DBNAME).getParent());d.mkdirs();dbx = SQLiteDatabase.openOrCreateDatabase(f,null);String crtsql = "如果不存在则创建表" + SequenceAction.tablename + "(" +SequenceAction.id_column + "INTEGER PRIMARY KEY AUTOINCREMENT," +SequenceAction.actionType_column + "TEXT," +SequenceAction.extraInfo_column + "文本" +")";dbx.execSQL(crtsql);/*也可以创建触发器*/String triggerSql = "CREATE TRIGGER IF NOT EXISTS user_zero_as_first_rowid AFTER INSERT ON " +SequenceAction.tablename +开始" +更新" + SequenceAction.tablename +设置" +SequenceAction.id_column + " = " + SequenceAction.id_column + " - 1 " +" WHERE " + SequenceAction.id_column + " = new."+ SequenceAction.id_column + ";"+"结尾 ";dbx.execSQL(triggerSql);} 别的 {dbx = SQLiteDatabase.openDatabase(this.getDatabasePath(TestDatabase.DBNAME).getPath(),null,Context.MODE_PRIVATE);}/*添加触发器以将 id 设置为比它们设置的小 1*/initial_sacount = DatabaseUtils.queryNumEntries(dbx,SequenceAction.tablename);/*启动时删除所有行*/String deleteAllSequenceIdRowsSql = "DELETE FROM " + SequenceAction.tablename;dbx.execSQL(deleteAllSequenceIdRowsSql);post_sacount = DatabaseUtils.queryNumEntries(dbx,SequenceAction.tablename);/*从 sqlite_sequence 表中删除序列行*/csr = dbx.query(sqlite_sequence_table,new String[]{"seq"},"name=?",new String[]{SequenceAction.tablename},空，空，空);如果(csr.moveToFirst()){initial_ssn = csr.getLong(csr.getColumnIndex("seq"));}String deleteSqlLiteSequenceRow = "DELETE FROM " +sqlite_sequence_table +" WHERE 名称 = '" + SequenceAction.tablename + "'";dbx.execSQL(deleteSqlLiteSequenceRow);csr = dbx.query(sqlite_sequence_table，新字符串[]{"seq"},姓名=?",new String[]{SequenceAction.tablename},空，空，空);如果(csr.moveToFirst()){post_ssn = csr.getLong(csr.getColumnIndex("seq"));}csr.close();Log.d("SEQACTSTATS",初始行数="+ String.valueOf(initial_sacount) +" 初始序列#=" + String.valueOf(initial_ssn) +" Post Delete Rowcount =" + String.valueOf(post_sacount) +" Post Delete Seq#=" + String.valueOf(post_ssn));dbx.close();}

初始运行的结果(即不存在数据库):-

D/SEQACTSTATS: Initial Rowcount=0 Initial Seq#=0 Post Delete Rowcount =0 Post Delete Seq#=0

来自后续运行(添加 40 行后):-

D/SEQACTSTATS: Initial Rowcount=40 Initial Seq#=40 Post Delete Rowcount =0 Post Delete Seq#=0

添加一个方法来列出所有行，如:-

private void listAllRows() {新线程(新运行(){@覆盖公共无效运行(){salist = mTestDB.SequenceActionDaoAccess().getAll();getSequenceActionList(salist);}}).开始();}

连同:-

@Overridepublic void getSequenceActionList(List 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):-

06-17 02:56:47.867 5526-5554/rt_mjt.roomtest D/SA: ID=0 AT=X0 EI=Y0ID=1 AT=X0 EI=Y0ID=2 AT=X0 EI=Y0ID=3 AT=X0 EI=Y0ID=4 AT=X1 EI=Y1ID=5 AT=X1 EI=Y1ID=6 AT=X1 EI=Y1ID=7 AT=X1 EI=Y106-17 02:56:47.868 5526-5554/rt_mjt.roomtest D/SA:ID=8 AT=X2 EI=Y2ID=9 AT=X2 EI=Y2ID=10 AT=X2 EI=Y2ID=11 AT=X2 EI=Y2ID=12 AT=X3 EI=Y3ID=13 AT=X3 EI=Y3ID=14 AT=X3 EI=Y3ID=15 AT=X3 EI=Y3ID=16 AT=X4 EI=Y406-17 02:56:47.869 5526-5554/rt_mjt.roomtest D/SA:ID=17 AT=X4 EI=Y4ID=18 AT=X4 EI=Y4ID=19 AT=X4 EI=Y4ID=20 AT=X5 EI=Y5ID=21 AT=X5 EI=Y5ID=22 AT=X5 EI=Y5ID=23 AT=X5 EI=Y5ID=24 AT=X6 EI=Y6ID=25 AT=X6 EI=Y6ID=26 AT=X6 EI=Y6ID=27 AT=X6 EI=Y606-17 02:56:47.870 5526-5554/rt_mjt.roomtest D/SA:ID=28 AT=X7 EI=Y7ID=29 AT=X7 EI=Y7ID=30 AT=X7 EI=Y7ID=31 AT=X7 EI=Y7ID=32 AT=X8 EI=Y8ID=33 AT=X8 EI=Y8ID=34 AT=X8 EI=Y8ID=35 AT=X8 EI=Y8ID=36 AT=X9 EI=Y9ID=37 AT=X9 EI=Y9ID=38 AT=X9 EI=Y9ID=39 AT=X9 EI=Y9

  • 注意结果可能会很奇怪，因为多个线程在没有控制/排序的情况下运行.

使用的 addSomeData 方法是 :-

private void addSomeData() {新线程(新运行(){@覆盖公共无效运行(){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);}}}) .开始();}

补充评论:-

<块引用>

我相信你必须在房间之前进入......" - 你的意思是执行在实例化 Room 之前清除运行索引的 SQL数据库?- 天呐

不一定，但在 Room 之前在您尝试对其进行任何操作之前打开数据库.添加了调用代码(在覆盖活动的 onStart() 方法中)之后立即调用一些 Room Db 访问 addSomeData.——迈克

这是在 RoomDatabase 实例化之后，但在使用它访问/打开数据库之前调用 resetSequenceAction 方法的示例(addSomeData 打开已经实例化的数据库并插入 10 行):-

@Override受保护的无效 onStart() {super.onStart();mTestDB = Room.databaseBuilder(this,TestDatabase.class,TestDatabase.DBNAME).build();//<<<<房间数据库实例化resetSequenceAction();//<<<<重置序列(如果需要，添加触发器)添加一些数据()；//这将是第一个打开的访问添加一些数据()；添加一些数据()；添加一些数据()；listAllRows();

I'm using Room in order to persist data.
I have a Entity that has an automatically generated (autoGenerate) primary key that mimics a ticket system. On every application run I need this key to start from 0.

Entity:

@Entity
public class SequenceAction {

    @PrimaryKey(autoGenerate = true)
    private Integer sequenceId;
    private String actionType;
    private String extraInfo;
    //getters & setters
}

Initialization:

// init sequenceAction object
// run with executor(sequenceId is automatically set on insert to table):
AppDatabase.getInstance(getContext()).sequenceActionDao().save(sequenceAction);

Things I've tried:

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.

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:

  1. "ALTER TABLE SequenceAction AUTO_INCREMENT = 0"

I get an error(I tried this with 'AUTOINCREMENT', same error):

android.database.sqlite.SQLiteException: near "AUTO_INCREMENT": syntax error (code 1): , while compiling: ALTER TABLE SequenceAction AUTO_INCREMENT = 0

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.

  1. "delete from sqlite_sequence where name='SequenceAction'"

No error but, the index is not reset either.

  1. As suggested here:

    "UPDATE SQLITE_SEQUENCE SET seq = -1 WHERE name = 'SequenceAction'"

No error but, no effect.

  1. "TRUNCATE TABLE 'SequenceAction';"

Error(Probably because SQLite doesn't support the TRUNCATE command):

android.database.sqlite.SQLiteException: near "TRUNCATE": syntax error (code 1): , while compiling: TRUNCATE TABLE 'SequenceAction';

  1. So... last try: DELETE FROM SequenceAction

No error, no effect.

解决方案

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.

....

"delete from sqlite_sequence where name='Sequence Action'" No error but, the index is not reset either.

You have to both delete all rows in the SequenceAction table AND delete the respective row from sqlite_sequence.

That is when the AUTOINCREMENT keyword is used then a different algorithm is used. This is along the lines of:-

Find the highest value of either - a) the value store for the table in the sqlite_sequence number and - b) the highest rowid value

An alternative would be to not use the AUTOINCREMENT keyword, rather to just have ?? INTEGER PRIMARY KEY (where ?? represents the column name).

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.

On every application run I need this key to start from 0.

However, SQLite will set the first value to 1 not 0.

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

  • The 2 DROP statements required only for testing to delete and redefine.

This results in :-

The first query returning :-

and the 2nd returning :-

So in essence you want :-

DELETE FROM SequenceAction;
DELETE FROM sqlite_sequence WHERE name = 'SequenceAction';

And also the Trigger if you want numbering to start from 0 rather than 1.

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
;

  • This just renumbers the very first inserted row (the algorithm then adds 1 fro subsequent inserts)

And then simply delete all rows from just the SequenceAction table, to reset the numbering.

Example using Room :-

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

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();
}

Along with :-

@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());
    }
}

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

  • Note results may be weird due to multiple threads running without control/sequencing.

The addSomeData method used being :-

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();
}

Addition re comments :-

"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

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

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屋！

查看全文
相关文章
移动开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆