JSP Design question

Hi,
I have to develop a website, where in i have to show a table with data,
I want to show only 10 records per page, I got a taglib on www.jsptags.com for paging logic which works fine,
I have a bean which calls a store procedure, and this bean has page scope
so each time when i load the page, it calls the store proceudre , then this taglib get the resultset and load 10 records,
when the user clicks on next page link, again the page is loaded and all results extracted and the pages shows next 10.
so each time i change the page the store procedure is executed.
Is there a way i can run the store procedure only once and save the resultset and use it for next displays.
I think one way is to keep the resultset in session and before calling the storeprocedure, check in session , if there is resultset dont call or else call the store procedure.
Does anyone have a better way of doing it

hi Ashish
Well we had the similar requirement.
The method that called the stored procedure was called once.
We stored the resultset returned by the stored procedure in a ArrayList in the method.
our method returned the ArrayList
In JSP page
we compared the size of the ArrayList
we manipulated the showing of only 10 datas
in jsp page with the help of for loop
I hope this will be of a help.

Hi ranchers,
I think storing data in session is bit expensive in memory. What my solution is extract only those record from database which you wana show. I mean your query manipulate only those record. What i do for this is following query.
select a.ename,a.eid from
(
select ename,eid from emp where
rownum<10
) a
where
a.rownum>0

"kulkarni_ash",
Thanks for participating here at the Ranch. However, the name you are using does not comply with our naming convention described at http://www.javaranch.com/name.jsp . Please log in with a new name, which meets these requirements.
You can change your name here.
Thanks.
Sean

Hi Ali,
can u supply the exact sql statement, say i have table called rs1002 which has 1000 records in it, so how can i write an SQL to get only 10 records at a time, how do u set the rowcount, i am using AS/400 database, which is DB2/400 , what database do u use for it, can u send me some details ablut writting this type of query

I would go out and look at the various design patterns that deal with this subject. Page by Page iterator is one design pattern that is implemented in the Java Pet Store example.

Hi Ashish,
My query is for Oracle. I hav no information regarding Dbase. You better find whether you can find rowcount in it or not.
Row Count is basically assigned by Oracle Compiler at run-time to each record result in a given query.

I thing maybe you can use XML

problem is how we do this

<% // basic template.
/*
This template will get you started you will have
to figure out the << Previous and Next >> links.
This solutions can be scaled to EJB or you can alternatly look at the Page-by-Page Iterator pattern on Sun's website.
I needed a listing of articles in a page-by-page format.. First, I used a JavaBean representing the data, then I retrieved the article list from the database and put the data in an java.util.ArrayList. I used the following parameters,
:: Start reading here..
*/
int recsPerPage = 5; // max no. of records ppg.
int totalRecords = 0; // use to get from List
int rowStart = -1; // param 1
int rowEnd = -1; // param 2
int noPages = 0; // total no. of pages needed
/* get the data, the getArticles method is
impl. in the JavaBean using basic JDBC
and SQL */
list = articleBean.getArticles(String username);
// Store the list in the session
session.setAttribute("myUserArt", list);
/* get total record count and get List from session
*/
ArrayList theUserArticles =
(ArrayList)session.getAttribute("myUserArt");
totalRecords = theUserArticles.size();
/* determine how many pages you need to display
the data.
Alg: noPg = (ttlRecs + recsPerPg-1)/ recsPerPg
*/
noPages = (totalRecords+recsPerPage-1)/recsPerPage;
/* the rowStart and rowEnd values are gotten from
the request, get the start and end.. Of course
you want some range checking and other things..
This is just a basic template..
*/
try {
rowStart = Integer.parseInt(request.getParameter("start"));
rowEnd =
Integer.parseInt(request.getParameter("end"));
} catch (NumberFormatException nfe) { ; }
/*
Make sure we display only 5 records per page
*/
if (rowStart < 0 || rowEnd < 0) {
rowStart = 0;
rowEnd = 5;
}
/*
Set up a counter to keep track of the last record
displayed on the current page and display my list
*/
int counter = 0;
%>
<table border="1" cellpadding="0" width="400">
<%
for (int i = rowStart; i < rowEnd; i++) {
%>
<tr>
<td> <%= theUserArticles.get(i) %> </td>
</tr>
<%
counter ++;
} // end for
%>
</table>

<%-- THE URL WOULD BE
http://shost.com/mypage.jsp?page=1&start=0&end=5
--%>
[ August 20, 2002: Message edited by: david andre king ]

Ali's SQL won't actually work with multiple queries. With Oracle, you can't specify a 'rownum>n' where n is anything but 0. Your best bet is to implement the design pattern mentioned above.
For the same issue, we ended up setting the Fetch size on our dataset to ensure that we weren't populating the whole thing with anything more than we needed to display at one time, and kept that dataset in the session.
ResultSet rs = null;
PreparedStatement ps = null;
int statementSize = 10;
ps = con.prepareStatement(yourQueryString);
ps.setFetchSize(statementSize);
ps.executeQuery();
rs = ps.getResultSet();
Then just keep calling rs.next as many times as is necessary to display your stuff. At the end of the fetch size, the next results will automatically be fetched.

select a.ename,a.eid from
(
select ename,eid from emp where
rownum<10
) a
where
a.rownum>0
This query do works for rownum > 0 because rownum is already assigned in above query whose alias is a.
rownum>0 wont work because it is assign at runtime to each record but in my case rownum is already assigned in above query.

There is a little prb with my above query.
Right one is here
select a.ename from
(
select rownum myrow,ename from emp where
rownum<10
) a
where
a.myrow>0
And Michel it will work for mutiple query also wat u hav to do is just change inner table query having alias a.