CODEWITHSUNDEEP
javajpa-2.0
user1300877
user1300877

Reputation: 169

How to write this query using JPA Criteria query?

Can anyone help me to get the JPA criteria query for the JPA query 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

Upvotes: 10

Views: 44506

Answers (2)

John Ericksen
John Ericksen

Reputation: 11113

Assuming that Person has a relation to Language here's what you would do in older 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 try at 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 that works for you.

Edit: Updated query to use only one where call.

Upvotes: 17

hawkpatrick
hawkpatrick

Reputation: 411

Although the answer given by johncarl was accepted it doesn't look correct to me. The JavaDocs for CriteriaQuery.where() say:

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

As I understand it, each of the following lines (giving restrictions) will override the restrictions given previously:

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 in the end only the last restriction (between start and end date) would remain.

I threfore suggest 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's wrong and comment it if you see a better solution! I'd be gracefull!

Upvotes: 8

Related Questions