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.

+3


source to share





All Articles