Step by Step Example of JDBC’s Efficient Result Set Fetching
Let’s walk through an example demonstrating how to fetch large result sets efficiently using fetch size in JDBC.
Step 1: Set Fetch Size
Adjust the fetch size before executing the query.
statement.setFetchSize(100);
Step 2: Execute Query
Retrieve the ResultSet from the executed query.
ResultSet resultSet = statement.executeQuery("SELECT * FROM large_table");
Step 3: Iterate Through ResultSet
Process each row retrieved from the ResultSet.
while (resultSet.next()) {
// Process each row
}
Step 4: Close Resources
Close the ResultSet, Statement, and Connection.
Implementation:
Java
import java.sql.*; public class LargeResultSetExample { // JDBC URL, username, and password of MySQL server private static final String JDBC_URL = "jdbc:mysql://localhost:3306/mydatabase" ; private static final String USERNAME = "username" ; private static final String PASSWORD = "password" ; // JDBC variables private static Connection connection = null ; private static Statement statement = null ; private static ResultSet resultSet = null ; public static void main(String[] args) { try { // set Fetch Size int fetchSize = 100 ; // adjust fetch size according to your requirements // open a connection connection = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD); // set fetch size before executing the query statement = connection.createStatement(); statement.setFetchSize(fetchSize); // execute Query resultSet = statement.executeQuery( "SELECT * FROM employees" ); // iterate Through ResultSet int rowCount = 0 ; while (resultSet.next()) { // process each row retrieved from the ResultSet int id = resultSet.getInt( "id" ); String name = resultSet.getString( "name" ); double salary = resultSet.getDouble( "salary" ); System.out.println( "ID: " + id + ", Name: " + name + ", Salary: " + salary); rowCount++; } System.out.println( "Total rows fetched: " + rowCount); } catch (SQLException e) { e.printStackTrace(); } finally { // close Resources try { if (resultSet != null ) resultSet.close(); if (statement != null ) statement.close(); if (connection != null ) connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } } |
Output:
ID: 1, Name: John Doe, Salary: 50000.0
ID: 2, Name: Jane Smith, Salary: 60000.0
ID: 3, Name: Alice Johnson, Salary: 55000.0
...
Total rows fetched: 1000
- Adapt the fetch size to the performance characteristics and requirements of the application.
- Use pagination in user interfaces to display large result sets.
- Apply performance monitoring and adjust fetch size as necessary.
Advanced Methods
- Batch processing: For bulk data operations, combine fetch size and batch processing.
- Using streaming ResultSets allows us to process large datasets in a memory-efficient manner.
How to Fetch Large Result Sets Efficiently Using Fetch Size in JDBC?
JDBC, or Java Database Connectivity, is a standard API used in Java programming to access relational databases. Fetching results quickly becomes essential for application performance when working with large datasets. JDBC offers features like fetch size to maximize the efficiency of data retrieval from the database server.
In this article, we will learn how to fetch large result sets efficiently using fetch size in JDBC.
- Recognizing Fetch Size: The number of rows that are fetched from the database server at once is referred to as the fetch size. For ResultSets, JDBC automatically establishes a certain fetch size (typically 10 rows). For big result sets, though, this default might not be the best option.
- Value of Effective Fetching: The overhead of network communication between the application and the database server is decreased by effective result set fetching. Reducing the quantity of round-trips.