SQL Pagination In Java using spring-jdbc

Conventional and Most Proven of Doing Pagination is Adding Limits in SQL Query based on RowNum/Limit / RowOver() functions based on differrent databases.

Pagination is a required feature in any Reporting framework and where we need to make the solution work across variety of datasource , say from age old ingres, Microsoft access to Latest High End Oracle,Sql Server ,Db2

But there is a new way we can do it and it did work well for me without any problems in production enviroments

Here is how i did
I created a class called BatchResultSetExtractor and overriden extractData method


public class BatchResultSetExtractor implements ResultSetExtractor {
private static final transient Log logger = LogFactory
.getLog(BatchResultSetExtractor.class);

public Object extractData(ResultSet rs) throws SQLException {
List results = (this.rowsExpected > 0 ? new ArrayList(this.rowsExpected)
: new ArrayList());
int rowNum = 0;
.......................
while (rs != null && rs.next()) {
if(rowNum offset + batchSize){
continue;
}
Object returnObject = this.rowMapper.mapRow(rs, rowNum++);
// If splitting output is disabled
if (!queryContext.isSpitOutput()) {
continue;
}

if (returnObject != null) {
totalRowsProcessed = totalRowsProcessed + 1;
// Get Batch Update Statement Here and Add Errored Out sql
// to Log
results.add(returnObject);
}

if (results.size() >= batchSize) {
// Perform Operation
results.clear();
}
}// End of result set while loop
} // End of method

}// End of class

Advertisements