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.

Amith Kumar · Accepted Answer · 2019-03-19 06:31:46Z

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, 2019 at 6:31

Amith Kumar

4,9381 gold badge25 silver badges31 bronze badges

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

2 Comments

t9217 Over a year ago

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 "?"

Amith Kumar Over a year ago

There are certain posts which might help you... stackoverflow.com/questions/1647583/… stackoverflow.com/questions/36601318/… vladmihalcea.com/…

coladict · Accepted Answer · 2019-03-27 12:06:26Z

0

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.

answered Mar 27, 2019 at 12:06

coladict

5,1151 gold badge20 silver badges31 bronze badges

Comments

javapedia.net · Accepted Answer · 2019-03-19 02:58:13Z

-1

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 %.

answered Mar 19, 2019 at 2:58

javapedia.net

2,7516 gold badges32 silver badges58 bronze badges

Collectives™ on Stack Overflow

How to using like array of string HIBERNATE

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related