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.
Let's see it step by step: Create an instance of Session from the SessionFactory object. Create an instance of CriteriaBuilder by calling the getCriteriaBuilder() method. Create an instance of CriteriaQuery by calling the CriteriaBuilder createQuery() method.
javax.persistence.criteria The type of a simple or compound predicate: a conjunction or disjunction of restrictions. A simple predicate is considered to be a conjunction with a single conjunct. Return the top-level conjuncts or disjuncts of the predicate.
Java Prime Pack The Criteria API is a predefined API used to define queries for entities. It is the alternative way of defining a JPQL query. These queries are type-safe, and portable and easy to modify by changing the syntax. Similar to JPQL it follows abstract schema (easy to edit schema) and embedded objects.
The ORDER BY clause is used to sort the data and arrange them either in ascending or descending order. The CriteriaQuery interface provides orderBy() method to define the type of ordering.
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);
}
The complete solution that work for me is the following:
CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<Object[]> criteriaQuery = builder.createQuery(Object[].class);
Root<Eje> ejeRoot = criteriaQuery.from(Eje.class);
Root<FactorCritico> factorRoot = criteriaQuery.from(FactorCritico.class);
int factorParam = 2;
criteriaQuery.multiselect(ejeRoot,factorRoot);
List<Predicate> predicates = new ArrayList<>();
Predicate ejeJoinFactorCritico = builder.equal(ejeRoot.get("id"), factorRoot.get("eje"));
predicates.add(ejeJoinFactorCritico);
if (factorParam> 0){
Predicate factorFilter = builder.equal(factorRoot.get("id"), factorParam);
predicates.add(factorFilter);
}
criteriaQuery.where(builder.and(predicates.toArray(new Predicate[] {})));
Query<Object[]> query = session.createQuery(criteriaQuery);
List<Object[]> resultList = query.getResultList();
for(Object[] objects: resultList){
Eje eje = (Eje) objects[0];
System.out.println("eje: "+eje.getName());
FactorCritico factor= (FactorCritico) objects[1];
System.out.println("--> factor: "+factor.getName()+ ", eje: "+ factor.getEje().getName());
//System.out.println("eje, factor size = "+eje.getFactorCriticos());
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With