Lazy loading datatable shows duplicate results with FetchType.EAGER

Question

I know there's a lot of questions about this but none of the solutions helped me.

I'm using PrimeFaces to build a lazy loadind datatable. That means that this datatable list is a LazyDataModel list, and I had to develop a LazyDataModel implementation where I overrode the load method. All of this can be learned from PrimeFaces showcase, and it works fine for most of cases, when the datable uses just one table from the database (which is not my case).

Now, I have two entities:

@Entity
@Table(name="UNIVERSITY")
public class University {

    @Id 
    @Column(name="ID_UNIVERSITY")   
    @GeneratedValue(strategy = GenerationType.AUTO ,generator="SQ_UNIVERSITY")  
    @SequenceGenerator(name="SQ_UNIVERSITY", sequenceName="SQ_UNIVERSITY")
    private Long idUniversity;

    @Column(name="NAME")    
    private String name;

    @ManyToOne
    @JoinColumn(name="ID_DIRECTOR")
    private Director director;

    @OneToMany(fetch=FetchType.EAGER, mappedBy = "university")
    private Set<Students> students = new HashSet<Students>(0);

    ...

    public int getStudentsQty(){
        return this.students.size();
    }

    ...

Where I'll use the getStudentsQty() method to fill one column from my datatable. And here's the Students entity:

@Entity
@Table(name="STUDENTS")
public class Students 
{

    @Id 
    @Column(name="ID_STUDENTS") 
    @GeneratedValue(strategy = GenerationType.AUTO ,generator="SQ_STUDENTS")    
    @SequenceGenerator(name="SQ_STUDENTS", sequenceName="SQ_STUDENTS")
    private Long idStudent;

    @ManyToOne
    @JoinColumn(name="ID_UNIVERSITY")
    private University student;

    @Column(name="NAME")
    private String name;

    ...

Here is the search method that my load implementation will use:

public List<University> find(int startingAt, int maxPerPage,
        final String sortField, final SortOrder sortOrder, Map<String, String> filters) {

    session = HibernateUtil.getSession();
    Criteria criteria =session.createCriteria(University.class);    

    List<String> aliases = new ArrayList<String>();

    if(maxPerPage > 0){
        criteria.setMaxResults(maxPerPage);
    }

    criteria.setFirstResult(startingAt);

    addFiltersToCriteria(filters, criteria, aliases);

    Order order = Order.desc("name");

    if(sortField != null && !sortField.isEmpty()){

        if(sortField.contains(".")){
            String first = (sortField.split("\\."))[0];
            if(!aliases.contains(first)){
                criteria.createAlias(first, first);
                aliases.add(first);
            }
        }

        if(sortOrder.equals(SortOrder.ASCENDING)){
            order = Order.asc(sortField);
        }
        else if(sortOrder.equals(SortOrder.DESCENDING)){
            order = Order.desc(sortField);
        }
    }
    criteria.addOrder(order);

    return (List<University>) criteria.list();
}

And now, my problem. If I use FetchType.LAZY, everything works fine, but the performance is terrible, so I wish to use EAGER. If I use EAGER the results will come duplicated as expected and explained here. I tried to implement equals() and hashCode() methods in the University entity to use a LinkedHashSet as suggested in the last link but it didn't worked I don't know how. I also tried to use DISTINCT with Criteria but it doesn't work because of the addOrder that I use, where it asks for joins.

So, I found this other suggestion which worked perfectly. Basically the solution is to do a second Criteria query, searching only for Universities with ID included in the original search. Like this:

private List<University> removeDuplicates(Order order,
        List<University> universities) {

    Criteria criteria;
    List<University> distinct = null;

    if(universities.size() > 0){

        Set<Long> idlist = new HashSet<Long>();

        for(University univ: universities){
            idlist.add(univ.getIdUniversity());
        }

        criteria = session.createCriteria(University.class);
        criteria.add(Restrictions.in("id", idlist)) ;

        distinct = (List<University>) criteria.list();

        return distinct;
    }
    else{
        return universities;
    }
}   

So it will bring, say, the first 100 lines for my lazy loadind pagination datatable. In the first Criteria search they will be sorted for the first page, and the same 100 correct rows will be present after my second Criteria search, but now they will be unsorted. It's the correct rows for the first page, but unsorted inside the first page. I cant use "addOder" in the second Criteria or else they will come duplicated.

And the strangest thing: if I try to sort the results with Collections.sort the results will be duplicated!!! How?? How can I order my result after all?

Thanks!!

EDIT: the students count is just an example, I'll need in another scenarios get information inside each associated entity.

Answer 1

If I understand correctly you are outputting a table listing universities and you want to show the number of students for each university. If so, loading x000 student records into memory just to get a count is crazy (regardless of whether you do it eagerly or lazily).

Try one of the following:

One

rather than loading the associated students to get the count use Hibernates @Formula functionality and add a derived property to you University entity.

@Formula(value = "select count(*) from students where university_id = ?")
private int studentCount;

Two

Create a a database view say university_summary_data which includes this count and create an Entity mapped to this view (works just like a table) then in University:

@OneToOne
private UniversitySummaryData data;

Three

Look into Hibernate's @LazyCollection(LazyCollectionOption.EXTRA) which will allow you to call size() on the mapped collection without loading all Students.

All much simpler solutions that what you have.

Answer 2

You say that you want to switch to EAGER loading because the performance with Lazy loading is terrible, but with EAGER loading your performance will be the same. You will still get the select n + 1 problem explained for example here and here with solution.

For performance to improve you need to modify the query that Hibernate will generate. Usually I do a left outer join in Hibernate to obtain this, e.g.

sessionFactory.getCurrentSession().createCriteria(University.class)
.createAlias("students", "students_alias", JoinType.LEFT_OUTER_JOIN)
.list();

And it's best to keep the Hibernate default of lazy loading.