[hibernate-issues] [Hibernate-JIRA] Created: (HHH-6904) Use LIMIT X, Y for list.subList() on immutable extra-lazy collections with @OrderBy
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
{{(a)LazyCollection(LazyCollectionOption.EXTRA)}} will still load the entire collection
unless {{javax.persistence.@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