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

[FCT (JIRA)]

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

FCT commented on HHH-1123:
--------------------------

My workaround basically substitue named-parameterS of clause in by: in(1000-elements) or in(1000-elements) or... if necessary, only if collection.size()>1000.

If collection.size()>1000 I use regular expression to manipulate original query and get a new query.
Named-parameters of in clause are replaced by new ins clauses that support 1000 elements each in.

Example 1 (in):
entity.identifier in (1,2,3,...,1001) is replaced by entity.identifier in (1,2,3,...,1000) or in (1001)

Example 1 (not in):
entity.identifier not in (1,2,3,...,1001) is replaced by not(entity.identifier in (1,2,3,...,1000) or in (1001))


I documented code with implications of each statement.


private static Query translateIn(final Collection<?> values,
		final Query query, final String param) {
	// collection size. *Can do a hit in database
	final int size = values.size();
	// mod to know how much elements are inputted in last clause IN
	final int mod = (size % 1000);
	// calculate how much INs are injected
	final int numberOfIn = (size / 1000) + (mod == 0 ? 0 : 1);
	// cache of new named-parameters
	final List<String> params = new ArrayList<String>();

	/*
                      * Regular expression find pattern of in 'clause. obj.property.id in (itens)' or 'clause. obj.property.id no in (itens)'
                      */
	final String regex = "([\\w\\.]+)\\s+(not\\s+)?in\\s*\\(\\s*:" + param
			+ "\\s*\\)";

	final StringBuilder in = new StringBuilder(" $2 (");

	// manipulation string. it will can be expensive.
	for (int i = 0; i < numberOfIn; i++) {
		// Cria um novo named-parameter.
		final StringBuilder newNameParam = new StringBuilder();
		newNameParam.append(param);
		newNameParam.append(NAMED_PARAMETER);
		newNameParam.append(i);

		/*
		 * generate string of replacement by group defined to pattern: $1 is property use in clause
		 */
		in.append("$1 in (:");
		in.append(newNameParam.toString());
		in.append(")");

		// hold params
		params.add(newNameParam.toString());

		if (i + 1 < numberOfIn) {
			// If there is more in clause to append then include or.
			in.append(" or ");
		}
	}

	in.append(") ");// clouse all 'in'

	// do replace of old in by new genereted ins. *Cost beacuse has compile a regular expression always.
	final String newQueryString = query.getQueryString().replaceAll(regex,
			in.toString());

	// Create new query. *Cost to create a new query
	final Query newQuery = getSession().createQuery(newQueryString);
	// Get list. *Cost create item by item 
	final List<Object> copy = new ArrayList<Object>(values);

	for (int count = 0, from = 0, to = 1000; count < numberOfIn; count++) {
		// Currently named-parameter
		final String nameParam = params.get(count);
		// get sublist and link named-parameter with it.
		newQuery.setParameterList(nameParam, copy.subList(from, to));

		/*
		 * to next step for update 'from' with 'to'
		 */
		from = to;

		if (count + 2 < numberOfIn) {
                                                                //execute this statament except in last iteration
			to += 1000;
		}
		else if (count + 2 == numberOfIn) {
                                                               //In this statement can set 1000 elements or mod calculated value itens list
			to += (mod == 0) ? 1000 : mod;
		}

	}

	return newQuery;
}



> Cannot put more than 1000 elements in a InExpression
> ----------------------------------------------------
>
>                 Key: HHH-1123
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1123
>             Project: Hibernate Core
>          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