Looking for Spring/Summer 2015 Internships . Resume

Search This Blog

Loading...

Thursday, 25 April 2013

Getting the total records while using pagination

We often have grids in our application where we need to show the results as pages. There is also often a row of text making the claim " Record  1 to 30 of 100". While the bounds of the result can be obtained easily as we know the limits to apply, it is getting the total record count that is interesting.
Consider we have a class of Entities with every Entity having an association to a Master class.
public class DataEntity {
    private Integer id;
    private Master master; // many Entities have one Master
        //other properties
    private String name;
        //setter getters...
}
Our UI includes a grid where we need to show all entities and their master id sorted by entity ids.
We can apply a criteria to get this result.
Criteria criteria = session.createCriteria(DataEntity.class);
criteria.add(Restrictions.isNotNull("name"));
        
criteria.setProjection(
        Projections.projectionList()
            .add(Projections.property("name"))
            .add(Projections.property("master.id"))
        );
criteria.addOrder(Order.asc("id"));
As it is a paged grid, we have a set page size. This means that we only need to load the records needed to display a selected page:
Criteria criteria = session.createCriteria(DataEntity.class);
criteria.add(Restrictions.isNotNull("name"));
        
criteria.setProjection(
    Projections.projectionList()
        .add(Projections.property("name"))
        .add(Projections.property("master.id"))
    );
criteria.addOrder(Order.asc("id"));
criteria.setFirstResult(2);//first record is 2
criteria.setMaxResults(3);//records from 2 to (2+3) 5
This is sufficient to render the grid. What remains is displaying the total size of the result. I found that in most cases we end up creating a separate method and projecting the row count. Or if they are using HQL, then they define a new query with all conditions same except the select portion.
This can turn into a maintenance headache. Every time the query changes ever so slightly you need to ensure that you update both the (queries or) criteria objects used . I came across a better idea for this problem provided in the book "Hibernate In Action" - Use a ScrollableResult.
public static void testSelectUsingProperties() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(DataEntity.class);
    criteria.add(Restrictions.isNotNull("name"));
        
    criteria.setProjection(
        Projections.projectionList()
            .add(Projections.property("name"))
            .add(Projections.property("master.id"))
        );
        
        
    ScrollableResults results = criteria.scroll();
    results.last();
    int total = results.getRowNumber() + 1;
    results.close();
        
    criteria.addOrder(Order.asc("id"));
    criteria.setFirstResult(2);//first record is 0
    criteria.setMaxResults(3);
    List<Object[]> rows = criteria.list();
    for (Object[] row : rows) {
        System.out.println(row[0] + " and " + row[1]);
    }
    System.out.println("Total records is  " + total);
    session.close();
}
In the code we have executed the criteria before setting the bound parameters. Also rather than execute the query using list method, we have used a ScrollableResults instance.
So Hibernate does not load the entire result set to the session cache or create any objects.
Instead we simply move our result set to point to the last record and we get its row number. As rows starts with 0, adding one to the value will give us the count of the result set.
The result is :
Hibernate: 
    /* criteria query */ 
    select
        this_.NAME as y0_,
        this_.MASTER_ID as y1_ 
    from
        DATA_ENTITY this_ 
    where
        this_.NAME is not null
Hibernate: 
    /* criteria query */ 
    select
        this_.NAME as y0_,
        this_.MASTER_ID as y1_ 
    from
        DATA_ENTITY this_ 
    where
        this_.NAME is not null 
    order by
        this_.ID asc limit ?,
        ?
entity_3 and 1
entity_4 and 2
entity_5 and 1
Total records is  10
What would have made the code cleaner is if there was some way to reset the Criteria bounds - i.e. the value of maxResults and firstResult. In which case I could have a generic method that would
  • take a criteria and ensure no page or size settings are applied
  • it would then get a ScrollableResults and find the result size
  • close the temporary ScrollableResults and return the size.
The setFirstResult and setMaxResults method however work with primitive values and not null.
A slightly more clean looking code would be to use a private method to manage our Criteria or Query object.
private static Criteria getCriteria(final Session session) {
    Criteria criteria = session.createCriteria(DataEntity.class);
    criteria.add(Restrictions.isNotNull("name"));

    criteria.setProjection(Projections.projectionList()
            .add(Projections.property("name"))
            .add(Projections.property("master.id")));
    criteria.addOrder(Order.asc("id"));
    return criteria;
}
Our entire Query is built here. Using it to display records or to get the count is a different functionality and is managed separate from this method:
public static void testSelectUsingProperties() {
    final Session session = sessionFactory.openSession();
    // get the count
    ScrollableResults results = getCriteria(session).scroll();
    results.last();
    int total = results.getRowNumber() + 1;
    results.close();

    // get the actual records to display
    final Criteria criteria = getCriteria(session);
    criteria.setFirstResult(2);// first record is 2
    criteria.setMaxResults(3);
    List<Object[]> rows = criteria.list();
    for (Object[] row : rows) {
        System.out.println(row[0] + " and " + row[1]);
    }
    System.out.println("Total records is  " + total);
    session.close();
}
If the query changes only our getCriteria() method will change.
A similar approach can also be applied for Query and SQLQuery interfaces.

2 comments:

  1. can you explain how will be the performance for say 10,000 records. if we use this pagination technique .

    ReplyDelete
    Replies
    1. The performance is heavily dependent on the query used. The above technique is more of an attempt to improve code manageability than performance.

      Delete