Hibernate, PostgreSQL: how to search in an array column

Question

I use the PostgreSQL table agents. It has a column zip_codes character(5)[] that stores the zip codes of the areas the agents are responsible for.

It stores agent1 with the zip codes {11111,22222} and agent2 with the zip code {33333}.

I want to look for all agents that are responsible for a special area.

Without Hibernate it is easy: SELECT * FROM agents WHERE '11111' = ANY (zip_codes) returns agent1.

But how do I make it with HQL? It does not know any. But if I use in instead, I will get wrong results: if I write select agents from Agents as agents where '{11111}' in (agents.zip_codes), agent1 will not be found. If I use '{33333}' instead, agent2 will be found.

Of course I can search with something like (in sql) WHERE zip_codes[1] = '11111' OR zip_codes[2] = '11111' (arrays in PostgreSQL start with index 1), but this is not handy for many entries in zip_codes.

Answer 1

you can regist dailect like this

public class MyPostgresSQLDialect extends PostgreSQLDialect {
  public MyPostgresSQLDialect() {
    super();

    this.registerFunction( "array_any", new SQLFunctionTemplate(StandardBasicTypes.INTEGER,"ANY(?1)") );
    this.registerFunction( "array_array", new SQLFunctionTemplate(StandardBasicTypes.INTEGER,"array[?1]") );

  }
}

now you can use in hql

String hql = " from tablename" +
            " where year = :year and month = :month and :version = array_any(versions)";

remeber regist dailect in sessionFactory

<prop key="hibernate.dialect">com.test.MyPostgresSQLDialect</prop>