How does setParameterList in hibernate work?

Question

I am having some problem with the setParameterList api of hibernate.

I am trying to pass in a Collection to a SQLQuery and doing an "in" clause search.The records exists in the DB and doing a raw query, I am able to retrieve them or if I just replace them in the same Hibernate SQL like emp.emp_name in ('Joe','John'), I am able to get the desired result set. I am confused as to why would Hibernate fail to replace the Collection in place 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 have looked at the Hibernate Documentation for setParameterList but I am not able to reason out this particular behavior.

Answer 1

I suspect the problem is precisely because you're using createSQLQuery. The single parameter here needs to be changed into multiple parameters in the real SQL, but by using a "raw" query you're telling Hibernate not to mess with the SQL.

Can you use a "normal" Hibernate query instead?