Spring Data JPA：主键含常量值的单向关联配置问题

订单表（customorder）与客户表（customer）均使用复合主键，客户表主键包含module和function字段用于多业务场景复用。在Order实体中通过@JoinColumnsOrFormulas结合@JoinFormula，用常量'MODULE_FOO'和'FUNCTION_BAR'关联Customer实体后，调用Repository的原生查询方法时出现异常，提示module和function列未定义。

相关代码 #

Order实体

@Getter
@Setter
@Entity
@Table(name = "customorder")
@IdClass(OrderId.class)
public class Order {

   @Id
   @Column("COMPANYNUMBER")
   private Integer companyNumber;

   @Id
   @Column("CUSTOMERNUMBER")
   private Integer customerNumber;

   @Id
   @Column("ORDERNUMBER")
   private Integer orderNumber; 

   ...

   @ManyToOne(fetch = FetchType.LAZY)
   @JoinColumnsOrFormulas({
        @JoinColumnOrFormula(column = @JoinColumn(name = "COMPANYNUMBER", referencedColumnName = "COMPANYNUMBER", insertable = false , updatable = false)),
        @JoinColumnOrFormula(column = @JoinColumn(name = "CUSTOMERNUMBER", referencedColumnName = "CUSTOMERNUMBER", insertable = false , updatable = false)),
        @JoinColumnOrFormula(formula = @JoinFormula(value = "'MODULE_FOO'", referencedColumnName = "MODULE")),
        @JoinColumnOrFormula(formula = @JoinFormula(value = "'FUNCTION_BAR'", referencedColumnName = "FUNCTION"))
    })
    private Customer customer; 
}

OrderId类

public class OrderId implements Serializable {

    private Integer companyNumber;

    private Integer customerNumber;
    
    private Integer orderNumber;
    
    
    public OrderId() {
    }


    public OrderId(Integer companyNumber, Integer customerNumber, Integer orderNumber) {
        super();
        this.companyNumber = companyNumber;
        this.customerNumber = customerNumber;
        this.orderNumber = orderNumber;
    }   
}

Customer实体

@Getter
@Setter
@Entity
@Table(name = "customer")
@IdClass(CustomerId.class)
public class Customer {

   @Id
   @Column("COMPANYNUMBER")
   private Integer companyNumber;

   @Id
   @Column("CUSTOMERNUMBER")
   private Integer customerNumber; 

   @Id
   @Column("MODULE")
   private String module;    

   @Id
   @Column("FUNCTION")
   private String function;    

   ...
}

CustomerId类

public class CustomerId implements Serializable {

    private Integer companyNumber;

    private Integer customerNumber;

    private String module;

    private String function;
    
    
    public CustomerId() {
    }


    public CustomerId(Integer companyNumber, Integer customerNumber, String module, String function) {
        super();
        this.companyNumber = companyNumber;
        this.customerNumber = customerNumber;
        this.module = module;
        this.function = function;
    }   
}

OrderDao接口

public interface OrderDao extends JpaRepository<Order, OrderId> {

   @Query(value = "SELECT COMPANYNUMBER, CUSTOMERNUMBER, ORDERNUMBER, ... FROM customorder WHERE COMPANYNUMBER = :companyNumber", nativeQuery = true)
   List<Order> findAllByCompanyNumber(@Param(value = "companyNumber") Integer companyNumber);
}

问题根源 #

使用@JoinFormula配置常量关联后，Hibernate在映射实体时会期望原生查询返回关联Customer所需的所有主键字段（包括module和function），但当前原生查询仅返回订单表字段，缺少这两个常量字段的值，导致映射失败。

修复方案 #

方案1：修改原生查询补充常量字段 #

在原生SQL中手动添加module和function的常量值，让查询结果包含关联所需的全部主键字段：

public interface OrderDao extends JpaRepository<Order, OrderId> {
   @Query(value = "SELECT COMPANYNUMBER, CUSTOMERNUMBER, ORDERNUMBER, ..., 'MODULE_FOO' as MODULE, 'FUNCTION_BAR' as FUNCTION FROM customorder WHERE COMPANYNUMBER = :companyNumber", nativeQuery = true)
   List<Order> findAllByCompanyNumber(@Param(value = "companyNumber") Integer companyNumber);
}

方案2：改用@Where注解替代@JoinFormula（推荐） #

移除@JoinColumnsOrFormulas，在关联上添加@Where注解过滤特定module和function的客户数据，无需修改原生查询：

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumns({
    @JoinColumn(name = "COMPANYNUMBER", referencedColumnName = "COMPANYNUMBER", insertable = false, updatable = false),
    @JoinColumn(name = "CUSTOMERNUMBER", referencedColumnName = "CUSTOMERNUMBER", insertable = false, updatable = false)
})
@Where(clause = "MODULE = 'MODULE_FOO' AND FUNCTION = 'FUNCTION_BAR'")
private Customer customer;

Hibernate会在加载关联Customer时自动添加过滤条件，避免原生查询的字段依赖问题。

方案3：使用DTO投影替代实体映射 #

若仅需查询订单数据、无需关联Customer，可定义DTO类承载查询结果，绕过实体关联的字段要求：

// 定义DTO
public class OrderDto {
    private Integer companyNumber;
    private Integer customerNumber;
    private Integer orderNumber;
    // 其他字段及构造方法

    public OrderDto(Integer companyNumber, Integer customerNumber, Integer orderNumber) {
        this.companyNumber = companyNumber;
        this.customerNumber = customerNumber;
        this.orderNumber = orderNumber;
    }
}

// 修改Repository方法
public interface OrderDao extends JpaRepository<Order, OrderId> {
   @Query(value = "SELECT COMPANYNUMBER, CUSTOMERNUMBER, ORDERNUMBER, ... FROM customorder WHERE COMPANYNUMBER = :companyNumber", nativeQuery = true)
   List<OrderDto> findAllByCompanyNumber(@Param(value = "companyNumber") Integer companyNumber);
}

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