Room框架中带自增主键表关联的DAO查询方法实现问询

我接触到的多是无主键表的关联实现示例，但我的数据库两张表都带主键，想写一个DAO方法关联User和Department表查询记录。核心需求是尽可能不创建额外数据类，比如用Map<User, Department>返回；如果这个方案不可行，其他办法也可以接受。

数据库表结构与实体类 #

User表 #

+----+----------+----------+---------------+
| id | username |   name   | department_id |
+----+----------+----------+---------------+
|  1 | johndoe  | John Doe |             3 |
|  2 | janedoe  | Jane Doe |             4 |
+----+----------+----------+---------------+

User实体类 #

@Entity(tableName = "user")
data class User(
    @PrimaryKey (autoGenerate = true) var id: Long,
    var username: String,
    var name: String,
    var department_id: Long)

Department表 #

+----+----------------+
| id |      name      |
+----+----------------+
|  1 | Sales          |
|  2 | Account        |
|  3 | Human Resource |
|  4 | Marketing      |
+----+----------------+

Department实体类 #

@Entity(tableName = "department")
data class Department(
    @PrimaryKey(autoGenerate = true) var id: Long,
    var name: String)

我的DAO尝试 #

@Dao
interface UserDAO {

    @Query("SELECT user.*, department.name AS 'department_name' FROM user " +
            "INNER JOIN department ON user.department_id = department.id " +
            "WHERE user.id = :id")
    fun findById(id: Long): Map<User, Department>
}

补充疑问 #

如果把Department嵌入User类中是否可行？如果可以，对应的DAO查询语句该怎么写？

修改后的User和Department实体类：

@Entity(tableName = "user")
data class User(
    @PrimaryKey (autoGenerate = true) var id: Long,
    var username: String,
    var name: String,
    @Embedded var department: Department)

data class Department(
    @PrimaryKey(autoGenerate = true)
    var id: Long,
    var name: String? = null): Parcelable

对应的DAO尝试：

@Query("SELECT * FROM user " +
    "INNER JOIN department d ON department.id = d.id " +
    "WHERE id = :id")
fun findById(id: Long): Map<User, Department>

可行解决方案 #

1. 直接返回Map不可行的原因 #

Room不支持直接返回Map<User, Department>这种类型，因为Room的返回类型只能是实体类、POJO、基本类型，或是它们的列表/数组，无法直接将查询结果映射为实体类作为键的Map结构。

2. 手动组装Map（无额外数据类） #

通过两次查询或批量查询后，在业务层手动组装Map：

@Dao
interface UserDAO {
    // 查询单个用户
    @Query("SELECT * FROM user WHERE id = :id")
    suspend fun getUserById(id: Long): User

    // 根据部门ID查询部门
    @Query("SELECT * FROM department WHERE id = :deptId")
    suspend fun getDepartmentById(deptId: Long): Department

    // 查询所有用户
    @Query("SELECT * FROM user")
    suspend fun getAllUsers(): List<User>

    // 批量查询部门
    @Query("SELECT * FROM department WHERE id IN (:deptIds)")
    suspend fun getDepartmentsByIds(deptIds: List<Long>): List<Department>
}

在Repository层组装：

// 单个用户的Map组装
suspend fun getUserWithDepartment(userId: Long): Map<User, Department> {
    val user = userDao.getUserById(userId)
    val dept = userDao.getDepartmentById(user.department_id)
    return mapOf(user to dept)
}

// 多个用户的Map组装
suspend fun getAllUsersWithDepartments(): Map<User, Department> {
    val users = userDao.getAllUsers()
    val deptIds = users.map { it.department_id }
    val deptMap = userDao.getDepartmentsByIds(deptIds).associateBy { it.id }
    return users.associateWith { deptMap[it.department_id]!! }
}

3. 使用Room官方推荐的@Relation关联（简洁型） #

虽然需要创建一个额外的包装类，但这是Room最规范的关联查询方式，类型安全且无需手动处理关联逻辑：

// 创建包装类
data class UserWithDepartment(
    @Embedded val user: User,
    @Relation(
        parentColumn = "department_id",
        entityColumn = "id"
    )
    val department: Department
)

对应的DAO方法：

@Dao
interface UserDAO {
    @Transaction
    @Query("SELECT * FROM user WHERE id = :id")
    fun getUserWithDepartment(id: Long): UserWithDepartment

    @Transaction
    @Query("SELECT * FROM user")
    fun getAllUsersWithDepartments(): List<UserWithDepartment>
}

4. 关于嵌入Department到User类的可行性 #

你尝试的@Embedded方式不可行：@Embedded的作用是将一个实体的字段直接嵌入到当前实体的数据库表中，意味着User表需要包含Department的所有字段（id、name），但你的数据库中User表只有department_id字段，与Department表是独立的关联关系，因此这种嵌入方式不符合现有数据库结构，会导致Room找不到对应列而报错。

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