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

[Subhani Sk]

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.