title: Lazy loading datatable shows duplicate results with FetchType.EAGER
body: <p>I know there's a lot of questions about this but none of the solutions helped me.</p>
  
  <p>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 <a href="http://www.primefaces.org/showcase/ui/datatableLazy.jsf" rel="nofollow noreferrer">PrimeFaces showcase</a>, and it works fine for most of cases, when the datable uses just one table from the database (which is not my case).</p>
  
  <p>Now, I have two entities:</p>
  
  <pre><code>@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&lt;Students&gt; students = new HashSet&lt;Students&gt;(0);
  
      ...
  
      public int getStudentsQty(){
          return this.students.size();
      }
  
      ...
  </code></pre>
  
  <p>Where I'll use the <code>getStudentsQty()</code> method to fill one column from my datatable. And here's the Students entity:</p>
  
  <pre><code>@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;
  
      ...
  </code></pre>
  
  <p>Here is the search method that my load implementation will use:</p>
  
  <pre><code>public List&lt;University&gt; find(int startingAt, int maxPerPage,
          final String sortField, final SortOrder sortOrder, Map&lt;String, String&gt; filters) {
  
      session = HibernateUtil.getSession();
      Criteria criteria =session.createCriteria(University.class);    
  
      List&lt;String&gt; aliases = new ArrayList&lt;String&gt;();
  
      if(maxPerPage &gt; 0){
          criteria.setMaxResults(maxPerPage);
      }
  
      criteria.setFirstResult(startingAt);
  
      addFiltersToCriteria(filters, criteria, aliases);
  
      Order order = Order.desc("name");
  
      if(sortField != null &amp;&amp; !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&lt;University&gt;) criteria.list();
  }
  </code></pre>
  
  <p>And now, my problem. If I use <code>FetchType.LAZY</code>, everything works fine, but the performance is terrible, so I wish to use <code>EAGER</code>. If I use <code>EAGER</code> the results will come duplicated as expected and explained <a href="https://stackoverflow.com/questions/1995080/hibernate-criteria-returns-children-multiple-times-with-fetchtype-eager">here</a>. I tried to implement <code>equals()</code> and <code>hashCode()</code> methods in the <code>University</code> entity to use a <code>LinkedHashSet</code> as suggested in the last link but it didn't worked I don't know how. I also tried to use <code>DISTINCT</code> with <code>Criteria</code> but it doesn't work because of the <code>addOrder</code> that I use, where it asks for joins.</p>
  
  <p>So, I found <a href="http://floledermann.blogspot.com.br/2007/10/solving-hibernate-criterias-distinct.html" rel="nofollow noreferrer">this other suggestion</a> which worked perfectly. Basically the solution is to do a second <code>Criteria</code> query, searching only for Universities with ID included in the original search. Like this:</p>
  
  <pre><code>private List&lt;University&gt; removeDuplicates(Order order,
          List&lt;University&gt; universities) {
  
      Criteria criteria;
      List&lt;University&gt; distinct = null;
  
      if(universities.size() &gt; 0){
  
          Set&lt;Long&gt; idlist = new HashSet&lt;Long&gt;();
  
          for(University univ: universities){
              idlist.add(univ.getIdUniversity());
          }
  
          criteria = session.createCriteria(University.class);
          criteria.add(Restrictions.in("id", idlist)) ;
  
          distinct = (List&lt;University&gt;) criteria.list();
  
          return distinct;
      }
      else{
          return universities;
      }
  }   
  </code></pre>
  
  <p>So it will bring, say, the first 100 lines for my lazy loadind pagination datatable. In the first <code>Criteria</code> search they will be sorted for the first page, and the same 100 correct rows will be present after my second <code>Criteria</code> search, but now they will be <strong>unsorted</strong>. It's the correct rows for the first page, but unsorted inside the first page. I cant use "addOder" in the second <code>Criteria</code> or else they will come duplicated. </p>
  
  <p>And the strangest thing: if I try to sort the results with <code>Collections.sort</code> the results will be duplicated!!! How?? How can I order my result after all?</p>
  
  <p>Thanks!!</p>
  
  <p><strong>EDIT</strong>: the students count is just an example, I'll need in another scenarios get information inside each associated entity.</p>