It's not a secret anymore!*
The moose likes JDBC and the fly likes Retreiving 20 rows per page Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Retreiving 20 rows per page" Watch "Retreiving 20 rows per page" New topic
Author

Retreiving 20 rows per page

Anil Sequeira
Greenhorn

Joined: Jun 18, 2001
Posts: 12
hello,
We have say 100 records, and we want to show 20 records at a time on our screens. This total of 100 records can grow with time, so we need to think dynamically here. I want to have "Next" and "Previous" links and also links to specific pages, like "1," "2," or whatever.I am using oracle as my database.
Regards
Anil

------------------
Anil Sequeira


Anil Sequeira
Marilyn Monickam
Ranch Hand

Joined: Jun 05, 2001
Posts: 66
Hi
The Statement class has a method setFetchSize(int rows) that specifies the number of rows to fetch from the database.It is added in JDBC API 2.0

Marilyn
P Subramanian
Greenhorn

Joined: Jul 18, 2001
Posts: 10
Hi,
The setFetchSize(int rows) can fetch you only limited amout of rows at any time. Also you cannot view the next 20 records and so on. If i am wrong correct me.
So what you can apply is:
1. Select the latest records from the Database and put in the Vector.
2. Form a logic by which you can pick 1-20 or 21-40 or..... and try to find how many pages can be formed based on No. Of records in the database and the No of rows . per page
3. Select that particular slot from the Vector to display in the web page
Subramanian
A Agrawal
Ranch Hand

Joined: Jul 13, 2001
Posts: 41
a different approch if you are using Oracle8i. You can use XML objcets returned, something you can simply send to client..there using XSL you can display records in the manner you want (first 10, next 10...onward) without making mutilple hits to database.

------------------
Amit Agrawal,
New Delhi, India.


Amit Agrawal,<BR>New Delhi, India.
mveitas
Greenhorn

Joined: Jul 18, 2001
Posts: 2
Another approach, although more compilcated is to store the ResultSet and therefore you will have access to the cursor behind the scenes. We have implemented this successfully, storing the connection, statement, and result set in the user session. When the user closes the browser we use javascript to catch this and send a POST request to our servlet to clean up the db connection info associated with this "query".
On a side note, you might want to play with the fetch size to get optimal throughput on your system.
Matt
Premkumar N
Greenhorn

Joined: Apr 21, 2001
Posts: 22
Holding the data in a Vector or holding a ResultSet has Memory and Process overhead also confusion-overhead!! :-)
One suggestion. Have a session or Hidden-form-variable that will store the range-or-id of the last value ...
Use it in the WHERE clause while querying the next time
(e.g) say the last value currently in the hidden-variable is "40"
then the next time when u query it will be ..
SELECT .. FROM .. WHERE X >40 AND X <= 60
----
Premkumar N (Bioinformatics Programmer)
Monsanto, India.


<a href="mailto:pondyprem@yahoo.com" rel="nofollow">pondyprem@yahoo.com</a> <br />Sr.Programmer Analyst ( Bioinformatics)<br />Monsanto Entrprises
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

One suggestion. Have a session or Hidden-form-variable that will store the range-or-id of the last value ...
Use it in the WHERE clause while querying the next time
(e.g) say the last value currently in the hidden-variable is "40"
then the next time when u query it will be ..
SELECT .. FROM .. WHERE X >40 AND X <= 60<br /> ---- <br /> Premkumar N (Bioinformatics Programmer)<br /> Monsanto, India.
<br /> just to add another option to the above: set your statement object <br /> to 20 then <br />
because you may not know the upper bound (may not be contiguous)
Jamie

Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

The code tags in my last reply didn't do my post any good!
So here it is again(hopefull a little more readable!)
just to add another option to the above: set your statement object setMaxRows(20) then
SELECT .. FROM .. WHERE X >40
because you may not know the upper bound (may not be contiguous)
Jamie

Anil Sequeira
Greenhorn

Joined: Jun 18, 2001
Posts: 12
HI,
Thanks everybody for your suggestions.I did Manage to do the needful.As suggested I set my upper and lower bounds in the query and used it with a rowid query.

------------------
Anil Sequeira
mveitas
Greenhorn

Joined: Jul 18, 2001
Posts: 2
Originally posted by mveitas:
Another approach, although more compilcated is to store the ResultSet and therefore you will have access to the cursor behind the scenes. We have implemented this successfully, storing the connection, statement, and result set in the user session. When the user closes the browser we use javascript to catch this and send a POST request to our servlet to clean up the db connection info associated with this "query".
On a side note, you might want to play with the fetch size to get optimal throughput on your system.
Matt

The reason why we are storing the connection info in our session is that our queries are very very expensive. We are using Oracle's Intermedia. The users are doing about, near, stem, etc queries that sometimes take up to a minute to process. I forgot to mention that the table/columns we are searching contain large amounts of text that are stored as CLOB type, 20 million rows (growing everyday). Querying the database each time would be quite an expensive operation and would tax the server. One caveat to this is that the developer needs to make sure that when the browser gets closed or the user leaves the results page, the connection info is removed from the session. The speed which we have achieved is amazing. Users are very happy to be able to scroll through the result set (20-30 at a time) without having to wait.
If anyone has any better suggestions regarding how I can do this, please let me know.
 
wood burning stoves
 
subject: Retreiving 20 rows per page
 
Similar Threads
how to control search result
Calling a web service in a Thread
Efficient way to calculate the number of threads
how can i achive page notioning in jsp
disable hyper links conditionally