Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Hibernate criteria return page and rowcount

Tags:

java

hibernate

Using Hibernate Criteria i am trying to achieve pagination but the problem is that for every page fetch i have to make two db calls one for results and another for total records count. Is there any efficient way so that in a single db call i can get both the data or i can reduce db calls.

 Criteria criteria=session.createCriteria(Student.class);
 criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
 criteria.add(Restrictions.ne("enquiryStatus", ENQUIRY.JOINED));
 criteria.setMaxResults(10);
 criteria.setFirstResult((paginate.getStartIndex()-1)*10);

 criteria.setProjection(Projections.rowCount());
//here i need to fetch total row count and records
like image 476
dinesh Avatar asked Aug 28 '13 10:08

dinesh


2 Answers

Yes you need the separate query to get the total result count.

    Query aCountQuery  = session.createQuery("select count(s.id) from Student s 
           where s.enquiryStatus != :enquiryStatus");
           aCountQuery.setParameter("enquiryStatus", ENQUIRY.JOINED);
    Long resultCount = (Long)aCountQuery.uniqueResult();

or

   Criteria criteria=session.createCriteria(Student.class);
        criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
        criteria.add(Restrictions.ne("enquiryStatus", ENQUIRY.JOINED));
        criteria.setProjection(Projections.rowCount())
   Long resultCount = (Long)criteria.uniqueResult();

Update

Now you can use same Criteria for results with pagination and result count

  Criteria criteria=session.createCriteria(Student.class);
     criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
     criteria.add(Restrictions.ne("enquiryStatus", ENQUIRY.JOINED));
     criteria.setMaxResults(10);
     criteria.setFirstResult((paginate.getStartIndex()-1)*10);
     List<Student> students = criteria.list();

    criteria.setProjection(null);
    criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
    Long resultCount = (Long)criteria.uniqueResult();
like image 90
Prabhakaran Ramaswamy Avatar answered Nov 18 '22 03:11

Prabhakaran Ramaswamy


As far As I know there is no direct method to do so.As simple as you can do is write a small hql query(my choice) ,Along with your main criteria.

Number count = (Number) session.createQuery(
    "select count(s.id) from Student s").uniqueResult();
like image 39
Suresh Atta Avatar answered Nov 18 '22 03:11

Suresh Atta