如何在Room数据库中处理API返回的一对多嵌套实体的增查

一、批量保存DTO数据到Room #

要避免嵌套循环，可借助Kotlin集合操作（flatMap）提取所有层级的实体，再通过Room事务批量插入，保证操作原子性：

1. 优化saveFetchResult实现 #

saveFetchResult = { dtos ->
    // 提取各层级实体，用flatMap展开嵌套列表
    val boards = dtos.map { it.toEntityModel() }
    val boardChildren = dtos.flatMap { it.boardChildren.map { child -> child.toEntityModel() } }
    val boardElements = dtos.flatMap { it.boardChildren.flatMap { child -> child.boardElements.map { elem -> elem.toEntityModel() } } }
    val hyperlinks = dtos.flatMap { 
        it.boardChildren.flatMap { child -> 
            child.boardElements.flatMap { elem -> 
                elem.hyperlinks.map { link -> link.toEntityModel() } 
            } 
        } 
    }

    // 调用Dao的事务方法完成批量插入
    boardDao.insertAllData(boards, boardChildren, boardElements, hyperlinks)
}

2. 给Dao添加事务与批量插入方法 #

@Dao
interface BoardDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertAllBoards(boards: List<BoardEntity>)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertAllBoardChildren(children: List<BoardChildEntity>)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertAllBoardElements(elements: List<BoardElementEntity>)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertAllHyperlinks(hyperlinks: List<BoardElementHyperlinkEntity>)

    // 用@Transaction确保所有插入操作原子性
    @Transaction
    suspend fun insertAllData(
        boards: List<BoardEntity>,
        children: List<BoardChildEntity>,
        elements: List<BoardElementEntity>,
        hyperlinks: List<BoardElementHyperlinkEntity>
    ) {
        insertAllBoards(boards)
        insertAllBoardChildren(children)
        insertAllBoardElements(elements)
        insertAllHyperlinks(hyperlinks)
    }
}

二、查询包含完整子列表的BoardEntity #

通过Room的@Relation注解定义关联数据类，直接查询出包含所有子层级的完整数据结构：

1. 定义关联数据类 #

// 包含BoardElement及其关联的Hyperlinks
data class BoardElementWithHyperlinks(
    @Embedded val element: BoardElementEntity,
    @Relation(
        parentColumn = "id",
        entityColumn = "boardElementId"
    )
    val hyperlinks: List<BoardElementHyperlinkEntity>
)

// 包含BoardChild及其关联的Elements
data class BoardChildWithElements(
    @Embedded val child: BoardChildEntity,
    @Relation(
        parentColumn = "id",
        entityColumn = "boardChildId",
        entity = BoardElementEntity::class
    )
    val elements: List<BoardElementWithHyperlinks>
)

// 最终的完整Board结构：包含所有子层级
data class BoardWithChildren(
    @Embedded val board: BoardEntity,
    @Relation(
        parentColumn = "id",
        entityColumn = "boardId",
        entity = BoardChildEntity::class
    )
    val children: List<BoardChildWithElements>
)

2. 在Dao中添加关联查询方法 #

@Dao
interface BoardDao {
    // ... 其他方法 ...

    @Transaction
    @Query("SELECT * FROM BoardEntity")
    suspend fun getAllBoardsWithChildren(): List<BoardWithChildren>
}

3. 在networkBoundResource的query中使用 #

query = {
    boardDao.getAllBoardsWithChildren()
}

这样就能直接获取包含所有嵌套子实体的BoardWithChildren列表，无需手动拼接查询结果。

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