• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

CachedRowSet giving "SQLException" when using nextPage( ) method on it.

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

Requirement: 2 lakhs records are coming from backend.
By considering the performance issues with App server (WebSphere), i am using CachedRowSet. When I am trying to get the records in chunks from the backend, I got SQLException.

Here is the piece of code i am using :

public HashMap getCompanyByAgentWeeklyProdReportRowList() {

HashMap reportRowMap = new HashMap();
ProdScreenReportRow resultRow = null;

java.sql.Date startDate = null;
java.sql.Date endDate = null;

Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
CachedRowSet crs = null;

try {
conn = getConnection();
crs = new CachedRowSetImpl();
crs.setPageSize(100);
crs.setMaxRows(100);

endDate = getEndDate(ProdScreenReport.WEEKLY_VIEW);
startDate = getStartDate(endDate, ProdScreenReport.WEEKLY_VIEW);
stmt = conn.prepareStatement(GET_COMPANY_BY_AGENT_DATA);
stmt.setDate(1, startDate);
stmt.setDate(2, endDate);
rs = stmt.executeQuery();
crs.populate(rs);

int noOfPagesCount=0;
//while (crs.next()) {
//System.out.println("weekly prod count : "+crs.getDouble("WKLY_PROD_CNT"));
//}

System.out.println("No. of rows in crs : "+crs.size());

// THIS LINE IS GIVING EXCEPTION.
while(crs.nextPage()) {
while (crs.next()) {
System.out.println("weekly prod count : "+crs.getDouble("WKLY_PROD_CNT"));

System.out.println("Page : "+(noOfPagesCount++));
}
}

closeResultSet(rs);
closePreparedStatement(stmt);
crs.close();
}
catch (Exception e) {
logger.error("Exception encountered in getWeeklyProdReportRowList()", e);
}
finally {
try{
crs.close();
}
catch(Exception e){
logger.error("Exception encountered in getWeeklyProdReportRowList() - may be with CachedRowSet : ", e);
}
closeResultSet(rs);
closePreparedStatement(stmt);
closeConnection(conn);
}

return reportRowMap;
}

Please help me how to solve this?
Can you suggest me any other good solution to solve this problem ( Fetching 2 lakhs of records (DB2) and display them in front end, I do need to use Pagination. In this case, when user hits on page 5, how can I get records from 501 to 600 if number of recrods per page is 100? )

Thanks in advance,
Subhani.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic