Android Room关联查询：如何通过Device关联Room与Group

一、服务器返回的DTO结构 #

服务器返回的序列化数据类如下：

@Serializable
data class RoomDto(
    val id: String,
    val name: String,
    val devices: List<String>
)
@Serializable
data class GroupDto(
    val id: String,
    val name: String,
    val devices: List<String>
)
@Serializable
data class DeviceDto(
    val id: String,
    val name: String,
    val room: String,
    val groups: List<String>
)

二、本地Room实体定义 #

本地数据库的实体类定义：

@Entity(tableName = "rooms")
data class RoomEntity(
    @PrimaryKey val id: String,
    val name: String,
    @ColumnInfo("device_ids") val deviceIds: List<String>
)
@Entity(tableName = "groups")
data class GroupEntity(
    @PrimaryKey val id: String,
    val name: String,
    val type: String,
    @ColumnInfo("device_ids") val deviceIds: List<String>
)
@Entity(tableName = "devices")
data class DeviceEntity(
    @PrimaryKey val id: String,
    val name: String,
    val type: String,
    @ColumnInfo("room_id") val roomId: String,
    @ColumnInfo("group_ids") val groupIds: List<String>
)

三、目标查询结构 #

需要查询出包含Room、关联Group和关联Device的组合数据结构：

data class RoomWithDevices(
    @Embedded
    val room: RoomEntity,

    @Relation(...)
    val groups: List<GroupEntity> = emptyList(),

    @Relation(...)
    val devices: List<DeviceEntity> = emptyList()
)

四、已创建的关联表 #

为了关联Room和Group，已创建多对多关联表：

@Entity(
    tableName = "room_group_map",
    foreignKeys = [
        ForeignKey(
            entity = RoomEntity::class,
            parentColumns = ["id"],
            childColumns = ["room_id"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = GroupEntity::class,
            parentColumns = ["id"],
            childColumns = ["group_id"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ],
    primaryKeys = ["room_id", "group_id"]
)
data class RoomGroupMap(
    @ColumnInfo("room_id") val roomId: String,
    @ColumnInfo("group_id") val groupId: String
)

五、实现步骤 #

1. 数据插入时填充关联表 #

因为Group和Room的关联关系是通过Device间接建立的（Device属于某个Room，同时属于多个Group），所以在将DTO转换为实体插入数据库时，需要同步填充room_group_map表：

• 遍历所有DeviceDto，获取其room（对应Room的id）和groups（对应Group的id列表）
• 对每个Group id，创建RoomGroupMap(roomId = deviceDto.room, groupId = groupId)，并插入到数据库
• 这样就能建立Room和Group之间的关联关系

2. 配置RoomWithDevices的关联注解 #

修改RoomWithDevices的@Relation注解，完成关联查询：

data class RoomWithDevices(
    @Embedded
    val room: RoomEntity,

    // 关联Room对应的Group列表，通过room_group_map中间表
    @Relation(
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = RoomGroupMap::class,
            parentColumn = "room_id",
            entityColumn = "group_id"
        )
    )
    val groups: List<GroupEntity> = emptyList(),

    // 关联Room对应的Device列表，直接通过Device的room_id字段
    @Relation(
        parentColumn = "id",
        entityColumn = "room_id"
    )
    val devices: List<DeviceEntity> = emptyList()
)

3. DAO层查询方法 #

在DAO中定义查询方法，即可直接获取包含关联数据的RoomWithDevices：

@Dao
interface RoomDao {
    @Transaction
    @Query("SELECT * FROM rooms")
    suspend fun getRoomsWithDevicesAndGroups(): List<RoomWithDevices>
}

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