JPA原生统计查询映射DTO提示无可用转换器错误如何解决

错误原因 #

你遇到的no converter found capable of converting from AbstractJPAQuery to DTO报错，核心原因是Spring Data JPA默认没有提供原生查询结果到自定义POJO类的自动映射转换器，你直接在Repository方法中返回自定义DTO类型，JPA无法完成自动转换。

可行解决方案 #

下面提供3种常用的无额外依赖解决方案，按需选择即可：

方案1：接口投影（最简单，无需额外配置） #

将你的DTO从POJO类改为接口，接口中定义和查询字段别名对应的getter方法即可，JPA会自动生成动态代理实现类完成映射：

// 替换原来的ConsentRequestCount类
public interface ConsentRequestCount {
    Long getPendingRequest();
    Long getApprovedRequest();
    Long getRejectedRequest();
    Long getTotalRequest();
}

Repository方法无需修改，直接返回这个接口类型即可正常使用。 #

方案2：Tuple返回手动转DTO（灵活度高，无需修改查询逻辑） #

将Repository方法的返回类型改为JPA自带的Tuple，查询后手动转换为你自定义的DTO：

1. 修改Repository方法：

@Query(value = "SELECT SUM(CASE WHEN Status = 'PENDING' THEN 1 ELSE 0 END) AS `pendingRequest`,\n" +
        "       SUM(CASE WHEN Status = 'ACTIVE' or Status ='READY' THEN 1 ELSE 0 END) AS `approvedRequest`,\n" +
        "       SUM(CASE WHEN Status = 'FAILED' THEN 1 ELSE 0 END) AS `rejectedRequest`,\n" +
        "       SUM(CASE WHEN Status = 'ACTIVE' or Status ='READY' or Status ='PENDING' or Status ='FAILED' THEN 1 ELSE 0 END) AS `totalRequest` FROM fiu_consent_db.consent_request",nativeQuery = true)
Tuple getAllConsentRequestCount();

2. 调用处手动转换为DTO：

// 你原来的ConsentRequestCount POJO类保留即可
Tuple tuple = consentRequestRepository.getAllConsentRequestCount();
ConsentRequestCount count = new ConsentRequestCount(
        tuple.get("pendingRequest", BigInteger.class).longValue(),
        tuple.get("approvedRequest", BigInteger.class).longValue(),
        tuple.get("rejectedRequest", BigInteger.class).longValue(),
        tuple.get("totalRequest", BigInteger.class).longValue()
);

注意：SUM函数在MySQL中返回的是大整数类型，直接转long可能报类型转换错误，建议先取BigInteger再调用longValue()转换。

方案3：SqlResultSetMapping配置映射（适合POJO类返回场景） #

如果你需要保留POJO类的结构，不想用接口或者手动转换，可以通过JPA的结果集映射注解配置自动转换：

1. 给你的ConsentRequestCount类添加如下注解：

@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity // 必须加，JPA要求映射类为实体
public class ConsentRequestCount {
    @Id // 实体必须有主键，查询仅返回一行，此字段无实际业务意义
    private Long id;

    private long pendingRequest;
    private long approvedRequest;
    private long rejectedRequest;
    private long totalRequest;

    // 配置结果集映射
    @SqlResultSetMapping(
            name = "ConsentRequestCountMapping",
            classes = @ConstructorResult(
                    targetClass = ConsentRequestCount.class,
                    columns = {
                            @ColumnResult(name = "pendingRequest", type = Long.class),
                            @ColumnResult(name = "approvedRequest", type = Long.class),
                            @ColumnResult(name = "rejectedRequest", type = Long.class),
                            @ColumnResult(name = "totalRequest", type = Long.class)
                    }
            )
    )
    @NamedNativeQuery(
            name = "getAllConsentRequestCount",
            query = "SELECT SUM(CASE WHEN Status = 'PENDING' THEN 1 ELSE 0 END) AS `pendingRequest`,\n" +
                    "SUM(CASE WHEN Status = 'ACTIVE' or Status ='READY' THEN 1 ELSE 0 END) AS `approvedRequest`,\n" +
                    "SUM(CASE WHEN Status = 'FAILED' THEN 1 ELSE 0 END) AS `rejectedRequest`,\n" +
                    "SUM(CASE WHEN Status = 'ACTIVE' or Status ='READY' or Status ='PENDING' or Status ='FAILED' THEN 1 ELSE 0 END) AS `totalRequest` FROM fiu_consent_db.consent_request",
            resultSetMapping = "ConsentRequestCountMapping"
    )
}

2. Repository方法直接定义返回对应类型即可：

ConsentRequestCount getAllConsentRequestCount();

注意事项 #

• 查询语句中的字段别名必须和映射的字段名/getter方法名完全一致，大小写匹配
• 数值类型建议优先用包装类（Long/Integer）避免空指针问题

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