android - 在 Kotlin 房间中插入多对多关系
问题描述
我正在使用 Android Room 开发一个项目,但我有点坚持使用多对多关系。我发现这个问题与我的类似,但我仍然无法让它发挥作用。
所以我基本上遵循文档并做了一个配方和成分关系:
@Entity(tableName ="zutaten_table")
data class ZutatenData(
@PrimaryKey (autoGenerate = true) val zutatid : Int,
@ColumnInfo(name = "zutname") var zutname : String,
//and some more columns
)
@Entity(tableName = "rezepte_table")
data class RezepteData(
@PrimaryKey (autoGenerate = true) val rezeptid : Int,
@ColumnInfo(name = "rezname") var rezname : String,
@ColumnInfo(name = "bild") var bild:Int
)
@Entity(primaryKeys = ["zutatid", "rezeptid"])
data class RefZutatRezept(
val zutatid: Int,
val rezeptid: Int
)
data class ZutatenWithRezept(
@Embedded val zutat: ZutatenData,
@Relation(
parentColumn = "zutatid",
entity = RezepteData::class,
entityColumn = "rezeptid",
associateBy = Junction(value = RefZutatRezept::class,
parentColumn = "zutatid",
entityColumn = "rezeptid"
)
)
val rezepte: List<RezepteData>
)
data class RezeptWithZutat(
@Embedded val rezept: RezepteData,
@Relation(
parentColumn = "rezeptid",
entityColumn = "zutatid",
associateBy = Junction(RefZutatRezept::class)
)
val zutaten: List<ZutatenData>
)
我对每种数据类型都有一个 Dao(不舒尔这是正确的方法......)
@Dao
interface ZutatDao{
@Query("SELECT * FROM zutaten_table ORDER BY zutname ASC")
fun getAlphabetizedZutaten(): LiveData<List<ZutatenData>>
@Insert(onConflict = OnConflictStrategy.IGNORE)
suspend fun insert(zutname: ZutatenData)
@Update
suspend fun update(zutname: ZutatenData)
@Query("DELETE FROM zutaten_table")
suspend fun deleteAll()
}
@Dao
interface RezeptDao{
@Query("SELECT * FROM rezepte_table ORDER BY rezname ASC")
fun getRezepte() : LiveData<List<RezepteData>>
@Insert(onConflict = OnConflictStrategy.IGNORE)
suspend fun insert(rezepte : RezepteData) :Long
@Query("DELETE FROM rezepte_table")
suspend fun deleteAll()
}
@Dao
interface ZutatwithRezeptDao{
@Transaction
@Query("SELECT * FROM zutaten_table ORDER BY zutname ASC")
suspend fun getZutatenWithRezept(): List<ZutatenWithRezept>
@Insert(onConflict = OnConflictStrategy.IGNORE)
suspend fun insert(join:RefZutatRezept)
}
@Dao
interface RezeptwithZutatDao {
@Transaction
@Query("SELECT * FROM rezepte_table ORDER BY rezname ASC")
suspend fun getRezeptwithZutat(): List<RezeptWithZutat>
@Insert(onConflict = OnConflictStrategy.IGNORE)
suspend fun insert(join: RefZutatRezept)
}
我现在想插入一个 RezeptWithZutat(我现在正在 populateDatabase 函数中做。)
var ListZutatRez1 = listOf<ZutatenData>( z_kart, z_quark, z_lein)
var rez = RezepteData(0,"Kartoffeln mit Quark", R.drawable.kartoffelnquark)
var rezges =RezeptWithZutat(rez, ListZutatRez1)
rezzut.insert(rezges)
在最后一行我得到错误:“类型不匹配:推断的类型是 RezeptWithZutat 但 RefZutatRezept 是预期的”我尝试了一些东西,但它们都给我带来了不同的错误。
所以我的问题是:将 RezeptWithZutat 自动插入到表中是正确的方法(为 RezeptData 和所有存在的 ZutatData 条目创建一个条目,并链接 RefZutatRezept 表中的所有内容。)
解决方案
这不是简单的一行修复。
首先,您需要能够检测是否存在 Zutaten,并且可能检测是否存在 Rezepte(因此现有 Rezept 的 RezeptWithZutat 确实会创建一个新的,但会添加到现有的)。
所以前两个新的道
- 请注意,以下代码使用抽象类而不是接口,因此使用抽象乐趣而不是乐趣。
- 所有的 dao 也被合并到一个抽象类中,即AllDao。
- 注意我不做 LiveData 的东西,所以代码不使用 LiveData 并且我复制的代码已经删除了所有 LiveData 以允许工作演示。
:-
@Query("SELECT zutatid FROM zutaten_table WHERE zutname=:zutname")
abstract fun getZutatenIdByName(zutname: String): Int
@Query("SELECT rezeptid FROM rezepte_table WHERE rezname=:rezname")
abstract fun getRezepteIDByName(rezname: String): Int
如果 zutaten/rezept 不存在,这些将返回 id 或 0。
接下来是一个函数,在 AllDao 中(它直接使用 Dao 的),插入 RezeptWithZutat:-
fun insertZutatenWithRezept(rezeptWithZutat: RezeptWithZutat) {
var rid = getRezepteIDByName(rezeptWithZutat.rezept.rezname)
/* find or insert Rezepte according to name */
if (rid < 1) {
rid = insert(RezepteData(0,rezeptWithZutat.rezept.rezname,rezeptWithZutat.rezept.bild)).toInt()
}
if (rid < 1) {
/*
could not find existing Rezepte or insert a new one?????
should not happen but in case do something here
*/
} else {
for (z in rezeptWithZutat.zutaten) {
var zid = getZutatenIdByName(z.zutname)
if (zid < 1) {
zid = insert(ZutatenData(0, z.zutname)).toInt()
}
if (zid < 1) {
/*
could not find existing Zutaten or insert new one?????
should not happen but in case do something here
*/
} else {
insert(RefZutatRezept(zutatid = zid, rezeptid = rid))
}
}
}
}
- 这不是最有效的方式,因为不存在的 Zutatens 可以一起插入(但使用更复杂的代码),同样 RefZutatRezept 可以一起插入(再次使用更复杂的代码)。
演示(为了方便/简洁,在主线程上运行)
使用上述内容和您的代码/ dao(如上更改)然后在活动中:-
db = TheDatabase.getInstance(this)
dao = db.getAllDao()
val r1 = dao.insert(RezepteData(rezeptid = 0,rezname = "REZ1",bild = 1))
val r2 = dao.insert(RezepteData(rezeptid = 0, rezname = "REZ2", bild = 1))
val milk = dao.insert(ZutatenData(zutatid = 0,zutname = "Milk"))
val sugar = dao.insert(ZutatenData(zutatid = 0,zutname = "Sugar"))
val flour = dao.insert(ZutatenData(zutatid = 0,zutname = "Flour"))
val eggs = dao.insert(ZutatenData(zutatid = 0,zutname = "Eggs"))
dao.insert(RefZutatRezept(milk.toInt(),r1.toInt()))
dao.insert(RefZutatRezept(flour.toInt(),r1.toInt()))
dao.insert(RefZutatRezept(eggs.toInt(),r1.toInt()))
Log.d("STAGE1", "data before adding R-With-Z")
showAllRezeptWithZutatsInDatabase()
var newRWithZ = RezeptWithZutat(
RezepteData(0,"REZ3",1),zutaten = listOf(
ZutatenData(0,"salt"),
ZutatenData(0,"nutmeg"),
ZutatenData(0,"cornflour"),
ZutatenData(0,"corriander"),
ZutatenData(0,"Milk"), // exists
ZutatenData(0,"Sugar"), // exists
ZutatenData(0,"More Sugar")
)
)
dao.insertZutatenWithRezept(newRWithZ)
Log.d("STAGE2", "data after adding R-With-Z")
showAllRezeptWithZutatsInDatabase()
newRWithZ = RezeptWithZutat(
RezepteData(0,"REZ3",1),
zutaten = listOf(
ZutatenData(0,"leek"),
ZutatenData(0,"apple"),
ZutatenData(0,"banana"),
ZutatenData(0,"pineapple"),
ZutatenData(0,"Milk"), // exists
ZutatenData(0,"Sugar"),
ZutatenData(0,"More Sugar")
) //Rezepte also exists
)
dao.insertZutatenWithRezept(newRWithZ)
Log.d("STAGE3", "data after adding R-With-Z")
showAllRezeptWithZutatsInDatabase()
}
fun showAllRezeptWithZutatsInDatabase() {
for(rzr: RezeptWithZutat in dao.getRezeptwithZutat()) {
Log.d("DBINFO","Rezept is ${rzr.rezept.rezname} Bild is ${rzr.rezept.bild} ID is ${rzr.rezept.rezeptid}")
for(z: ZutatenData in rzr.zutaten) {
Log.d("DBINFO","\tZutaten is ${z.zutname} ID is ${z.zutatid}")
}
}
}
运行(一次)时,日志包括:-
2021-10-25 20:55:47.370 D/STAGE1: data before adding R-With-Z
2021-10-25 20:55:47.377 D/DBINFO: Rezept is REZ1 Bild is 1 ID is 1
2021-10-25 20:55:47.377 D/DBINFO: Zutaten is Milk ID is 1
2021-10-25 20:55:47.377 D/DBINFO: Zutaten is Flour ID is 3
2021-10-25 20:55:47.377 D/DBINFO: Zutaten is Eggs ID is 4
2021-10-25 20:55:47.377 D/DBINFO: Rezept is REZ2 Bild is 1 ID is 2
2021-10-25 20:55:47.425 D/STAGE2: data after adding R-With-Z
2021-10-25 20:55:47.428 D/DBINFO: Rezept is REZ1 Bild is 1 ID is 1
2021-10-25 20:55:47.428 D/DBINFO: Zutaten is Milk ID is 1
2021-10-25 20:55:47.428 D/DBINFO: Zutaten is Flour ID is 3
2021-10-25 20:55:47.428 D/DBINFO: Zutaten is Eggs ID is 4
2021-10-25 20:55:47.428 D/DBINFO: Rezept is REZ2 Bild is 1 ID is 2
2021-10-25 20:55:47.428 D/DBINFO: Rezept is REZ3 Bild is 1 ID is 3
2021-10-25 20:55:47.428 D/DBINFO: Zutaten is salt ID is 5
2021-10-25 20:55:47.429 D/DBINFO: Zutaten is nutmeg ID is 6
2021-10-25 20:55:47.429 D/DBINFO: Zutaten is cornflour ID is 7
2021-10-25 20:55:47.429 D/DBINFO: Zutaten is corriander ID is 8
2021-10-25 20:55:47.429 D/DBINFO: Zutaten is Milk ID is 1
2021-10-25 20:55:47.429 D/DBINFO: Zutaten is Sugar ID is 2
2021-10-25 20:55:47.429 D/DBINFO: Zutaten is More Sugar ID is 9
2021-10-25 20:55:47.459 D/STAGE3: data after adding R-With-Z
2021-10-25 20:55:47.461 D/DBINFO: Rezept is REZ1 Bild is 1 ID is 1
2021-10-25 20:55:47.461 D/DBINFO: Zutaten is Milk ID is 1
2021-10-25 20:55:47.461 D/DBINFO: Zutaten is Flour ID is 3
2021-10-25 20:55:47.461 D/DBINFO: Zutaten is Eggs ID is 4
2021-10-25 20:55:47.461 D/DBINFO: Rezept is REZ2 Bild is 1 ID is 2
2021-10-25 20:55:47.461 D/DBINFO: Rezept is REZ3 Bild is 1 ID is 3
2021-10-25 20:55:47.462 D/DBINFO: Zutaten is salt ID is 5
2021-10-25 20:55:47.462 D/DBINFO: Zutaten is nutmeg ID is 6
2021-10-25 20:55:47.462 D/DBINFO: Zutaten is cornflour ID is 7
2021-10-25 20:55:47.462 D/DBINFO: Zutaten is corriander ID is 8
2021-10-25 20:55:47.462 D/DBINFO: Zutaten is Milk ID is 1
2021-10-25 20:55:47.462 D/DBINFO: Zutaten is Sugar ID is 2
2021-10-25 20:55:47.462 D/DBINFO: Zutaten is More Sugar ID is 9
2021-10-25 20:55:47.462 D/DBINFO: Zutaten is leek ID is 10
2021-10-25 20:55:47.462 D/DBINFO: Zutaten is apple ID is 11
2021-10-25 20:55:47.462 D/DBINFO: Zutaten is banana ID is 12
2021-10-25 20:55:47.462 D/DBINFO: Zutaten is pineapple ID is 13
建议
- RefZutatRezept 表的 rezeptid 列上的索引。
- rezname 和 zutname 的唯一索引(例如,当前您可能拥有 id 为 1 的 Milk 和 id 为 2 的 Milk 等等)。
- RefZutatRezept 表上的外键约束以强制引用完整性并级联删除和更新
Cascade 会将父级的相关更改应用于子级。
例如,如果您删除了一个 Zutaten,那么 RefZutatRezept 中所有引用该 Zutaten 的行都将被自动删除
- 没有你可能会遇到问题,因为会有对不存在的 Zutaten 的引用。同样对于任何 Rezepte 的
onUpdate 操作并不那么重要,因为您不太可能更改 zutatid 或 rezptid。
因此,您不妨考虑以下事项:-
@Entity(
tableName ="zutaten_table",
indices = [ Index(value = ["zutname"],unique = true)]
)
data class ZutatenData(
@PrimaryKey(autoGenerate = true) val zutatid : Int,
@ColumnInfo(name = "zutname") var zutname : String
//and some more columns
)
和
@Entity(tableName = "rezepte_table",
indices = [Index(value = ["rezname"],unique = true)]
)
data class RezepteData(
@PrimaryKey(autoGenerate = true) val rezeptid : Int,
@ColumnInfo(name = "rezname") var rezname : String,
@ColumnInfo(name = "bild") var bild:Int
)
和
@Entity(
primaryKeys = ["zutatid", "rezeptid"],
indices = [Index(value = ["rezeptid"])],
foreignKeys = [
ForeignKey(
entity = ZutatenData::class,
parentColumns = ["zutatid"],
childColumns = ["zutatid"],
onDelete = ForeignKey.CASCADE,
onUpdate = ForeignKey.CASCADE
),
ForeignKey(
entity = RezepteData::class,
parentColumns = ["rezeptid"],
childColumns = ["rezeptid"],
onDelete = ForeignKey.CASCADE,
onUpdate = ForeignKey.CASCADE
)
]
)
data class RefZutatRezept(
val zutatid: Int,
val rezeptid: Int
)
推荐阅读
- firebase - Firestore 数据库可容纳多个用户独立排列项目
- c++ - 为什么 CreateProcess API 调用会导致内存访问冲突?
- reactjs - react-router 重定向不会更改 Switch 中的 url
- excel - 使用 Excel 保存带有名称的 Word 文件
- sql - 所有公司的没有 ClosePrice 的日期的 SQL
- amazon-s3 - 从 S3 加载数据时,我应该如何构建 Sapper/Svelte 路由?
- python - 从 VS Code 调试 python(在 Docker 中)
- machine-learning - 使用基于更大语料库的另一个模型添加词汇并改进词嵌入
- javascript - 无法收听克隆的键盘输入
- nginx - 是否应该将 CORS 标头添加到我的服务器或 CDN?