JPA Query - how to get a specific result set?

Question

In an app based on JPA2/Hibernate/Oracle+Spring+Wicket, I use the following model:

public class Item {
 private String name;
 private Set<Application> apps;
 private ...
}

public class Application {
 private String applicant;
 private Item item;
 private Status status;
 private ...
}

The mapping between Item and Application is that every item has many applications, every application points to just one item.

Now I want to search for Applications that satisfy a complex set of criteria and it'd be great if the result set was a set of pairs < Item, List< Application >> (it cannot be just Set< Item >, because usually only a subset of applications for a specific item would satisfy the criteria).

Could you recommend me a way how to do this? My first idea was to query first for pairs < ItemID, AppID > and then iterate through these and produce the result set manually, but it seems quite cumbersome and ineffective.

Answer 1

If you want to express it in a single query (so, you use JOIN to express criteria on the Applications), querying for pairs is the only way to do it - that's how relational databases work.

Answer 2

I' suggest using JPA2 criteria queries with tuples and subqueries. Something in the line of (pseudocode):

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> crit = cb.createTupleQuery();
Subquery<Application> subquery = cb.subquery(Application.class);
subquery.where(...);
subquery.correlate(...);
crit.select(cb.tuple(cb.from(Item.class), subquery);

It's pseudocode because I don't have the exact syntax for subqueries in mind right now and no Eclipse to try it out, sorry. There's a nice introduction to subqueries in Keith, Schincariol. Pro JPA 2: Mastering the Java Persistence API.