时间戳

首页 > 解决方案 > 如何在实体内部的android房间中使用外键和复合键，它只保存一个键，另一个是隐式给出的

foreign-keys - 如何在实体内部的android房间中使用外键和复合键，它只保存一个键，另一个是隐式给出的

问题描述

假设我有一个类别实体，其中只有两种类型（A 类和 B 类）。主键是由类别名称和布尔值组成的复合键，说明该类别是否属于类别 A（否则为 B）。

一个 Item 可以同时属于这两种类别类型。因此，在我的项目实体中，我有两个外键（categoryAName 和 categoryBName），如果它们不属于任何类别，它们都可能为空。如果仅隐式给出类别类型，是否有办法将外键与其在项目实体中的两个复合键映射？

我的代码如下所示：

@Entity(tableName = "category_table", primaryKeys = ["name", "isCategoryA"])
data class CategoryEntity(val name: String, val isCategoryA: Boolean)


@Entity(
    tableName = "item_table",
    foreignKeys = [ForeignKey(
        entity = CategoryEntity::class,
        parentColumns = ["name", "isCategoryA"],
        childColumns = ["categoryAName", "???"], // how would one set the colum, if we don't want to save the value, when it's known implicitly?
        onDelete = ForeignKey.SET_NULL,
        onUpdate = ForeignKey.CASCADE
    ), ForeignKey(
        entity = CategoryEntity::class,
        parentColumns = ["name", "isCategoryA"],
        childColumns = ["categoryBName", "???"], // isCategoryA would always be false
        onDelete = ForeignKey.SET_NULL,
        onUpdate = ForeignKey.CASCADE
    )]
)
data class ItemEntity(
    @PrimaryKey val name: String,
    var categoryAName: String?,
    var categoryBName: String?
)

有没有办法实现这一点，而无需添加仅包含冗余信息的两个额外列？还是有更好的方法来实现这一点？

标签： foreign-keysandroid-roomcomposite-key

解决方案

还是有更好的方法来实现这一点？

我相信更好的方法是合并一个多对多关系，因此一个表（实体）可以促进这一点。尽管我确实发现您提供的解释难以理解，因为您说类别属于类别。

也就是说，您要么有 2 个类别，要么有 4 个类别（该示例假设 4 个类别分别属于自己或另一个）。

一个项目似乎可以有 1、2、3 或 4 个类别。

因此，您可以：-

  • 类别的实体
  • 项目的实体
  • 和一个实体，用于将项目映射到它可能具有的 4 个（或 2 个）类别。

所以 ：-

CategoryEntity可以是：-

@Entity(tableName = "category_table",
    indices = [
        Index(
            value = ["name","isCategoryA"],
            unique = true
        )
    ])
data class CategoryEntity(
    @PrimaryKey(autoGenerate = true) val categoryId: Long,
    val name: String,
    val isCategoryA: Boolean
    )
  • 添加唯一标识类别的 id 列。

ItemEntity可以是：-

@Entity(
    tableName = "item_table"
)
data class ItemEntity(
    @PrimaryKey val name: String
)
  • 大大简化

项目类别图

@Entity(
        primaryKeys = ["categoryId","itemName"],
    foreignKeys = [
        ForeignKey(
            entity = CategoryEntity::class,
            parentColumns = ["categoryId"],
            childColumns = ["categoryId"]
        ),
        ForeignKey(
            entity = ItemEntity::class,
            parentColumns = ["name"],
            childColumns = ["itemName"]
        )
    ]
)
data class ItemCategoryMap(
    val categoryId: Long, val itemName: String
)

根据映射表ItemWithCategories将 Item 与相应类别组合的 POJO ：-

data class ItemWithCategories(
    @Embedded
    val item: ItemEntity,
    @Relation(
        entity = CategoryEntity::class,
        entityColumn = "categoryId",
        parentColumn = "name",
        associateBy = Junction(
            ItemCategoryMap::class,
            parentColumn = "itemName",
            entityColumn = "categoryId")
    )
    val categories: List<CategoryEntity>
)

阿道AllDao :-

@Dao
interface AllDao {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(categoryEntity: CategoryEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(itemEntity: ItemEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(itemCategoryMap: ItemCategoryMap): Long
    @Transaction
    @Query("SELECT * FROM item_table")
    fun getAllItemsWithCategories(): List<ItemWithCategories>
    @Query("SELECT name FROM ITEM_TABLE WHERE rowid=:rowid")
    fun getItemNameByRowid(rowid: Long): String
}

@Database TheDatabase：-

@Database(entities = [CategoryEntity::class, ItemEntity::class, ItemCategoryMap::class], version = 1, exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDao

    companion object {
        private var instance: TheDatabase? = null
         public fun getInstance(context: Context): TheDatabase {
             if (instance == null) {
                 instance = Room.databaseBuilder(context,TheDatabase::class.java,"mydb")
                     .allowMainThreadQueries()
                     .build()
             }
             return instance as TheDatabase
         }
    }
}
  • 在主线程上运行演示的注意事项

最后一个 Activity 将所有内容放在一起并输出具有类别 (0-4) 的提取项目：-

class MainActivity : AppCompatActivity() {

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

        db = TheDatabase.getInstance(this)
        dao = db.getAllDao()

        val catATrueID =dao.insert(CategoryEntity(0,"CatA",true))
        val catAFalseId = dao.insert(CategoryEntity(0,"CatA",false))
        val catBTrueId = dao.insert(CategoryEntity(0,"CatB",true))
        val catBFalseId = dao.insert(CategoryEntity(0,"CatB",false))
        val catOoops = dao.insert(CategoryEntity(0,"CatB",false))

        val item1name = dao.getItemNameByRowid(dao.insert(ItemEntity("Item1")))
        dao.insert(ItemCategoryMap(catATrueID,item1name))
        dao.insert(ItemCategoryMap(catAFalseId,item1name))
        dao.insert(ItemCategoryMap(catBTrueId,item1name))
        dao.insert(ItemCategoryMap(catBFalseId,item1name))

        val item2name = dao.getItemNameByRowid(dao.insert(ItemEntity("Item2")))
        dao.insert(ItemCategoryMap(catBFalseId,item2name))
        dao.insert(ItemCategoryMap(catATrueID,item2name))

        val item3name = dao.getItemNameByRowid(dao.insert(ItemEntity("Item3")))
        dao.insert(ItemCategoryMap(catBTrueId,item3name))

        val item4name = dao.getItemNameByRowid(dao.insert(ItemEntity("item4")))

        for(iwc: ItemWithCategories in dao.getAllItemsWithCategories()) {
            Log.d("DBINFO",
                "Item is ${iwc.item.name} has ${iwc.categories.size} categories." )
            for (c: CategoryEntity in iwc.categories) {
                Log.d("DBINFO","Category is ${c.name} ID is ${c.categoryId} IsCategoryA is ${c.isCategoryA}")
            }
        }
    }
}

结果（仅为演示设计运行一次）：-

2021-06-24 14:56:02.614 D/DBINFO: Item is Item1 has 4 categories.
2021-06-24 14:56:02.614 D/DBINFO: Category is CatA ID is 1 IsCategoryA is true
2021-06-24 14:56:02.614 D/DBINFO: Category is CatA ID is 2 IsCategoryA is false
2021-06-24 14:56:02.615 D/DBINFO: Category is CatB ID is 3 IsCategoryA is true
2021-06-24 14:56:02.615 D/DBINFO: Category is CatB ID is 4 IsCategoryA is false
2021-06-24 14:56:02.615 D/DBINFO: Item is Item2 has 2 categories.
2021-06-24 14:56:02.615 D/DBINFO: Category is CatB ID is 4 IsCategoryA is false
2021-06-24 14:56:02.615 D/DBINFO: Category is CatA ID is 1 IsCategoryA is true
2021-06-24 14:56:02.615 D/DBINFO: Item is Item3 has 1 categories.
2021-06-24 14:56:02.615 D/DBINFO: Category is CatB ID is 3 IsCategoryA is true
2021-06-24 14:56:02.615 D/DBINFO: Item is item4 has 0 categories.

附加的

关于

如我的代码示例所示，一个项目可能只属于每种类型的一个类别。因此，该项目最多只能属于 2 个类别。此外，两个类别可能具有相同的名称，但仅当它们属于不同的类型时（这就是存在复合键的原因）。不应该有两个具有相同名称的类别，具有相同的类型。

然后，您可以通过在类别名称项目名称组合上具有唯一索引来限制唯一类别名称。因此，可以将ItemCategoryMap修改为：-

@Entity(
        primaryKeys = ["categoryId","itemName"],
    indices = [Index("itemName","categoryName",unique = true)] /* ADDED FOR ADDITIONAL */,
    foreignKeys = [
        ForeignKey(
            entity = CategoryEntity::class,
            parentColumns = ["categoryId"],
            childColumns = ["categoryId"]
        ),
        ForeignKey(
            entity = ItemEntity::class,
            parentColumns = ["name"],
            childColumns = ["itemName"]
        )
    ]
)
data class ItemCategoryMap(
    val categoryId: Long , val categoryName: String /* ADDED FOR ADDITIONAL */, val itemName: String
)

由于现在需要类别名称，因此需要类别名称，因为索引必须特定于单个表。因此，在插入 categoryName 时是必需的。

因此修改调用（MainActivity）以更改为使用（其余代码保持原样）：-

    val item1name = dao.getItemNameByRowid(dao.insert(ItemEntity("Item1")))
    dao.insert(ItemCategoryMap(catATrueID,"CatA",item1name))
    dao.insert(ItemCategoryMap(catAFalseId,"CatA",item1name))
    dao.insert(ItemCategoryMap(catBTrueId,"CatB",item1name))
    dao.insert(ItemCategoryMap(catBFalseId,"CatB",item1name))

    val item2name = dao.getItemNameByRowid(dao.insert(ItemEntity("Item2")))
    dao.insert(ItemCategoryMap(catBFalseId,"CatB",item2name))
    dao.insert(ItemCategoryMap(catATrueID,"CatA",item2name))

    val item3name = dao.getItemNameByRowid(dao.insert(ItemEntity("Item3")))
    dao.insert(ItemCategoryMap(catBTrueId,"CatB",item3name))

    val item4name = dao.getItemNameByRowid(dao.insert(ItemEntity("item4")))

结果现在是：-

2021-06-25 10:01:22.671 D/DBINFO: Item is Item1 has 2 categories.
2021-06-25 10:01:22.671 D/DBINFO: Category is CatA ID is 1 IsCategoryA is true
2021-06-25 10:01:22.671 D/DBINFO: Category is CatB ID is 3 IsCategoryA is true
2021-06-25 10:01:22.671 D/DBINFO: Item is Item2 has 2 categories.
2021-06-25 10:01:22.672 D/DBINFO: Category is CatA ID is 1 IsCategoryA is true
2021-06-25 10:01:22.672 D/DBINFO: Category is CatB ID is 4 IsCategoryA is false
2021-06-25 10:01:22.672 D/DBINFO: Item is Item3 has 1 categories.
2021-06-25 10:01:22.672 D/DBINFO: Category is CatB ID is 3 IsCategoryA is true
2021-06-25 10:01:22.672 D/DBINFO: Item is item4 has 0 categories.

那是：-

  • 尝试插入重复的 CatB false usingval catOoops = dao.insert(CategoryEntity(0,"CatB",false))被忽略，因此按照以下方式添加重复项（即仅 4 个类别行）：-

  • 在此处输入图像描述

  • Item1 只有第一个 CatA（即 CatA true，CatA false）被忽略。CatB 也是如此。

  • Item2 两者都有，因为插入两者时没有违反规则。

  • Item3 只有一个没有违反规则的。

  • 项目没有。

如果您想获得具有关联项目的类别，那么您可以拥有CategoryWithItems：-

data class CategoryWithItems (
    @Embedded
    val category: CategoryEntity,
    @Relation(
        entity = ItemEntity::class,
        entityColumn = "name",
        parentColumn = "categoryId",
        associateBy = Junction(
            ItemCategoryMap::class,
            parentColumn = "categoryId",
            entityColumn = "itemName")
    )
    val items: List<ItemEntity>
)

还有一个诸如：-

@Query("SELECT * FROM category_table")
fun getAllCategoriesWithItems(): List<CategoryWithItems>

使用（上面加载的数据）： -

    for(cwi: CategoryWithItems in dao.getAllCategoriesWithItems()) {
        Log.d("DBINFO", "Category is ${cwi.category.name} isCategoryA is ${cwi.category.isCategoryA} id is ${cwi.category.categoryId}, it has ${cwi.items.size} associated Items")
        for(i: ItemEntity in cwi.items) {
            Log.d("DBINFO","\tItem is ${i.name}")
        }
    }

结果将是：-

2021-06-25 10:53:24.852 D/DBINFO: Category is CatA isCategoryA is true id is 1, it has 2 associated Items
2021-06-25 10:53:24.852 D/DBINFO:   Item is Item1
2021-06-25 10:53:24.852 D/DBINFO:   Item is Item2
2021-06-25 10:53:24.852 D/DBINFO: Category is CatA isCategoryA is false id is 2, it has 0 associated Items
2021-06-25 10:53:24.853 D/DBINFO: Category is CatB isCategoryA is true id is 3, it has 2 associated Items
2021-06-25 10:53:24.853 D/DBINFO:   Item is Item1
2021-06-25 10:53:24.853 D/DBINFO:   Item is Item3
2021-06-25 10:53:24.853 D/DBINFO: Category is CatB isCategoryA is false id is 4, it has 1 associated Items
2021-06-25 10:53:24.853 D/DBINFO:   Item is Item2

当然可以使用 WHERE 子句。

注意/警告在代码中的使用

val item1name = dao.getItemNameByRowid(dao.insert(ItemEntity("Item1")))

（等，即通过rowid获取插入项目的名称，如果重新运行将导致失败，它的使用只是为了简化演示代码）。

上述解决方案还可以满足更多的 2 个类别名称，即类别名称没有限制（存储限制除外）。

推荐阅读