POJO中的POJO，实体关系问题

Question

我的数据库中有五个表: AREA，AREA_TYPE，SAMPLE，PACK，UNIT

    @Entity(tableName = "AREA")
    data class AreaEntity(
       @PrimaryKey val id:String,
       val title:String,
       @ColumnInfo(name = "area_type_id") val areaTypeId:Int,
       @ColumnInfo(name = "is_active") val isActive:Boolean
    )

    @Entity(tableName = "AREA_TYPE")
    data class AreaTypeEntity(
       @PrimaryKey val id:String,
       val title:String,
       @ColumnInfo(name = "title") val parentAreaId : String
    )

    @Entity(tableName = "SAMPLE")
    data class SampleEntity(
       @PrimaryKey val id:String,
       val title:String,
    )

    @Entity(tableName = "PACK")
    data class PackEntity(
       @PrimaryKey val id:String,
       val title:String,
    )

    @Entity(tableName = "UNIT")
    data class UnitEntity(
       @PrimaryKey val id:String,
       @ColumnInfo(name = "sample_id") val parentAreaId : String,
       @ColumnInfo(name = "area_id") val areaId:Int,
       @ColumnInfo(name = "pack_type_id") val packTypeId: Int,
       @ColumnInfo(name = "is_active") val isActive:Boolean
    )

• 单元表有三个外键: sample_id、area_id、pack_id

。

• 每个区域与区域类型有一对一的关系。

我有一个用于Area-AreaType关系的AreaPOJO：

     data class AreaPOJO (
           @Embedded val areaEntity : AreaEntity

           @Relation (
                   parentColumn = "area_id",
                   entityColumn = "id"
           )
           val areaTypeEntity : AreaTypeEntity
     )

表的可视视图(https://i.stack.imgur.com/bXzl5.png)

因此，我假设我将有一个POJO作为单元，用于这样的关系：

     data class UnitPOJO (
           @Embedded val unitEntity : UnitEntity

           @Relation (
                   parentColumn = "area_id",
                   entityColumn = "id"
           )
           val areaEntity : AreaEntity
           
           @Relation (
                   parentColumn = "pack_id",
                   entityColumn = "id"
           )
           val packEntity : PackEntity
           
           @Relation (
                   parentColumn = "sample_id",
                   entityColumn = "id"
           )
           val sampleEntity : SampleEntity
      )

有了这个POJO，我可以得到AreaEntity，SampleEntity，UnitEntity，但是不能得到AreaTypeEntity for UnitPOJO。当我使用AreaPOJO而不是AreaEntity时，我有一个编译错误，它告诉我使用AreaPOJO的“前缀”。当我使用前缀时，这次AreaPOJO会出现一个错误，即它找不到用于关系的列名。

因此，我陷入了困境:)简单地说，我需要所有五个表中的所有字段用于查询：

    "SELECT * FROM UNIT 
              INNER JOIN AREA ON UNIT.AREA_ID = AREA.ID
              INNER JOIN AREA_TYPE ON AREA.AREA_TYPE_ID = AREA_TYPE.ID 
              INNER JOIN SAMPLE ON UNIT.SAMPLE_ID = SAMPLE.ID 
              INNER JOIN PACK ON UNIT.PACK_ID = PACK.ID"  

Answer 1

首先，使用前缀，这是一个避免歧义列名的选项(例如，哪个id列是正确的？(修辞)但是接下来您必须使用查询来包含AS (隐式或显式)来重命名提取的列。

我建议，使用独特的列名是避免这种模棱两可的方法。

在祖父母/孙子身上。

简而言之，您是接近的，但您检索的是一个AreaPOJO (具有类型的区域)，而不是一个AreaEntity，但是您必须告诉Room使用AreaEntity类(因为该类用于确定AreaEntity的列，然后AreaPOJO中的@关系知道获取嵌入的AreaType)。

因此，虽然未经过测试但已成功编译，但考虑到以下各点：

@Entity(tableName = "UNIT")
data class UnitEntity(
    @PrimaryKey val id:String,
    @ColumnInfo(name = "sample_id") val parentAreaId : String,
    @ColumnInfo(name = "area_id") val areaId:Int,
    @ColumnInfo(name = "pack_type_id") val packTypeId: Int,
    @ColumnInfo(name = "is_active") val isActive:Boolean
)
@Entity(tableName = "AREA_TYPE")
data class AreaTypeEntity(
    @PrimaryKey @ColumnInfo(name = "area_type_id") val id:String, //<<<<< unique name
    val title:String,
    @ColumnInfo(name = "area_type_title") val parentAreaId : String
)

data class AreaPOJO(
    @Embedded val areaEntity : AreaEntity,
    @Relation(
        parentColumn = "area_type_id", //<<<<< changed accrodingly
        entityColumn = "area_type_id" //<<<<< changed accordingly
    )
    val areaTypeEntity : AreaTypeEntity
)

data class UnitPOJO (
    @Embedded val unitEntity : UnitEntity,

    @Relation (
        entity = AreaEntity::class, //<<<<< ADDED 
        parentColumn = "area_id",
        entityColumn = "area_id"
    )
    val areaWithAreaType : AreaPOJO,
    @Relation (
        parentColumn = "pack_type_id",
        entityColumn = "pack_id"
    )
    val packEntity : PackEntity,
    @Relation (
        parentColumn = "sample_id",
        entityColumn = "sample_id"
    )
    val sampleEntity : SampleEntity
)

• 注意到UnitPOJO中的pack_type_id和pack_id，而不是sample_id和sample_id中的示例引用/关系。
  • I建议考虑使用唯一的名称，例如pack_type_id正在引用/映射单元和包之间的关系，也许可以在单元中命名列pack_id_map。因此，列名更具有描述性，而且也是唯一的。缺点是有更多的coding.

使用上面的@查询可以是:-

@Transaction
@Query("SELECT * FROM UNIT")
fun getUnitsWithRelations(): List<UnitPOJO>

• 根据是否使用流/实时数据等进行了明显的调整。

说到这一点，上面提到的效率很低，因为当Room处理它构建的@Relation和每个@Relation从父级获取所有子级的底层查询时(我相信在每个父级的基础上)。在您的示例中，您似乎有1到多个关系，因此可以使用@Embedded，但查询必须更加复杂。

工作示例

下面是一个基于您的代码的工作示例

• 同时使用@Relation和@Embedded分辨率
  • @Relation‘s’s和AreaPOJO
  • @Embedded版本都以
  • 为前缀

Units)

• Extracts

• 向每个表中添加数据(每个表3行，除了5行-单元和相关数据使用alternatives

• includes外键约束来强制引用和维护完整性-参见https://sqlite.org/foreignkeys.html

应该指出的是，有些变化已经做了，因为我相信你，一些不寻常的，在猜测中，没有任何关系。例如，在只需要一个区域的情况下，您似乎具有与AreaType相关的区域。也就是说，一个区域将有一个AreaType作为父区域，但是如果一个ArearType也有一个区域作为父区域，那么您将得到鸡和蛋场景。

• 假设一个区域有许多可用的AreaTypes中的一个作为父母。

首先是类(见注释) :-

@Entity(
    tableName = "AREA",

    /* Enforces/Maintains referential Integrity */
    /* i.e does not allow orphans */
    foreignKeys = [
        ForeignKey(
            entity =  AreaTypeEntity::class,
            parentColumns =  ["area_type_id"],
            childColumns = ["area_type_id_map" ],
            onDelete = ForeignKey.CASCADE /* ????? */,
            onUpdate = ForeignKey.CASCADE /* ????? */
        )
    ]
)
data class AreaEntity(
    @PrimaryKey @ColumnInfo(name = "area_id")val id:String, //<<<<< unique name
    @ColumnInfo(name = "area_title") val title:String,
    @ColumnInfo(name = "area_type_id_map") val areaTypeId:String, //<<<<< see Area Type
    @ColumnInfo(name = "area_is_active") val isActive:Boolean
)
@Entity(tableName = "SAMPLE")
data class SampleEntity(
    @PrimaryKey @ColumnInfo(name = "sample_id") val id:String, //<<<<< unique name
    @ColumnInfo(name = "sample_title") val title:String,
)
@Entity(tableName = "PACK")
data class PackEntity(
    @PrimaryKey @ColumnInfo(name = "pack_id") val id:String, //<<<<< unique name
    @ColumnInfo(name = "pack_title") val title:String,  //<<<<< unique name
)

@Entity(
    tableName = "UNIT",
    foreignKeys = [
        ForeignKey(
            entity = SampleEntity::class,
            parentColumns = ["sample_id"],
            childColumns = ["sample_id_map"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = AreaEntity::class,
            parentColumns = ["area_id"],
            childColumns = ["area_id_map"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = PackEntity::class,
            parentColumns = ["pack_id"],
            childColumns = ["pack_id_map"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class UnitEntity(
    @PrimaryKey val id:String,
    @ColumnInfo(name = "sample_id_map") val sampleId : String,
    @ColumnInfo(name = "area_id_map") val areaId:String,
    @ColumnInfo(name = "pack_id_map") val packTypeId: String,
    @ColumnInfo(name = "unit_is_active") val isActive:Boolean
)
@Entity(
    tableName = "AREA_TYPE"
)
data class AreaTypeEntity(
    @PrimaryKey @ColumnInfo(name = "area_type_id") val id:String, //<<<<< unique name
    @ColumnInfo(name = "area_type_title") val title:String,

    /* ???? should an area type have an area as a parent? potential issues if so */
    /* commented out
    @ColumnInfo(name = "area_type_title") val parentAreaId : String //<<<<< unique name
    */
)

data class AreaPOJO(
    @Embedded val areaEntity : AreaEntity,
    @Relation(
        parentColumn = "area_type_id_map", //<<<<< changed accordingly
        entityColumn = "area_type_id" //<<<<< changed accordingly
    )
    val areaTypeEntity : AreaTypeEntity
)

data class UnitPOJO (
    @Embedded val unitEntity : UnitEntity,
    @Relation (
        entity = AreaEntity::class, //<<<<< ADDED
        parentColumn = "area_id_map",
        entityColumn = "area_id"
    )
    val areaWithAreaType : AreaPOJO,
    @Relation (
        parentColumn = "pack_id_map",
        entityColumn = "pack_id"
    )
    val packEntity : PackEntity,
    @Relation (
        parentColumn = "sample_id_map",
        entityColumn = "sample_id"
    )
    val sampleEntity : SampleEntity
)
data class AlternativeAreaPOJO (
        @Embedded val areaEntity: AreaEntity,
        @Embedded val areaTypeEntity: AreaTypeEntity
        )

data class AlternativeUnitPOJO (
        @Embedded val unitEntity: UnitEntity,
        @Embedded val alternativeAreaPOJO: AlternativeAreaPOJO,
        @Embedded val packEntity: PackEntity,
        @Embedded val sampleEntity: SampleEntity
        )

@Dao注释接口AllDao :-

@Dao
interface AllDAO {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(areaEntity: AreaEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(unitEntity: UnitEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(sampleEntity: SampleEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(packEntity: PackEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(areaTypeEntity: AreaTypeEntity)

    @Transaction
    @Query("SELECT * FROM UNIT")
    fun getUnitsWithRelations(): List<UnitPOJO>

    @Query("SELECT * FROM UNIT " +
            "INNER JOIN AREA ON UNIT.area_id_map = AREA.area_id " +
            "INNER JOIN AREA_TYPE ON AREA.area_type_id_map = AREA_TYPE.area_type_id " +
            "INNER JOIN SAMPLE ON UNIT.sample_id_map = SAMPLE.sample_id " +
            "INNER JOIN PACK ON UNIT.pack_id_map = PACK.pack_id")
    fun getAlternativeUnitsWithRelations(): List<AlternativeUnitPOJO>
}

@Database注释类TheDatabase :-

@Database(entities = [
    AreaEntity::class,
    SampleEntity::class,
    PackEntity::class,
    UnitEntity::class,
    AreaTypeEntity::class
                     ],
    version = 1,
    exportSchema = false
)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDAO(): AllDAO

    companion object {
        private var instance: TheDatabase? = null
        fun getInstance(context: Context): TheDatabase {
            if (instance == null) {
                instance = Room.databaseBuilder(
                    context,
                    TheDatabase::class.java,
                    "the_database.db"
                )
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

为了方便和简洁起见，已经使用了.allowMainThreadQueries。。

活动中的代码(设计只运行一次)：-

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 TAG = "DBINFO"

        val p1 = PackEntity("P001","Pack1")
        val p2 = PackEntity("P002","Pack2")
        val p3 = PackEntity("P003","Pack3")
        dao.insert(p1)
        dao.insert(p2)
        dao.insert(p3)
        val s1 = SampleEntity("S001","Sample1")
        val s2 = SampleEntity("S002","Sample2")
        val s3 = SampleEntity("S003","Sample3")
        dao.insert(s1)
        dao.insert(s2)
        dao.insert(s3)
        val at1 = AreaTypeEntity("AT001","AreaType1")
        val at2 = AreaTypeEntity("AT002","AreaType2")
        val at3 = AreaTypeEntity("AT003","AreaType3",)
        dao.insert(at1)
        dao.insert(at2)
        dao.insert(at3)
        val a1 = AreaEntity("A001","Area1",at1.id,true)
        val a2 = AreaEntity("A002","Area2",at2.id,false)
        val a3 = AreaEntity("A003","Area3",at1.id,true)
        dao.insert(a1)
        dao.insert(a2)
        dao.insert(a3)

        dao.insert(UnitEntity("U001",s1.id,a1.id,p1.id,true))
        dao.insert(UnitEntity("U002",s2.id,a2.id,p2.id, false))
        dao.insert(UnitEntity("U003",s3.id,a3.id,p3.id,true))
        dao.insert(UnitEntity("U004",s1.id,a2.id,p3.id,false))
        dao.insert(UnitEntity("U005",s3.id,a2.id,p1.id, true))

        for(uwr in dao.getUnitsWithRelations()) {
            Log.d(TAG,
                "Unit is ${uwr.unitEntity.id} " +
                    "Active = ${uwr.unitEntity.isActive} " +
                        "Sample is ${uwr.sampleEntity.title} " +
                        "Area is ${uwr.areaWithAreaType.areaEntity.title} " +
                        "AreaType is ${uwr.areaWithAreaType.areaTypeEntity.title}"
            )
        }

        for (auwr in dao.getAlternativeUnitsWithRelations()) {
            Log.d(TAG,
                "Unit is ${auwr.unitEntity.id} " +
                        "Active is ${auwr.unitEntity.isActive} " +
                        "Sample is ${auwr.sampleEntity.title} " +
                        "Area is ${auwr.alternativeAreaPOJO.areaEntity.title} " +
                        "AreaType is ${auwr.alternativeAreaPOJO.areaTypeEntity.title}"
            )
        }
    }
}

最后，日志的结果输出：-

2022-04-05 09:32:40.528 D/DBINFO: Unit is U001 Active = true Sample is Sample1 Area is Area1 AreaType is AreaType1
2022-04-05 09:32:40.528 D/DBINFO: Unit is U002 Active = false Sample is Sample2 Area is Area2 AreaType is AreaType2
2022-04-05 09:32:40.529 D/DBINFO: Unit is U003 Active = true Sample is Sample3 Area is Area3 AreaType is AreaType1
2022-04-05 09:32:40.529 D/DBINFO: Unit is U004 Active = false Sample is Sample1 Area is Area2 AreaType is AreaType2
2022-04-05 09:32:40.529 D/DBINFO: Unit is U005 Active = true Sample is Sample3 Area is Area2 AreaType is AreaType2

2022-04-05 09:32:40.537 D/DBINFO: Unit is U001 Active is true Sample is Sample1 Area is Area1 AreaType is AreaType1
2022-04-05 09:32:40.537 D/DBINFO: Unit is U002 Active is false Sample is Sample2 Area is Area2 AreaType is AreaType2
2022-04-05 09:32:40.537 D/DBINFO: Unit is U003 Active is true Sample is Sample3 Area is Area3 AreaType is AreaType1
2022-04-05 09:32:40.537 D/DBINFO: Unit is U004 Active is false Sample is Sample1 Area is Area2 AreaType is AreaType2
2022-04-05 09:32:40.537 D/DBINFO: Unit is U005 Active is true Sample is Sample3 Area is Area2 AreaType is AreaType2

• ，即对于两个备选方案，结果是相同的，当然，这些关系与预期的

一样有效。

Answer 2

实际上，我已经用这个POJO解决了这个问题：

    data class UnitPOJO
         (
         @Embedded val unit: UnitEntity,
         @Relation(
              parentColumn = "sample_id",
              entityColumn = "id"
          )
          val sampleEntity: SampleEntity,

          @Relation(
               parentColumn = "pack_type_id",
               entityColumn = "id"
           )
           val pack: PackEntity,

           @Relation(
              parentColumn = "area_id",
              entityColumn = "id",
              entity = AreaEntity::class
           )
           val area: AreaPOJO

    )

AreaPOJO是这样的：

     data class AreaPOJO(
         @Embedded val areaEntity: AreaEntity,
         @Relation(
              parentColumn = "area_type_id",
              entityColumn = "id"
          )
          val areaTypeEntity: AreaTypeEntity
       )

但是，我肯定会考虑您的警告@MikeT在使用分层数据时如何命名字段/列名。如果出了问题或者我得到了意想不到的结果，我一定会用这个方法来解决问题。