Forums de discussion
Custom SQL issue with IN-Clause
Debopam Mitra, modifié il y a 7 années.
Custom SQL issue with IN-Clause
Junior Member Publications: 39 Date d'inscription: 27/09/12 Publications récentes
I am trying to write custom SQL. But I am facing some strange issue while using IN-clause. Following is the SQL Statement:
Following is my *FinderImpl code:
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?
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?
Andrew Jardine, modifié il y a 7 années.
RE: Custom SQL issue with IN-Clause
Liferay Legend Publications: 2416 Date d'inscription: 22/12/10 Publications récentes
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! The next thing I would try is add this line to my portal-ext.properties --
.. 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?
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! 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, modifié il y a 7 années.
RE: Custom SQL issue with IN-Clause
Junior Member Publications: 39 Date d'inscription: 27/09/12 Publications récentes
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.
I tried what you asked. I even turned on the hibernate.show_sql=true to find any clues.
Unfortunately I see the same problem.