Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Correct usage of JPA criteria API, Predicates and where method of CriteriaQuery

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.

like image 313
balteo Avatar asked Sep 03 '12 08:09

balteo


People also ask

How use JPA Criteria API?

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.

What is predicate in Criteria API?

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.

Why do we need JPA Criteria API?

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.

Which method can be used to sort the extracted results using criteria queries?

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.


2 Answers

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);
}
like image 158
perissf Avatar answered Oct 28 '22 05:10

perissf


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());
        }
like image 43
Jorge Santos Neill Avatar answered Oct 28 '22 06:10

Jorge Santos Neill