matroska wrote:
> Hi,
>
> I have a very large db table ( 1 milion record) and I would like to
> paginate results not only with paginate control but calling many
> paginated db query to reduce memory and cpu overhead. I have found
> many tutorials on paginating large result with datatable but none
> using dataproviders. Is it possible? How can I achieve this?
>
> Thanks in advance
> Tobia Loschiavo
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe_at_woodstock.dev.java.net
> For additional commands, e-mail: users-help_at_woodstock.dev.java.net
>
>
>
It can be done but gets a little tricky. First, you cannot use the
pagination controls that are available with the table. The underlying
infrastructure requires that it knows the total rows and may even
iterate through all of them, so with a million rows, you are right back
where you started with consuming tremendous amounts of memory and having
terrible performance. The infrastructure really needs redesign to not
require that it have to be able to provide a count or traverse all of
the rows so that large tables can be supported easier.
What you can do is provide your own TableDataProvider. I use a class
derived from ObjectListDataProvider and fetch a page of records at a
time from the database, build the list and render. For paging, I use
the table footer facet and add my own paging controls that look like the
ones provided by the table. When fetching the records for the page, I
fetch an extra record to determine if there is another page so that I
can properly enable/disable the paging controls (next, last, previous,
first).
If you are doing filtering and sorting you also have more issues. Of
course your fetching of the page of records needs to take into
consideration the filtering and sorting applied. Fortunately if the
TableDataProvider that you supply to the TableRowGroup is the same
instance as the TableDataSorter and TableDataFilter, then it is assumed
that your TableDataProvider will provide sorted and filtered records.
To integrate into the table nicely, you will need to implement
TableDataSorter and TableDataFilter in your TableDataProvider class.
You will need to keep track of the TableDataSortCriteria and
TableDataFilterCriteria that is applied by the user through interacting
with the table controls on the page and use this criteria when
retrieving your data for the page. One wrinkle is that the code updates
the TableDataSorterCriteria directly without calling into the
TableDataSort when the sort criteria is changed from ascending to
descending or vise-versa. Not good because your custom TableDataSorter
implementation has no idea that the criteria has been changed.
But in the end, it can be done. It is a lot of work and should be much
easier but if you really want to you can handle large tables.