I am trying to test a 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 at assertEquals(1, boites.size());
indicating that no result is returned i.e. (java.lang.AssertionError: expected:<1> but was:<0>
).
I strongly suspect something is wrong here:
for (Predicate predicate : criteria) {
c.where(criteriaBuilder.and(predicate));
}
But I am not sure how to "and" the criteria.
Can anyone please provide advice?
P.S. FYI, BoiteDataOnDemand
inserts random 10 rows into the boite
table.
EDIT: code was edited to make it shorter.
Starting from your hint in the final part of your post, I agree that the way you are adding predicates for the where
clause is not correct.
I see two ways of proceeding:
First way
Using an array of predicates
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
Modifying the same predicate in the 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 looking again your code sample, I see that you have already created in the correct way a list of Predicate
S: you have called it criteria
. You are only using them in the wrong way. See the last line of my first example.
EDIT 2
In order to see if the problem is generated by the use of PredicateExpressionS
, that are not specifically needed in your case, try to temporarily remove them. Modify your 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);
}