How do I set the fetch size of a statement?

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 well regarding to the number of network communication generated between the program and the database server.

package org.kodejava.example.sql;

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

public class SetFetchSizeExample {

    public static void main(String[] args) {
        Connection connection = null;
        try {
            connection = getConnection();
            Statement statement = connection.createStatement();

            // Set the fetch size to 100. 

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

            while ( {
                System.out.println("code:" + rs.getString("code") +
                        ", name:" + rs.getString("name") +
                        ", price:" + rs.getString("price") +
                        ", qty:" + rs.getString("qty"));

        } catch (Exception e) {
        } finally {
            try {
            } catch (SQLException e) {


     * Get a connection to database.
     * @return a connection to database.
     * @throws Exception when an exception occurs.
    private static Connection getConnection() throws Exception {
        String url = "jdbc:mysql://localhost/kodejavadb";
        return DriverManager.getConnection(url, "root", "");

     * Close a connection to database.
     * @param connection a connection to be closed.
     * @throws SQLException when an exception occurs.
    private static void closeConnection(Connection connection) 
            throws SQLException {
        if (connection != null && !connection.isClosed()) {

Wayan Saryada

Founder at Kode Java Org
I am a programmer, a runner, a recreational diver, currently live in the island of Bali, Indonesia. Mostly programming in Java, Spring Framework, Hibernate / JPA. You can support my works by donating here. Thank you 🙂.

Leave a Reply

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