如何添加到以前预先填充的房间数据库？-解网

问：

以前，我预先填充了一些数据并将其添加到 Room 数据库中。但是我需要添加一些新数据。作为添加这些的一种方式，我可以通过直接请求 dao 来做到这一点，但应用程序每次打开时都会这样做。这将给应用程序带来不必要的负担。那么有没有比下面代码更好的方法呢？我问是否有比这更好的方法。

private fun addNewWord(){
    val newWord1 = Word(1, "John", "Weight")
    val newWord2 = Word(2, "Alex", "So"
    wordViewModel.addWord(newWord1, newWord2)
}

我找到了一个像我上面写的代码一样的解决方案，但我认为它不够正确。我正在寻找更好的方法。这是最佳实践吗？

Kotlin Android-Room

0赞 CommonsWare 1/2/2023

“但是应用程序每次打开时都会这样做”——如果这不是你想要的，那就做点别的事情。例如，不要每次都调用，而只在数据库中还没有新单词时才调用它。addNewWord()

0赞 burhanyaprak 1/2/2023

我也考虑过这个解决方案，但是这次应用程序会在每次打开时检查要添加的新单词是否都是数据库。但如果这是最好的方法，我会实施它。

答：

3赞 MikeT 1/2/2023 #1

这些都更好（就这将给应用程序带来不必要的负担而言），假设不必要的负担是与每次运行应用程序时尝试插入记录相关的开销（更正确地说，每当打开数据库时）。

最简单的解决方案，但并不是真正更好的解决方案（甚至可能是您尝试过的）是忽略重复项（如果您还没有忽略它们）。这涉及使用一个或多个列或列组合具有 UNQIUE 索引的位置。INSERT OR IGNORE ....

所有 Room 表（FTS（全文搜索）除外）必须具有主键。主键隐式 UNIQUE。因此，如果使用，则忽略 UNIQUE CONFLICT（不插入行并忽略会导致异常的冲突）。INSERT OR IGNORE ....

为了方便起见，要指定 INSERT OR IGNORE @Insert您可以指定@Insert批注的 onConflict 值。例如：

@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(word: Word): Long

方便返回插入行的 rowid，如果由于该行被忽略而未插入，则返回 -1，可以检查该行以查看是否插入了行。@Insert
- 通常隐藏的列，存在于 ALL Room 表中（因为它存在于任何不是虚拟表的 SQLite 表或定义为 WITHOUT ROWID 表的表中）。

您还可以通过注释的参数在列或列的组合上指定唯一索引，例如：-indicies@Entity

@Entity(
    indices = [
        /* A composite UNIQUE index on word1 combined with word2 */
        Index(value = ["word1","word2"], unique = true)
        /* example
        * if a row exists that has A as word1 and B as word2 then (index value can be considered as AB):-
        *       inserting a new row with B and A would be inserted (index value BA)
        *       inserting a new row with B and B (or A and A) would be inserted (index value BB or AA)
        *       inserting a row with B and A would result in a UNIQUE CONFLICT
        *       inserting a row with BA and nothing (aka null) would be inserted (assuming NOT NULL was not coded or implied for column word2)
        */

        /*
            An Index on a single column (word1 so the value in the word1 column cannot be duplicated)
            NOTE the above would not apply if this index was also included
            In this case then word1 =A and word2 = A above (third insert (or A and A)) would be considered a duplicate
         */
        , Index(value = ["word1"], unique = true)
])
data class Word(
    @PrimaryKey
    var id: Long?=null,
    var word1: String,
    var word2: String
)

但是，这个简单的解决方案仍将运行，并在运行应用程序时尝试插入新数据。

没有“不必要的负担”的更好解决方案

如果目标是只应用一次新数据，那么就需要一种方法来查看数据是否已经应用，也许是通过迁移（又名新版本）。

迁移只会运行一次，因为 Room 会检查/更新数据库文件头中的user_version。

如果在指定新数据库版本后安装应用程序，迁移也将运行。

工作演示

也许可以考虑以下基于迁移的工作演示，具体取决于您的数据：-

房间数据库代码：-

@Entity(
    indices = [
        /* A composite UNIQUE index on word1 combined with word2 */
        Index(value = ["word1","word2"], unique = true)
        /* example
        * if a row exists that has A as word1 and B as word2 then (index value can be considered as AB):-
        *       inserting a new row with B and A would be inserted (index value BA)
        *       inserting a new row with B and B (or A and A) would be inserted (index value BB or AA)
        *       inserting a row with B and A would result in a UNIQUE CONFLICT
        *       inserting a row with BA and nothing (aka null) would be inserted (assuming NOT NULL was not coded or implied for column word2)
        */
])
data class Word(
    @PrimaryKey
    var id: Long?=null,
    var word1: String,
    var word2: String
)

@Dao
interface TheDAOs {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(word: Word): Long
    @Query("SELECT * FROM word")
    fun getAllWords(): List<Word>
}

const val DATABASE_NAME = "the_database.db"
const val ASSET_NAME = DATABASE_NAME
@Database(entities = [Word::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getTheDAOs(): TheDAOs

    companion object {
        private var instance: TheDatabase?=null
        fun getInstance(context: Context): TheDatabase {
            if (instance==null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java, DATABASE_NAME)
                    .createFromAsset(ASSET_NAME,pdc)
                    .allowMainThreadQueries() /* For convenience of the demo */
                    .addCallback(cb)
                    .addMigrations(mig1to2)
                    .build()
            }
            return instance as TheDatabase
        }
        private val mig1to2 = object: Migration(1,2) {
            override fun migrate(database: SupportSQLiteDatabase) {
                Log.d("MIG1-2", "Migration is running")
                val cv = ContentValues()
                cv.put("word1", "NEWWORD W1=W")
                cv.put("word2", "NEWWORD W2=W")
                database.insert("word", OnConflictStrategy.IGNORE, cv)
                cv.clear()
                cv.put("word1", "NEWWORD W1=X")
                cv.put("word2", "NEWWORD W2=X")
                database.insert("word", OnConflictStrategy.IGNORE, cv)
                cv.clear()
                cv.put("word1", "NEWWORD W1=Y")
                cv.put("word2", "NEWWORD W2=Y")
                database.insert("word", OnConflictStrategy.IGNORE, cv)
                cv.clear()
                cv.put("word1", "NEWWORD W1=Z")
                cv.put("word2", "NEWWORD W2=Z")
                database.insert("word", OnConflictStrategy.IGNORE, cv)
            }
        }
        val cb = object: RoomDatabase.Callback() {
            val TAG = "DBCALLBACK"

            override fun onCreate(db: SupportSQLiteDatabase) {
                super.onCreate(db)
                Log.d(TAG,"onCreate called")
            }

            override fun onOpen(db: SupportSQLiteDatabase) {
                super.onOpen(db)
                Log.d(TAG,"onOpen called")
            }

            override fun onDestructiveMigration(db: SupportSQLiteDatabase) {
                super.onDestructiveMigration(db)
                Log.d(TAG,"onDestructiveMigration called")
            }

        }
        val pdc = object: PrepackagedDatabaseCallback(){
            val TAG = "PPDOPEN"
            override fun onOpenPrepackagedDatabase(db: SupportSQLiteDatabase) {
                super.onOpenPrepackagedDatabase(db)
                Log.d(TAG,"Prepackaged Database has been copied and opened")
            }
        }
    }
}

请注意，数据库版本为 1
包含回调以显示调用的时间和内容。
文件the_database.db位于 assets 文件夹中，有 3 行，如下所示：-

使用的活动代码是：-

class MainActivity : AppCompatActivity() {

    lateinit var db: TheDatabase
    lateinit var dao: TheDAOs
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = TheDatabase.getInstance(this)
        dao = db.getTheDAOs()
        for(w in dao.getAllWords()) {
            Log.d("DBINFO","Word ID is ${w.id} WORD1 is ${w.word1} WORD2 is ${w.word2}")
        }
    }
}

即，它只是访问数据库，提取所有行并将数据写入日志。

在数据库版本 1 上作为新安装运行时，日志的输出为：-

2023-01-02 15:26:47.055 D/PPDOPEN: Prepackaged Database has been copied and opened
2023-01-02 15:26:47.119 D/DBCALLBACK: onOpen called
2023-01-02 15:26:47.124 D/DBINFO: Word ID is 1 WORD1 is ORIGINALWORD1=W1_A WORD2 is  ORIGINALWORD2=W2_A
2023-01-02 15:26:47.124 D/DBINFO: Word ID is 2 WORD1 is ORIGINALWORD1=W1_B WORD2 is  ORIGINALWORD2=W2_B
2023-01-02 15:26:47.124 D/DBINFO: Word ID is 3 WORD1 is ORIGINALWORD1=W1_C WORD2 is  ORIGINALWORD2=W2_C

如果再次运行，仍然在版本 1，则输出为：-

2023-01-02 15:28:27.976 D/DBCALLBACK: onOpen called
2023-01-02 15:28:27.981 D/DBINFO: Word ID is 1 WORD1 is ORIGINALWORD1=W1_A WORD2 is  ORIGINALWORD2=W2_A
2023-01-02 15:28:27.981 D/DBINFO: Word ID is 2 WORD1 is ORIGINALWORD1=W1_B WORD2 is  ORIGINALWORD2=W2_B
2023-01-02 15:28:27.981 D/DBINFO: Word ID is 3 WORD1 is ORIGINALWORD1=W1_C WORD2 is  ORIGINALWORD2=W2_C

即，由于数据库存在，因此未调用预打包数据库的副本，并且输出在其他方面相同。

如果数据库版本更改为 2（不更改架构）并重新运行应用程序，则输出为：-

2023-01-02 15:31:32.464 D/MIG1-2: Migration is running
2023-01-02 15:31:32.529 D/DBCALLBACK: onOpen called
2023-01-02 15:31:32.536 D/DBINFO: Word ID is 1 WORD1 is ORIGINALWORD1=W1_A WORD2 is  ORIGINALWORD2=W2_A
2023-01-02 15:31:32.536 D/DBINFO: Word ID is 2 WORD1 is ORIGINALWORD1=W1_B WORD2 is  ORIGINALWORD2=W2_B
2023-01-02 15:31:32.536 D/DBINFO: Word ID is 3 WORD1 is ORIGINALWORD1=W1_C WORD2 is  ORIGINALWORD2=W2_C
2023-01-02 15:31:32.536 D/DBINFO: Word ID is 4 WORD1 is NEWWORD W1=W WORD2 is NEWWORD W2=W
2023-01-02 15:31:32.536 D/DBINFO: Word ID is 5 WORD1 is NEWWORD W1=X WORD2 is NEWWORD W2=X
2023-01-02 15:31:32.536 D/DBINFO: Word ID is 6 WORD1 is NEWWORD W1=Y WORD2 is NEWWORD W2=Y
2023-01-02 15:31:32.536 D/DBINFO: Word ID is 7 WORD1 is NEWWORD W1=Z WORD2 is NEWWORD W2=Z

即调用迁移，并根据迁移中的代码引入新数据。

如果应用程序重新运行（仍为版本 2），则：-

2023-01-02 15:34:21.336 D/DBCALLBACK: onOpen called
2023-01-02 15:34:21.342 D/DBINFO: Word ID is 1 WORD1 is ORIGINALWORD1=W1_A WORD2 is  ORIGINALWORD2=W2_A
2023-01-02 15:34:21.342 D/DBINFO: Word ID is 2 WORD1 is ORIGINALWORD1=W1_B WORD2 is  ORIGINALWORD2=W2_B
2023-01-02 15:34:21.342 D/DBINFO: Word ID is 3 WORD1 is ORIGINALWORD1=W1_C WORD2 is  ORIGINALWORD2=W2_C
2023-01-02 15:34:21.342 D/DBINFO: Word ID is 4 WORD1 is NEWWORD W1=W WORD2 is NEWWORD W2=W
2023-01-02 15:34:21.342 D/DBINFO: Word ID is 5 WORD1 is NEWWORD W1=X WORD2 is NEWWORD W2=X
2023-01-02 15:34:21.342 D/DBINFO: Word ID is 6 WORD1 is NEWWORD W1=Y WORD2 is NEWWORD W2=Y
2023-01-02 15:34:21.342 D/DBINFO: Word ID is 7 WORD1 is NEWWORD W1=Z WORD2 is NEWWORD W2=Z

即，不会调用迁移，并且所有数据都保留。

如果应用程序被卸载，然后安装/运行（根据新安装），则：-

2023-01-02 15:37:25.096 D/PPDOPEN: Prepackaged Database has been copied and opened
2023-01-02 15:37:25.113 D/MIG1-2: Migration is running
2023-01-02 15:37:25.169 D/DBCALLBACK: onOpen called
2023-01-02 15:37:25.175 D/DBINFO: Word ID is 1 WORD1 is ORIGINALWORD1=W1_A WORD2 is  ORIGINALWORD2=W2_A
2023-01-02 15:37:25.175 D/DBINFO: Word ID is 2 WORD1 is ORIGINALWORD1=W1_B WORD2 is  ORIGINALWORD2=W2_B
2023-01-02 15:37:25.175 D/DBINFO: Word ID is 3 WORD1 is ORIGINALWORD1=W1_C WORD2 is  ORIGINALWORD2=W2_C
2023-01-02 15:37:25.175 D/DBINFO: Word ID is 4 WORD1 is NEWWORD W1=W WORD2 is NEWWORD W2=W
2023-01-02 15:37:25.175 D/DBINFO: Word ID is 5 WORD1 is NEWWORD W1=X WORD2 is NEWWORD W2=X
2023-01-02 15:37:25.175 D/DBINFO: Word ID is 6 WORD1 is NEWWORD W1=Y WORD2 is NEWWORD W2=Y
2023-01-02 15:37:25.176 D/DBINFO: Word ID is 7 WORD1 is NEWWORD W1=Z WORD2 is NEWWORD W2=Z

即，预打包的数据库已被复制并调用了迁移，从而引入了新数据（该数据不在预打包的数据库中）。

其他方式

另一种方法是利用预打包数据库中的版本号。这将涉及通过资产管理器访问预打包的版本，从标头（文件的前 100 个字节）中提取版本号（偏移量 60 时为 4 个字节），如果前者高于则存在新数据，则将其与实际数据库中的版本号进行比较。因此，可以打开复制的行的两个数据库。

Room 使用版本号（user_version），因此另一种方法是改用应用程序 ID（4 字节偏移量 68）。

这两者都需要在预打包的数据库中设置值，并将它们与新的发行版/APK 一起更新。

另一种选择是让核心数据库/文件通过互联网访问，并采用检测数据更改的方法。

其他方法可能是通过额外的列甚至额外的表来引入检测。但是，负担越小，解决方案的复杂性就越大。

0赞 burhanyaprak 1/3/2023

杰作！迁移的方式正是我想要的解决方案。

上一个：RecyclerView 未在 NestedScrollView 中滚动

下一个：确定用户是否为高级用户 - Qonversion API

如何添加到以前预先填充的房间数据库？

How to add to previously pre-populated Room database?

评论

评论