Pageable data list with Hibernate

[robmorgan]

Hi all,

I'm trying to figure out the best way to implement a pageable data list with Spring and Hibernate.

I've set up a basic app which connects to a MySQL DB, selects all the records from a table and returns them to a JSP as a Collection, i.e.

getHibernateTemplate().find("from MyClass myObject");

I've hooked this up to Display Tag (http://displaytag.sourceforge.net), and it works perfectly for small data sets. However, as it reads the entire data set from the DB on every request, it clearly won't scale well.

Short from implementing the whole thing myself, which I'd rather not do as I'm sure I'd just be reinventing the wheel, the only alternative to Display Tag that I can find is ValueList (http://valuelist.sourceforge.net). ValueList looks like it'll do exactly what I want, but with a fairly steep learning curve.

I'm keen to hear how other people are tackling this everyday problem. Is there an established best practice? Am I on the right track with ValueList, shall I just get on with learning it?

Any pointers would be much appreciated.

Thanks,

Rob

[liren]

The valueList can do this? As I know,It seems that the valuelist fetch all data from database once ;-(

To do this, I have to control the ResultSet myself and move the resultset cursor when I need new data.

Paging in J2EE is too hard to do the same thing in .NET. Asp.net have DataSet to do paging and sorting . I don't know why java don't have the paging function component like asp.net's DataSet.

[robmorgan]

Yep, it would seem it is intended to facilitate server side paging. From the ValueList site:

I inherited a J2EE web solution that chose a different component to display tables. Because the other component does all the sorting and paging in the JRE, not the database where it belongs; the solution did not scale at all! I searched the web to find a replacement and was discouraged by the results. So I grabbed code for past projects and started valuelist.sourceforge.net

I can't believe the only practical solution is .NET - surely someone must be up for fighing J2EE's corner :wink:

[delnoij]

Rob,

Hibernate itself provides support for Pagination, through the setMaxResults(int maxResults) and setFirstResult(int firstResult) methods of the Query interface.

In my DAO's I implemented a solution using this API, Spring's HibernateDaoSupport and the code Gavin King posted here: http://blog.hibernate.org/cgi-bin/bl.../2004/08/14hh/.

I also worked with ValueList; it does provide some usefull build-in features, but I also found that it adds a lot off overhead in terms of configuration. I am also unsure as to whether ValueList performs well under high load.

Hope this helps. Thomas

[Lawrence Ho]

Paging in Java/J2EE is hard? Hibernate handles it just fine!
http://www.hibernate.org/hib_docs/v3...ing-pagination

[robmorgan]

Thanks all.

Ok great, Spring/Hibernate provide a simple way to split up the data set.

All I need to do now is integrate this with ValueList and I'm in business.

This page covers how to do this using the Hibernate adapter:

http://valuelist.sourceforge.net/ada...20Adapter.html

I've yet to follow it through properly, but on first glance I see the HQL is buried in the config file. I'd much rather keep this in my DAO object - does anyone know how to do this?

[delnoij]

I think you can do away with ValueList altogether when you decide to use the built-in Pagination support that Hibernate offers in combination with Spring's HibernateDaoSupport.

-- Thomas

[robmorgan]

Thanks Thomas, the pattern you describe is surely useful for enabling paging at the DAO level, but unless I'm missing something, it won't help me present the data in a typical pageable, sortable table form. To do this, I'm going to have to write all the code for generating the table, sorting/paging URLs etc, myself.

This really is an everyday problem, for which I'm sure there must be an established solution. Indeed, a taglib such as ValueList should fit the bill and integrate with your pattern perfectly - I think!?

What are your thoughts on this - would you normally code all the table generation stuff from scratch?

[delnoij]

Rob.

You're right - the presentation of the Data in a sortable table form is not covered by the Hibernate / Spring solution.

I just found this post describing how to integrate ValueList with existing DAO's:
http://forum.springframework.org/vie...ight=valuelist

This looks quite good actually, you will get the best of both and no HQL buried in config files (which was the reason I wasn't very enthousiastic about ValueList) HMMM ... I might give this approach a try myself.

Rgrds, Thomas

[croco]

Hi,

I have done such a taglib for the company I am working for, but I can't give you the sources. However, I can provide some hints. Regarding the UI relates stuff, my taglib implementation is very simillar with the display tag or value list tag implementations; the main difference is how you specify the tabular data that needs to be displayed.
My taglib needs is some sort of tabular data source, like value list needs a ValueListAdapter or display tag needs a collection. For this I have a TableModel interface that looks something like this:

public interface TabelModel {
  public int getRowCount();
  public int getColumnCount();
  public Object getCellValue(int row, int column);
}

There is also a SortableTableModel for tabular data that needs sorting by its columns:

public interface SortableTableModel extends TabelModel {
  public void sort(int column, boolean ascending);
}

When displaying page x of data, the taglib will call TableModel.getCellValue() for each row bewteen x* pageSize and (x+1) * pageSize. When sorting by one of the columns, the SortableTableModel.sort() method will be called.

I have some support classes that take care of the pagination like this:
When first page is required, obtain the list of all the ids of the rows in the table:

select id from my_table where ... order by col1 asc

Keeping in memory lists having up to tens of thousands ids (Integer or Long objects) is not a big deal, and it should not cause any memory related problems. Having the list of all ids, you can also display the total number of rows.
Besides the list of all ids, it also keeps in memory a local cache for the current page of data being displayed. So, when the getCellValue() is called for the cell of the page, the local cahe is loaded by executing a query like:

 select * from my_table where id in (id1, id2,id3 ....)

Then the rest of the cell values are retrieved from the cache. When dispalying another page, only the page data query is executed:

 select * from my_table where id in ( ....)

The page data query is supposed to be faster, because you explicitely specify the ids of the rows you need. The cached list of all ids is refershed only when sorting by a column or explicitelly requesting a refresh. This suits very well for large datasets and for comoplex queries; you pay the cost of the query only at the initialization or sorting requests, when the ids query is excuted. In my opinion this works better that the solution mentioned on hibernate docs (http://blog.hibernate.org/cgi-bin/bl...hh/%5B/url%5D.) where you have to pay the cost of the query each time you browse back and forth through the pages of data.

This is just a specific implementation of the TableModel, targeted at browsing through large tabular data sets. Of course you can provide your own TableModel implementation that is more suitable in other cases.