Hibernate Subquery Question

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 fig开发者_Go百科ure 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.

---

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

---

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.

---

You can also use @Formula for the totalPayments field. Disadvantage is, that the "sum" is computed every time you load the entity. So, you may use LAZY @Formula - do build time enhancement or Pawel Kepka's trick: http://justonjava.blogspot.com/2010/09/lazy-one-to-one-and-one-to-many.html Disadvantage is, that is you have more LAZY @Fromula and you hit just one of them, all of them are loaded. Another solution may be to use @MappedSuperclass and more subclasses. Each subclass may have different @Formula fields. And one more solution beside DB view: Hibernate @Subselect.