I am using Hibernate 4.3.8 as ORM tool for our MySql database. I have a class to be mapped which is annotated as follows:
Code:
@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 perform read queries via Hibernate, I am asked to use Criteria API. I should mention at the beginning that using HQL or SQL are not options.
Using the following code performs exactly what I want to do: Performs a second select query to retrieve collection elements and returns exactly 20 MyClass objects.
Code:
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 queries generated:
Code:
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 put a restriction on collection elements, hibernate performs a single join query. When number of rows (not distinct root entities) in the result set of this single query reaches to the limit, Hibernate returns the existing MyClass objects as result. If each MyClass objects as 2 children, 10 MyClass objects are returned.
Code:
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 query:
Code:
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 obtain 20 MyClass objects while adding restrictions on collection elements? Any suggestions & answers are welcome!
NOTE: @Fetch(FetchMode.JOIN) annotation is used for other code base (like selecting by id, etc.). It should does not have any effect on my question since I am setting FetchMode.SELECT for criteria object separately.
Login
FAQ
Search