Hibernate Named Parameters in SQLQuery

Question

Hibernate named parameters could not be inserted into SQL query:

String values="'a', 'b', 'c', 'd'";
SQLQuery query = getSession().createSQLQuery("SELECT * FROM data WHERE value IN (:values)");
query.setParameter("values", values);
List<Object[]> data = query.list(); // returns data: size = 0 ...

However, if I will write:

String values="'a', 'b', 'c', 'd'";
SQLQuery query = getSession().createSQLQuery("SELECT * FROM data WHERE value IN (" + values + ")");
List<Object[]> data = query.list(); // returns data: size = 15 ...

Second way returns me data instead of first way.

What I do wrong using named parameters in SQL?

Priyesh · Accepted Answer · 2015-10-02 15:18:23Z

10

The values variable should be a list or an array of Strings, not a string, as the parameter is in an 'IN' clause. So, change your code to the below and it should work:

String[] values= {"a", "b", "c", "d"};
SQLQuery query = getSession().createSQLQuery("SELECT * FROM data WHERE value IN (:values)");
query.setParameterList("values", values);
List<Object[]> data = query.list();

edited Oct 2, 2015 at 15:18

answered Oct 1, 2015 at 14:01

Priyesh

2,0711 gold badge18 silver badges26 bronze badges

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

3 Comments

Viktor M. Over a year ago

To be sure, I understand correectly that my concatenated string didn't work only because of IN clause, yep? If it will be something = "my_string" It will work, I quess?

Stanislav Over a year ago

@user1376885 Yes, it's a Hibernate's behaviour for IN clause. If it was a JPA Query, it should work with setParameter too.

Priyesh Over a year ago

If your query is something like select * from some_table where some_column = :value, then setParameter("value","my_string") will work. But, because the IN clause expects a list of values, it does not work when a string is passed.

Stanislav · Accepted Answer · 2015-10-01 14:11:24Z

2

Your second approach doesn't create a named parameter in the query, it just concatenates a string and you get a permanent query like it was made so:

SQLQuery query = getSession().createSQLQuery("SELECT * FROM data WHERE value IN ('a', 'b', 'c', 'd')");

To make the first one work with Hibernate, you have to pass an array or a collection of Objects, not a single string and use a setParameterList method. Just like:

 query.setParameterList("reportID", new Object[]{"a","b","c","d"});

edited Oct 1, 2015 at 14:11

answered Oct 1, 2015 at 14:03

Stanislav

28.3k9 gold badges91 silver badges84 bronze badges

Collectives™ on Stack Overflow

Hibernate Named Parameters in SQLQuery

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related