Room数据库报错：不存在custom_sort_shopping_list_items表怎么解决？

问题描述 #

基于ShoppingListItemEntity创建了结构相同但表名不同的CustomSortShoppingListItemEntity（对应表custom_sort_shopping_list_items），但调用ShoppingListItemDao中的countCustomSortListItems方法时，Room抛出表不存在的错误。询问是否需要单独创建Dao及解决办法。

错误信息 #

error: There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such table: custom_sort_shopping_list_items)

public abstract java.lang.Object countCustomSortListItems(long listId, @org.jetbrains.annotations.NotNull()

相关代码 #

ShoppingListItemEntity 实体类 #

@Entity(
    tableName = "shopping_list_items",
    foreignKeys = [ForeignKey(
        entity = ShoppingListEntity::class,
        parentColumns = arrayOf("id"),
        childColumns = arrayOf("shopping_list_id"),
        onUpdate = ForeignKey.CASCADE,
        onDelete = ForeignKey.CASCADE
    )]
)
data class ShoppingListItemEntity(
    @PrimaryKey(autoGenerate = true)
    var id: Long = 0L,
    @ColumnInfo(name = "shopping_list_id")
    var shoppingListId: Long = 0L,
    @ColumnInfo(name = "name")
    val name: String,
    @ColumnInfo(name = "category")
    val category: String,
    @ColumnInfo(name = "quantity")
    val quantity: String,
    @ColumnInfo(name = "set_quantity")
    val setQuantity: String,
    @ColumnInfo(name = "set_total")
    val setTotal: String,
    @ColumnInfo(name = "unit")
    val unit: String,
    @ColumnInfo(name = "item_ppu")
    val itemPPU: String,
    @ColumnInfo(name = "item_set_ppu")
    val itemSetPPU: String,
    @ColumnInfo(name = "notes")
    val notes: String,
    @ColumnInfo(name = "coupon_amount")
    val couponAmount: String,
    @ColumnInfo(name = "set_coupon_amount")
    val setCouponAmount: String,
    @ColumnInfo(name = "item_total")
    val itemTotal: String,
    @ColumnInfo(name = "item_set_total")
    val itemSetTotal: String,
    @ColumnInfo(name = "image_uri")
    val itemImageUri: String?,
    @ColumnInfo(name = "thumbnail_uri")
    val itemThumbnailUri: String?,
    @ColumnInfo(name = "is_in_cart")
    val isInCart: Boolean
)

CustomSortShoppingListItemEntity 实体类 #

@Entity(
    tableName = "custom_sort_shopping_list_items",
    foreignKeys = [ForeignKey(
        entity = ShoppingListEntity::class,
        parentColumns = arrayOf("id"),
        childColumns = arrayOf("shopping_list_id"),
        onUpdate = ForeignKey.CASCADE,
        onDelete = ForeignKey.CASCADE
    )]
)
data class CustomSortShoppingListItemEntity(
    @PrimaryKey(autoGenerate = true)
    var id: Long = 0L,
    @ColumnInfo(name = "shopping_list_id")
    var shoppingListId: Long = 0L,
    @ColumnInfo(name = "name")
    val name: String,
    @ColumnInfo(name = "category")
    val category: String,
    @ColumnInfo(name = "quantity")
    val quantity: String,
    @ColumnInfo(name = "set_quantity")
    val setQuantity: String,
    @ColumnInfo(name = "set_total")
    val setTotal: String,
    @ColumnInfo(name = "unit")
    val unit: String,
    @ColumnInfo(name = "item_ppu")
    val itemPPU: String,
    @ColumnInfo(name = "item_set_ppu")
    val itemSetPPU: String,
    @ColumnInfo(name = "notes")
    val notes: String,
    @ColumnInfo(name = "coupon_amount")
    val couponAmount: String,
    @ColumnInfo(name = "set_coupon_amount")
    val setCouponAmount: String,
    @ColumnInfo(name = "item_total")
    val itemTotal: String,
    @ColumnInfo(name = "item_set_total")
    val itemSetTotal: String,
    @ColumnInfo(name = "image_uri")
    val itemImageUri: String?,
    @ColumnInfo(name = "thumbnail_uri")
    val itemThumbnailUri: String?,
    @ColumnInfo(name = "is_in_cart")
    val isInCart: Boolean
)

ShoppingListItemDao 接口 #

@Dao
interface ShoppingListItemDao {
    //===== SHOPPING LIST TABLE =====//

    //Create
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertShoppingListItem(item: ShoppingListItemEntity)

    //Read
    @Query("SELECT (SELECT COUNT(*) FROM shopping_list_items WHERE shopping_list_id = :listId)")
    suspend fun countListItems(listId: Long): Int

    @Query("SELECT * FROM shopping_list_items WHERE shopping_list_id = :listId")
    fun getAllShoppingListItems(listId: Long): PagingSource<Int, ShoppingListItemEntity>

    @Query("SELECT * FROM shopping_list_items WHERE id = :id")
    suspend fun getShoppingListItemById(id: Long): ShoppingListItemEntity

    //Update
    @Update(entity = ShoppingListItemEntity::class)
    suspend fun updateShoppingListItem(item: ShoppingListItemEntity)

    //Delete
    @Delete
    suspend fun deleteShoppingListItem(item: ShoppingListItemEntity)

    //===== CUSTOM SORT TABLE =====//

    //Create
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertCustomSortShoppingListItem(item: CustomSortShoppingListItemEntity)

    //Read --> Throws error here
    @Query("SELECT (SELECT COUNT(*) FROM custom_sort_shopping_list_items WHERE shopping_list_id = :listId)")
    suspend fun countCustomSortListItems(listId: Long): Int

    @Query("SELECT * FROM custom_sort_shopping_list_items WHERE shopping_list_id = :listId")
    fun getAllCustomSortShoppingListItems(listId: Long): PagingSource<Int, CustomSortShoppingListItemEntity>

    //Update
    @Update(entity = ShoppingListItemEntity::class)
    suspend fun updateCustomSortShoppingListItem(item: CustomSortShoppingListItemEntity)

    //Delete
    @Delete
    suspend fun deleteCustomSortShoppingListItem(item: CustomSortShoppingListItemEntity)

    //Update All
    @Query("DELETE FROM custom_sort_shopping_list_items WHERE shopping_list_id = :listId")
    suspend fun deleteAllCustomSortShoppingListItems(listId: Long)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertAllCustomSortShoppingListItems(customItems: List<CustomSortShoppingListItemEntity>)
}

解决方案 #

不需要单独创建Dao，按以下步骤修复：

1. Database类添加新实体
   确保Room Database类的@Database注解中，entities数组包含CustomSortShoppingListItemEntity，否则Room不会自动创建对应的表。示例：

   @Database(
       entities = [ShoppingListEntity::class, ShoppingListItemEntity::class, CustomSortShoppingListItemEntity::class],
       version = 2, // 注意版本号需要更新
       exportSchema = true
   )
   abstract class AppDatabase : RoomDatabase() {
       abstract fun shoppingListItemDao(): ShoppingListItemDao
       // 其他Dao...
   }
   

2. 修复Update注解的实体参数
   updateCustomSortShoppingListItem方法中的@Update(entity = ShoppingListItemEntity::class)指定了错误的实体类，应该改为CustomSortShoppingListItemEntity：

   @Update(entity = CustomSortShoppingListItemEntity::class)
   suspend fun updateCustomSortShoppingListItem(item: CustomSortShoppingListItemEntity)
   

3. 升级数据库版本并处理迁移
   由于新增了实体，需要将Database的version号加1。如果不需要保留旧数据，可以临时使用fallbackToDestructiveMigration()快速测试：

   Room.databaseBuilder(context, AppDatabase::class.java, "app_database")
       .fallbackToDestructiveMigration()
       .build()
   

   若需要保留数据，需编写对应的Migration类处理表创建逻辑。

4. 清理并重新构建项目
   执行Clean Project和Rebuild Project操作，确保Room重新生成所有数据库相关的代码。

内容的提问来源于stack exchange，提问作者Raj Narayanan