How do you implement pagination in PostgreSQL to display a limited number of rows per page?

In PostgreSQL, pagination can be implemented using the LIMIT and OFFSET clause in the SELECT query. The LIMIT clause allows you to specify the number of rows to be fetched and the OFFSET clause specifies the number of rows to be skipped.

Let’s say we have a table named ‘users‘ with columns ‘id‘, ‘name‘, ‘email‘, and ‘age‘, and we want to display 10 rows per page. The SQL query for the first page would look like this:

SELECT id, name, email, age FROM users LIMIT 10 OFFSET 0;

This query will fetch the first 10 rows from the ‘users‘ table starting from the first row.

For the second page, we need to skip the first 10 rows and fetch the next 10 rows. The SQL query for the second page would look like this:

SELECT id, name, email, age FROM users LIMIT 10 OFFSET 10;

This query will fetch the next 10 rows from the ‘users‘ table starting from the 11th row.

To implement pagination in Java, you can use a library like Spring Data JPA or plain JDBC. Here’s an example using plain JDBC:

import java.sql.*;

public class PaginationExample {
    private static final String QUERY = "SELECT id, name, email, age FROM users LIMIT ? OFFSET ?;";
    private static final int PAGE_SIZE = 10;

    public static void main(String[] args) throws SQLException {
        int pageNumber = 2;
        int offset = (pageNumber - 1) * PAGE_SIZE;

        try (Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost/mydatabase", "myuser", "mypassword");
             PreparedStatement stmt = conn.prepareStatement(QUERY)) {

            stmt.setInt(1, PAGE_SIZE);
            stmt.setInt(2, offset);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    int id = rs.getInt("id");
                    String name = rs.getString("name");
                    String email = rs.getString("email");
                    int age = rs.getInt("age");

                    System.out.printf("%dt%st%st%dn", id, name, email, age);
                }
            }
        }
    }
}

In this example, we calculate the offset based on the page number and multiply it by the page size. Then we set the parameters for the prepared statement using the ‘setInt‘ method, and execute the query using the ‘executeQuery‘ method. Finally, we loop through the ResultSet and retrieve the values for each column using the ‘getInt‘ and ‘getString‘ methods.