如何在Room中使用关联表查询指定格式的数据

要实现你需要的Response格式数据，需要利用Room的@Embedded、@Relation以及Junction注解处理多表关联，步骤如下：

1. 完善实体类的Room注解 #

先确保数据类添加Room必需的实体注解和外键约束，让Room识别表间关系：

import androidx.room.Entity
import androidx.room.ForeignKey
import androidx.room.PrimaryKey

@Entity(tableName = "train")
data class Train(
    @PrimaryKey val id: Int,
    val isFollow: Boolean
)

@Entity(
    tableName = "train_status",
    foreignKeys = [ForeignKey(
        entity = Train::class,
        parentColumns = ["id"],
        childColumns = ["trainId"],
        onDelete = ForeignKey.CASCADE
    )]
)
data class TrainStatus(
    @PrimaryKey val id: Int,
    val trainId: Int,
    val origin: String,
    val destination: String
)

@Entity(
    tableName = "previous_train",
    foreignKeys = [
        ForeignKey(
            entity = TrainStatus::class,
            parentColumns = ["id"],
            childColumns = ["trainStatusId"],
            onDelete = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = TrainStatus::class,
            parentColumns = ["id"],
            childColumns = ["previousTrainStatusId"],
            onDelete = ForeignKey.CASCADE
        )
    ]
)
data class PreviousTrain(
    @PrimaryKey val id: Int,
    val trainStatusId: Int,
    val previousTrainStatusId: Int
)

2. 定义嵌套数据类并配置关联关系 #

针对你需要的TrainStatusWithPreviousTrain和Response，通过Room注解配置表间关联逻辑：

import androidx.room.Embedded
import androidx.room.Junction
import androidx.room.Relation

// 单个TrainStatus及其关联的历史TrainStatus
data class TrainStatusWithPreviousTrain(
    @Embedded val trainStatus: TrainStatus,
    // 通过PreviousTrain关联表，关联当前TrainStatus和历史TrainStatus
    @Relation(
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = PreviousTrain::class,
            parentColumn = "trainStatusId",
            entityColumn = "previousTrainStatusId"
        )
    )
    val previousTrain: TrainStatus
    // 注意：如果一个TrainStatus对应多个历史状态，这里要改为List<TrainStatus>
)

// 最终返回的响应结构：Train + 对应的所有关联状态数据
data class Response(
    @Embedded val train: Train,
    // 关联Train和其对应的所有TrainStatus
    @Relation(
        parentColumn = "id",
        entityColumn = "trainId"
    )
    val trainStatusWithPreviousTrain: List<TrainStatusWithPreviousTrain>
)

3. 在Dao中定义查询方法 #

使用@Transaction注解保证多表查询的原子性，Room会自动根据配置的关联关系加载嵌套数据：

import androidx.room.Dao
import androidx.room.Query
import androidx.room.Transaction
import kotlinx.coroutines.flow.Flow

@Dao
interface TrainDao {
    // 根据ID查询单个Train的完整数据
    @Transaction
    @Query("SELECT * FROM train WHERE id = :trainId")
    suspend fun getTrainWithFullData(trainId: Int): Response?

    // 查询所有Train的完整数据
    @Transaction
    @Query("SELECT * FROM train")
    suspend fun getAllTrainsWithFullData(): List<Response>

    // 可选：返回Flow实现数据监听
    @Transaction
    @Query("SELECT * FROM train WHERE id = :trainId")
    fun getTrainWithFullDataFlow(trainId: Int): Flow<Response?>
}

关键说明 #

• @Transaction：必须添加，多表关联查询需要保证操作原子性，避免数据不一致。
• Junction：用于指定中间关联表PreviousTrain，明确两个TrainStatus之间的关联逻辑。
• 关联关系适配：如果业务中一个TrainStatus对应多个历史状态，需把TrainStatusWithPreviousTrain中的previousTrain改为List<TrainStatus>，Room会自动加载所有关联数据。

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