Spring JPA Specification Where condition parenthesis

Question

How can i use spring JPA Specification for where condition as below:

Where cond1 and (cond2 or cond3)

AND

Where (cond1 and cond2) or cond3.

Specification code is as below:

Specification<DocRecord> updatedAtAndCountGTZeroSpec = new Specification<DocRecord>() {

    @Override
    public Predicate toPredicate(Root<DocRecord> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        return cb.or(
            (cb.and(
                cb.lessThanOrEqualTo(root.get("updatedAt"),new Timestamp(now.getTimeInMillis())),
                cb.lessThan(root.get("attemptCount"), 3))
            ),
            (cb.equal(root.get("attemptCount"), 0))
        );
    }
};

Above is production where condition as below:

where docrecord0_.updated_at < ? and docrecord0_.attempt_count<3 or docrecord0_.attempt_count=0

Countermarch

Well, you did the second one. What's the problem? – Succeed

How to achieve first one. My main concern is for parenthesisation in Specification. – Countermarch

cb.and(cond1, cb.or(cond2, cond3))? Not sure I understand your issue... – Machicolation

In your second snippet, you have two conditions separated by or. So you start by cb.or. In your first snippet, the two main conditions are separated by and, so start by cb.and. I don't get what the difficulty is. – Succeed

I have the same problem, the query generated should be : where docrecord0_.updated_at < ? and (docrecord0_.attempt_count<3 or docrecord0_.attempt_count=0) but what i get on console is : where docrecord0_.updated_at < ? and docrecord0_.attempt_count<3 or docrecord0_.attempt_count=0 without any parenthesis – Syringa

Answer 1

You can build the 'OR' specification, then add an 'AND' specification for the first one

Specification<?> spec = spec1.or(spec2);
spec = spec.and(spec3);

spec is actually equal to (spec1 OR spec2) AND spec3

Joby

Answer 2

This is a known formatting issue with Hibernate's show_sql output. The actual SQL query is correct and executes properly with proper parentheses, but the show_sql display has formatting problems.You can verify in this way:

spring.jpa.properties.hibernate.use_sql_comments=true

Dermatitis