9

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.

Improve this question
2
  • The code you have posted is much too long. Please only post the relevant parts of it. Commented Sep 3, 2012 at 9:27
  • I gave up on JPA's criteria API. QueryDSL with its fluent API is much simpler to understand and more concise. See: queryDSL Commented Sep 6, 2012 at 19:50

2 Answers 2

Reset to default
27

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 PredicateS: 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); }
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks for your input Perissf. Can you please tell me the type and value for the value variable? Regards.
That was just pseudo-code. You can use your Predicates instead. You will probably not use the loop but the concept is valid anyway for your if clauses. Hope it's clear.
The behavior of my test is the same after using the last line of your code i.e. c.where(criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]))); Any other idea?
I've altered the code as advised in your second edit as well as commented out the following line: q.setParameter("identifiant", "%" + identifiant + "%"); It makes no difference unfortunately...
I think that: c.where(criteriaBuilder.and(predicates.toArray(new Predicate[] {})));is redundant as it could be rewritten as: c.where(predicates.toArray(new Predicate[] {}));
2

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()); }
Improve this answer

2 Comments

Explanation to large code block would be helpful for future readers
I have a variable "factorParam' used as a flag, if it variable is greater than zero then a Predicate object is added to an Predicate list object, after that, the list predicate is added to where sentence.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.