Select an object from a joined table using JPA-APIs

Question

I have been fighting the next problem for about two days and hope you can give me a push in the right direction. The tutorials and examples that I found during my research have always shown how easily api criteria can be combined. First of all, I have two classes:

@Entity public class Offer { private String name; @ManyToOne private Location location; private String tags; } 

and

 @Entity public class Location { private String name; private string tags; } 

Since I need to avoid circular references, the connection between these classes is only unidirectional. There are many additional attributes in this class, and I want to create dynamic queries depending on my search filter. The following SQL statement should explain what I like:

 SELECT l FROM Offer o JOIN o.location l WHERE o.tags LIKE :sometag AND l.tags LIKE :someothertag 

After implementing this with api criteria, I got this code:

 CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder(); CriteriaQuery<Location> criteriaQuery = criteriaBuilder.createQuery(Location.class); criteriaQuery = criteriaQuery.distinct(true); Join location; ArrayList<Predicate> whereList = new ArrayList<Predicate>(); // if filter by offer, use offer as main table and join location table if (filter.getOfferTags() != null) { Root<Offer> offer = criteriaQuery.from(Offer.class); location = offer.join("location"); // limit to offering tags Path<String> tagPath = offer.get("tags"); for (String tag : filter.getOfferTags()) { Predicate whereTag = criteriaBuilder.like(tagPath, "%" + tag + "%"); whereList.add(whereTag); } } else { // else use location table as base location = (Join<Location, Location>) criteriaQuery.from(Location.class); } 

But if I did this, I get the following error message from my H2 database:

 Column "LOCATION.ID" not found; SQL statement: SELECT DISTINCT LOCATION.ID, LOCATION.NAME FROM OFFER t0, LOCATION t1 WHERE t0.TAGS LIKE ? AND t1.TAGS LIKE ? 

The database expects t1.ID and t1.NAME in the select clause, not LOCATION.ID and LOCATION.NAME . How can I tell JPA to create a “correct” request? Am I missing something in my code?

I am using Glassfish 3.1.1 with Eclipse Link and H2 base.

Answer 1

I think you just missed the selection in your query:

 criteriaQuery.select(location); 

Answer 2

I don’t think you need the explanation to indicate a connection in the request - because the proposal already has a mapping to a location.

Something like this will suffice:

 CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Location> cq = criteriaBuilder.createQuery(Location.class); Root<Offer> offer = criteriaQuery.from(Offer.class); cq.select(offer.get("location")); cq.where(... )