Spring Data JPA联合查询映射视图报错：无可用转换器

出现ConverterNotFoundException的核心原因有两个：

1. 原生查询返回的字段名（user_uid）与视图类PlayerAccountView的属性名（playerUid）不匹配，Spring无法自动识别映射关系；
2. 原生查询默认返回TupleBackedMap类型，Spring Data JPA不会自动将其转换为自定义的DTO/视图类，需要明确指定映射规则。

方案一：修改SQL字段别名匹配视图类属性（推荐） #

直接在SQL中给查询字段添加与视图类属性名完全一致的别名，Spring Data JPA 2.0+版本支持自动将原生查询结果映射到自定义类（Kotlin data class默认带有匹配的构造函数，满足要求）。

修改后的Repository代码：

@Query(
    value = """
SELECT user_uid AS playerUid, operator_uid FROM player_account_table_one WHERE job_id = :sessionId
UNION
SELECT user_uid AS playerUid, operator_uid FROM player_account_table_two WHERE job_id = :sessionId
UNION
SELECT user_uid AS playerUid, operator_uid FROM player_account_table_three WHERE job_id = :sessionId
UNION
SELECT user_uid AS playerUid, operator_uid FROM player_account_table_four WHERE job_id = :sessionId
UNION
SELECT user_uid AS playerUid, operator_uid FROM player_account_table_five WHERE job_id = :sessionId
UNION
SELECT user_uid AS playerUid, operator_uid FROM player_account_table_six WHERE job_id = :sessionId
""",
    nativeQuery = true,
)
fun findUsersViewResult(@Param("sessionId") sessionId: Long): List<PlayerAccountView>

注意：别名需与PlayerAccountView的属性名严格大小写一致（Kotlin对大小写敏感）。

方案二：使用@SqlResultSetMapping定义映射规则 #

通过JPA的@SqlResultSetMapping注解明确指定字段到视图类的映射关系，无需修改SQL语句。

1. 创建一个用于承载映射配置的空实体类（或复用现有实体类）：

@Entity
@SqlResultSetMapping(
    name = "PlayerAccountViewMapping",
    classes = [
        ConstructorResult(
            targetClass = PlayerAccountView::class,
            columns = [
                ColumnResult(name = "user_uid", type = String::class),
                ColumnResult(name = "operator_uid", type = String::class)
            ]
        )
    ]
)
class DummyEntity // 仅用于承载映射配置，无需对应数据库表

2. 修改Repository的@Query注解，指定使用该映射：

@Query(
    value = """
SELECT user_uid, operator_uid FROM player_account_table_one WHERE job_id = :sessionId
UNION
SELECT user_uid, operator_uid FROM player_account_table_two WHERE job_id = :sessionId
UNION
SELECT user_uid, operator_uid FROM player_account_table_three WHERE job_id = :sessionId
UNION
SELECT user_uid, operator_uid FROM player_account_table_four WHERE job_id = :sessionId
UNION
SELECT user_uid, operator_uid FROM player_account_table_five WHERE job_id = :sessionId
UNION
SELECT user_uid, operator_uid FROM player_account_table_six WHERE job_id = :sessionId
""",
    nativeQuery = true,
    resultSetMapping = "PlayerAccountViewMapping"
)
fun findUsersViewResult(@Param("sessionId") sessionId: Long): List<PlayerAccountView>

方案三：手动转换结果集 #

如果上述方案无法适用，可先让Repository返回Map类型的结果集，再在业务层手动转换为视图类。

1. 修改Repository方法返回类型：

@Query(
    value = """
SELECT user_uid, operator_uid FROM player_account_table_one WHERE job_id = :sessionId
UNION
SELECT user_uid, operator_uid FROM player_account_table_two WHERE job_id = :sessionId
UNION
SELECT user_uid, operator_uid FROM player_account_table_three WHERE job_id = :sessionId
UNION
SELECT user_uid, operator_uid FROM player_account_table_four WHERE job_id = :sessionId
UNION
SELECT user_uid, operator_uid FROM player_account_table_five WHERE job_id = :sessionId
UNION
SELECT user_uid, operator_uid FROM player_account_table_six WHERE job_id = :sessionId
""",
    nativeQuery = true,
)
fun findUsersViewResult(@Param("sessionId") sessionId: Long): List<Map<String, Any>>

2. 在Service层手动转换：

fun getPlayerAccountViews(sessionId: Long): List<PlayerAccountView> {
    return repository.findUsersViewResult(sessionId).map { map ->
        PlayerAccountView(
            playerUid = map["user_uid"] as String,
            operatorUid = map["operator_uid"] as String
        )
    }
}

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