面试题：Kotlin的Room库关联关系处理

Question

假设在Kotlin项目中有两个实体类，一个是`Student`类，包含学生ID、姓名等字段；另一个是`Course`类，包含课程ID、课程名等字段。一个学生可以选修多门课程，一门课程也可以有多个学生选修。请使用Room持久化库来设计这两个实体类以及它们之间的关联关系，并编写对应的DAO接口方法来实现获取某个学生所选的所有课程信息。

Answer 1

1. 定义Student实体类

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

@Entity
data class Student(
    @PrimaryKey
    val studentId: Int,
    val name: String
)

2. 定义Course实体类

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

@Entity
data class Course(
    @PrimaryKey
    val courseId: Int,
    val courseName: String
)

3. 定义关联表及关联类

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

@Entity(
    primaryKeys = ["studentId", "courseId"],
    foreignKeys = [
        ForeignKey(
            entity = Student::class,
            parentColumns = ["studentId"],
            childColumns = ["studentId"],
            onDelete = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = Course::class,
            parentColumns = ["courseId"],
            childColumns = ["courseId"],
            onDelete = ForeignKey.CASCADE
        )
    ]
)
data class StudentCourseCrossRef(
    val studentId: Int,
    val courseId: Int
)

4. 定义联合类用于存储学生及其课程信息

data class StudentWithCourses(
    val student: Student,
    val courses: List<Course>
)

5. 定义DAO接口

import androidx.room.Dao
import androidx.room.Join
import androidx.room.Query

@Dao
interface StudentCourseDao {
    @Query("""
        SELECT * FROM Student
        JOIN StudentCourseCrossRef ON Student.studentId = StudentCourseCrossRef.studentId
        JOIN Course ON StudentCourseCrossRef.courseId = Course.courseId
        WHERE Student.studentId = :studentId
    """)
    fun getStudentCourses(studentId: Int): List<StudentWithCourses>
}