Java Hibernate Query returning duplicate results in batch mode

Question

I have the following method in my DAO to query the DB (oracle) using hibernate, batch loading 50 rows of data per query. batchSize is set to 50, batchNumber to 1, and the SortOrder object has isAscending set to true with the columnName equal to one of the columns in the row.

For some reason within the first 50 results rows 35-40 are skipped, and then I am getting duplicate results starting from the 2nd query's 51st row.

Commenting out the logic regarding SortOrder fixes the issue of the duplicates, but I dont understand why I am getting duplicate results.....any help will be appreciated.

public List<Cats> getCatsList(CatQueryCriteria params)
{
   List<Cat> cats= new ArrayList<Cat>();
   Session sess = (Session) em.getDelegate();
   Criteria criteria = sess.createCriteria(Cat.class, "cat");
   //Returns list of catIDs
   List<Long> catIDList = catInfo.returnCatIDs(sess, params);
   //Eliminate duplicates
   criteria.setResultTransformer(DistinctRootEntityResultTransformer.INSTANCE);

   if(catIDList.size() != 0)
   {
      //Add criteria
      criteria.add(Restrictions.in("cat.catID", catIDList));

      //Add in Sort Order
      if(params.getSortOrder() != null)
      {
          for (SortOrder order: params.getSortOrder())
          {
              if(order.isAscending())
              {
                 criteria.addOrder(Order.asc(order.getColumnName()));
              }
              else
              {
                 criteria.addOrder(Order.desc(order.getColumnName()));
              }
          }
      }

     //Batch loading

     if(params.getBatchSize() > 0)
     {
         criteria.setMaxResults(params.getBatchSize());
         if (params.getBatchNumber() > 0)
         {
             criteria.setFirstResult((params.getBatchNumber()) *params.getBatchSize());
         }
      }
     //Execute query
     cats =  criteria.list();
  }
 return cats;
}

Answer 1

It is a complex problem. Please check this issue:

Hibernate - distinct results with pagination

Also for criteria.setFirstResult(), index starts from 0. So if you set index to 1 you will see the results in second page