How to using like array of string HIBERNATE

Question

I'm trying to pass an array of string as a parameter to my query but I get the following error

ERROR: operator does not exist: text ~~ record Dica: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Here is my query

select * from table where value  like any (array[?1]);

when I run it using hibernate the query is like:

 select * from table where value  like any (array[('%foo%', '%bar%', '%baz%')]);

There's a best way to pass my array as parameter?? I think that is important to say that my array is dynamic so i can't fiz it in my query.

score 0 · Answer 1 · answered Mar 19 '19 at 06:31

0

First use createNativeQuery instead of createQuery as syntax is native to PSQL.
Second query syntax should be select * from table where value like any (array?1) as ?1 will be substituted by ['%foo%', '%bar%', '%baz%'], so your end query will match required PSQL syntax. select * from table where value like any (array['%foo%', '%bar%', '%baz%'])

answered Mar 19 '19 at 06:31

Amith Kumar

4,400
1
21
28

Well, i've tried your suggestion but i can't use "... like any (array?1)" because i get the error: ERROR: syntax error at or near "?" – t9217 Mar 19 '19 at 13:23
There are certain posts which might help you... https://stackoverflow.com/questions/1647583/mapping-a-postgresql-array-with-hibernate https://stackoverflow.com/questions/36601318/how-to-use-the-postgres-any-clause-with-jpa-hibernate-native-queries-array-para https://vladmihalcea.com/how-to-map-java-and-sql-arrays-with-jpa-and-hibernate/ – Amith Kumar Mar 20 '19 at 03:32

score 0 · Answer 2 · answered Mar 27 '19 at 12:06

Firstly, your syntax is wrong.

Instead of:

select * from table where value  like any (array[?1]);

You should use:

select * from table where value like any (:vals);

You cannot use array[?] or array[:var] to construct the variable. That is invalid syntax.

Secondly for Hibernate 5.2 up to 5.4 you can just add this dependency and you can use the most common object arrays directly. Primitive arrays are not supported and are not supposed to be.

score -1 · Answer 3 · answered Mar 19 '19 at 02:58

use setParameterList method.

String queryStr = "select * from table where value in :valueList";

Query query = SessionFactory.getCurrentSession().createQuery(queryStr);


query.setParameterList("valueList", new Object[]{"%foo%","%bar%","%baz%"});

Please note that I used in clause and it doesn't support wildcard characters such as %.

How to using like array of string HIBERNATE

3 Answers3

Linked