Fórum

Custom SQL issue with IN-Clause

Debopam Mitra, modificado 7 Anos atrás.

Custom SQL issue with IN-Clause

Junior Member Postagens: 39 Data de Entrada: 27/09/12 Postagens Recentes
I am trying to write custom SQL. But I am facing some strange issue while using IN-clause. Following is the SQL Statement:

SELECT {emp*} FROM employees emp
INNER JOIN dept_emp dept USING(emp_no)
WHERE dept.dept_no IN (?)


Following is my *FinderImpl code:

session = openSession();
String customSQL = CustomSQLUtil.get(ContantsUtil.FIND_EMP_BY_DEPT_NO);
SQLQuery sqlQuery = session.createSQLQuery(customSQL);
sqlQuery.addEntity("emp", EmployeesImpl.class);
QueryPos queryPos = QueryPos.getInstance(sqlQuery);
queryPos.add(deptNos); //deptNos is the Long array of dept_no from the method param. 
List = sqlQuery.list();


Following are the issues:

1. Whenever I am sending 1 dept_no through the array the sql works properly. The moment I send more than one dept_no an exception is thrown:

java.lang.IllegalArgumentException: Positional parameter does not exist: 1 in query: SELECT {emp*} FROM employees emp INNER JOIN dept_emp dept USING(emp_no) WHERE dept.dept_no IN (?)

2. So I tried a different way if send deptNos as comma-seperated String directly. So my code looks like:
queryPos.add(StringUtil.merge(deptNos));
The code works fine there are no errors; but the records are only sent for the the dept_no in the array deptNos 0th index. Which means I am not getting all the data.

Do anyone have any idea?
thumbnail
Andrew Jardine, modificado 7 Anos atrás.

RE: Custom SQL issue with IN-Clause

Liferay Legend Postagens: 2416 Data de Entrada: 22/12/10 Postagens Recentes
Hi Debopam,

I'll take a stab at this one. The first thing I was going to suggest while reading your thread was to convert the array to a string of comma separated values, but you've done that! emoticon The next thing I would try is add this line to my portal-ext.properties --

 hibernate.show_sql=true


.. restarting the server (of course) and then seeing what query is generated for your CustomSQL. I'm not sure if that will help or anything, but it's worth a shot since it only takes a few minutes to try. Can you try that out and let us know what you get?
Debopam Mitra, modificado 7 Anos atrás.

RE: Custom SQL issue with IN-Clause

Junior Member Postagens: 39 Data de Entrada: 27/09/12 Postagens Recentes
Hi Andrew,

I tried what you asked. I even turned on the hibernate.show_sql=true to find any clues.
Unfortunately I see the same problem.