CODEWITHSUNDEEP
javahibernate
Viktor M.
Viktor M.

Reputation: 4633

Hibernate Named Parameters in SQLQuery

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?

Upvotes: 3

Views: 19124

Answers (2)

Priyesh
Priyesh

Reputation: 2071

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

Upvotes: 10

Stanislav
Stanislav

Reputation: 28126

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

Upvotes: 2

Related Questions