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.

Share your table structure please, are zip codes saved in different table? — Vlad Hanzha
– Vlad Hanzha, Commented Feb 14, 2019 at 9:28
This is a text field type for them? In that case the problem is that you're looking for {11111} with closing bracket, but your field also contains 22222. You should look for 11111 in that case, without brackets — Vlad Hanzha
– Vlad Hanzha, Commented Feb 14, 2019 at 9:31
Do you mean select agents from Agents as agents where '11111' in (agents.zip_codes)? Then PostgreSQL throws an error: malformed array literal: "11111" ... Array value must start with "{" or dimension information. — Johannes Flügel
– Johannes Flügel, Commented Feb 14, 2019 at 9:35

vimuth · Accepted Answer · 2019-05-29 04:01:12Z

4

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>

edited May 29, 2019 at 4:01

vimuth

5,87355 gold badges98 silver badges128 bronze badges

answered May 29, 2019 at 3:42

user10492653

563 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Johannes Flügel Over a year ago

Thanks, this is much better than my workaround, because I can use the IN clause for non array columns.

Johannes Flügel · Accepted Answer · 2019-02-14 11:07:01Z

0

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 (");
  }
}

edited Feb 14, 2019 at 11:07

answered Feb 14, 2019 at 10:53

Johannes Flügel

3,2923 gold badges20 silver badges32 bronze badges

Collectives™ on Stack Overflow

Hibernate, PostgreSQL: how to search in an array column

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related