插入Kotlin房间的多对多关系

Question

我正在做一个使用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现在(我正在做的populateddatabase函数在此刻。)

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表中的所有内容)

Answer 1

这不是一个简单的一行修复。

首先，您需要能够检测是否存在Zutaten，以及是否存在Rezepte(因此，对于现有Rezept的RezeptWithZutat会创建一个新的RezeptWithZutat，但会添加到现有的rezepat)。

所以前两个新刀

• 注意下面的代码没有使用接口，而是使用了抽象类，所以使用了抽象的fun而不是fun。
• 所有的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

1. RefZutatRezept表rezeptid列的索引。
2. 在rezname和zutname上的唯一索引(目前您可以使用例如id为1的Milk和id为2的Milk等)。
RefZutatRezept表上的
3. 外键约束，以强制引用完整性和级联删除和更新

   1. Cascade会将父级的相关更改应用到子级。

   2. 。如果你删除了一个Zutaten，那么RefZutatRezept中所有引用Zutaten的行都会被自动删除

      1. 没有你可能会遇到问题，因为会有一个不存在的祖塔顿的引用。同样适用于任何Rezepte的

   3. 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
)