Spring Boot多表原生查询报错：无合适类型转换器问题排查

错误信息：

No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap]

相关代码文件 #

ProductService.java #

package com.corellana.bookStore.Product;
import java.util.List;
import org.springframework.stereotype.Service;

import com.corellana.bookStore.ProductDTO.ListProductsOrderOffer;

import lombok.RequiredArgsConstructor;

@Service
@RequiredArgsConstructor
public class ProductService {
    private final ProductRepository productRepository;


    public List<ListProductsOrderOffer> findProductsOrderOffer(Integer idCategory){
        return  productRepository.findProductsOrderOffer(idCategory);
    }

}

ProductRepository.java #

package com.corellana.bookStore.Product;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.corellana.bookStore.ProductDTO.ListProductsOrderOffer;

@Repository
public interface ProductRepository extends JpaRepository<Product,Long>  {
 
    @Query(value ="select p.id_product," + 
    "p.name_product "+ 
    "from product p " + 
    "inner join editorial e on (p.id_editorial = e.id_editorial) " + 
    "inner join category c on (p.id_category = c.id_category) " + 
    "where c.id_category = :idCategory and p.status_product = true " + 
    "order by p.name_product ASC", nativeQuery = true)
    List<ListProductsOrderOffer> findProductsOrderOffer(@Param("idCategory") Integer idCategory);


}

ListProductsOrderOffer.java #

package com.corellana.bookStore.ProductDTO;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;


@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder

public class ListProductsOrderOffer {

    public Integer   idProduct;
    public String    nameProduct;
    
}

ProductController.java #

package com.corellana.bookStore.Product;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.corellana.bookStore.ProductDTO.ListProductsOrderOffer;
import com.corellana.bookStore.ProductDTO.ProductDto;

import lombok.RequiredArgsConstructor;

@RestController
@RequestMapping("/product")
@RequiredArgsConstructor
public class ProductController {
    private final ProductService productService;


    @GetMapping("/find-products-order-offer")
    public ResponseEntity<?>  findProductsOrderOffer( @RequestParam Integer idCategory){
        
        Map<String, Object> response = new HashMap<>();
        List<ListProductsOrderOffer> productDto = productService.findProductsOrderOffer(idCategory)
        .stream()
        .map(Product -> ListProductsOrderOffer.builder()
        .idProduct(Product.getIdProduct())
        .nameProduct(Product.getNameProduct())
        .build())
        .toList();
            
        response.put("data", productDto);
        response.put("success", true);
        return ResponseEntity.ok(response);
    } 


}

问题原因及解决办法 #

核心原因 #

Spring Data JPA原生查询默认返回TupleBackedMap（键值对形式的查询结果），JPA无法自动将这种Map结构直接转换为自定义DTO；同时控制器存在逻辑错误：仓库返回的是List<ListProductsOrderOffer>，却被当作Product实体做map转换，会触发额外类型错误。

解决办法（三种可选方案） #

方案1：JPQL构造函数映射（推荐）

改用JPQL查询，通过new关键字直接调用DTO构造函数，查询字段顺序必须和DTO构造函数参数顺序完全一致：

@Query(value = "select new com.corellana.bookStore.ProductDTO.ListProductsOrderOffer(p.id_product, p.name_product) " + 
               "from product p " + 
               "inner join editorial e on (p.id_editorial = e.id_editorial) " + 
               "inner join category c on (p.id_category = c.id_category) " + 
               "where c.id_category = :idCategory and p.status_product = true " + 
               "order by p.name_product ASC") // 移除nativeQuery=true，使用JPQL
List<ListProductsOrderOffer> findProductsOrderOffer(@Param("idCategory") Integer idCategory);

方案2：手动转换Map为DTO

保持原生查询，修改返回类型为List<Map<String, Object>>，在Service层手动转换：

1. 修改仓库方法：

@Query(value ="select p.id_product as idProduct," + // 起别名，和DTO属性名一致
               "p.name_product as nameProduct "+ 
               "from product p " + 
               "inner join editorial e on (p.id_editorial = e.id_editorial) " + 
               "inner join category c on (p.id_category = c.id_category) " + 
               "where c.id_category = :idCategory and p.status_product = true " + 
               "order by p.name_product ASC", nativeQuery = true)
List<Map<String, Object>> findProductsOrderOffer(@Param("idCategory") Integer idCategory);

2. 调整Service层逻辑：

import java.util.stream.Collectors;

public List<ListProductsOrderOffer> findProductsOrderOffer(Integer idCategory){
    return productRepository.findProductsOrderOffer(idCategory)
            .stream()
            .map(map -> ListProductsOrderOffer.builder()
                    .idProduct((Integer) map.get("idProduct"))
                    .nameProduct((String) map.get("nameProduct"))
                    .build())
            .collect(Collectors.toList());
}

3. 修正控制器代码，移除多余转换：

List<ListProductsOrderOffer> productDto = productService.findProductsOrderOffer(idCategory);

方案3：第三方工具自动映射

引入ModelMapper或MapStruct等映射库，配置映射规则后自动将查询结果转换为DTO，适合复杂场景，但需额外配置依赖和映射接口。

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