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

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