Client Side sorting + Hibernate Paging?

Question

I use GWT for UI and Hibernate/Spring for buisness-layer.Following GWT widget is used to display the records.(http://collectionofdemos.appspot.com/demo/com.google.gwt.gen2.demo.scrolltable.PagingScrollTableDemo/PagingScrollTableDemo.html).I assume the sorting is done in client side.

I do not retrieve the entire result set since its huge. I use

principals = getHibernateTemplate().findByCriteria(criteria,
        fromIndex, numOfRecords);

to retrive data.Theres no criteria for sorting in Hibernate layer.

This approach does not give the correct behaviour since it only Sorts the current dataset in the client.

What is the best solution for this problem?

NOTE : I can get the primary-Sort-column and other sort Columns using the UI framework. May be I can sort the result using primary-sort-column in the hibernate layer?

Answer 1

It will be confusing to the user if you sort on a partial result in the GUI, and page on the server.

Since the data set is huge, sending the entire data set to the user and do both paging and sorting there is a no-go.

That only leaves both sorting and paging on the server. You can use Criteria.addOrder() to do sorting in hibernate. See this tutorial.

Answer 2

You need to sort on the server.

Then you can either:

• send the complete resultset to the client and handle pagination on the client side. The problem is that the resultset may be big to retrieve from db and sent to the client.

• handle the pagination on the server side. The client and the server request only one page at a time from the db. The problem then is that you will order the same data again and again to extract page 1, page 2, etc. each time you ask the db for a specific page. This can be a problem with large database.

• have a trade-off between both (for large database):

  • Set a limit, say 300 items
  • The server asks the db for the first 301 items according to the order by
  • The server keept the resultset (up to 301 items) in a cache
  • The client request the server page by page
  • The server handles the pagination using the cache
  • If there are 301 items, the client displays "The hit list contains more than 300 items. It has been truncated".

Note 1: Usually, the client doesn't care if he can't go to the last page. You can improve the solution to count for the total number of rows first (no need of order by then) so that you can display message that is better to the user, e.g. "Result contained 2023 elements, only first 300 can be viewed".

Note 2: if you request the data page by page in the database without using any order criterion, most db (at least Oracle) don't guarantee any ordering. So you may have the same item in page 1 and 2 if you make two requests to the database. The same problem happens if multiple items have the same value that is use to order by (e.g. same date). The db doesn't guarantee any ordering between element with the same value. If this is the case, I would then suggest to use the PK as the last order criterion to order by (e.g. ORDER BY date, PK) so that the paging is done in a consistent way.

Note 3: I speak about client and server, but you can adapt the idea to your particular situation.

Answer 3

1. Always have a sort column. By default it could by "name" or "id"
2. Use server side paging. I.e. pass the current page index and fetch the appropriate data subset.
3. In the fetch criteria / query use the sort column. If none is selected by the client, use the default.

Thus you will have your desired behaviour without trade-offs.