Problem with paginated, ordered, restricted queries.

defaux · **Joined:** Thu Jun 09, 2011 6:04 pm **Posts:** 1

I'm having issues with how sorting seems to be handled in the following situation:
I have a table of Contacts, holding their first and last name in separate columns. I want to pull the results paginated and sorted alphabetically by name. What I did first was following:

Code:

      Criteria criteria = session.createCriteria(Contact.class);
      criteria.addOrder(Order.asc("lastName"));
      criteria.addOrder(Order.asc("firstName"));
      criteria.setFirstResult(pageSize * (pageNumber - 1));
      criteria.setMaxResults(pageSize);
      result = criteria.list();

This worked how I wanted it to: Results are sorted by last name, then by first, in the following fashion (imagine the entries 1-10 are sorted numerically):
Page 1: 1, 2, 3, 4
Page 2: 5, 6, 7, 8
Page 3: 9, 10

However, the table contains a number of entries with null last names, which I want to remove from the result set. AKA I don't want it pulling results 1 and 2. So I try:

Code:

      Criteria criteria = session.createCriteria(Contact.class);
      criteria.add(Restrictions.isNotNull("lastName"));
      criteria.addOrder(Order.asc("lastName"));
      criteria.addOrder(Order.asc("firstName"));
      criteria.setFirstResult(pageSize * (pageNumber - 1));
      criteria.setMaxResults(pageSize);
      result = criteria.list();

Now the results are sorted only across the current page of results, instead of across the table. AKA I get something like:
Page 1: 3, 7, 8, 10
Page 2: 4, 5, 6, 9

I haven't been able to figure out how to get around this, and I didn't notice anything in the docs. Anyone have any suggestions?

I'm using Hibernate 3.6.2 with MySQL 5.5.11.
Thanks

sanne.grinovero · **Posted:** Fri Jun 10, 2011 5:44 am

Hi,
I'm not sure about the specific queries being performed in your case (would need way more details), but often databases don't support this so the sorting has to be done "in memory".

Looking at what you're trying to implement it seems you would also get much better user experience (search quality and also performance) by throwing in Hibernate Search, and this would also solve all sorting issues. An alternative is you load all the data in memory, sort it in memory, and then show only the relevant part; don't do it if you have many contacts of course.