如何通过JPA Repository关联查询返回自定义构造对象

首先修正你原SQL里的两处错误：

• 未定义的别名H1需改为H
• 表名要和实体映射的数据库表名一致，替换为T_ORDER和T_ORDER_HISTORY

下面提供两种可靠的映射方案：

方案一：构造函数映射（最简单直接） #

给OrderSimple添加匹配查询字段顺序的构造函数，在@Query中直接调用该构造函数完成映射。

步骤1：修改OrderSimple类，添加构造函数 #

@Getter @Setter
public class OrderSimple {
    private Long orderId;
    private Long userId;
    private String orderDesc;
    private Date orderDate;

    // 构造函数参数顺序、类型需与SQL返回字段完全匹配
    public OrderSimple(Long orderId, String orderDesc, Date orderDate, Long userId) {
        this.orderId = orderId;
        this.orderDesc = orderDesc;
        this.orderDate = orderDate;
        this.userId = userId;
    }
}

步骤2：修正并更新@Query语句 #

@Query(value = "SELECT H.order_id, H.description, H2.min_order_date, O.user_id " +
               "FROM T_ORDER_HISTORY H " +
               "JOIN T_ORDER O ON O.user_id = H.user_id " +
               "JOIN (SELECT order_id, MIN(order_date) AS min_order_date FROM T_ORDER_HISTORY GROUP BY order_id) H2 ON H.order_id = H2.order_id " +
               "WHERE O.order_date = ?1", 
       nativeQuery = true)
List<OrderSimple> getHistoryById(Date orderDate);

注意：原方法参数Integer Id与SQL条件O.order_date=?1类型不匹配，需调整为Date或修改查询条件（根据实际业务需求）。

方案二：使用@SqlResultSetMapping+@ConstructorResult（适合复杂复用场景） #

通过JPA注解配置结果集映射规则，无需依赖构造函数顺序，映射规则可复用。

步骤1：在实体类上添加映射配置（以Order实体为例） #

@Getter @Setter
@Entity(name = "T_ORDER")
@SqlResultSetMapping(
    name = "OrderSimpleMapping",
    classes = @ConstructorResult(
        targetClass = OrderSimple.class,
        columns = {
            @ColumnResult(name = "order_id", type = Long.class),
            @ColumnResult(name = "user_id", type = Long.class),
            @ColumnResult(name = "description", type = String.class),
            @ColumnResult(name = "min_order_date", type = Date.class)
        }
    )
)
public class Order {
    // 原有字段保持不变
    @Id @Column(name = "ORDER_ID")
    private Long orderId;
    @Column(name = "USER_ID")
    private Long userId;
    @Column(name = "ORDER_DESC")
    private String orderDesc;
    @Column(name = "ORDER_DATE")
    private Date orderDate;
    @Column(name = "ORDER_DESC2")
    private String orderDesc2;
}

步骤2：修改@Query指定映射规则 #

@Query(value = "SELECT H.order_id, O.user_id, H.description, H2.min_order_date " +
               "FROM T_ORDER_HISTORY H " +
               "JOIN T_ORDER O ON O.user_id = H.user_id " +
               "JOIN (SELECT order_id, MIN(order_date) AS min_order_date FROM T_ORDER_HISTORY GROUP BY order_id) H2 ON H.order_id = H2.order_id " +
               "WHERE O.order_date = ?1", 
       nativeQuery = true,
       resultSetMapping = "OrderSimpleMapping")
List<OrderSimple> getHistoryById(Date orderDate);

核心说明 #

• 原报错could not resolve property order_id的本质是：JPA原生查询无法自动将结果映射到非实体类DTO，必须明确指定映射规则
• 不要使用@OneToMany等关联注解，该场景为临时业务查询，不属于JPA托管的实体关系，强行使用会引发错误
• 确保SQL中的表名、字段名与数据库实际完全匹配，大小写敏感的数据库需注意字符一致性

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