Hi,

I am using a Hibernate Query to fetch the list of employees and then this list is used to fetch extended details.

List<Long> employeeList;

String test=(" Select * from table_name where company="ABC" and employee id IN (:employee)");

Query query=session.createquery(test);
query.setParameterList("employee",employeeList);

The problem i had with this was oracle does not accept more than 1000 values in IN clause and had a run time exception.

Now to overcome this i am splitting the employeeList and trying to pass it as smaller list but with OR inbetween each list like

String test=(" Select * from table_name where company="ABC" and( employee id IN(:e0) OR employee id IN(:e1) OR employee id IN(:e2))");

Note: the list might have any number of smaller list as employeelist is dynamic so "e0".."e1" might be dynamic.

Query query=session.createquery(test);

now i am passing values using for loop

int counter=0;

for(int i=0;i<employee.size();i++)
{
String param="e"+counter;
query.setParameterList(param,employee.get(i);
counter++;
}

here employee is list which is having smaller list.

ArrayList<ArrayList<Long>> employee=new ArrayList<ArrayList<Long>>();

the real problem is if i pass values less than 50 in each smaller list hibernate parses it and return the list. If each list has more than 50 elements hibernate throws for following exceptions.

java.lang.StringIndexOutOfBoundsException: String index out of range: 1417
at java.lang.String.charAt(String.java:686)
at org.hibernate.util.StringHelper.replace(StringHelper.java:119)
at org.hibernate.util.StringHelper.replace(StringHelper.java:123)
at org.hibernate.impl.AbstractQueryImpl.expandParameterList(AbstractQueryImpl.java:773)
at org.hibernate.impl.AbstractQueryImpl.expandParameterLists(AbstractQueryImpl.java:742)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)

Also i cannot more than 150 OR in query. then again hibernate throws the same exception.

Please help me out on this and why hibernate throws this exception.

Thanks,
Shivraj