Criteria Query, JPA 2 - build query with IN clause using predicates

Question

Im using the below code for PostgresDB with JPA Criteria Query

public static Specification<EventRecord> findEventRecords(final String id,
        final Boolean status,
        final Date createdTime, final Date updatedTime, final String userId, 
        final List<ChannelType> channelType, final List<String> eventType) {

             return (root, query, builder) -> {
                List<Predicate> predicates = new ArrayList<Predicate>();
                predicates.add(builder.and(builder.equal(root.get( TestRecord_.id), id))); //1st param from Rest API
                predicates.add(builder.and(builder.equal(root.get( TestRecord_.status),status))); //2nd param from Rest API
                predicates.add(builder.and(builder.greaterThanOrEqualTo(root.get( TestRecord_.createdTime), createdTime))); //3rd param from Rest API
                predicates.add(builder.and(builder.lessThanOrEqualTo(root.get( TestRecord_.updatedTime), updatedTime))); //4th param from Rest API

                if (eventType != null && !eventType.isEmpty()) {
                    predicates.add(builder.and(builder.equal(root.get( TestRecord_.eventType), eventType))); // gives error saying No value specified for parameter 5.
                }

                Predicate[] predicatesArray = new Predicate[predicates.size()];
                return builder.and(predicates.toArray(predicatesArray));
        };
    }
    }

I tried with in and isMember() but I still get error, How can I use a IN clause to build a query using Predicates.

Query :
 select * from test_record where (id=? and status = true and created_time = ? and updated_time = ? and event_type IN (value1, value2, value3);

Update

Query That i want to build with Predicates :
 select * from test_record where (id=? and status = ? and created_time = ? and updated_time = ? and event_type IN ?;

So Im getting same error when I'm passing any list VALUES, how do resolve these error:

org.postgresql.util.PSQLException: No value specified for parameter 5.
    at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:257) ~[postgresql-42.2.2.jar:42.2.2]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:290) ~[postgresql-42.2.2.jar:42.2.2]
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441) ~[postgresql-42.2.2.jar:42.2.2]

PostgreSQL only gives that message if there ARE 5 parameter slots in the SQL, and the one you quote has 3. — , Jul 10 '18 at 12:52
There are 5 params, I haven't specified in the query, just updating it now — user2340345, Jul 11 '18 at 04:20

score 0 · Answer 1 · answered Jul 10 '18 at 11:56

0

A quick suggestion will be to create an expression with IN predicate and add it to your query builder. I haven't executed the code but something like below should work.

List<String> list = Arrays.asList(new String[]{"SomeValue1", "SomeValue1"});

Expression<String> inExpression = root.get( TestRecord_.eventType);
Predicate inPredicate = inExpression.in(list);

if (eventType != null && !eventType.isEmpty()) {
    predicates.add(builder.and(builder.equal(inPredicate)));
}

answered Jul 10 '18 at 11:56

Ronald James

647
1
5
13

gives this error - `The method equal(Expression>, Expression>) in the type CriteriaBuilder is `not applicable for the arguments (Predicate)` – user2340345 Jul 11 '18 at 04:25

score 0 · Answer 2 · answered Mar 30 '22 at 14:42

0

you can use something like this:

predicates.add(root.get("xx").in(ids));

tks!

answered Mar 30 '22 at 14:42

Victor Loureiro

1

Criteria Query, JPA 2 - build query with IN clause using predicates

2 Answers2