如何在Kotlin中为Room数据库添加模型类并分表存储数据

我有一个需要导入Room数据库的保存模型，要求将List<Images>以外的信息存入名为ReplacedData的独立表，Images列表存入Images表，请问该如何实现，对应的查询语句是什么？
模型JSON如下：

{"AccountNumber": "S234567","ActionRequiredID": "6,7","AdditionalRemarks": "","AndroidVersion": "31","AntennaTypeID": 6,"AppVersion": "2.1.3","CTRatio": "","CategoryID": 1,"CurrentMeterSealNumber": "thhjj","CycleName": "Nov-2022","EndTime": "2022-11-07 12:05:51","ICCIDNumber": "thb","Images": [{"ID": 0,"PicFormat": "jpg","PicName": "S23456-TRS_6-TI1-team1_2022-11-07_17-35-05.jpg","PicPath": "/storage/emulated/0/Download/team1/MReader/troubleshoot/Troubleshooting-Nov-2022/Photos/","PicSize": "36 KB","PictureTypeID": 18,"ServiceID": 5,"SurveyID": 6}],"IsGPSEnabled": true,"IsLiveGPS": true,"IsOnline": true,"Latitude": 11.0501224,"LocationAccuracy": 9.133,"LocationProvider": "fused","Longitude": 76.9678775,"MeterLocationID": 1,"MeterModelID": 5,"MeterSerialNumber": "61604448","NewMeterSealNumber": "","PingTestID": 1,"ReasonForFailureID": "5","ReasonID": 39,"Remarks": "","Seal1": "","Seal2": "","Seal3": "","Seal4": "","ServiceID": 5,"SignalLevelID": 2,"SimInField": "cghb","StartTime": "2022-11-07 12:05:05","StatusID": 12,"StatusTroubleshootID": 4,"UserID": 161,"WorkOrderID": 6,"WorkOrderNumber": "TRS_6","reason": "Completed","status": "Completed"}

1. 定义Room实体类 #

1.1 ReplacedData主表实体 #

主表设WorkOrderID作为主键（业务上唯一），把原JSON里除Images外的所有字段都映射进来：

@Entity(tableName = "ReplacedData")
data class ReplacedData(
    @PrimaryKey val WorkOrderID: Int,
    val AccountNumber: String,
    val ActionRequiredID: String,
    val AdditionalRemarks: String,
    val AndroidVersion: String,
    val AntennaTypeID: Int,
    val AppVersion: String,
    val CTRatio: String,
    val CategoryID: Int,
    val CurrentMeterSealNumber: String,
    val CycleName: String,
    val EndTime: String,
    val ICCIDNumber: String,
    val IsGPSEnabled: Boolean,
    val IsLiveGPS: Boolean,
    val IsOnline: Boolean,
    val Latitude: Double,
    val LocationAccuracy: Double,
    val LocationProvider: String,
    val Longitude: Double,
    val MeterLocationID: Int,
    val MeterModelID: Int,
    val MeterSerialNumber: String,
    val NewMeterSealNumber: String,
    val PingTestID: Int,
    val ReasonForFailureID: String,
    val ReasonID: Int,
    val Remarks: String,
    val Seal1: String,
    val Seal2: String,
    val Seal3: String,
    val Seal4: String,
    val ServiceID: Int,
    val SignalLevelID: Int,
    val SimInField: String,
    val StartTime: String,
    val StatusID: Int,
    val StatusTroubleshootID: Int,
    val UserID: Int,
    val WorkOrderNumber: String,
    val reason: String,
    val status: String
)

1.2 Images子表实体 #

图片表加WorkOrderID作为外键，关联主表的主键，确保图片和工单绑定；主键用自增ID，原JSON里的ID设默认值：

@Entity(
    tableName = "Images",
    foreignKeys = [ForeignKey(
        entity = ReplacedData::class,
        parentColumns = ["WorkOrderID"],
        childColumns = ["WorkOrderID"],
        onDelete = ForeignKey.CASCADE // 删除工单时自动删除关联图片
    )]
)
data class Images(
    @PrimaryKey(autoGenerate = true) val ID: Int = 0,
    val PicFormat: String,
    val PicName: String,
    val PicPath: String,
    val PicSize: String,
    val PictureTypeID: Int,
    val ServiceID: Int,
    val SurveyID: Int,
    val WorkOrderID: Int // 外键，关联工单ID
)

2. 定义DAO数据访问接口 #

写好插入和查询的方法，用@Transaction实现主表子表关联查询：

@Dao
interface ReplacedDataDao {
    // 插入单条工单数据
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertReplacedData(replacedData: ReplacedData)

    // 批量插入图片
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertImages(images: List<Images>)

    // 查询指定工单的完整数据（工单+关联图片）
    @Transaction
    @Query("SELECT * FROM ReplacedData WHERE WorkOrderID = :workOrderId")
    suspend fun getReplacedDataWithImages(workOrderId: Int): ReplacedDataWithImages?

    // 查询所有工单的完整数据
    @Transaction
    @Query("SELECT * FROM ReplacedData")
    suspend fun getAllReplacedDataWithImages(): List<ReplacedDataWithImages>

    // 单独查询某工单的图片
    @Query("SELECT * FROM Images WHERE WorkOrderID = :workOrderId")
    suspend fun getImagesByWorkOrderId(workOrderId: Int): List<Images>
}

// 关联数据类，用于封装工单+图片的组合结果
data class ReplacedDataWithImages(
    @Embedded val replacedData: ReplacedData,
    @Relation(
        parentColumn = "WorkOrderID",
        entityColumn = "WorkOrderID"
    )
    val images: List<Images>
)

3. 定义Room数据库类 #

声明包含的实体类，提供DAO实例：

@Database(entities = [ReplacedData::class, Images::class], version = 1, exportSchema = false)
abstract class AppDatabase : RoomDatabase() {
    abstract fun replacedDataDao(): ReplacedDataDao

    // 单例实现（按需调整）
    companion object {
        @Volatile
        private var INSTANCE: AppDatabase? = null
        fun getDatabase(context: Context): AppDatabase {
            return INSTANCE ?: synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    AppDatabase::class.java,
                    "app_database"
                ).build()
                INSTANCE = instance
                instance
            }
        }
    }
}

4. 数据插入逻辑 #

解析JSON后，分别插入主表和子表：

// 假设已将JSON解析为OriginalModel类
suspend fun insertOriginalModel(originalModel: OriginalModel, dao: ReplacedDataDao) {
    // 构建工单实体并插入
    val replacedData = ReplacedData(
        WorkOrderID = originalModel.WorkOrderID,
        AccountNumber = originalModel.AccountNumber,
        // 其他字段依次对应赋值...
        status = originalModel.status
    )
    dao.insertReplacedData(replacedData)

    // 构建图片实体列表，绑定当前工单ID后批量插入
    val images = originalModel.Images.map { img ->
        Images(
            PicFormat = img.PicFormat,
            PicName = img.PicName,
            PicPath = img.PicPath,
            PicSize = img.PicSize,
            PictureTypeID = img.PictureTypeID,
            ServiceID = img.ServiceID,
            SurveyID = img.SurveyID,
            WorkOrderID = originalModel.WorkOrderID
        )
    }
    dao.insertImages(images)
}

5. 常用查询语句 #

5.1 查询指定工单的完整数据 #

Room自动关联子表，对应SQL：

SELECT * FROM ReplacedData WHERE WorkOrderID = :workOrderId

直接调用DAO的getReplacedDataWithImages(workOrderId)即可拿到工单+图片的组合数据。

5.2 查询所有工单的完整数据 #

对应SQL：

SELECT * FROM ReplacedData

调用getAllReplacedDataWithImages()返回所有工单及其关联图片。

5.3 单独查询某工单的图片 #

对应SQL：

SELECT * FROM Images WHERE WorkOrderID = :workOrderId

调用getImagesByWorkOrderId(workOrderId)返回该工单下的所有图片。

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