Android Room实现多层嵌套关联查询及批量删除方法问询

Room支持嵌套@Relation处理多层级父子关联，你需要逐层定义包含子列表的实体类，最终组装完整层级结构：

1. 定义C关联D的实体 #

data class CWithDs(
    @Embedded val c: C,
    @Relation(
        parentColumn = "_id",
        entityColumn = "parent_id",
        entity = D::class
    )
    val listD: List<D>
)

2. 定义B关联CWithDs的实体 #

data class BWithCs(
    @Embedded val b: B,
    @Relation(
        parentColumn = "_id",
        entityColumn = "parent_id",
        entity = C::class
    )
    val listC: List<CWithDs>
)

3. 最终的FullEntity（A关联BWithCs） #

替换你原有的FullEntity，用嵌套关联实体实现完整层级：

data class FullEntity(
    @Embedded val a: A,
    @Relation(
        parentColumn = "_id",
        entityColumn = "parent_id",
        entity = B::class
    )
    val listB: List<BWithCs>
)

无需手动编写复杂多表LEFT JOIN，Room会自动处理嵌套@Relation的查询逻辑，只需在DAO中添加带@Transaction的查询方法（保证原子性）：

@Dao
interface FullDao {
    @Transaction
    @Query("SELECT * FROM a WHERE _id = :aId")
    suspend fun getFullEntityById(aId: Long): FullEntity?
}

提供两种实现方式，优先推荐外键级联删除：

方式一：外键级联删除（推荐） #

在各子表实体类中配置外键，设置onDelete = ForeignKey.CASCADE，删除A时会自动递归删除所有关联的B、C、D：

B表实体配置外键 #

@Entity(
    tableName = "b",
    foreignKeys = [
        ForeignKey(
            entity = A::class,
            parentColumns = ["_id"],
            childColumns = ["parent_id"],
            onDelete = ForeignKey.CASCADE
        )
    ]
)
data class B(
    @PrimaryKey(autoGenerate = true) val _id: Long,
    val parent_id: Long
)

C表实体配置外键 #

@Entity(
    tableName = "c",
    foreignKeys = [
        ForeignKey(
            entity = B::class,
            parentColumns = ["_id"],
            childColumns = ["parent_id"],
            onDelete = ForeignKey.CASCADE
        )
    ]
)
data class C(
    @PrimaryKey(autoGenerate = true) val _id: Long,
    val parent_id: Long
)

D表实体配置外键 #

@Entity(
    tableName = "d",
    foreignKeys = [
        ForeignKey(
            entity = C::class,
            parentColumns = ["_id"],
            childColumns = ["parent_id"],
            onDelete = ForeignKey.CASCADE
        )
    ]
)
data class D(
    @PrimaryKey(autoGenerate = true) val _id: Long,
    val parent_id: Long
)

DAO中删除方法 #

只需删除A，级联操作自动处理所有关联数据：

@Dao
interface FullDao {
    // ... 其他方法
    @Query("DELETE FROM a WHERE _id = :aId")
    suspend fun deleteAAndRelated(aId: Long)
}

方式二：手动分层删除（无外键时使用） #

若无法使用外键，需按从底层到顶层的顺序删除，并用@Transaction保证所有删除操作原子性：

@Dao
interface FullDao {
    // ... 其他方法
    @Transaction
    suspend fun deleteAllRelated(aId: Long) {
        deleteDsByAId(aId)
        deleteCsByAId(aId)
        deleteBsByAId(aId)
        deleteA(aId)
    }

    @Query("DELETE FROM d WHERE parent_id IN (SELECT _id FROM c WHERE parent_id IN (SELECT _id FROM b WHERE parent_id = :aId))")
    suspend fun deleteDsByAId(aId: Long)

    @Query("DELETE FROM c WHERE parent_id IN (SELECT _id FROM b WHERE parent_id = :aId)")
    suspend fun deleteCsByAId(aId: Long)

    @Query("DELETE FROM b WHERE parent_id = :aId")
    suspend fun deleteBsByAId(aId: Long)

    @Query("DELETE FROM a WHERE _id = :aId")
    suspend fun deleteA(aId: Long)
}

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