Hibernation setting limits on collection items conflicts with fetch mode
I am using Hibernate 4.3.8 as ORM tool for our MySql database. I have a class for display that is annotated like this:
@Entity
@DynamicUpdate
@Table(name = "myclass")
public class MyClass {
@Id
@Column(name = "myClassId")
private String id;
@Column(name = "status")
private String status;
@ElementCollection(fetch = FetchType.EAGER)
@CollectionTable(name = "myclass_children", joinColumns = @JoinColumn(name = "myClassId"))
@Column(name = "child")
@Fetch(FetchMode.JOIN)
@BatchSize(size = 100)
@Cascade(value = CascadeType.ALL)
private Set<String> children;
}
To execute read requests through Hibernate, I am asked to use the criteria API. I should mention at the beginning that using HQL or SQL is not a parameter.
Using the following code does exactly what I want to do: Executes a second select query to retrieve the items from the collection and returns exactly 20 MyClass objects.
public List<MyClass> listEntities() {
Session session = sessionFactory.openSession();
try {
Criteria criteria = session.createCriteria(MyClass.class);
criteria.setFetchMode("children", FetchMode.SELECT);
criteria.add(Restrictions.eq("status", "open"));
criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
criteria.setMaxResults(20);
}
}
Here are the generated queries:
select
this.myClassId as myClassId_1_0_0,
this.status as status_2_0_0
from
myclass this
where
status = ?
limit ?
select
children0.myClassId as myClassId1_0_0,
children0.child as child2_0_0
from
myclass_children as children0_
where
children0_.myClassId in (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
However, when I try to set a constraint on the items in the collection, hibernate makes one connection request. When the number of rows (not individual root objects) in the result set of this single query reaches the limit, Hibernate returns the existing MyClass objects as the result. If each MyClass object has 2 children, 10 MyClass objects are returned.
public List<MyClass> listEntities() {
Session session = sessionFactory.openSession();
try {
Criteria criteria = session.createCriteria(MyClass.class);
criteria.setFetchMode("children", FetchMode.SELECT);
criteria.createCriteria("children", "ch", JoinType.LEFT_OUTER_JOIN);
criteria.add(Restrictions.eq("status", "open"));
criteria.add(Restrictions.in("ch.elements", Arrays.asList("child1", "child2"));
criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
criteria.setMaxResults(20);
}
}
Here is the generated request:
select
this.id as id1_0_0,
this.status as status2_0_0,
ch.child as child1_0_2
from
myclass this
left outer join
myclass_children ch1_
on this.myClassId = ch1_.myClassId
where
this.status = ? limit ?
What can I do to get 20 MyClass objects by adding constraints on the elements of the collection? Any suggestions and answers are greatly appreciated!
NOTE. The @Fetch (FetchMode.JOIN) annotation is used for another codebase (for example, fetch by ID, etc.). It shouldn't influence my question as I am setting FetchMode.SELECT for criteria object separately.
source to share
No one has answered this question yet
Check out similar questions: