HQL - Check if an Array contains a value

Question

I have an array field in my first entity Class as below:

class Entity1{
       private Integer col1;
       private String col2;
       private Integer[] col3Arr;
}

I have another entity class as below:

class Entity2{
       private Integer col1;
       private String col2;
       private Integer col3;
}

I am fetching records by joining multiple other entities along with which I have to join Entity1 if col3Arr contains a value col3 from Entity 2

With PSQL, I could easily achieve this by following statement

//Other part of query
join Entity2 e2 on (//conditions from other joined tables//)
join Entity1 e1 on e2.col3=ANY(e1.col3Arr)

What is the HQL equivalent of ANY? Or is there any other way in HQL to check if an array contains a specific value?

Answer 1

For mapping the arrays you will need a custom type. You can use the hibernate-types project for this: https://vladmihalcea.com/how-to-map-java-and-sql-arrays-with-jpa-and-hibernate/

Did you try to use e2.col3 = FUNCTION('ANY', e1.col3Arr) yet? If that doesn't work, I would suggest you create 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) + ")";
    }
}

You will have to register the function within the Dialect.