CriteriaBuilder join / subquery with resulting Integer

Question

I'm trying to create a query using CriteriaBuilder to select all Product with a stock greater than zero. Stock is sum(DeliveryRow.amount) - sum(DispatchRow.amount). Both ofcourse only containing the right Product.

I have tried creating Subquery for both DeliveryRow and DispatchRow though I feel like this should be done using a join().

Classes

Product { 
    (...)
}

DeliveryRow {
    @ManyToOne
    private Product product;

    private int amount;
}

DispatchRow {
    @ManyToOne
    private Product product;

    private int amount;
}

Query

In this query I'm not sure how to handle the xxx. I've tried making to subqueries but that didn't work out.

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Product> query = cb.createQuery(Product.class);
Root product = query.from(Product.class);
query.select(product);

// sum of DeliveryRow.amount where DeliveryRow.product = Product
// minus 
// sum of DispatchRow.amount where DispatchRow.product = Product
Expression stock = xxx;  

query.where(cb.gt(stock, Integer.parseInt(0)));
return em.createQuery(query).getResultList();

Any suggestions on how to solve this?

Kevin Bowersox · Accepted Answer · 2013-05-11T15:38:49.177

1

I have recently been doing research on JPA/JPQL, studying the three different approaches for retrieving entities: NamedQueries, em.CreateQuery and CriteriaBuilder. The CriteriaBuilder in my opinion is the most awkward of the three to use. I would recommend creating a NamedQuery to handle this situation, it will be a lot easier to implement and read.

Using this JPQL expression you could retrieve all of the products with a stock greater than zero:

SELECT p.name, SUM(delRow.amount) - SUM(disRow.amount) 
FROM Product p join p.deliveryRows delRow join p.dispatchRows disRow
HAVING SUM(delRow.amount) - SUM(disRow.amount) > 0

/* This assumes product has a Collection<DispatchRow> named dispatchRows 
   and a Collection<DeliveryRow> named deliveryRows.
*/

Make this a named query in the `Product' entity

//This should be concatenated or on one line
@NamedQuery(name="Product.hasStock" 
    query="SELECT p.name, SUM(delRow.amount) - SUM(disRow.amount) 
    FROM Product p join p.deliveryRows delRow join p.dispatchRows disRow
    HAVING SUM(delRow.amount) - SUM(disRow.amount) > 0");

Then execute this query with an EntityManager

@PersistenceContext
EntityManager em;

public void execute(){
  List<Object[]> products = 
      em.createNamedQuery("Product.hasStock").getResultList();

  /* Projections return a List<Object[]> where position 1 in the object array
     corresponds with the first field in the select statement, position two
     corresponds with the second field and so on...  These can also be strongly typed
     if an object is created and the constructor is specified in JPQL statement
  */
}

I know this is a different approach than using the Criteria API, but in my opinion JPQL queries are vastly superior to the Criteria API. Compared to the JPQL syntax, which is very similar to SQL the API felt less concise and intuitive. If you decide to take this route, I have created a video tutorial that demonstrates @NamedQueries and shows how to strongly type the results of queries containing projections. It can be found here.

edited May 11 '13 at 15:38

answered May 11 '13 at 15:33

Kevin Bowersox

93,289
19
159
189

I've worked with NamedQueries before, though I've using CriteriaBuilder because of the typesafety, aren't you worried about that? – Menno May 11 '13 at 15:39
What is your concern with type safety, the Object[]? – Kevin Bowersox May 11 '13 at 15:40
No the query being a `String`. Any changes on say `DispatchRow` could cause issues? – Menno May 11 '13 at 15:43
@Aquillo So the Query contains one reference to the amount field on the `DispatchRow`. So your saying if this column got remained or had its type changed it would break the query? How wouldn't that break a criteria built via the API? Some how you will need to reference that field using its name and applying an aggregate function to it will require the type to be some form of numeric. Maybe I'm missing something, could you elaborate? – Kevin Bowersox May 11 '13 at 15:47
Well, using CriteriaBuilder (with a MetaModel) any serious IDE would notice errors when changing any field of a relevant Object. Otherwise it would still show errors on compilation. NamedQueries won't? – Menno May 11 '13 at 15:49
Gotcha, so your looking to detect errors at compile time as opposed to runtime when something breaks. Good point. This could be where a good set of unit tests comes in handy. I found this post further elaborating: http://stackoverflow.com/questions/7334064/jpa-named-queries-vs-criteria-api – Kevin Bowersox May 11 '13 at 15:51
The query needed some adjustments (like a `GROUP BY` on the `Product`), but I got it working. Thanks! – Menno May 11 '13 at 17:50
@Aquillo Good work. I'm glad to hear you got this resolved. Sorry that query was off I had no way to test. – Kevin Bowersox May 11 '13 at 19:23

CriteriaBuilder join / subquery with resulting Integer

1 Answers1