How to Fetch Large Result Sets Efficiently Using Fetch Size in JDBC?

JDBC, or Java Database Connectivity, is a standard API used in Java programming to access relational databases. Fetching results quickly becomes essential for application performance when working with large datasets. JDBC offers features like fetch size to maximize the efficiency of data retrieval from the database server.

In this article, we will learn how to fetch large result sets efficiently using fetch size in JDBC.

• Recognizing Fetch Size: The number of rows that are fetched from the database server at once is referred to as the fetch size. For ResultSets, JDBC automatically establishes a certain fetch size (typically 10 rows). For big result sets, though, this default might not be the best option.
• Value of Effective Fetching: The overhead of network communication between the application and the database server is decreased by effective result set fetching. Reducing the quantity of round-trips.

Step by Step Example of JDBC’s Efficient Result Set Fetching

Let’s walk through an example demonstrating how to fetch large result sets efficiently using fetch size in JDBC.

Step 1: Set Fetch Size

Adjust the fetch size before executing the query.

statement.setFetchSize(100);

Step 2: Execute Query

Retrieve the ResultSet from the executed query.

ResultSet resultSet = statement.executeQuery("SELECT * FROM large_table");

Step 3: Iterate Through ResultSet

Process each row retrieved from the ResultSet.

while (resultSet.next()) {
    // Process each row
}

Step 4: Close Resources

Close the ResultSet, Statement, and Connection.

Implementation:

Java

import java.sql.*;

public class LargeResultSetExample {

    // JDBC URL, username, and password of MySQL server
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/mydatabase";
    private static final String USERNAME = "username";
    private static final String PASSWORD = "password";

    // JDBC variables
    private static Connection connection = null;
    private static Statement statement = null;
    private static ResultSet resultSet = null;

    public static void main(String[] args) {
        try {
            // set Fetch Size
            int fetchSize = 100; // adjust fetch size according to your requirements

            // open a connection
            connection = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD);

            // set fetch size before executing the query
            statement = connection.createStatement();
            statement.setFetchSize(fetchSize);

            // execute Query
            resultSet = statement.executeQuery("SELECT * FROM employees");

            // iterate Through ResultSet
            int rowCount = 0;
            while (resultSet.next()) {
                // process each row retrieved from the ResultSet
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                double salary = resultSet.getDouble("salary");
                System.out.println("ID: " + id + ", Name: " + name + ", Salary: " + salary);
                rowCount++;
            }

            System.out.println("Total rows fetched: " + rowCount);

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

Output:

ID: 1, Name: John Doe, Salary: 50000.0
ID: 2, Name: Jane Smith, Salary: 60000.0
ID: 3, Name: Alice Johnson, Salary: 55000.0
...
Total rows fetched: 1000

• Adapt the fetch size to the performance characteristics and requirements of the application.
• Use pagination in user interfaces to display large result sets.
• Apply performance monitoring and adjust fetch size as necessary.

Advanced Methods

• Batch processing: For bulk data operations, combine fetch size and batch processing.
• Using streaming ResultSets allows us to process large datasets in a memory-efficient manner.