Hibernate子查询问题

发布于 2024-08-07 14:22:25 字数 581 浏览 5 评论 0原文

我希望这应该是一个简单的问题。

我有一张发票，该发票上有付款清单。

我尝试使用 Criteria API 返回发票列表及其付款总额。所以，在 SQL 中我想要这样的东西：

SELECT i.*, (SELECT SUM(PMT_AMOUNT) FROM INVOICE_PAYMENTS p WHERE p.INVOICE = i.INVOICE) FROM INVOICES i

我一辈子都无法弄清楚如何使用 Criteria API 来实现这一点。执行以下操作：

Criteria crit = session.createCriteria(Invoice.class)
criteria.setProjection(Projections.projectionList()
          .add(Projections.sum("payements.paymentAmount").as("paymentTotal"))

简单地返回 1 行，其中包含所有发票的预计付款总额，这实际上是您所期望的，但这已是我所能得到的最接近的结果。

非常感谢任何帮助。

原文

This should be a simple one I hope.

I have an invoice and that invoice has a list of payments.

Using the Criteria API I am trying to return a list of invoices and their payment total. So, in SQL I want something like this:

SELECT i.*, (SELECT SUM(PMT_AMOUNT) FROM INVOICE_PAYMENTS p WHERE p.INVOICE = i.INVOICE) FROM INVOICES i

I can't for the life of me figure out how to achieve this with the Criteria API. Doing something like:

Criteria crit = session.createCriteria(Invoice.class)
criteria.setProjection(Projections.projectionList()
          .add(Projections.sum("payements.paymentAmount").as("paymentTotal"))

Simply returns 1 row with the projected payment total for all invoices, which is actually what you'd expect, but this is as close as I can get.

Any help is greatly appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

宣告ˉ结束 2024-08-14 14:22:25

Criteria 有一种方法可以返回发票列表以及该发票的总付款额。

从理论上讲，答案是您可以在投影查询上使用分组属性，将结果按发票分组为总付款。第二部分是，您可以在发票上使用瞬态“totalPayment”值，并使用转换器选择投影到发票结构中。这比处理不同属性的 ArrayList 更容易，但取决于您需要将结果用于什么目的。

为了演示这一点，这里是一个小 Invoice 类的重要部分：

public class Invoice{
   private String name;

   @Transient private int totalPayments;
   @OneToMany Set<Payment> payments = new HashSet<Payment>();

   // getters and setters
...
}

然后这是您可以使用的标准

Criteria criteria = session.createCriteria(Invoice.class)
            .createAlias("payments", "pay")
            .setProjection(Projections.projectionList()
                .add(Projections.groupProperty("id"))
                .add(Projections.property("id"), "id")
                .add(Projections.property("name"), "name")
                .add(Projections.sum("pay.total").as("totalPayments")))
            .setResultTransformer(Transformers.aliasToBean(Invoice.class));

List<Invoice> projected = criteria.list();

这是生成的 sql

Hibernate: 
   select this_.id as y0_, 
          this_.id as y1_, 
          this_.name as y2_, 
          sum(pay1_.total) as y3_ 
   from invoice this_ 
   inner join invoice_payment payments3_ on this_.id=payments3_.invoice_id 
   inner join payment pay1_ on payments3_.payments_id=pay1_.id 
   group by this_.id

There is a way with Criteria to return a list of Invoices along with the total payments for that invoice.

In theory, the answer is that you can use a grouping property on a projection query to group the result into total payment by invoice. The second part is that you could use a transient "totalPayment" value on the Invoice and select the projection into the Invoice structure using a transformer. This would be easier than dealing with an ArrayList of different properties but would depend on what you needed to use the results for.

To demonstrate this, here is the important part of a small Invoice class:

public class Invoice{
   private String name;

   @Transient private int totalPayments;
   @OneToMany Set<Payment> payments = new HashSet<Payment>();

   // getters and setters
...
}

Then this is the criteria that you could use

Criteria criteria = session.createCriteria(Invoice.class)
            .createAlias("payments", "pay")
            .setProjection(Projections.projectionList()
                .add(Projections.groupProperty("id"))
                .add(Projections.property("id"), "id")
                .add(Projections.property("name"), "name")
                .add(Projections.sum("pay.total").as("totalPayments")))
            .setResultTransformer(Transformers.aliasToBean(Invoice.class));

List<Invoice> projected = criteria.list();

And this is the sql that is generated

Hibernate: 
   select this_.id as y0_, 
          this_.id as y1_, 
          this_.name as y2_, 
          sum(pay1_.total) as y3_ 
   from invoice this_ 
   inner join invoice_payment payments3_ on this_.id=payments3_.invoice_id 
   inner join payment pay1_ on payments3_.payments_id=pay1_.id 
   group by this_.id

回复收藏 0 原文

无名指的心愿 2024-08-14 14:22:25

我很确定您无法返回投影中的实体。

有两种可能：

运行两个条件查询，一个用于实际发票，一个用于总计
使用 HQL 执行查询

我还没有测试过这个，但它应该是这样的：

select i, (select sum(p.amount) from InvoicePayments p where p.invoice = i.invoice) from Invoice i

必须等到明天，我有一个非常工作中类似的数据结构我应该能够测试它。

I'm pretty sure you can't return entities in a Projection.

There are two possibles:

Run two criteria queries, one for the actual invoices and one for there totals
Use HQL to perform the query

I haven't tested this but it should go something like:

select i, (select sum(p.amount) from InvoicePayments p where p.invoice = i.invoice) from Invoice i

Will have to wait until tomorrow, I have a very similar data structure at work I should be able to test this then.

回复收藏 0 原文

四叶草在未来唯美盛开 2024-08-14 14:22:25

您还可以对 totalPayments 字段使用 @Formula。缺点是，每次加载实体时都会计算“总和”。因此，您可以使用 LAZY @Formula - 增强构建时间或 Pawel Kepka 的技巧： http://justonjava.blogspot.com/2010/09/lazy-one-to-one-and-one-to-many.html 缺点是，是你有更多的LAZY @Fromula，而你只点击了其中一个，所有的都被加载了。另一个解决方案可能是使用 @MappedSuperclass 和更多子类。每个子类可能有不同的@Formula字段。除了数据库视图之外，还有另一种解决方案：Hibernate @Subselect。

回复收藏 0 原文

~没有更多了~