CODEWITHSUNDEEP
postgresqlhibernatehql
Johannes Flügel
Johannes Flügel

Reputation: 3262

Hibernate, PostgreSQL: how to search in an array column

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.

Upvotes: 2

Views: 1930

Answers (2)

user10492653
user10492653

Reputation: 56

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>

Upvotes: 4

Johannes Fl&#252;gel
Johannes Fl&#252;gel

Reputation: 3262

Workaround with Hibernate Interceptor

This is not a nice solution:

Write the hql query select agents from Agents as agents where '11111' in (agents.zip_codes) and use the Hibernate Interceptor

public class CustomInterceptor extends EmptyInterceptor {

  @Override
  public String onPrepareStatement(String sql) {
    return sql.replaceAll("\\s+in\\s*\\(", " = any (");
  }
}

Upvotes: 0

Related Questions