Rule Definition
When you don’t set a fetch size (via Statement.setFetchSize(int) or similar APIs), the JDBC driver may default to loading the entire result set into memory at once. Consequences: Memory exhaustion / OutOfMemoryError: If the result set is large (millions of rows), the JVM heap can be consumed, causing crashes or degraded performance. Resource hogging: The database server, JDBC driver, and client-side buffer will all allocate more memory than necessary. Denial of Service risk (CWE-770): An attacker (or even a legitimate user running a wide query) can cause resource exhaustion by forcing the application to retrieve excessively large datasets. Slow response times: Fetching everything at once introduces huge latency before the first row can be processed (vs. streaming row-by-row with a smaller fetch size). Scalability issues: Applications with many concurrent queries can saturate memory and reduce throughput if each query pulls full result sets eagerly.

Remediation
Specify a fetch size (via Statement.setFetchSize(int) or similar APIs) when querying large tables (XXL tables).

Violation Code Sample

public class LargeResultSetIssue {
    public static void main(String[] args) throws Exception {
        Connection conn = DriverManager.getConnection(
            "jdbc:postgresql://localhost:5432/mydb", "user", "password");

        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM orders");  // ⚠️ VIOLATION: ❌ No fetch size set → driver may fetch ALL rows into memory

        while (rs.next()) ...

Fixed Code Sample

public class LargeResultSetFix {
    public static void main(String[] args) throws Exception {
        Connection conn = DriverManager.getConnection(
            "jdbc:postgresql://localhost:5432/mydb", "user", "password");

        Statement stmt = conn.createStatement(
            ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY
        );

        stmt.setFetchSize(500);  // ✅ FIXED : Set fetch size (tune depending on DB and workload)

        ResultSet rs = stmt.executeQuery("SELECT * FROM orders");

        while (rs.next()) ...
Fix :
The driver will fetch rows in chunks of 500 instead of all at once.
Memory footprint stays bounded, processing starts faster.
Adjust fetch size (100, 500, 1000) depending on DB performance and network latency.

CWE-770: Allocation of Resources Without Limits or Throttling https://cwe.mitre.org/data/definitions/770.html Oracle JDBC Performance Guide – Using Fetch Size https://docs.oracle.com/cd/A97335_01/apps.102/a83724/resltse5.htm#:~:text=To%20set%20the%20fetch%20size,each%20trip%20to%20the%20database. PostgreSQL JDBC Driver – Controlling fetch size https://jdbc.postgresql.org/documentation/query/#example52setting-fetch-size-to-turn-cursors-on-and-off Baeldung – JDBC Performance Optimization https://www.baeldung.com/jdbc-resultset#fetchsize