Customize JPA CriteriaQuery's on-clause

Question

Is there a way to further restrict a join by adding some expressions? With plain sql i write:

SELECT c.*, COUNT(i.id) invoice_count
FROM customers c
LEFT JOIN invoices i ON i.customer_id = c.id
    AND i.creation_time >= '2012-01-01' -- <= extra restriction
    AND i.creation_time < '2013-01-01' -- <= extra restriction
GROUP BY c.id

I haven't found a way to implement this with JPA 2.0 CriteriaQuery.

Update: As requested my (simplified) code so far (without the extra restriction):

CriteriaQuery<CustomerAndInvoiceCount> criteriaQuery = criteriaBuilder.createQuery(CustomerAndInvoiceCount.class);

Root<Customer> customer = criteriaQuery.from(Customer.class);
ListJoin<Customer, Invoice> invoices = customer.join(Customer_.invoices, JoinType.LEFT);

criteriaQuery.select(criteriaBuilder.construct(
        CustomerAndInvoiceCount.class,
        customer,
        criteriaBuilder.count(invoices)));
criteriaQuery.groupBy(customer);

can you show us what you have tried so far? – sanbhat Jun 05 '13 at 15:26 — sanbhat, Jun 05 '13 at 15:26
I have added some Java code. – mazatwork Jun 05 '13 at 15:58 — mazatwork, Jun 05 '13 at 15:58

score 0 · Answer 1 · answered Jun 05 '13 at 16:21

0

You should just be able to add the criteria predicates without worrying if they are part of the ON clause or the WHERE clause.

answered Jun 05 '13 at 16:21

robert_difalco

4,821
4
36
58

There is a difference: If i add the predicates to the WHERE clause, i lose customers if they don't have any invoices for the specified date range. – mazatwork Jun 05 '13 at 16:24
SELECT c.*, COUNT(i.id) invoice_count FROM customers c LEFT JOIN invoices i ON i.customer_id = c.id WHERE i.creation_time >= '2012-01-01' AND i.creation_time < '2013-01-01' GROUP BY c.id; – robert_difalco Jun 05 '13 at 16:28
This does not work because i want to retrieve **all** customers with their invoice count. Adding the predicate to the where clause returns customers with at least one invoice. – mazatwork Jun 05 '13 at 16:34
@mazatwork Are you using Eclipse or Hibernate? ON support is not standard yet. Personally, I would do this as a NamedNativeQuery but if you must use Criteria you will have to use vendor specific enhancements. – robert_difalco Jun 05 '13 at 16:46
2

Here's a link that may help: http://stackoverflow.com/questions/11081955/jpa-criteria-api-multiple-condition-on-left-join – robert_difalco Jun 05 '13 at 16:47
We use Hibernate 4. It seems we have to wait for hibernate 5 which will implement JPA 2.1. – mazatwork Jun 05 '13 at 16:54
1

Also check this url: https://forums.oracle.com/forums/thread.jspa?threadID=2177735, it suggest the same but also provide option to use JPQL something like "from Customer c left join c.invoices c1 with c1.creationTime >= :dateParam" – Snehal Patel Jun 07 '13 at 14:39

Customize JPA CriteriaQuery's on-clause

1 Answers1