Java SQL Server: Pagination

Summary: in this tutorial, you will learn how to break up a large data set into smaller pages in Java by executing a SELECT statement with FETCH and OFFSET clauses.

This tutorial begins where the Selecting Data from a Table in Java tutorial left off.

Pagination data

The pagination technique allows you to break a large result set into smaller ones. In SQL Server, you can use the FETCH and OFFSET clauses or ROW_NUMBER() function to perform pagination.

We’ll show you how to paginate data retrieved from the Authors table using a SELECT statement with the FETCH and OFFSET clauses.

Step 1. Modify the AuthorDB class and define a find() method:

public List<Author> find(int limit, int offset) throws DBException {

  var sql = "SELECT AuthorID, FirstName, LastName, BirthDate " +
                  "FROM Authors   " +
                  "ORDER BY AuthorID " +
                  "OFFSET ? ROWS FETCH FIRST ? ROWS ONLY";

  var authors = new ArrayList<Author>();

  try (var statement = connection.prepareStatement(sql)) {
    // Set values to parameters
    statement.setInt(1, offset);
    statement.setInt(2, limit);

    try (var resultset = statement.executeQuery()) {
      while (resultset.next()) {
        authors.add(createAuthorFrom(resultset));
      }
    }

    return authors;

  } catch(SQLException e) {
    throw new DBException(e.getMessage());
  }
}Code language: Java (java)

How it works.

First, define the find() method that accepts two parameters limit and offset. The limit specifies the maximum number of Author objects to return and the offset determines the number of rows to skip before starting to retrieve the Author objects:

public List<Author> find(int limit, int offset) throws DBException {Code language: Java (java)

The find() method returns a list of Author objects and throws a DBException if any error occurs while selecting data.

Second, construct a SELECT statement that retrieves data from the Authors table with two parameters (offset and limit):

var sql = "SELECT AuthorID, FirstName, LastName, BirthDate " +
                  "FROM Authors   " +
                  "ORDER BY AuthorID " +
                  "OFFSET ? ROWS FETCH FIRST ? ROWS ONLY";Code language: Java (java)

Third, initialize an empty list of Author objects:

var authors = new ArrayList<Author>();Code language: Java (java)

Fourth, create a PreparedStatement object with the SELECT statement by calling the prepareStatement method of the Connection object:

try (var statement = connection.prepareStatement(sql)) {Code language: Java (java)

Fifth, set the values to the statement:

statement.setInt(1, offset);
statement.setInt(2, limit);Code language: Java (java)

Sixth, execute the query, create an Author object from each row of the result set, and add it to the authors list:

try (var resultset = statement.executeQuery()) {
  while (resultset.next()) {
    authors.add(createAuthorFrom(resultset));
  }
}Code language: Java (java)

Seventh, return the authors list:

return authors;Code language: Java (java)

Finally, throw a new DBException if any error occurs while selecting data:

} catch(SQLException e) {
  throw new DBException(e.getMessage());
}Code language: Java (java)

Step 2. Modify the main() method of the Main class to use the find() method to get the first five authors from the Authors table:

import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        try (var connection = SQLServerConnection.connect()) {
            var authorDB = new AuthorDB(connection);
            // Find the first 5 authors
            var authors = authorDB.find(5, 0);
            for(var author: authors) {
                System.out.println(author.getAuthorId() + " - " + author.getFirstName() + ", " + author.getLastName());
            }
        } catch (SQLException | DBException e) {
            System.err.println(e.getMessage());
        }
    }
}Code language: Java (java)

Step 3. Run the Java program. It’ll show the following authors in the output:

1 - John, Doe
2 - Lisa, Rodriguez
3 - Susan, Williams
4 - Anthony, Martinez
5 - Linda, JohnsonCode language: Java (java)

If you want to get the next five authors, you can change the offset argument to 5 as follows:

var authors = authorDB.find(5, 5);Code language: Java (java)

Rerun the Java program, you’ll get the following output:

6 - Daniel, Hall
7 - Thomas, Williams
8 - Richard, Martin
9 - Barbara, King
10 - Jessica, SmithCode language: Java (java)

Download the project source code

Download the project source code

Summary

• Use a SELECT statement with the OFFSET and FETCH clauses to implement the pagination feature.