How to Execute a SQL Query with Pagination in JDBC?

To execute a SQL query with pagination in JDBC (Java Database Connectivity), normally we use SQL syntax to limit the number of rows returned and then iterate through the results as per the need. We often use SQL’s LIMIT and OFFSET clauses to execute page SQL queries in JDBC.

Steps to Execute SQL Query with Pagination in JDBC

Below are the steps to execute a SQL query with pagination in JDBC.

Step 1: Establish a Database Connection

Connection connection = DriverManager.getConnection
  ("jdbc:mysql://localhost:3306/your_database", "username", "password");

Step 2: Prepare the SQL Query

String sql = "SELECT * FROM your_table LIMIT ? OFFSET ?";

Step 3: Define Pagination Parameters

int pageSize = 10; // Number of records per page
int pageNumber = 2; // Desired page number
int offset = (pageNumber - 1) * pageSize;

Step 4: Create a PreparedStatement

PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, pageSize);
preparedStatement.setInt(2, offset);

Step 5: Execute the Query

ResultSet resultSet = preparedStatement.executeQuery();

Step 6: Process the ResultSet

while (resultSet.next()) {
    // data retrieved from the result set
    // Example:
    // int id = resultSet.getInt("id");
    // String name = resultSet.getString("name");
    // Process data as needed
}

Step 7: Close Resources

resultSet.close();
preparedStatement.close();
connection.close();

This is how we execute a SQL query with pagination using JDBC.

Java Program to Execute a SQL Query with Pagination in JDBC

Java

// Java program to Execute a SQL Query with Pagination in JDBC
import java.sql.*;
public class PaginationExample 
{
    public static void main(String args[]) 
    {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            // Establishing database connection
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/your_database", "username", "password");

            // Put here SQL query with pagination
            String sql = "SELECT * FROM your_table LIMIT ? OFFSET ?";
            
            // Define pagination parameters
              // Number of records per page
            int pageSize = 10; 
          
              // Desired page number
            int pageNumber = 2; 
            int offset = (pageNumber - 1) * pageSize;

            // Creating PreparedStatement
            preparedStatement = connection.prepareStatement(sql);
          
              // Set the limit
            preparedStatement.setInt(1, pageSize);
          
              // Set the offset
            preparedStatement.setInt(2, offset); 

            // Execute query
            resultSet = preparedStatement.executeQuery();

            // Process ResultSet
            while (resultSet.next()) {
                // data retrieved from the result set
                // Example:
                // int id = resultSet.getInt("id");
                // String name = resultSet.getString("name");
                // Process data as needed
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // Closing resources
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Explanation of the Program:

• Use DriverManager.getConnection to create a connection to the file.
• Define SQL queries using LIMIT and OFFSET clauses. Replace your_table with your table name.
• Create page parameters (pageSize and pageNumber). Calculate offset based on page number and page size.
• Create a ready object and use setInt to set the parameters and offset value.
• Use executiveQuery to execute the query.
• Use ResultSet to get the data.