Pagination using Apache Derby

Question

Is there a good example of how to implement pagination using Apache Derby so that the entire resultset is not returned every time a new page is selected?

It would also be helpful if the query could be sorted by a user-specified column.

Answer 1

These are the derby keywords that help when implementing pagination: OFFSET and FETCH

Example usage:

//all these values are supplied by the GUI
int rowsPerPage = 10;
int pageNumber = 3;
String columnToSortBy = "first_name"; //this value should never be directly edited by the user. Otherwise it could be used for sql injection

StringBuilder statement = new StringBuilder();
statement.append("select * from users\n");
statement.append("order by " + columnToSortBy + "\n");
statement.append("offset ? rows fetch first ? rows only");

try (PreparedStatement ps = conn.prepareStatement(statement.toString())) {

   ps.setInt(1, pageNumber * rowsPerPage);
   ps.setInt(2, rowsPerPage);

   ResultSet rs = ps.executeQuery();
   printResultSet(rs);
}

A more elaborate example:

//all these values are supplied by the GUI
int rowsPerPage = 10;
int pageNumber = 3;
String columnToSortBy = "first_name"; //this value should never be directly edited by the user. Otherwise it could be used for sql injection

String selectClause = "select * from users\n";
String whereClause = "where first_name like ?";
String orderClause = "order by " + columnToSortBy + "\n";
String limitClause = "offset ? rows fetch first ? rows only";

//build the sql statement
String statement = "";
statement += selectClause;
statement += whereClause;

//get the total rows
int totalRows = 0;
try (PreparedStatement ps = conn.prepareStatement(statement)) {
   ps.setString(1, "Sam%");

   ResultSet rs = ps.executeQuery();
   while (rs.next()) {
        totalRows++;
   }
}
System.out.println(totalRows + " total rows\n");        

//change the statement to use pagination
statement += orderClause;
statement += limitClause;

//get the 3rd page
try (PreparedStatement ps = conn.prepareStatement(statement)) {

   ps.setString(1, "Sam%");
   ps.setInt(2, pageNumber * rowsPerPage);
   ps.setInt(3, rowsPerPage);

   ResultSet rs = ps.executeQuery();
   printResultSet(rs);
}

System.out.println("\nNext page\n");

//get the 4th page
try (PreparedStatement ps = conn.prepareStatement(statement)) {

   ps.setString(1, "Sam%");
   ps.setInt(2, (pageNumber + 1) * rowsPerPage);
   ps.setInt(3, rowsPerPage);

   ResultSet rs = ps.executeQuery();
   printResultSet(rs);
}