Spring Data JPA-PK中具有常数值的单向联接

Question

我使用Spring Data JPA/Hibernate，有两个表，一个用于订单，另一个用于客户。每个都有一个复合主键，因为这是由DB模型给出的。客户表是在多个上下文中使用的通用表，因此具有两列"；模块"&quot；功能"；在主键中。现在我想加入订单实体中的客户，该订单实体具有恒定的"值；模块"；以及"；函数"；，这似乎不适用于@JoinFormula。。

订单实体：

@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; 
}

订单实体的Id类别：

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;
}   
}

客户实体：

@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;    
...
}

客户实体的Id类别：

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;
}   
}

除此之外，我通过相应存储库中的自定义本地查询读取订单：

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)
}

当我尝试通过repository方法加载数据时，我收到一个解释，即由于检测到未定义的列名，sql无法执行。详细地说，常量列模块和函数无法解析。。

有什么想法吗，如何正确设置这个星座？

感谢您的帮助和想法！

Answer 1

我不熟悉@JoinColumnOrFormula，但我实现了你的实体，我注意到你在这里的引用列名应该是"MODULE"而不是"MODULL"。还有一个名为"order"的表，它是SQL中的保留字-"order BY">

@JoinColumnOrFormula(formula = @JoinFormula(value = "'MODUL_FOO'", referencedColumnName = "MODUL"))

Answer 2

与此同时，我找到了一个非常简单的解决方案，在这里我更改了OrderDao:中的@JoinColumns和Query

内部订单实体：

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

内部订单dao：

@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)

也许这对将来的某个人会有所帮助。。