Displaying a large ResultSet, in multiple HTML pages

[Benjamin Weaver]

What is the best way to display the results of a large query in successsive html pages, each page containing 30 rows? The data would include slightly expensive data (jpeg thumbnails). Is it best to return return the entire resultSet and display successive lots of 30 entries from that set? Or is it best to send a new sql query to retrieve each successive lot of 30?

[Chris Hall]

Check out this recent thread:
https://coderanch.com/t/284560/JSP/java/First-Previous-Next-Last-Page
My opinion is to make only one call to the database since that is usually the most expensive operation.
[ March 10, 2004: Message edited by: Chris Hall ]

[Srikanth Shenoy]

Chris,
Of course - I fully agree database is the most expensive
I kind of disagree with you as far as the solution goes.
The solution of one query fetchall data as long as the size of the resultset is manageable in the memory. Well, actually I have posted my take on the solution in the same thread you pointed out.
https://coderanch.com/t/284560/JSP/java/First-Previous-Next-Last-Page
Let me know if you see any wrinkles in it and I would like to improvise on it.
Thanks.

[Chris Hall]

Srikanth,
Wrinkles in your solution? If it works...it works!
That is a very clever solution for a case when the data is too much for memory. Of course I overlooked that fact that the exceptional case often comes up, so the plain vanilla solution will not work.
Just wondering, how much of this did you code, and how much was taken care of by the third party applications Pager Taglib and Display Tag? I actually solved this problems years ago, and at the time the plain vanilla solution worked. I will have to keep those packages in mind if I come across this problem again.
Chris

[Srikanth Shenoy]

I had to code only the following (which is very little):
1) Only every prev/next all I had to do was call the ValueListHandler, and that would transparently return me a collection of 10. I put it in request as an attribute and the display tag (or Pager Taglib) took care of the rest (including paging logic)
2) I had to implement the ValueListIterator to cache and do a pre fetch as and when needed. We named the sqls and stored them in XML, so even the prefetch was generic enough.
Thats all.
Cheers.

[brad balmer]

One, widely used way, do do exactly what you are asking can be found:
http://displaytag.sourceforge.net/
It is a set of tags that will display data in a table format allowing to show x number of rows at a time.

[Srikanth Shenoy]

Yep.. That is one of my suggested approaches.
Check this thread out:
https://coderanch.com/t/284560/JSP/java/First-Previous-Next-Last-Page

[Vero Couderc]

Hi,
if anybody could help me with the Displaytag as I am trying to make it work for long enough now and Iam not successful on this!!!
Customers.java:
package Mypackage;
import java.sql.*;
public class Customers {
private String company_name;
private String address;
private String address2;
private String city;


public Customers(ResultSet rs){
try{
setCompany_name(rs.getString("company_name"));
setCity(rs.getString("city"));
setAddress2(rs.getString("address2"));
setAddress(rs.getString("address"));
} catch(Exception e){

}
}

public void setCompany_name(String company_name) {
this.company_name = company_name;
}
public String getCompany_name() {
return company_name;
}

public void setAddress(String address) {
this.address = address;
}

public String getAddress() {
return address;
}

public void setAddress2(String address2) {
this.address2 = address2;
}

public String getAddress2() {
return address2;
}
public void setCity(String city) {
this.city = city;
}
public String getCity() {
return city;
}
}
test.jsp:
<%
PreparedStatement prep1 = conn.prepareStatement("select company_name, address, address2, city from customer order by company_name limit " + currentRs + "," + records);
ResultSet rs = prep1.executeQuery();
List customers_list = new ArrayList();
while(rs.next()){
Customers customers = new Customers(rs);
customers_list.add(customers);

}
conn.close();
%>
<display:table name="<%= customers_list %>">
<display:column property="company_name" title="company_name" />
<display:column property="address" title="address"/>
<display:column property="address2" title="address2"/>
<display:column property="city" title="city"/>
</display:table>
I've tried some other ways too and still get stuck!
Originally I have the page working fine with the preparedStatement and the html table displaying the results and the pages numbers etc but I would like to adapt it to better codes, can anyone help please?
Many thanks
Vero

[Brahim Bakayoko]

Whether to cache or not depends on the application.
In most cases, you should not cache.
The reasons:
- most users don't click through every page
- you will need to implement a cache expiration if the data changes
- memory issues, particularly if the result is very large
You should retrieve from the database only the data you need per page.
In Oracle, you do it this way:

 
SELECT c.P, c.L, c.N, c.Q, c.A, c.T, c.Pn, c.M, c.Ma
FROM (SELECT a.P, a.L, a.N, a.Q, a.A, a.T, b.Pn, b.M, b.Ma
FROM TABLE_NAME a, TABLE(SELECT b_.Specs FROM TABLE_NAME b_ WHERE b_.P = a.P) b
WHERE ... ORDER BY a.L asc
) c 
GROUP BY rownum, c.P, c.L, c.N, c.Q, c.A, c.T, c.Pn, c.M, c.Ma 
HAVING rownum >  start_  and rownum <  stop_;

Use count(*) to get count of the result before the query limit.
[ May 11, 2004: Message edited by: Brahim Bakayoko ]

[michael yue]

Could it be on line
List customers_list = new ArrayList();

Originally posted by Vero Couderc:
Hi,

if anybody could help me with the Displaytag as I am trying to make it work for long enough now and Iam not successful on this!!!

Customers.java:
package Mypackage;
import java.sql.*;

public class Customers {
private String company_name;
private String address;
private String address2;
private String city;


public Customers(ResultSet rs){
try{
setCompany_name(rs.getString("company_name"));
setCity(rs.getString("city"));
setAddress2(rs.getString("address2"));
setAddress(rs.getString("address"));
} catch(Exception e){

}
}

public void setCompany_name(String company_name) {
this.company_name = company_name;
}

public String getCompany_name() {
return company_name;
}

public void setAddress(String address) {
this.address = address;
}

public String getAddress() {
return address;
}

public void setAddress2(String address2) {
this.address2 = address2;
}

public String getAddress2() {
return address2;
}

public void setCity(String city) {
this.city = city;
}

public String getCity() {
return city;
}
}

test.jsp:
<%
PreparedStatement prep1 = conn.prepareStatement("select company_name, address, address2, city from customer order by company_name limit " + currentRs + "," + records);
ResultSet rs = prep1.executeQuery();
List customers_list = new ArrayList();
while(rs.next()){

Customers customers = new Customers(rs);
customers_list.add(customers);

}
conn.close();

%>

<display:table name="<%= customers_list %>">
<display:column property="company_name" title="company_name" />
<display:column property="address" title="address"/>
<display:column property="address2" title="address2"/>
<display:column property="city" title="city"/>
</display:table>

I've tried some other ways too and still get stuck!
Originally I have the page working fine with the preparedStatement and the html table displaying the results and the pages numbers etc but I would like to adapt it to better codes, can anyone help please?

Many thanks
Vero