java examples
Home Java Examples Resources Java Interview Questions
Brainpower SEO

How to 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 com.javacoderanch.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. 
            //
            statement.setFetchSize(100);

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

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

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    /**
     * Get a connection to database.
     * @return a connection to database.
     * @throws Exception when an exception occurs.
     */
    private static Connection getConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost/javacoderanchdb";
        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()) {
            connection.close();
        }
    }
}