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

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]
javajpaspring-data-jpajpa-criteria


Solution 1:[1]

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)));
}

Solution 2:[2]

you can use something like this:

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

tks!

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ronald James
Solution 2 Victor Loureiro

Related Questions