Spring Data JPA原生查询映射DTO报错：无匹配类型转换器

问题根源 #

你的原生SQL查询结果默认被Spring Data JPA封装为TupleBackedMap，但Repository方法直接声明返回ArrayList<TransferTest>，框架没有内置转换器能自动将Map转换为自定义DTO，因此抛出转换错误。

以下是三种可行的解决方法：

方法1：JPQL构造函数投影（推荐，简洁直观） #

将原生SQL改为JPQL，通过构造函数投影直接生成DTO：

1. 给TransferTest添加匹配查询结果列顺序的构造函数：

@Data
@AllArgsConstructor
@NoArgsConstructor
public class TransferTest {
    private Integer transfer_to;
    private String username;

    // 若不用@AllArgsConstructor，可手动编写构造函数
    public TransferTest(Integer transfer_to, String username) {
        this.transfer_to = transfer_to;
        this.username = username;
    }
}

2. 修改Repository的查询为JPQL格式：

@Repository
public interface HomeTransferPageRepository extends JpaRepository<TransferEntity, Integer> {
    @Query(value="select new com.name.dto.TransferTest(DISTINCT t.transfer_to, r.username) " +
            "from TransferEntity t left join RegistrationEntity r on t.transfer_to = r.id " +
            "where t.transfer_from = :transfer_from " +
            "order by t.transaction_date desc " +
            "limit 10")
    List<TransferTest> getTransferRequests(@Param("transfer_from") Integer transfer_from);
}

注意：JPQL中使用实体类的属性名（而非数据库列名），且需确保RegistrationEntity是已定义的JPA实体类。

方法2：原生SQL配合@SqlResultSetMapping（保留原生SQL场景） #

通过JPA结果集映射规则，指定原生查询结果到DTO的转换逻辑：

1. 在TransferEntity上添加结果集映射配置：

@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "transfers_table")
@SqlResultSetMapping(
        name = "TransferTestMapping",
        classes = @ConstructorResult(
                targetClass = TransferTest.class,
                columns = {
                        @ColumnResult(name = "transfer_to", type = Integer.class),
                        @ColumnResult(name = "username", type = String.class)
                }
        )
)
public class TransferEntity {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Integer id;
    private Integer transfer_from;
    private Integer transfer_to;
    private Double transaction_amount;
    private Date transaction_date;
}

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

@Repository
public interface HomeTransferPageRepository extends JpaRepository<TransferEntity, Integer> {
    @Query(value="select DISTINCT transfer_to, username from (select transfer_to, transaction_date, username from transfers_table left join registration_entity on transfers_table.transfer_to = registration_entity.id where transfer_from= :transfer_from ORDER BY transaction_date DESC LIMIT 10 )  as x", 
            nativeQuery=true,
            resultSetMapping = "TransferTestMapping")
    List<TransferTest> getTransferRequests(Integer transfer_from);
}

方法3：手动转换（灵活可控） #

让Repository返回Map集合，在业务层手动转换为DTO：

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

@Repository
public interface HomeTransferPageRepository extends JpaRepository<TransferEntity, Integer> {
    @Query(value="select DISTINCT transfer_to, username from (select transfer_to, transaction_date, username from transfers_table left join registration_entity on transfers_table.transfer_to = registration_entity.id where transfer_from= :transfer_from ORDER BY transaction_date DESC LIMIT 10 )  as x", 
            nativeQuery=true)
    List<Map<String, Object>> getTransferRequests(Integer transfer_from);
}

2. 在DAO层手动完成转换：

@Autowired
private HomeTransferPageRepository homeTransferPageRepository;

public List<TransferTest> getSentTransfers(TransferEntity entity) {
    List<Map<String, Object>> resultMaps = homeTransferPageRepository.getTransferRequests(entity.getId());
    List<TransferTest> transferTests = new ArrayList<>();
    for (Map<String, Object> map : resultMaps) {
        TransferTest test = new TransferTest();
        test.setTransfer_to((Integer) map.get("transfer_to"));
        test.setUsername((String) map.get("username"));
        transferTests.add(test);
    }
    return transferTests;
}

同步修改Service层的返回类型为List<TransferTest>即可。

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