To set the fetch size for large queries in Java using JDBC, you use the setFetchSize(int rows) method on a Statement or PreparedStatement object.

This gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed. This is particularly useful for large result sets to avoid loading everything into memory at once or to reduce the number of network round-trips.

Using JDBC Statement

Here is how you can apply it to a standard Statement:

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

// ... existing code ...
try (Connection conn = DriverManager.getConnection(url, user, password);
     Statement stmt = conn.createStatement()) {

    // Set the fetch size to 100 rows
    stmt.setFetchSize(100);

    try (ResultSet rs = stmt.executeQuery("SELECT * FROM very_large_table")) {
        while (rs.next()) {
            // Process rows
        }
    }
} catch (Exception e) {
    e.printStackTrace();
}

Important Considerations

1. Driver Support: setFetchSize is a hint. Not all JDBC drivers honor this value in the same way.
2. MySQL Specifics: By default, the MySQL driver fetches all rows into memory. To stream results (fetch row-by-row), you must set the fetch size to Integer.MIN_VALUE and use a forward-only, read-only result set:

   stmt.setFetchSize(Integer.MIN_VALUE);
   

3. Oracle Specifics: Oracle has a default fetch size (usually 10). Increasing this to 100 or 500 can significantly improve performance for large queries.

4. Memory vs. Network:
   • Small fetch size: Saves memory but increases network round-trips (slower).
   • Large fetch size: Reduces network round-trips (faster) but consumes more client-side memory.

Using Spring Data JPA / Jakarta EE

Since your project uses Spring Data JPA, you can also set the fetch size using the @QueryHints annotation on your repository methods:

import jakarta.persistence.QueryHint;
import org.springframework.data.jpa.repository.QueryHints;
import static org.hibernate.jpa.HibernateHints.HINT_FETCH_SIZE;

@QueryHints(value = @QueryHint(name = HINT_FETCH_SIZE, value = "100"))
List<User> findAllByStatus(String status);