How does setParameterList work in sleep mode?

Question

I am having a problem with the setParameterList api parameter in sleep mode.

I am trying to pass a collection to SQLQuery and search in the "in" section. The records exist in the database and execute an unprocessed query, I can load them or if I just replace them in the same Hibernate SQL like emp.emp_name in ('Joe','John') , I can get the desired result set. I am confused by why Hibernate will not replace the collection instead of the named parameter. Here is the code:

 session.createSQLQuery("select emp_id as id from emp where emp.emp_name in (:empNames)") .addScalar("id",Hibernate.INTEGER) .setParameterList("empNames",new String[]{"Joe","John"}) .list() 

I looked at the Hibernate Documentation for setParameterList , but I can't reason about this particular behavior.

Answer 1

I suspect that the problem is precisely that you are using createSQLQuery . The only parameter here should be changed to several parameters in real SQL, but with the "raw" query you are talking to, Hibernate should not interact with SQL.

Can you use a "regular" Hibernate request?

Answer 2

Just remove the brackets around the parameter name:

 session.createSQLQuery("select emp_id as id from emp where emp.emp_name in :empNames ") .addScalar("id",Hibernate.INTEGER) .setParameterList("empNames",new String[]{"Joe","John"}) .list() 

Answer 3

I would not suggest using (N) Hibernate parameter lists. Query plans are not used in the cache when the number of elements in the parameter list is different. Thus, this means that your request is often difficult to parse and compile. Queries run slower, database loading is higher, and the plan cache is full of plans created for the same query.