GeeCON Prague 2014*
The moose likes JSP and the fly likes Displaying a large ResultSet, in multiple HTML pages Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Java » JSP
Bookmark "Displaying a large ResultSet, in multiple HTML pages" Watch "Displaying a large ResultSet, in multiple HTML pages" New topic
Author

Displaying a large ResultSet, in multiple HTML pages

Benjamin Weaver
Ranch Hand

Joined: Apr 08, 2003
Posts: 161
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
Ranch Hand

Joined: Dec 04, 2002
Posts: 39
Check out this recent thread:
http://www.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
author
Ranch Hand

Joined: Jan 24, 2004
Posts: 184
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.
http://www.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.


Srikanth Shenoy
Author of Struts Survival Guide : Basics to Best Practices
Chris Hall
Ranch Hand

Joined: Dec 04, 2002
Posts: 39
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
author
Ranch Hand

Joined: Jan 24, 2004
Posts: 184
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
Ranch Hand

Joined: Mar 08, 2004
Posts: 57
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
author
Ranch Hand

Joined: Jan 24, 2004
Posts: 184
Yep.. That is one of my suggested approaches.
Check this thread out:
http://www.coderanch.com/t/284560/JSP/java/First-Previous-Next-Last-Page
Vero Couderc
Greenhorn

Joined: Feb 19, 2004
Posts: 3
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
Ranch Hand

Joined: Aug 29, 2003
Posts: 155
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:

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

SCJP, SCWCD, SCBCD, IBM CSD WebSphere v5, <br />A+, MCP 2000 and 2000 server, CST, and few incompleted certification tracks.<br /> <br />Ivory Coast<br /> <br />Analyze your web Request/Response @ <a href="http://webtools.servehttp.com" target="_blank" rel="nofollow">http://webtools.servehttp.com</a> down for a while...
michael yue
Ranch Hand

Joined: Nov 20, 2003
Posts: 204
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Displaying a large ResultSet, in multiple HTML pages