CODEWITHSUNDEEP
hibernatefunctionjpacriteriacriteria-api
zysaaa
zysaaa

Reputation: 1877

Use array_position with JPA Criteria API?

I am using springjpa & postgresql,there is a field in my database which is bitint array type,And in Java,it is a Long value.

I am using spring jpa CriteriaBuilder&Specification to make a array position function :

private Predicate toPredicate(String propName, Query q, Root<T> root, CriteriaBuilder cb) {
    ...
    return cb.isNotNull(cb.function("array_position", Integer.class,
                            xx expression,
                            cb.literal(q.value)));  // q.value is a long type value
}

And it goes error when i execution query:

Hibernate: 
    select
        distinct ruletemp0_.id as id1_119_,
        ruletemp0_.applicant as applican2_119_,
        ruletemp0_.approver as approver3_119_,
        ruletemp0_.devices as devices4_119_,
        ruletemp0_.end_time as end_time5_119_,
        ruletemp0_.execution_id as executio6_119_,
        ruletemp0_.execution_type as executio7_119_,
        ruletemp0_.extra as extra8_119_,
        ruletemp0_.rule_id as rule_id9_119_,
        ruletemp0_.start_time as start_t10_119_,
        ruletemp0_.state as state11_119_,
        ruletemp0_.user_id as user_id12_119_ 
    from
        tbl_ruletemp ruletemp0_ 
    where
        array_position(ruletemp0_.user_id, 1) is not null 
        or array_position(ruletemp0_.user_id, 2) is not null
2019-10-30 10:44:42.230 [WARN ] [main] [org.hibernate.engine.jdbc.spi.SqlExceptionHelper::logExceptions] SQL Error: 0, SQLState: 42883
2019-10-30 10:44:42.230 [ERROR] [main] [org.hibernate.engine.jdbc.spi.SqlExceptionHelper::logExceptions] ERROR: function array_position(bigint[], integer) does not exist
  建议：No function matches the given name and argument types. You might need to add explicit type casts.

It seems jpa does not recognize my Long type value,i try to use:

BigInteger bigInteger = new BigInteger(String.valueOf(q.value));
return cb.isNotNull(cb.function("array_position", Integer.class,
                            toExpression(propName, root),
                            cb.literal(bigInteger)));

Still error. If i execute SQL query in pg directly :

select * from tbl_ruletemp t where array_position(t.user_id, cast(1 as BIGINT)) is not null;

will work.

And ideas?

Upvotes: 2

Views: 2355

Answers (1)

coladict
coladict

Reputation: 5095

You can create a cast expression with the criteria builder, but it may not be necessary. Instead of using the number as a literal, pass it as a parameter.

ParameterExpression<BigInteger> pid = cb.parameter(BigInteger.class, "pid");
return cb.isNotNull(cb.function("array_position", Integer.class,
                            toExpression(propName, root),
                            pid));

Then simply bind the parameter as you normally would. And since you're using arrays, perhaps it would be better to use arrayoverlap instead of chaining array_position calls and or expressions.

Upvotes: 1

Related Questions