The value of fetch size is used to decide about the no. of rows that will be transferred between the database server and the client at a point of time.

If we set the fetch size to 10, the JDBC driver will ask server to transfer 10 rows at a time. If there are 10000 records (rows) to be transferred, the JDBC driver fetches all the rows within 1000 attempts.

If the fetch size is 1000, 10000 rows will be transferred within 100 attempts i.e.; as the fetch size increases, the no. of data transfers reduces. Generally the performance of the application improves

A developer can identify an optimum (optimal) value for the fetch size by running the tests using different values for the fetch size.

FetchSizeDemo.java

/**
* A demo class to explain fetch size
* @author Santhosh Reddy Mandadi
* @since 26-Jun-2012
* @version 1.0
*/

import java.sql.*;

public class FetchSizeDemo
{
public static void main(String[] args) throws Exception
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection = DriverManager.getConnection("jdbc:oracle:@localhost:1521:xe","scott","tiger");
String vsql;
vsql = "SELECT * FROM PRODUCTS";
Statement statement = connection.createStatement();
statement.setFetchSize(10);
ResultSet resultSet = statement.executeQuery(vsql);
while(resultSet.next())
{
System.out.println("Current row: "+resultSet.getRow());
System.out.println("Product ID: "+resultSet.getString(1));
System.out.println("Product Name: "+resultSet.getString(2));
System.out.println("Product Price: "+resultSet.getString(3));
System.out.println("**************************************");
}
}
}