postgresqlhibernatespring-data-jpaquerydsl

How to pass String[] as varchar[] through Querydsl to PostgreSQL?


Trying to solve Postgresql Array Functions with QueryDSL more cleanly, I've got this far.

// obj.foo is an ArrayPath<String[], String>
bindings.bind(obj.foo).first((path, value) -> 
        Expressions.booleanTemplate("array_contains({0}, {1}) = true", path, value));

this ends up as correct-looking SQL

where array_contains(obj0_1_.foo, ?)=true

but it seems the String[] variable is not passed correctly

org.postgresql.util.PSQLException: ERROR: function array_contains(character varying[], bytea) does not exist

How can I either (if possible)

  1. get the String[] value to bind as a varchar[]?
  2. express the necessary cast in the booleanTemplate?

Solution

  • Instead of passing the String[] directly, wrap it in a TypedParameterValue.

    The hibernate-types library does not yet support varchar[], but you can use it to build something that does:

    public class VarcharArrayType extends AbstractHibernateType<String[]> {
        public static VarcharArrayType INSTANCE = new VarcharArrayType();
    
        public VarcharArrayType() {
            super(ArraySqlTypeDescriptor.INSTANCE, new TypeDescriptor());
        }
    
        public String getName() {
            return "varchar-array";
        }
    
        public static class TypeDescriptor extends StringArrayTypeDescriptor {
            @Override
            protected String getSqlArrayType() {
                return "varchar";
            }
        }
    }
    

    Update: in Hibernate 6 this works out of the box. No additional libraries, classes, or settings requires.