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

[Steve Ebersole]

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 );
}

>
> Here's another scenario.  Suppose I have "master" table, suppose
> "orders".  And another table "order_line_items",which is a one-to-many,
> and another table "order_state_change" which is also one-to-many.  And
> suppose also that there is a somewhat complex (computationally or I/O
> cost) set of conditions to filter the retrieved results.

I have to be honest.  I do not see at all how this bears on the issue 
of limiting the number of literals in an IN list.


> There is a huge Inefficiency with multiple one-to-many joins (and no
> global temp table) because you have to either:
> 1) execute the complex where clause once for each one-to-many
> 2) build in your temp segment  then fetch across your network a
> Cartesian product of the one-to-manys
> 3) use an "in (id1, id2, id3...)" clause for each one-to-many

Yes, Hibernate itself can attempt to build a query of this sort with 
the "subselect" fetching strategy.  Which is another potential problem 
area for this..

I am not unsympathetic to the desire to have Hibernate (aka, "someone 
else") handle this for you.  Its just that I have not seen a  
suggestion to handle this in a reasonably generic manner.  If I hear 
one, then maybe I will change my mind.

-- 
steve at hibernate.org
http://hibernate.org