[hibernate-issues] [Hibernate-JIRA] Created: (HHH-6904) Use LIMIT X, Y for list.subList() on immutable extra-lazy collections with @OrderBy

[Bogdan Butnaru (JIRA)]

Use LIMIT X, Y for list.subList() on immutable extra-lazy collections with @OrderBy
-----------------------------------------------------------------------------------

                 Key: HHH-6904
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6904
             Project: Hibernate Core
          Issue Type: Improvement
          Components: core
    Affects Versions: 3.6.5
            Reporter: Bogdan Butnaru
            Priority: Minor


As far as I know, an entity list marked with {{@LazyCollection(LazyCollectionOption.EXTRA)}} will still load the entire collection unless {{javax.persistence. at OrderColumn}} is used. The trouble is that @OrderColumn must be an index.

It would be nice if Hibernate took advantage of {{org.hibernate.annotations.OrderBy}} and the various paging features of databases to do the same thing. Consider the configuration below:

{code}
@Entity 
class A {
  ...
}

@Entity 
class B {
  @Immutable
  @OneToMany
  @LazyCollection(LazyCollectionOption.EXTRA)
  @JoinTable(name = "a_of_b")
  @OrderBy(clause = "timestamp DESC")
  List<A> listOfA;
}

List<A> firstA(B b, int max){
  List<A> list = b.listOfA();
  return list.subList(0, Math.min(list.size(), max));
}
{code}

In this case, Hibernate loads the entire list, even if max is much smaller. It generates something like:

{code}
SELECT a.* FROM a JOIN a_of_b j ON a.id = j.a_id
WHERE j.id = ? ORDER BY timestamp DESC
{code}

when instead it could do:

{code}
SELECT a.* FROM a JOIN a_of_b j ON a.id = j.a_id
WHERE j.id = ? ORDER BY timestamp DESC LIMIT ?, ?
{code}

with the {{LIMIT}} set to whatever was passed to {{subList}}. (Except that if it already loaded some overlapping slices of the list before it could skip those parts.)

I haven't thought a lot about how updates should be handled, but at least for the case of immutable collections it should work nicely. I haven't worked much with cursors, but AFAIK the same thing could be done for databases that support them.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira