How do I set the fetch size of a statement?

By Wayan in JDBC 0 Comment

Fetch size is the number of rows that should be fetched from the database on a single database network trip, when more rows are needed another request is sent by the application to the database server.

Setting the correct fetch size will help our program to perform better by reducing the number of network communication generated between the program and the database server.

package org.kodejava.jdbc;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SetFetchSizeExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            Statement statement = connection.createStatement();

            // Set the fetch size to 100.
            statement.setFetchSize(100);

            // Execute the given sql query
            String q = "select id, code, name, price from products";
            ResultSet rs = statement.executeQuery(q);

            while (rs.next()) {
                System.out.println("id:" + rs.getLong("id") +
                        ", code:" + rs.getString("code") +
                        ", name:" + rs.getString("name") +
                        ", price:" + rs.getString("price"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Example result of the code snippet above is:

id:1, code:P0000001, name:UML Distilled 3rd Edition, price:25.00
id:3, code:P0000003, name:PHP Programming, price:20.00
id:4, code:P0000004, name:Longman Active Study Dictionary, price:40.00
id:5, code:P0000005, name:Ruby on Rails, price:24.00
id:6, code:P0000006, name:Championship Manager, price:0.00
id:7, code:P0000007, name:Transport Tycoon Deluxe, price:0.00
id:8, code:P0000008, name:Roller Coaster Tycoon 3, price:0.00
id:9, code:P0000009, name:Pro Evolution Soccer, price:0.00
id:10, code:P0000010, name:Data Structures, Algorithms, price:50.99

Maven dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.0.32</version>
</dependency>

Maven Central

Latest posts by Wayan (see all)
Views: 5,493

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.