How to write this query using the Criteria query for JPA?

Question

Can someone help me get a JPA criteria request for a JPA request mentioned below.

SELECT p,l FROM Person p LEFT JOIN Language l ON (p.language = l.language and l.locale like :locale) AND p.name like :name AND p.time BETWEEN :startDate AND :endDate order by name asc 

Answer 1

Assuming Person is related to the language here, what are you doing in the earlier Hibernate:

 Criteria criteria = entityManager.createCriteria(Person.class); Criteria languageCriteria = criteria.createCriteria("language"); languageCriteria.add(Restrictions.like("locale", locale)); criteria.add(Restrictions.like("name", name)); criteria.add(Restrictions.between("time", startDate, endDate)); criteria.addOrder(Order.asc("name")); 

and my first attempt in JPA 2.0:

 CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<Person> criteria = builder.createCriteria(Person.class); Root<Person> pRoot = criteria.from(Person.class); Join<Person, Language> langJoin = criteria.join("language", JoinType.LEFT); Predicate conjunction = builder.conjunction(); criteria.where(builder.and( builder.like(langJoin.get(Language_.locale), locale), builder.like(pRoot.get(Person_.name), name), builder.between(pRoot.get(Person_.time), startDate, endDate)); criteria.orderBy(builder.asc(pRoot.get(Person_.name))); 

Please let me know if this works for you.

Edit: The updated request to use only one where call.

Answer 2

Although the answer provided by johncarl was accepted, it does not look right for me. JavaDocs for CriteriaQuery.where () say:

Modify the query to restrict the query result to match the specified boolean expression. Replaces previously added constraint (s), if any.

As I understand it, each of the following lines (with restrictions) will override the restrictions specified earlier:

 criteria.where(builder.like(langJoin.get(Language_.locale), locale)); criteria.where(builder.like(pRoot.get(Person_.name), name)); criteria.where(builder.between(pRoot.get(Person_.time), startDate, endDate)); 

This means that at the end there will be only the last restriction (between the start and end dates).

I suggest the following modifications to johncarl's answer:

 CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<Person> criteria = builder.createCriteria(Person.class); Root<Person> pRoot = criteria.from(Person.class); Join<Person, Language> langJoin = criteria.join("language", JoinType.LEFT); Predicate[] restrictions = new Predicate[] { builder.like(langJoin.get(Language_.locale), locale), builder.like(pRoot.get(Person_.name), name), builder.between(pRoot.get(Person_.time), startDate, endDate) }; criteria.where(builder.and(restrictions)); criteria.orderBy(builder.asc(pRoot.get(Person_.name))); 

However, this code looks really ugly! Please feel free to edit if it is wrong, and comment on it if you see a better solution! I would be graceful!