Java 如何使用 JPA 和 Hibernate 映射计算属性

How to map calculated properties with JPA and Hibernate

提问by Francois

My Java bean has a childCount property. This property is not mapped to a database column. Instead, it should be calculated by the database with a COUNT()functionoperating on the join of my Java bean and its children. It would be even better if this property could be calculated on demand / "lazily", but this is not mandatory.

我的 Java bean 有一个 childCount 属性。此属性未映射到数据库列。相反，它应该由数据库使用COUNT()对我的 Java bean 及其子项的连接进行操作的函数来计算。如果可以按需/“懒惰”地计算此属性会更好，但这不是强制性的。

In the worst case scenario, I can set this bean's property with HQL or the Criteria API, but I would prefer not to.

在最坏的情况下，我可以使用 HQL 或 Criteria API 设置此 bean 的属性，但我不想这样做。

The Hibernate @Formulaannotation may help, but I could barely find any documentation.

Hibernate@Formula注释可能会有所帮助，但我几乎找不到任何文档。

Any help greatly appreciated. Thanks.

非常感谢任何帮助。谢谢。

采纳答案by Pascal Thivent

JPA doesn't offer any support for derived property so you'll have to use a provider specific extension. As you mentioned, @Formulais perfect for this when using Hibernate. You can use an SQL fragment:

JPA 不提供对派生属性的任何支持，因此您必须使用提供者特定的扩展。正如您所提到的，@Formula在使用 Hibernate 时非常适合于此。您可以使用 SQL 片段：

@Formula("PRICE*1.155")
private float finalPrice;

Or even complex queries on other tables:

甚至对其他表的复杂查询：

@Formula("(select min(o.creation_date) from Orders o where o.customer_id = id)")
private Date firstOrderDate;

Where idis the idof the current entity.

当id是id当前实体的。

The following blog post is worth the read: Hibernate Derived Properties - Performance and Portability.

以下博客文章值得一读：Hibernate 派生属性 - 性能和可移植性。

Without more details, I can't give a more precise answer but the above link should be helpful.

没有更多细节，我无法给出更准确的答案，但上面的链接应该会有所帮助。

See also:

也可以看看：

• Section 5.1.22. Column and formula elements(Hibernate Core documentation)
• Section 2.4.3.1. Formula(Hibernate Annotations documentation)

• 第5.1.22节。列和公式元素（Hibernate Core 文档）
• 第2.4.3.1节。公式（Hibernate Annotations 文档）

回答by Vlad Mihalcea

As explained in this article, you have three options:

如本文所述，您有三个选择：

• either you are calculating the attribute using a @Transientmethod
• you can also use @PostLoadentity listener
• or you can use the Hibernate specific @Formulaannotation

• 要么您正在使用@Transient方法计算属性
• 您还可以使用@PostLoad实体侦听器
• 或者您可以使用 Hibernate 特定的@Formula注释

While Hibernate allows you to use @Formula, with JPA, you can use the @PostLoadcallback to populate a transientproperty with the result of some calculation:

虽然 Hibernate 允许您使用@Formula，但对于 JPA，您可以使用@PostLoad回调将一些计算结果填充到瞬态属性中：

@Column(name = "price")
private Double price;

@Column(name = "tax_percentage")
private Double taxes;

@Transient
private Double priceWithTaxes;

@PostLoad
private void onLoad() {
    this.priceWithTaxes = price * taxes;
}

For more complex queries, you can use the Hibernate @Formula, as explained in this article:

对于更复杂的查询，您可以使用 Hibernate @Formula，如本文所述：

@Formula(
    "round(" +
    "   (interestRate::numeric / 100) * " +
    "   cents * " +
    "   date_part('month', age(now(), createdOn)" +
    ") " +
    "/ 12) " +
    "/ 100::numeric")
private double interestDollars;

回答by Christian Beikov

Take a look at Blaze-Persistence Entity Viewswhich works on top of JPA and provides first class DTO support. You can project anything to attributes within Entity Views and it will even reuse existing join nodes for associations if possible.

看看Blaze-Persistence Entity Views，它在 JPA 之上工作并提供一流的 DTO 支持。您可以将任何内容投影到实体视图中的属性，如果可能，它甚至可以重用现有的连接节点进行关联。

Here is an example mapping

这是一个示例映射

@EntityView(Order.class)
interface OrderSummary {
  Integer getId();
  @Mapping("SUM(orderPositions.price * orderPositions.amount * orderPositions.tax)")
  BigDecimal getOrderAmount();
  @Mapping("COUNT(orderPositions)")
  Long getItemCount();
}

Fetching this will generate a JPQL/HQL query similar to this

获取它会生成一个与此类似的 JPQL/HQL 查询

SELECT
  o.id,
  SUM(p.price * p.amount * p.tax),
  COUNT(p.id)
FROM
  Order o
LEFT JOIN
  o.orderPositions p
GROUP BY
  o.id

Here is a blog post about custom subquery providers which might be interesting to you as well: https://blazebit.com/blog/2017/entity-view-mapping-subqueries.html

这是一篇关于自定义子查询提供程序的博客文章，您可能也会感兴趣：https: //blazebit.com/blog/2017/entity-view-mapping-subqueries.html

回答by bob269

i have tried this on my code

我在我的代码上试过这个

 @Formula(value = "(select DATEDIFF(expiry_date,issue_date)  from document_storage)")
    private String  myColumn;

the errors i get when i run and display my view even before trying to display the column on mustache is something like this

我什至在尝试在 mustache 上显示列之前运行并显示我的视图时遇到的错误是这样的

java.lang.NullPointerException
    at java.base/java.lang.String$CaseInsensitiveComparator.compare(String.java:1241)
    at java.base/java.lang.String$CaseInsensitiveComparator.compare(String.java:1235)
    at java.base/java.util.TreeMap.getEntryUsingComparator(TreeMap.java:374)
    at java.base/java.util.TreeMap.getEntry(TreeMap.java:343)
    at java.base/java.util.TreeMap.get(TreeMap.java:277)
    at com.mysql.cj.result.DefaultColumnDefinition.findColumn(DefaultColumnDefinition.java:182)

the question i asked was is there a way to get values of Calculated column using JPA / Hibernate with mySQL?

我问的问题是有没有办法使用 JPA/Hibernate 和 mySQL 来获取计算列的值？

what am i doing wrong ?

我究竟做错了什么 ？