[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1123) Cannot put more than 1000 elements in a InExpression

[Shyamsunder (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1123?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_29378 ] 

Shyamsunder commented on HHH-1123:
----------------------------------

I have faced the same issue in my project.  After failed trails to find a suitable solution, I have written the method addRestrictions(). This method divides the List of values into sublists with size<=1000. It creates LogicalExpression using Restrictions.in() for each sublist. These LogicalExpressions are joined with OR condition using Restictions.or(); This methods returns Criterion object.

private Criterion addRestrictions(String propertyName, List list) {	
		int size = list.size();		
		int fromIndex = 0;
		int toIndex = (size>1000)?1000:size;
		List subList = list.subList(fromIndex, toIndex);	
		Criterion lhs = Restrictions.in(propertyName, subList);
		toIndex = subList.size();
		size = size-toIndex;
		while(size>0) {
			fromIndex=toIndex;
			toIndex += (size>1000)?1000:size;
			subList = list.subList(fromIndex, toIndex);
			Criterion rhs = Restrictions.in(propertyName, subList);
			lhs = Restrictions.or(lhs, rhs);
			size = size-subList.size();
		}//while
		return lhs;
	}

How to Use it: I have a class Employee with many to one reference to Person. Now I need to search for employees where person is IN list of person objects. So here is my HQL using the above method:

List personsList; //Here personsList has more than 1000 entries.
Criteria criteria = session.createCriteria(Employee.class).add(addRestrictions("person", personsList);
List result = criteria.list();

P.S. I have executed this program with input size of 1050, 2100, 3100 and 3800 persons in personsList. I have recieved the following error message for all queries when personList.size>2000. So you have to increase shared memory on Oracle side.

# of _persons: 2007
Jan 18, 2008 1:12:42 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 4031, SQLState: 61000
Jan 18, 2008 1:12:42 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: ORA-04031: unable to allocate 96 bytes of shared memory ("shared pool","select this_.id as id4_0_, t...","sql area","optdef : apanlg")


> Cannot put more than 1000 elements in a InExpression
> ----------------------------------------------------
>
>                 Key: HHH-1123
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1123
>             Project: Hibernate3
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.1 rc2, 3.2.0.alpha1
>         Environment: Oracle 9i
>            Reporter: Alexis Seigneurin
>         Attachments: patch.txt
>
>   Original Estimate: 1 hour
>  Remaining Estimate: 1 hour
>
> The number of elements that we can put in a "in" expression is limited to a certain amount (1000 for Oracle, for instance). When creating a criteria query, the org.hibernate.criterion.InExpression class should split the expression into several smaller ones.
> Attached is a patch which splits the expression by slices of 500 elements. For example, if we have 1001 elements to put in the "in" expression, the result would be :
> (entity.field in (?, ?, ?...) or entity.field in (?, ?, ?...) or entity.field in (?))
> The surrounding parantheses are useful to avoid problems with other conditions (a "and" condition taking over the one of the "or" conditions).

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira