dcsimg
how does one do paging ( next, prev ) with JDBC ?
4 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   sam_pitroda
Posted On:   Wednesday, October 17, 2001 06:09 PM

I have a DB with about 1000 rows.( in reality I may not know total rows ).
In my GUI I want to show only 100 rows at a time. How do I implement
paging with JDBC ? Looks like there is nothing that gives me this
feature. I tried with SQL feature of row_num > 100 and row_num < 200
( in case when user clicks on Next button second time ) but looks like
row_num flag works only with " <" and NOT with ">" and " <" together.
This migh be a classis problem in the field. How do people solve it ?

Re: how does one do paging ( next, prev ) with JDBC ?

Posted By:   Prasoon_Choudhary  
Posted On:   Wednesday, January 23, 2002 02:56 AM

I think storing huge data on server side is not a good solution if the search query returns say 1000 records. What if 1000 users are searching on your site . On oracle you can get the result page by page.

Suppose there is a dept table and we want to get all depts page by page on some search criteria say for matching city. The query will be :-

select deptName from (select rownum y, deptName from dept where city='new york' order by deptName )X where X.y between 1 and 20



to get next 20 records use where X.y between 21 and 30.

On other database there might be some other tricks. For example on mySQL, I have achieved the same thing using this query :-


select deptName from dept where city='new york' order by deptName limit 1,20


"limit" keyword is part of mysql sql implementation. To get next 20 records use limit 20,20. Please check the usage of limit on mysql as I am out of touch with mySQl for a long time now.

Re: how does one do paging ( next, prev ) with JDBC ?

Posted By:   Alan_Ko  
Posted On:   Thursday, November 29, 2001 11:02 PM

(Oracle only):


Write a function that returns a Vector.
Each Vector returned represents the rows in a single page. e.g. Vector getPage(100, 200) returns your 2nd page(with page size=100). The sql inside this function looks like:


select aa from

(select aa from TAB order by aa)

where rownum<=200

minus

select aa from

(select aa from TAB where order by aa)
where rownum<=100;


The subquery seems unnecessary but it
solves the "ORDER BY" problem. You can
only have individual page sorted but
not subsequent pages sorted if you use


select aa from TAB where rownum<=200

minus

select aa from from TAB where where
rownum<=100 order by aa;


The drawback is: selecting the whole set
and sort it twice in a single sql with
a large amount of data... perhaps slow?
How slow? Need data from Oracle guys...

Re: how does one do paging ( next, prev ) with JDBC ?

Posted By:   Yang_Liu  
Posted On:   Tuesday, October 23, 2001 04:25 AM

if oracle , you can use the "minus"

select ....

minus
select ...

Re: how does one do paging ( next, prev ) with JDBC ?

Posted By:   Anonymous  
Posted On:   Thursday, October 18, 2001 01:32 AM

Paging will be handled by JAVA with out developer interfere. In your case to get optimal performance you can call setFetchSize(100) on resultset which is used for buffering the records. Logic used to implement prev and next is same like any other languages. I hope you are not asking logic to implement. Let me know in case of further questions.
About | Sitemap | Contact