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 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 = "kodejava";
private static final String PASSWORD = "s3cr*t";
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 product";
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.1.0</version>
</dependency>
Latest posts by Wayan (see all)
- How do I create a servlet filter to make secure cookies? - April 18, 2024
- How do I configure secure cookies using web.xml? - April 18, 2024
- How do I create a table with multiple header in iText 8? - March 16, 2024