JPA Criteria API: Iterate through list and create predicates

Question

I'm trying to migrate an iBatis project into rest service using Spring boot, Spring data JPA and Hibernate. I'm stuck at a query conversion, any help is appreciated. This is the iBatis query I'm trying to migrate.

<foreach item="item" collection="currentStatus">
    <choose>
        <when test="item in fStatusList">
            ((j_status_code != #{item}) AND (f_status_code = #{item}) AND
            (EXP_FLAG IS NULL OR EXP_FLAG ='F' ))
        </when>
        <when test="item in jStatusList">
            ((max_jsc.status_code = #{item}) AND
                <if test="item ==419">
                    EXP_FLAG='H'
                </if>
                <if test="item ==449">
                    EXP_FLAG='C'
                </if>
            </when>
        <otherwise>
            ((j_status_code = #{item}) AND (f_status_code_name is null))
        </otherwise>
    </choose>
</foreach>

This is what I have so far.

    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Job> cq = cb.createQuery(Job.class);
    Root<Job> job = cq.from(Job.class);
    List<Predicate> predicates = new ArrayList<>();
    for (int status : statusList) {
    if (IntStream.of(fStatusList()).anyMatch(x -> x == status)) {
        predicates.add(cb.equal(job.get("f_status_code"), status));
        predicates.add(
            cb.or(
                cb.isNull(job.get("EXP_FLAG")),
                cb.equal(
                    job.get("EXP_FLAG"),"F")));
      } else if (IntStream.of(jStatusList()).anyMatch(x -> x == status)) {
        predicates.add(cb.equal(job.get("jobStatusCode"), status));
        if (status == 10) {
          predicates.add(
              cb.equal(
                  job.get("EXP_FLAG"), "H"));
        } else if (status == 15) {
          predicates.add(
              cb.equal(
                  job.get("EXP_FLAG"), "C"));
        }
      } else {
        predicates.add(
            cb.and(
                cb.isNull(job.get("f_status_code")),
                cb.equal(job.get("j_status_code"), status)));
      }
}
    cq.where(predicates.toArray(new Predicate[0]));
    TypedQuery<Job> query = entityManager.createQuery(cq);

The problem is that the where condition is getting formed like below. The iteration needs to add OR condition, instead of AND for every statuses. How can I achieve this ?

where
 (
 job0_.f_status_code is null
 ) 
 and job0_.j_status_code=315 
 and job0_.j_status_code=449 
 and job0_.exp_flag=?

Also please advise if using JPA Criteria API is the best option in these scenarios. It seemed pretty complicated and unreadable for some simple iBatis conditions. Is there a better alternative while using Hibernate ?

You are using Spring so look at Spring Data JPA extension with specification pattern. See here for an example using criteria API and an alternative - QueryDsl - which I find easier to work with: https://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl/. See also the following which notes that *the JPA Criteria API has one major flaw:It is very hard to implement complex queries and even harder to read them.* and also discusses QueryDsl as an alternative: https://www.petrikainulainen.net/programming/spring-framework/spring-data-jpa-tutorial-part-five-querydsl/ — Alan Hay, Jan 29 '19 at 10:25
Thanks. I did take a look at specification pattern and it seemed pretty hard to implement dynamic queries like this one. Will take a look at QueryDsl. — jijo, Jan 29 '19 at 16:58

perissf · Accepted Answer · 2019-01-29T15:07:49.513

CriteriaQuery#where() creates a conjunction when passed an array of predicates, i.e. it uses and operator between them. If you want to use disjunction, i.e. the or operator, simply wrap them with CriteriaBuilder#or():

cq.where(cb.or(predicates.toArray(new Predicate[] {})));

In regards to your second question, consider that Hibernate Criteria has been deprecated in favour of JPA Criteria API: Deprecated createCriteria method in Hibernate 5

JPA Criteria API: Iterate through list and create predicates

1 Answers1