[hibernate-dev] HHH-1123 - Cannot put more than 1000 elements in a InExpression

[David Mansfield]

On 12/05/2011 04:44 PM, Steve Ebersole wrote:
> On Mon 05 Dec 2011 12:59:19 PM CST, David Mansfield wrote:
>> While I do agree wholeheartedly with most of what you say, I do think
>> it's unfair to say that the requirement is "fundamentally broken".  That
>> label should be reserved for SQL itself ;-)
>>
>> If there is some opaque business rule R that operates on a large set of
>> input data, and that rule is implemented in Java, using Hibernate to get
>> the input data, it's quite possible that the rule generates a large list
>> of entities to retrieve.  Is there a better way to batch fetch a
>> s***load of specific entities, given a List or Set of identifiers?
>
> for ( Long identifier : setOrListOfIdentifiers ) {
>    session.get( YourEntity.class, identifier );
> }
>
I guess if you use session.load instead of session.get you won't get 
10,000 queries over the network if batch fetching is enabled, which does 
exactly what the original issue discusses, it splits the "in" clause 
into chunks and executes it in an intelligent manner.  It's a shame the 
same can't be worked into a solution when a criteria or hql query is 
used - to me this is more a breakage in SQL than in hibernate.

If you use session.get your performance will be absolutely horrendous 
compared to an "in" or global temp table (I state this mainly for the 
record, not because it's some new information to you ;-)

Of course you are completely right that a global temp is unworkable in 
reality though.


Thanks,
David