Criteria API how to write = ANY(?1) expression?

Question

I have a query that I want to translate to Criteria API.

A query

select a, b, c from myTable t where t.e = ANY(?1)

After java processes it (native sql query) the final query looks like this

select a, b, c from myTable t where t.e = ANY(array['prop1', 'prop2', 'prop3'])

My Question is how to translate = ANY(?1) part to Criteria API?

I see that any() definition is

public <Y> Expression<Y> any(Subquery<Y> subquery) 

How to put array of values to it?

I'm using PostgreSQL

Jardiniere

Due to postgresql being the only database that supports ANSI SQL2003 arrays, a feature like this has never been a priority. There is a plug-and-play library to add array support to Hibernate 5.2-5.4, but you will have to use your own native query to make it look like t.e = ANY(?) and bind an array variable. JPQL simply does not support the syntax you're asking for. – Gleeman

so, the only way is to convert this query to in? – Jardiniere

Yes, that would be the solution. And you must also consider that if the collection of values in the in (values) is empty, that will result in a syntax error in the server side. x in () is invalid syntax, because it's expected that if your collection of values is empty that expression will always evaluate to false, which often means you can just skip or simplify the query. – Gleeman

Answer 1

You will need a custom SQLFunction that renders the SQL you desire e.g.

public class ArrayAny implements SQLFunction {

    @Override
    public boolean hasArguments() {
        return true;
    }

    @Override
    public boolean hasParenthesesIfNoArguments() {
        return true;
    }

    @Override
    public Type getReturnType(Type firstArgumentType, Mapping mapping) throws QueryException {
        return firstArgumentType;
    }

    @Override
    public String render(Type firstArgumentType, List args, SessionFactoryImplementor factory) throws QueryException {
        return "any(" + args.get(0) + "::text[])";
    }
}

You will have to register the function within the Dialect. Then you should be able to use the function like this:

query.where(
  criteriaBuilder.equal(
    root.get("e"),
    criteriaBuilder.function('any', String.class, arrayAsString),
  )
);

Colmar