Proper use of the JPA criteria API, Predicates and where is the CriteriaQuery method

Question

I am trying to test the JPA repository. Here is my client / test code:

@Test public void testFindBoitesByMultiFieldWithIdentifiantSet() { BoiteDataOnDemand dod = new BoiteDataOnDemand(); Boite boite = dod.getSpecificBoite(0); boite.setIdentifiant("theIdentifiant"); boiteRepository.save(boite); assertEquals(10, Boite.countBoites()); BoiteQueryInfo boiteQueryInfo = new BoiteQueryInfo(); boiteQueryInfo.setIdentifiant("theIdentifiant"); List<Boite> boites = boiteRepository.findBoitesByMultiField(boiteQueryInfo, 1, 5, "identifiant", "desc"); assertEquals(1, boites.size()); } 

Here is the repository method:

 @Override public List<Boite> findBoitesByMultiField(BoiteQueryInfo boiteQueryInfo, Integer firstResult_, Integer maxResults_, String sort_, String order_) { log.debug("findBoitesByMultiField"); final String identifiant = boiteQueryInfo.getIdentifiant(); final Date dateOuvertureFrom = boiteQueryInfo.getDateOuvertureFrom(); CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder(); CriteriaQuery<Boite> c = criteriaBuilder.createQuery(Boite.class); Root<Boite> boite = c.from(Boite.class); List<Predicate> criteria = new ArrayList<Predicate>(); ... if (identifiant != null && !identifiant.trim().equals("")) { ParameterExpression<String> parameter = criteriaBuilder.parameter(String.class, "identifiant"); Predicate condition = criteriaBuilder.like(boite.<String> get("identifiant"), parameter); criteria.add(condition); } if (dateOuvertureFrom != null && dateOuvertureTo != null) { ParameterExpression<Date> parameterDateOuvertureFrom = criteriaBuilder.parameter(Date.class, "dateOuvertureFrom"); ParameterExpression<Date> parameterDateOuvertureTo = criteriaBuilder.parameter(Date.class, "dateOuvertureTo"); Predicate condition = criteriaBuilder.between(boite.<Date> get("dateOuverture"), parameterDateOuvertureFrom, parameterDateOuvertureTo); criteria.add(condition); } else if (dateOuvertureFrom != null) { ParameterExpression<Date> parameter = criteriaBuilder.parameter(Date.class, "dateOuvertureFrom"); Predicate condition = criteriaBuilder.greaterThanOrEqualTo(boite.<Date> get("dateOuverture"), parameter); criteria.add(condition); } else if (dateOuvertureTo != null) { ParameterExpression<Date> parameter = criteriaBuilder.parameter(Date.class, "dateOuvertureTo"); Predicate condition = criteriaBuilder.lessThanOrEqualTo(boite.<Date> get("dateOuverture"), parameter); criteria.add(condition); } ... if (order.equalsIgnoreCase("desc")) { c.orderBy(criteriaBuilder.desc(boite.get(sort))); } else { c.orderBy(criteriaBuilder.asc(boite.get(sort))); } for (Predicate predicate : criteria) { c.where(criteriaBuilder.and(predicate)); } TypedQuery<Boite> q = em.createQuery(c); if (identifiant != null && !identifiant.trim().equals("")) { q.setParameter("identifiant", "%" + identifiant + "%"); } if (dateOuvertureFrom != null && dateOuvertureTo != null) { q.setParameter("dateOuvertureFrom", dateOuvertureFrom); q.setParameter("dateOuvertureTo", dateOuvertureTo); } else if (dateOuvertureFrom != null) { q.setParameter("dateOuvertureFrom", dateOuvertureFrom); } else if (dateOuvertureTo != null) { q.setParameter("dateOuvertureTo", dateOuvertureTo); } ... return q.setFirstResult(firstResult).setMaxResults(maxResults).getResultList(); } 

However, the test always fails with assertEquals(1, boites.size()); indicating that the result is not returned, i.e. ( java.lang.AssertionError: expected:<1> but was:<0> ).

I strongly suspect that something is wrong here:

 for (Predicate predicate : criteria) { c.where(criteriaBuilder.and(predicate)); } 

But I'm not sure how the “and” criteria are.

Can anyone give some advice?

PS FYI, BoiteDataOnDemand inserts a random 10 rows into the boite table.

EDIT: The code has been edited to make it shorter.

Answer 1

Starting with your hint at the end of your post, I agree that the way to add predicates for the where clause is incorrect.

I see two ways:

First way

Using a predicate array

 List<Predicate> predicates = new ArrayList<Predicate>(); for (Key key : keys) { predicates.add(criteriaBuilder.equal(root.get(key), value)); } c.where(criteriaBuilder.and(predicates.toArray(new Predicate[] {}))); 

Second way

Changing the same predicate in a loop

 Predicate predicate = criteriaBuilder.conjunction(); for (Key key : keys) { Predicate newPredicate = criteriaBuilder.equal(root.get(key), value); predicate = criteriaBuilder.and(predicate, newPredicate); } c.where(predicate); 

---

edited

After reviewing the sample code again, I see that you created the Predicate S list correctly: you named it criteria . You are using them incorrectly. See the last line of my first example.

---

EDIT 2

To find out if the problem was generated using PredicateExpressionS , which are not specifically needed in your case, try temporarily removing them. Change the first criteria from

 if (identifiant != null && !identifiant.trim().equals("")) { ParameterExpression<String> parameter = criteriaBuilder.parameter(String.class, "identifiant"); Predicate condition = criteriaBuilder.like(boite.<String> get("identifiant"), parameter); criteria.add(condition); } 

to

 if (identifiant != null && !identifiant.trim().equals("")) { Predicate condition = criteriaBuilder.like(boite.get("identifiant"), "%" + identifiant + "%"); criteria.add(condition); }