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 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.
Contact Us