Handling Pagination with JDBC

Pagination is essential when dealing with large datasets in databases. Instead of retrieving all records simultaneously, we can fetch data in smaller chunks using pagination techniques. In this article, we'll explore how to implement pagination efficiently with JDBC using a single SQL's LIMIT and OFFSET clauses also via the implementation of PageRequest and PageResponse objects.

Why Use Pagination?

Without pagination, applications may experience performance bottlenecks due to excessive memory and processing demands. By fetching only the required subset of data, pagination ensures:

• Improved Performance: Reduces load on the database and application.
• Better User Experience: Allows smooth navigation through large datasets.
• Efficient Resource Utilization: Limits memory and network consumption.

Pagination Using LIMIT and OFFSET

SQL Pagination with LIMIT and OFFSET

The LIMIT clause specifies the number of rows to return, while OFFSET determines the starting point. The basic syntax is:

SELECT column_names FROM table_name ORDER BY column_name LIMIT ? OFFSET ?;

• LIMIT ? – Defines the maximum number of rows per page.
• OFFSET ? – Skips the specified number of rows before returning results.

Implementing Pagination in JDBC

To fetch paginated results using JDBC, follow these steps:

Step 1: Establish a Database Connection

Ensure you have the JDBC driver and a connection to your database:

Step 2: Create a Method for Fetching Paginated Results

Implement a method that retrieves a specific page of results:

 @Override
  public List<User> selectAllPageable(int pageNumber, int pageSize) {
    List<User> users = new ArrayList<>();
    String sql = "SELECT u.id, u.user_name, u.first_name, u.last_name " +
                 " FROM users u  LIMIT ? OFFSET ?";
    try (PreparedStatement statement = getConnection()
                                          .prepareStatement(sql)) {
      int offset = (pageNumber - 1) * pageSize;
      statement.setInt(1, pageSize);
      statement.setInt(2, offset);
      try (ResultSet result = statement.executeQuery()) {
        while (result.next()) {
          users.add(
              new User(
                  result.getLong("id"),
                  result.getString("user_name"),
                  result.getString("first_name"),
                  result.getString("last_name")));
        }
      }
    } catch (SQLException e) {
      logger.error(e);
    }
    return users;
  }

Best Practices for Pagination in JDBC

• Use Proper Indexing: Index columns used in ORDER BY to optimize query performance.
• Avoid Large Offsets: Instead of OFFSET, consider using keyset pagination (e.g., WHERE id > last_seen_id).
• Cache Results: Reduce database hits by caching frequently requested pages.
• Use COUNT(*) for Total Pages: Fetch the total row count to determine the number of pages:

ResultSet countRs = stmt.executeQuery("SELECT COUNT(*) FROM users");
if (countRs.next()) {
    int totalRecords = countRs.getInt(1);
    int totalPages = (int) Math.ceil((double) totalRecords / pageSize);
}

Handling Pagination with JDBC Using PageRequest and PageResponse

Defining PageRequest and PageResponse

To streamline pagination, we use a PageRequest object to encapsulate request parameters like page number and size. A PageResponse object is used to structure the paginated response.

**
 * For request of slice of data from db
 *
 * @author scelac
 */
public interface PageRequest {

  /**
   * Returns the page to be returned
   *
   * @return int
   */
  int getPageNumber();

  /**
   * number of items to be returned.
   *
   * @return int
   */
  int getPageSize();

  /**
   * Returns the offset to be taken according to the underlying page and page size.
   *
   * @return int
   */
  int getOffset();
}

/**
 * this generic interface is used for page response
 *
 * @author scelac
 */
public interface PageResponse<T> {
  /**
   * Represent list of elements of this page
   *
   * @return List
   */
  List<T> getContent();

  /**
   * total element in the requested table based on how much element was selected by param LIMIT we
   * can calculate how many pages will have
   *
   * @return long
   */
  long getTotalElements();
}

After we declare two  generic interfaces for handling pagination with JDBC, we now need to  add 2 implementations for each interface:

import com.celac.jdbc.app.sql.PageRequest;

/**
 * @author scelac
 */
public class PageRequestImpl implements PageRequest {
  private final int pageNumber;
  private final int pageSize;

  public PageRequestImpl(int pageNumber, int pageSize) {
    this.pageNumber = pageNumber;
    this.pageSize = pageSize;
  }

  @Override
  public int getPageNumber() {
    return pageNumber;
  }

  @Override
  public int getPageSize() {
    return pageSize;
  }

  @Override
  public int getOffset() {
    return (pageNumber - 1) * pageSize;
  }
}

import com.celac.jdbc.app.sql.PageResponse;
import java.util.ArrayList;
import java.util.List;

/**
 * @author scelac
 */
public class PageResponseImpl<T> implements PageResponse<T> {
  private final List<T> content = new ArrayList<>();
  private long totalElements = 0;

  public PageResponseImpl(List<T> content, long totalElements) {
    if (!(totalElements < 0 && content == null)) {
      this.content.addAll(content);
      this.totalElements = totalElements;
    }
  }

  @Override
  public List<T> getContent() {
    return content;
  }

  @Override
  public long getTotalElements() {
    return totalElements;
  }
}

Implementing Pagination in JDBC

To fetch paginated results using JDBC, follow these steps:

Step 1: Establish a Database Connection

Ensure you have the JDBC driver and a connection to your database:

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class PaginationExample {
    private static final String URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String USER = "your_username";
    private static final String PASSWORD = "your_password";

    public static Connection getDataSourcesConnection()  throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
}

Step 2: Create a Method for Fetching Paginated Results

Implement a method that retrieves a specific page of results:

  @Override
  public PageResponse<User> selectAllPaginated(PageRequest pageRequest) {
    PageResponse pageResponse = null;
    long totalElements = 0 ;
    String sql = "SELECT u.id, u.user_name, u.first_name, u.last_name " +
                 " FROM users u  LIMIT ? OFFSET ?";
    try (PreparedStatement statement = getConnection().prepareStatement(sql)) {
      statement.setInt(1, pageRequest.getPageSize() );
      statement.setInt(2, pageRequest.getOffset());
      try (ResultSet result = statement.executeQuery()) {
        List<User> users  = new ResultSetProcessorImpl<>(result, new UserListRowMapper()).process();
        totalElements =  getTotalElementsFromTableUsers();
        pageResponse = new PageResponseImpl(users,totalElements);
      }

    } catch (SQLException e) {
      logger.error(e);
      pageResponse = new PageResponseImpl(Collections.emptyList(),totalElements);
    }
    return pageResponse;
  }

  private long getTotalElementsFromTableUsers() {
    long totalElements = 0;
    String sql = "SELECT count(u.id)  FROM users as u";
    try (PreparedStatement statement = getConnection().prepareStatement(sql);
         ResultSet result = statement.executeQuery()) {
      if(result.next()){
        totalElements =  result.getInt(1);
      }
    } catch (SQLException e) {
      logger.error(e);
       return totalElements;
    }
    return totalElements;
  }

Step 3: Create UserListRowMapper

What is that RowMapper and ResultSetProcessor and how to use it with JDBC, can be found here: Processing JDBC ResultSet with RowMapper 

/**
 * @author scelac
 */
public class UserListRowMapper implements ResultSetRowMapper<List<User>> {
  @Override
  public List<User> mapRow(ResultSet rs) throws SQLException {
    List<User> users = new ArrayList<>();
    while (rs.next()) {
      users.add(
              new User(
                      rs.getLong(1), 
                      rs.getString(2), 
                      rs.getString(3), 
                      rs.getString(4)));
    }
    return users;
  }
}

Conclusion

Implementing pagination in JDBC using PageRequest and PageResponse enhances application performance and user experience. By following best practices, you can ensure efficient data retrieval and scalability. Happy coding!