CRUD và các loại Statement

03-crud-va-statement/
  docker-compose.yml           MySQL 8.4, database shop, user app (cổng 3307)
  pom.xml                      thuộc tính main.class, main.arg, jvm.heap cho exec:exec
  src/main/java/dev/canhdominich/jdbc/crud/
    DbConfig, Db               cấu hình kết nối, mở kết nối, tạo schema
    Product.java               record ánh xạ một dòng bảng product
    CrudDemo.java              insert, findById, search, update, delete
    SqlInjectionDemo.java      đăng nhập nối chuỗi và đăng nhập PreparedStatement; xếp chồng lệnh
    PreparedModes.java         client-side, server-side, server-side có cache: 5000 truy vấn
    AffectedRows.java          giá trị executeUpdate trả về với và không có useAffectedRows
    OrderByAndIn.java          ORDER BY ?, whitelist cột sắp xếp, danh sách IN
    LargeResult.java           đọc 400.000 dòng: default, stream, cursor, và bị chặn khi đang stream
    StoredProcedureDemo.java   CallableStatement với tham số IN và OUT

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>dev.canhdominich.javabackend</groupId>
    <artifactId>jdbc-crud-statement</artifactId>
    <version>1.0.0</version>
    <name>JDBC - CRUD va Statement</name>

    <properties>
        <maven.compiler.release>17</maven.compiler.release>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <main.class>dev.canhdominich.jdbc.crud.CrudDemo</main.class>
        <main.arg>none</main.arg>
        <jvm.heap>-Xmx512m</jvm.heap>
        <mysql.connector.version>26.7.0</mysql.connector.version>
        <junit.version>6.0.3</junit.version>
        <testcontainers.version>2.0.5</testcontainers.version>
        <assertj.version>3.27.7</assertj.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <version>${mysql.connector.version}</version>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>${junit.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>testcontainers-junit-jupiter</artifactId>
            <version>${testcontainers.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>testcontainers-mysql</artifactId>
            <version>${testcontainers.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.assertj</groupId>
            <artifactId>assertj-core</artifactId>
            <version>${assertj.version}</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>3.5.6</version>
            </plugin>
            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>exec-maven-plugin</artifactId>
                <version>3.6.3</version>
                <configuration>
                    <executable>java</executable>
                    <arguments>
                        <argument>${jvm.heap}</argument>
                        <argument>-classpath</argument>
                        <classpath/>
                        <argument>${main.class}</argument>
                        <argument>${main.arg}</argument>
                    </arguments>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

services:
  mysql:
    image: mysql:8.4
    container_name: jdbc-lab-mysql
    environment:
      MYSQL_ROOT_PASSWORD: root_pw
      MYSQL_DATABASE: shop
      MYSQL_USER: app
      MYSQL_PASSWORD: app_pw
    ports:
      - "${MYSQL_PORT:-3307}:3306"
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "127.0.0.1", "-uroot", "-proot_pw"]
      interval: 3s
      timeout: 3s
      retries: 30

docker compose up -d
docker compose ps

package dev.canhdominich.jdbc.crud;

import java.math.BigDecimal;
import java.time.LocalDateTime;

public record Product(Long id, String sku, String name, BigDecimal price, int stock, LocalDateTime createdAt) {
}

static long insert(Connection connection, String sku, String name, BigDecimal price, int stock) throws SQLException {
    String sql = "INSERT INTO product(sku, name, price, stock) VALUES (?, ?, ?, ?)";
    try (PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
        ps.setString(1, sku);
        ps.setString(2, name);
        ps.setBigDecimal(3, price);
        ps.setInt(4, stock);
        ps.executeUpdate();
        try (ResultSet keys = ps.getGeneratedKeys()) {
            keys.next();
            return keys.getLong(1);
        }
    }
}

static Optional<Product> findById(Connection connection, long id) throws SQLException {
    try (PreparedStatement ps = connection.prepareStatement("SELECT id, sku, name, price, stock, created_at FROM product WHERE id = ?")) {
        ps.setLong(1, id);
        try (ResultSet rs = ps.executeQuery()) {
            return rs.next() ? Optional.of(map(rs)) : Optional.empty();
        }
    }
}

private static Product map(ResultSet rs) throws SQLException {
    return new Product(
            rs.getLong("id"),
            rs.getString("sku"),
            rs.getString("name"),
            rs.getBigDecimal("price"),
            rs.getInt("stock"),
            rs.getObject("created_at", LocalDateTime.class));
}

static int updatePrice(Connection connection, long id, BigDecimal price) throws SQLException {
    try (PreparedStatement ps = connection.prepareStatement("UPDATE product SET price = ? WHERE id = ?")) {
        ps.setBigDecimal(1, price);
        ps.setLong(2, id);
        return ps.executeUpdate();
    }
}

package dev.canhdominich.jdbc.crud;

public record DbConfig(String url, String user, String password) {

    public static DbConfig fromEnvironment() {
        return new DbConfig(
                env("JDBC_URL", "jdbc:mysql://localhost:3307/shop"),
                env("DB_USER", "app"),
                env("DB_PASSWORD", "app_pw"));
    }

    public DbConfig withUrl(String newUrl) {
        return new DbConfig(newUrl, user, password);
    }

    public DbConfig withPassword(String newPassword) {
        return new DbConfig(url, user, newPassword);
    }

    private static String env(String name, String fallback) {
        String value = System.getenv(name);
        return value == null || value.isBlank() ? fallback : value;
    }
}

package dev.canhdominich.jdbc.crud;

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

final class Db {

    private Db() {
    }

    static Connection open(DbConfig config) throws SQLException {
        return DriverManager.getConnection(config.url(), config.user(), config.password());
    }

    static Connection open(DbConfig config, String urlOptions) throws SQLException {
        String separator = config.url().contains("?") ? "&" : "?";
        return DriverManager.getConnection(config.url() + separator + urlOptions, config.user(), config.password());
    }

    static void resetSchema(Connection connection) throws SQLException {
        try (Statement statement = connection.createStatement()) {
            statement.execute("DROP TABLE IF EXISTS product");
            statement.execute("DROP TABLE IF EXISTS app_user");
            statement.execute("DROP TABLE IF EXISTS audit_log");
            statement.execute("""
                    CREATE TABLE product (
                        id BIGINT AUTO_INCREMENT PRIMARY KEY,
                        sku VARCHAR(32) NOT NULL UNIQUE,
                        name VARCHAR(100) NOT NULL,
                        price DECIMAL(10,2) NOT NULL,
                        stock INT NOT NULL DEFAULT 0,
                        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
                    )""");
            statement.execute("""
                    CREATE TABLE app_user (
                        id BIGINT AUTO_INCREMENT PRIMARY KEY,
                        username VARCHAR(50) NOT NULL UNIQUE,
                        password_hash VARCHAR(100) NOT NULL,
                        role VARCHAR(20) NOT NULL
                    )""");
            statement.execute("CREATE TABLE audit_log (id BIGINT AUTO_INCREMENT PRIMARY KEY, message VARCHAR(200) NOT NULL)");
            statement.execute("INSERT INTO app_user(username, password_hash, role) VALUES ('admin', 'hash-admin', 'ADMIN'), ('an', 'hash-an', 'USER')");
            statement.execute("INSERT INTO audit_log(message) VALUES ('first entry')");
        }
    }
}

package dev.canhdominich.jdbc.crud;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

public final class CrudDemo {

    private CrudDemo() {
    }

    public static void main(String[] args) throws SQLException {
        try (Connection connection = Db.open(DbConfig.fromEnvironment())) {
            Db.resetSchema(connection);
            long id = insert(connection, "KB-01", "Keyboard", new BigDecimal("49.90"), 12);
            System.out.println("generated id = " + id);
            System.out.println("found = " + findById(connection, id).orElseThrow());
            System.out.println("update rows = " + updatePrice(connection, id, new BigDecimal("59.90")));
            System.out.println("search = " + searchByMinPrice(connection, new BigDecimal("20.00")));
            System.out.println("delete rows = " + delete(connection, id));
            System.out.println("delete again rows = " + delete(connection, id));
        }
    }

    static long insert(Connection connection, String sku, String name, BigDecimal price, int stock) throws SQLException {
        String sql = "INSERT INTO product(sku, name, price, stock) VALUES (?, ?, ?, ?)";
        try (PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, sku);
            ps.setString(2, name);
            ps.setBigDecimal(3, price);
            ps.setInt(4, stock);
            ps.executeUpdate();
            try (ResultSet keys = ps.getGeneratedKeys()) {
                keys.next();
                return keys.getLong(1);
            }
        }
    }

    static Optional<Product> findById(Connection connection, long id) throws SQLException {
        try (PreparedStatement ps = connection.prepareStatement("SELECT id, sku, name, price, stock, created_at FROM product WHERE id = ?")) {
            ps.setLong(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? Optional.of(map(rs)) : Optional.empty();
            }
        }
    }

    static List<Product> searchByMinPrice(Connection connection, BigDecimal min) throws SQLException {
        try (PreparedStatement ps = connection.prepareStatement("SELECT id, sku, name, price, stock, created_at FROM product WHERE price >= ? ORDER BY price")) {
            ps.setBigDecimal(1, min);
            try (ResultSet rs = ps.executeQuery()) {
                List<Product> result = new ArrayList<>();
                while (rs.next()) {
                    result.add(map(rs));
                }
                return result;
            }
        }
    }

    static int updatePrice(Connection connection, long id, BigDecimal price) throws SQLException {
        try (PreparedStatement ps = connection.prepareStatement("UPDATE product SET price = ? WHERE id = ?")) {
            ps.setBigDecimal(1, price);
            ps.setLong(2, id);
            return ps.executeUpdate();
        }
    }

    static int delete(Connection connection, long id) throws SQLException {
        try (PreparedStatement ps = connection.prepareStatement("DELETE FROM product WHERE id = ?")) {
            ps.setLong(1, id);
            return ps.executeUpdate();
        }
    }

    private static Product map(ResultSet rs) throws SQLException {
        return new Product(
                rs.getLong("id"),
                rs.getString("sku"),
                rs.getString("name"),
                rs.getBigDecimal("price"),
                rs.getInt("stock"),
                rs.getObject("created_at", LocalDateTime.class));
    }
}

generated id = 1
found = Product[id=1, sku=KB-01, name=Keyboard, price=49.90, stock=12, createdAt=...]
update rows = 1
delete rows = 1
delete again rows = 0

package dev.canhdominich.jdbc.crud;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public final class SqlInjectionDemo {

    private SqlInjectionDemo() {
    }

    public static void main(String[] args) throws SQLException {
        DbConfig config = DbConfig.fromEnvironment();
        try (Connection connection = Db.open(config)) {
            Db.resetSchema(connection);
            String[][] attempts = {
                    {"an", "hash-an"},
                    {"an", "wrong"},
                    {"admin' -- ", "anything"},
                    {"nobody", "x' OR '1'='1"}
            };
            for (String[] attempt : attempts) {
                System.out.println("login(" + attempt[0] + " | " + attempt[1] + ")  concat=" + vulnerableLogin(connection, attempt[0], attempt[1])
                        + "  prepared=" + safeLogin(connection, attempt[0], attempt[1]));
            }
        }
        stacked(config, "default URL");
        stacked(config, "allowMultiQueries=true");
    }

    static String vulnerableLogin(Connection connection, String username, String password) throws SQLException {
        String sql = "SELECT role FROM app_user WHERE username = '" + username + "' AND password_hash = '" + password + "'";
        try (Statement statement = connection.createStatement(); ResultSet rs = statement.executeQuery(sql)) {
            return rs.next() ? "LOGGED_IN as " + rs.getString(1) : "denied";
        } catch (SQLException e) {
            return "error " + e.getSQLState();
        }
    }

    static String safeLogin(Connection connection, String username, String password) throws SQLException {
        try (PreparedStatement ps = connection.prepareStatement("SELECT role FROM app_user WHERE username = ? AND password_hash = ?")) {
            ps.setString(1, username);
            ps.setString(2, password);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? "LOGGED_IN as " + rs.getString(1) : "denied";
            }
        }
    }

    static String stacked(DbConfig config, String label) throws SQLException {
        String outcome;
        try (Connection connection = label.startsWith("allow") ? Db.open(config, "allowMultiQueries=true") : Db.open(config)) {
            Db.resetSchema(connection);
            String input = "x'; DROP TABLE audit_log; -- ";
            String sql = "SELECT role FROM app_user WHERE username = '" + input + "'";
            try (Statement statement = connection.createStatement()) {
                statement.execute(sql);
                outcome = "query ran";
            } catch (SQLException e) {
                outcome = "rejected " + e.getClass().getSimpleName() + " SQLState=" + e.getSQLState();
            }
            try (Statement statement = connection.createStatement(); ResultSet rs = statement.executeQuery("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'audit_log'")) {
                rs.next();
                outcome += ", audit_log exists = " + (rs.getInt(1) == 1);
            }
        }
        System.out.println("stacked query with " + label + ": " + outcome);
        return outcome;
    }
}

login(an | hash-an)  concat=LOGGED_IN as USER  prepared=LOGGED_IN as USER
login(an | wrong)  concat=denied  prepared=denied
login(admin' --  | anything)  concat=LOGGED_IN as ADMIN  prepared=denied
login(nobody | x' OR '1'='1)  concat=LOGGED_IN as ADMIN  prepared=denied
stacked query with default URL: rejected SQLSyntaxErrorException SQLState=42000, audit_log exists = true
stacked query with allowMultiQueries=true: query ran, audit_log exists = false

package dev.canhdominich.jdbc.crud;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

public final class OrderByAndIn {

    private static final Map<String, String> SORTABLE = Map.of("price", "price", "name", "name", "stock", "stock");

    private OrderByAndIn() {
    }

    public static void main(String[] args) throws SQLException {
        try (Connection connection = Db.open(DbConfig.fromEnvironment())) {
            Db.resetSchema(connection);
            try (Statement statement = connection.createStatement()) {
                statement.execute("INSERT INTO product(sku, name, price, stock) VALUES ('A', 'Charger', 30.00, 5), ('B', 'Adapter', 10.00, 9), ('C', 'Battery', 20.00, 1)");
            }
            System.out.println("ORDER BY ? with 'price' : " + names(connection, "SELECT name FROM product ORDER BY ?", "price"));
            System.out.println("ORDER BY price           : " + names(connection, "SELECT name FROM product ORDER BY price", null));
            System.out.println("whitelist sort=name      : " + names(connection, "SELECT name FROM product ORDER BY " + sortColumn("name"), null));
            try {
                sortColumn("price; DROP TABLE product");
            } catch (IllegalArgumentException e) {
                System.out.println("whitelist rejects        : " + e.getMessage());
            }
            System.out.println("IN list                  : " + findBySkus(connection, List.of("A", "C")));
        }
    }

    static String sortColumn(String requested) {
        String column = SORTABLE.get(requested);
        if (column == null) {
            throw new IllegalArgumentException("unsupported sort column: " + requested);
        }
        return column;
    }

    static List<String> findBySkus(Connection connection, List<String> skus) throws SQLException {
        String placeholders = String.join(", ", Collections.nCopies(skus.size(), "?"));
        try (PreparedStatement ps = connection.prepareStatement("SELECT name FROM product WHERE sku IN (" + placeholders + ") ORDER BY name")) {
            for (int i = 0; i < skus.size(); i++) {
                ps.setString(i + 1, skus.get(i));
            }
            return readNames(ps);
        }
    }

    static List<String> names(Connection connection, String sql, String parameter) throws SQLException {
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            if (parameter != null) {
                ps.setString(1, parameter);
            }
            return readNames(ps);
        }
    }

    private static List<String> readNames(PreparedStatement ps) throws SQLException {
        List<String> names = new ArrayList<>();
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                names.add(rs.getString(1));
            }
        }
        return names;
    }
}

ORDER BY ? with 'price' : [Charger, Adapter, Battery]
ORDER BY price           : [Adapter, Battery, Charger]
whitelist sort=name      : [Adapter, Battery, Charger]
whitelist rejects        : unsupported sort column: price; DROP TABLE product
IN list                  : [Battery, Charger]

private static final Map<String, String> SORTABLE = Map.of("price", "price", "name", "name", "stock", "stock");

static String sortColumn(String requested) {
    String column = SORTABLE.get(requested);
    if (column == null) {
        throw new IllegalArgumentException("unsupported sort column: " + requested);
    }
    return column;
}

String placeholders = String.join(", ", Collections.nCopies(skus.size(), "?"));
PreparedStatement ps = connection.prepareStatement("SELECT name FROM product WHERE sku IN (" + placeholders + ") ORDER BY name");

package dev.canhdominich.jdbc.crud;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public final class PreparedModes {

    static final int ROUNDS = 5000;

    private PreparedModes() {
    }

    public static void main(String[] args) throws SQLException {
        DbConfig config = DbConfig.fromEnvironment();
        try (Connection connection = Db.open(config)) {
            Db.resetSchema(connection);
            try (Statement statement = connection.createStatement()) {
                statement.execute("INSERT INTO product(sku, name, price, stock) VALUES ('KB-01', 'Keyboard', 49.90, 12)");
            }
        }
        String[] modes = {"", "useServerPrepStmts=true", "useServerPrepStmts=true&cachePrepStmts=true"};
        for (String mode : modes) {
            try (Connection connection = mode.isEmpty() ? Db.open(config) : Db.open(config, mode)) {
                run(connection, 200);
                long start = System.nanoTime();
                run(connection, ROUNDS);
                long ms = (System.nanoTime() - start) / 1_000_000;
                System.out.printf("%-48s %d queries in %d ms%n", mode.isEmpty() ? "default (client-side)" : mode, ROUNDS, ms);
            }
        }
    }

    static void run(Connection connection, int rounds) throws SQLException {
        for (int i = 0; i < rounds; i++) {
            try (PreparedStatement ps = connection.prepareStatement("SELECT name FROM product WHERE sku = ?")) {
                ps.setString(1, "KB-01");
                try (ResultSet rs = ps.executeQuery()) {
                    rs.next();
                }
            }
        }
    }
}

default (client-side)                            5000 queries in 3049 ms
useServerPrepStmts=true                          5000 queries in 3671 ms
useServerPrepStmts=true&cachePrepStmts=true      5000 queries in 1433 ms

docker exec jdbc-lab-mysql mysql -uroot -proot_pw -e "SET GLOBAL log_output='TABLE'; TRUNCATE mysql.general_log; SET GLOBAL general_log='ON';"
mvn -q exec:exec -Dmain.class=dev.canhdominich.jdbc.crud.PreparedModes
docker exec jdbc-lab-mysql mysql -uroot -proot_pw -e "SET GLOBAL general_log='OFF'; SELECT thread_id, command_type, COUNT(*) n FROM mysql.general_log WHERE CONVERT(argument USING utf8mb4) LIKE '%FROM product WHERE sku%' OR command_type IN ('Prepare','Execute','Close stmt') GROUP BY thread_id, command_type ORDER BY thread_id, command_type; TRUNCATE mysql.general_log;"

package dev.canhdominich.jdbc.crud;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public final class AffectedRows {

    private AffectedRows() {
    }

    public static void main(String[] args) throws SQLException {
        DbConfig config = DbConfig.fromEnvironment();
        for (String options : new String[]{"", "useAffectedRows=true"}) {
            try (Connection connection = options.isEmpty() ? Db.open(config) : Db.open(config, options)) {
                Db.resetSchema(connection);
                try (Statement statement = connection.createStatement()) {
                    statement.execute("INSERT INTO product(sku, name, price, stock) VALUES ('KB-01', 'Keyboard', 49.90, 12)");
                }
                System.out.println((options.isEmpty() ? "default" : options)
                        + ": same value -> " + setStock(connection, 12)
                        + ", new value -> " + setStock(connection, 13)
                        + ", no such row -> " + setStockWhere(connection, 99, 5));
            }
        }
    }

    static int setStock(Connection connection, int stock) throws SQLException {
        return setStockWhere(connection, 1, stock);
    }

    static int setStockWhere(Connection connection, long id, int stock) throws SQLException {
        try (PreparedStatement ps = connection.prepareStatement("UPDATE product SET stock = ? WHERE id = ?")) {
            ps.setInt(1, stock);
            ps.setLong(2, id);
            return ps.executeUpdate();
        }
    }
}

default: same value -> 1, new value -> 1, no such row -> 0
useAffectedRows=true: same value -> 0, new value -> 1, no such row -> 0

package dev.canhdominich.jdbc.crud;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public final class LargeResult {

    static final int ROWS = 400_000;

    private LargeResult() {
    }

    public static void main(String[] args) throws SQLException {
        String mode = args.length > 0 ? args[0] : "default";
        DbConfig config = DbConfig.fromEnvironment();
        if (mode.equals("seed")) {
            seed(config);
            return;
        }
        if (mode.equals("blocked")) {
            blocked(config);
            return;
        }
        String options = mode.equals("cursor") ? "useCursorFetch=true" : "";
        long start = System.nanoTime();
        try (Connection connection = options.isEmpty() ? Db.open(config) : Db.open(config, options);
             PreparedStatement ps = connection.prepareStatement("SELECT id, payload FROM big_table", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
            switch (mode) {
                case "stream" -> ps.setFetchSize(Integer.MIN_VALUE);
                case "cursor" -> ps.setFetchSize(1000);
                default -> {
                }
            }
            long count = 0;
            long bytes = 0;
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    count++;
                    bytes += rs.getString("payload").length();
                }
            }
            long ms = (System.nanoTime() - start) / 1_000_000;
            System.out.println(mode + ": rows=" + count + " chars=" + bytes + " time=" + ms + " ms");
        } catch (SQLException e) {
            String cause = e.getCause() == null ? "none" : e.getCause().getClass().getSimpleName();
            System.out.println(mode + ": SQLException message=\"" + e.getMessage() + "\" cause=" + cause);
        }
    }

    static void blocked(DbConfig config) throws SQLException {
        try (Connection connection = Db.open(config);
             PreparedStatement ps = connection.prepareStatement("SELECT id FROM big_table", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
            ps.setFetchSize(Integer.MIN_VALUE);
            try (ResultSet rs = ps.executeQuery()) {
                rs.next();
                try (Statement other = connection.createStatement()) {
                    other.executeQuery("SELECT 1");
                } catch (SQLException e) {
                    System.out.println("second statement while streaming: " + e.getClass().getSimpleName() + " SQLState=" + e.getSQLState() + " message=" + e.getMessage());
                }
            }
        }
    }

    static void seed(DbConfig config) throws SQLException {
        try (Connection connection = Db.open(config); Statement statement = connection.createStatement()) {
            statement.execute("DROP TABLE IF EXISTS big_table");
            statement.execute("CREATE TABLE big_table (id BIGINT PRIMARY KEY, payload VARCHAR(255) NOT NULL)");
            statement.execute("SET SESSION cte_max_recursion_depth = " + ROWS);
            statement.execute("INSERT INTO big_table SELECT n, REPEAT('x', 200) FROM (WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < " + ROWS + ") SELECT n FROM seq) s");
            try (ResultSet rs = statement.executeQuery("SELECT COUNT(*) FROM big_table")) {
                rs.next();
                System.out.println("seeded " + rs.getLong(1) + " rows");
            }
        }
    }
}

mvn -q exec:exec -Dmain.class=dev.canhdominich.jdbc.crud.LargeResult -Dmain.arg=seed
mvn -q exec:exec -Dmain.class=dev.canhdominich.jdbc.crud.LargeResult -Dmain.arg=default -Djvm.heap=-Xmx64m
mvn -q exec:exec -Dmain.class=dev.canhdominich.jdbc.crud.LargeResult -Dmain.arg=stream -Djvm.heap=-Xmx64m
mvn -q exec:exec -Dmain.class=dev.canhdominich.jdbc.crud.LargeResult -Dmain.arg=cursor -Djvm.heap=-Xmx64m
mvn -q exec:exec -Dmain.class=dev.canhdominich.jdbc.crud.LargeResult -Dmain.arg=blocked

default: SQLException message="Java heap space" cause=OutOfMemoryError
stream: rows=400000 chars=80000000 time=2169 ms
cursor: rows=400000 chars=80000000 time=4114 ms

package dev.canhdominich.jdbc.crud;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

public final class StoredProcedureDemo {

    private StoredProcedureDemo() {
    }

    public static void main(String[] args) throws SQLException {
        try (Connection connection = Db.open(DbConfig.fromEnvironment())) {
            Db.resetSchema(connection);
            try (Statement statement = connection.createStatement()) {
                statement.execute("INSERT INTO product(sku, name, price, stock) VALUES ('A', 'Charger', 30.00, 5), ('B', 'Adapter', 10.00, 9)");
                statement.execute("DROP PROCEDURE IF EXISTS count_products");
                statement.execute("CREATE PROCEDURE count_products(IN min_price DECIMAL(10,2), OUT total INT) SELECT COUNT(*) INTO total FROM product WHERE price >= min_price");
            }
            System.out.println("count_products(15.00) = " + countAtLeast(connection, new BigDecimal("15.00")));
            System.out.println("count_products(5.00)  = " + countAtLeast(connection, new BigDecimal("5.00")));
        }
    }

    static int countAtLeast(Connection connection, BigDecimal minPrice) throws SQLException {
        try (CallableStatement call = connection.prepareCall("{call count_products(?, ?)}")) {
            call.setBigDecimal(1, minPrice);
            call.registerOutParameter(2, Types.INTEGER);
            call.execute();
            return call.getInt(2);
        }
    }
}

count_products(15.00) = 1
count_products(5.00)  = 2