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?

Answer 1

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

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