MySQL

suggest change

Java programs talk to MySQL through JDBC, the standard java.sql API that ships with the JDK. The MySQL-specific part is the driver, MySQL Connector/J (com.mysql:mysql-connector-j), a pure-Java implementation of the MySQL client protocol. Spring's JdbcTemplate, jOOQ, MyBatis and Hibernate all run on top of JDBC, so the basics below apply to them too. The same driver also works with MariaDB, although MariaDB has its own driver (org.mariadb.jdbc:mariadb-java-client).

Add the driver to pom.xml:

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>26.7.0</version>
</dependency>

With Gradle: runtimeOnly("com.mysql:mysql-connector-j:26.7.0"). The old coordinates mysql:mysql-connector-java stopped getting updates at 8.0.33, so switch to the new ones if your build still uses them.

The examples were run with JDK 25 against MySQL 9.7, using a database and user created like this:

CREATE DATABASE shop;
CREATE USER 'app'@'%' IDENTIFIED BY 'secret';
GRANT ALL ON shop.* TO 'app'@'%';

Each example is a standalone program you can run with java -cp mysql-connector-j-26.7.0.jar Example.java.

Connecting

A MySQL JDBC URL has the form jdbc:mysql://host:port/database?param=value&.... The default port is 3306. Since JDBC 4 the driver registers itself, so you don't need Class.forName("com.mysql.cj.jdbc.Driver").

Connection, Statement and ResultSet all hold resources, so always open them with try-with-resources:

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

public class Connect {
    static final String URL = "jdbc:mysql://localhost:3306/shop";

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection(URL, "app", "secret")) {
            DatabaseMetaData meta = conn.getMetaData();
            System.out.println("Driver:  " + meta.getDriverName() + " " + meta.getDriverVersion());
            System.out.println("Server:  " + meta.getDatabaseProductVersion());

            try (Statement st = conn.createStatement();
                 ResultSet rs = st.executeQuery(
                         "SELECT DATABASE(), CURRENT_USER(), @@character_set_connection, @@time_zone")) {
                rs.next();
                System.out.println("DB:      " + rs.getString(1));
                System.out.println("User:    " + rs.getString(2));
                System.out.println("Charset: " + rs.getString(3));
                System.out.println("TZ:      " + rs.getString(4));
            }
        }
    }
}

Output:

Driver:  MySQL Connector/J mysql-connector-j-26.7.0 (Revision: 559f62df01d4f618440da174ff45ba87f7b3b2a8)
Server:  9.7.2
DB:      shop
User:    app@%
Charset: utf8mb4
TZ:      SYSTEM

Connector/J 8.0 and later use utf8mb4 by default, which is real UTF-8 including emoji. MySQL's older utf8 (an alias for utf8mb3) can't store 4-byte characters, so avoid it.

Useful URL parameters:

Parameter Purpose
sslMode=VERIFY_IDENTITY Encrypt the connection and verify the server certificate and host name (default is PREFERRED)
connectTimeout=10000 Milliseconds to wait for the TCP connection
socketTimeout=30000 Milliseconds to wait on a read before giving up
connectionTimeZone=UTC Time zone the driver assumes for the server session (see the types section)
rewriteBatchedStatements=true Rewrites batches into multi-row INSERTs (see below)
useAffectedRows=true Report rows actually changed instead of rows matched
allowPublicKeyRetrieval=true Needed for caching_sha2_password users when not using TLS (local development only)

Don't hard-code credentials. Read them from environment variables or a secrets manager.

Creating a table

Statement.execute() runs DDL. Text blocks keep multi-line SQL readable:

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

public class Schema {
    static final String URL = "jdbc:mysql://localhost:3306/shop";

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection(URL, "app", "secret");
             Statement st = conn.createStatement()) {
            st.execute("DROP TABLE IF EXISTS products");
            st.execute("""
                CREATE TABLE products (
                    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    sku        VARCHAR(32)   NOT NULL UNIQUE,
                    name       VARCHAR(200)  NOT NULL,
                    price      DECIMAL(10,2) NOT NULL,
                    in_stock   BOOLEAN       NOT NULL DEFAULT TRUE,
                    attrs      JSON,
                    created_at TIMESTAMP(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
                """);
            System.out.println("Table created");
        }
    }
}

Output:

Table created

Some MySQL-specific choices here:

Inserting rows with PreparedStatement

Always pass values through PreparedStatement placeholders (?) instead of building SQL by string concatenation. That prevents SQL injection. To get the AUTO_INCREMENT id of a new row, pass Statement.RETURN_GENERATED_KEYS and read getGeneratedKeys():

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Insert {
    static final String URL = "jdbc:mysql://localhost:3306/shop";

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

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection(URL, "app", "secret")) {
            System.out.println("id " + insertProduct(conn, "KB-01", "Mechanical keyboard", new BigDecimal("89.99")));
            System.out.println("id " + insertProduct(conn, "MS-02", "Wireless mouse", new BigDecimal("24.50")));
            System.out.println("id " + insertProduct(conn, "MN-03", "27\" monitor", new BigDecimal("279.00")));

            // A malicious value is just data when passed as a parameter
            String evil = "x'); DROP TABLE products; --";
            System.out.println("id " + insertProduct(conn, "EV-66", evil, BigDecimal.ONE));

            try (PreparedStatement ps = conn.prepareStatement("DELETE FROM products WHERE sku = ?")) {
                ps.setString(1, "EV-66");
                System.out.println("deleted " + ps.executeUpdate() + " row(s)");
            }
        }
    }
}

Output:

id 1
id 2
id 3
id 4
deleted 1 row(s)

The "evil" name was stored as plain text. Because it was sent as a parameter, it was never parsed as SQL.

Querying and mapping to records

Mapping each row to a Java record keeps JDBC code out of the rest of the application. Use rs.getObject(column, Type.class) to get java.time types directly:

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

public class Query {
    static final String URL = "jdbc:mysql://localhost:3306/shop";

    record Product(long id, String sku, String name, BigDecimal price, boolean inStock,
                   LocalDateTime createdAt) {}

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

    static List<Product> findCheaperThan(Connection conn, BigDecimal max) throws SQLException {
        String sql = """
                SELECT id, sku, name, price, in_stock, created_at
                FROM products WHERE price < ? ORDER BY price""";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setBigDecimal(1, max);
            try (ResultSet rs = ps.executeQuery()) {
                List<Product> result = new ArrayList<>();
                while (rs.next()) {
                    result.add(map(rs));
                }
                return result;
            }
        }
    }

    static Optional<Product> findBySku(Connection conn, String sku) throws SQLException {
        String sql = "SELECT id, sku, name, price, in_stock, created_at FROM products WHERE sku = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, sku);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? Optional.of(map(rs)) : Optional.empty();
            }
        }
    }

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection(URL, "app", "secret")) {
            for (Product p : findCheaperThan(conn, new BigDecimal("100"))) {
                System.out.printf("%d %-6s %-20s %8s %s%n", p.id(), p.sku(), p.name(), p.price(), p.inStock());
            }
            System.out.println(findBySku(conn, "MN-03").map(Product::name).orElse("not found"));
            System.out.println(findBySku(conn, "XX-99").map(Product::name).orElse("not found"));
            Product kb = findBySku(conn, "KB-01").orElseThrow();
            System.out.println("created_at class: " + kb.createdAt().getClass().getSimpleName());
        }
    }
}

Output:

2 MS-02  Wireless mouse          24.50 true
1 KB-01  Mechanical keyboard     89.99 true
27" monitor
not found
created_at class: LocalDateTime

Things to know:

Transactions

A new connection is in auto-commit mode: each statement commits on its own. To group statements, turn auto-commit off, then call commit() or rollback():

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Tx {
    static final String URL = "jdbc:mysql://localhost:3306/shop";

    static void transfer(Connection conn, long from, long to, BigDecimal amount) throws SQLException {
        conn.setAutoCommit(false);
        try (PreparedStatement debit = conn.prepareStatement(
                     "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?");
             PreparedStatement credit = conn.prepareStatement(
                     "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
            debit.setBigDecimal(1, amount);
            debit.setLong(2, from);
            debit.setBigDecimal(3, amount);
            if (debit.executeUpdate() != 1) {
                throw new SQLException("insufficient funds in account " + from);
            }
            credit.setBigDecimal(1, amount);
            credit.setLong(2, to);
            credit.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }

    static void printBalances(Connection conn) throws SQLException {
        try (Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery("SELECT id, balance FROM accounts ORDER BY id")) {
            while (rs.next()) {
                System.out.println("  account " + rs.getLong(1) + ": " + rs.getBigDecimal(2));
            }
        }
    }

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection(URL, "app", "secret")) {
            try (Statement st = conn.createStatement()) {
                st.execute("DROP TABLE IF EXISTS accounts");
                st.execute("CREATE TABLE accounts (id BIGINT PRIMARY KEY, balance DECIMAL(12,2) NOT NULL)");
                st.execute("INSERT INTO accounts VALUES (1, 100.00), (2, 50.00)");
            }
            System.out.println("Isolation level: " + conn.getTransactionIsolation()
                    + " (REPEATABLE_READ = " + Connection.TRANSACTION_REPEATABLE_READ + ")");

            transfer(conn, 1, 2, new BigDecimal("30.00"));
            System.out.println("After first transfer:");
            printBalances(conn);

            try {
                transfer(conn, 2, 1, new BigDecimal("500.00"));
            } catch (SQLException e) {
                System.out.println("Rolled back: " + e.getMessage());
            }
            printBalances(conn);
        }
    }
}

Output:

Isolation level: 4 (REPEATABLE_READ = 4)
After first transfer:
  account 1: 70.00
  account 2: 80.00
Rolled back: insufficient funds in account 2
  account 1: 70.00
  account 2: 80.00

InnoDB's default isolation level is REPEATABLE READ, unlike most other databases, which default to READ COMMITTED. The balance >= ? condition in the UPDATE makes the check and the change one atomic step, so two concurrent transfers can't both pass the check. If you read a row and then update it, lock it with SELECT ... FOR UPDATE in the same transaction.

InnoDB detects deadlocks and rolls back one of the transactions with error 1213 (SQLState 40001). Lock wait timeouts show up as error 1205. In both cases, retry the whole transaction.

Batch inserts

Sending rows one at a time costs one network round trip per row. With addBatch()/executeBatch() the statements are grouped, but Connector/J still sends them one by one unless you add rewriteBatchedStatements=true to the URL. With that option the driver rewrites the batch into multi-row INSERT ... VALUES (...), (...), ... statements:

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Batch {
    static final String BASE = "jdbc:mysql://localhost:3306/shop";

    static void load(String url, String prefix) throws SQLException {
        try (Connection conn = DriverManager.getConnection(url, "app", "secret")) {
            conn.setAutoCommit(false);
            String sql = "INSERT INTO products (sku, name, price) VALUES (?, ?, ?)";
            long start = System.nanoTime();
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                for (int i = 1; i <= 5000; i++) {
                    ps.setString(1, "%s-%04d".formatted(prefix, i));
                    ps.setString(2, "Cable #" + i);
                    ps.setBigDecimal(3, new BigDecimal("4.99"));
                    ps.addBatch();
                    if (i % 1000 == 0) {
                        int[] counts = ps.executeBatch();
                        if (i == 1000) {
                            System.out.println("  counts.length = " + counts.length + ", counts[0] = " + counts[0]);
                        }
                    }
                }
            }
            conn.commit();
            System.out.printf("  %s: 5000 rows in %d ms%n", prefix, (System.nanoTime() - start) / 1_000_000);
        }
    }

    public static void main(String[] args) throws SQLException {
        System.out.println("plain batch:");
        load(BASE, "CBA");
        System.out.println("rewriteBatchedStatements=true:");
        load(BASE + "?rewriteBatchedStatements=true", "CBB");

        try (Connection conn = DriverManager.getConnection(BASE, "app", "secret");
             Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery("SELECT COUNT(*) FROM products WHERE sku LIKE 'CB_-%'")) {
            rs.next();
            System.out.println("cables in table: " + rs.getLong(1));
        }
    }
}

Output:

plain batch:
  counts.length = 1000, counts[0] = 1
  CBA: 5000 rows in 2090 ms
rewriteBatchedStatements=true:
  counts.length = 1000, counts[0] = -2
  CBB: 5000 rows in 194 ms
cables in table: 10000

In this run, rewriting made the load about 10 times faster, and the gap grows with network latency. The trade-off is that the driver can't report a per-row count any more, so each entry is Statement.SUCCESS_NO_INFO (-2). Flush the batch every few hundred or thousand rows so a single packet doesn't exceed the server's max_allowed_packet limit (64 MB by default).

Dates, time zones, JSON and UUIDs

Time zones are the most common source of MySQL bugs. A TIMESTAMP column is stored in UTC and converted to and from the session time zone. A DATETIME column is stored as-is with no time zone at all. Set connectionTimeZone so that the driver and the session agree, and use forceConnectionTimeZoneToSession=true to make the driver set the session time zone for you. Then OffsetDateTime and Instant values round-trip correctly.

MySQL has a native JSON type with functions and the -> / ->> path operators. Connector/J sends and returns JSON as a String, so use a library such as Jackson to convert it to objects.

MySQL has no UUID type. The compact way to store one is BINARY(16):

import java.nio.ByteBuffer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDate;
import java.time.OffsetDateTime;
import java.time.ZoneOffset;
import java.util.UUID;

public class Types {
    // Make the driver and the session agree on UTC
    static final String URL = "jdbc:mysql://localhost:3306/shop"
            + "?connectionTimeZone=UTC&forceConnectionTimeZoneToSession=true";

    static byte[] toBytes(UUID u) {
        return ByteBuffer.allocate(16)
                .putLong(u.getMostSignificantBits())
                .putLong(u.getLeastSignificantBits())
                .array();
    }

    static UUID fromBytes(byte[] b) {
        ByteBuffer bb = ByteBuffer.wrap(b);
        return new UUID(bb.getLong(), bb.getLong());
    }

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection(URL, "app", "secret")) {
            try (Statement st = conn.createStatement()) {
                st.execute("DROP TABLE IF EXISTS orders");
                st.execute("""
                    CREATE TABLE orders (
                        id         BINARY(16) PRIMARY KEY,
                        customer   VARCHAR(100) NOT NULL,
                        ship_date  DATE,
                        placed_at  TIMESTAMP(3) NOT NULL,
                        details    JSON NOT NULL
                    )
                    """);
            }

            UUID id = UUID.fromString("0192f3c4-5e6a-7b8c-9d0e-1f2a3b4c5d6e");
            OffsetDateTime placed = OffsetDateTime.of(2026, 10, 17, 14, 30, 0, 0, ZoneOffset.ofHours(2));
            String details = """
                {"items": [{"sku": "KB-01", "qty": 1}, {"sku": "MS-02", "qty": 2}],
                 "gift": true, "note": "Zażółć gęślą jaźń 😀"}""";

            try (PreparedStatement ps = conn.prepareStatement(
                    "INSERT INTO orders (id, customer, ship_date, placed_at, details) VALUES (?, ?, ?, ?, ?)")) {
                ps.setBytes(1, toBytes(id));
                ps.setString(2, "alice");
                ps.setObject(3, LocalDate.of(2026, 10, 20));
                ps.setObject(4, placed);
                ps.setString(5, details);
                ps.executeUpdate();
            }

            String sql = """
                SELECT id, ship_date, placed_at,
                       details->>'$.note'           AS note,
                       JSON_LENGTH(details, '$.items') AS item_count,
                       details->>'$.items[1].sku'   AS second_sku
                FROM orders
                WHERE id = ? AND details->'$.gift' = true""";
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                ps.setBytes(1, toBytes(id));
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        System.out.println("id:         " + fromBytes(rs.getBytes("id")));
                        System.out.println("ship_date:  " + rs.getObject("ship_date", LocalDate.class));
                        System.out.println("placed_at:  " + rs.getObject("placed_at", OffsetDateTime.class));
                        System.out.println("as Instant: " + rs.getObject("placed_at", OffsetDateTime.class).toInstant());
                        System.out.println("note:       " + rs.getString("note"));
                        System.out.println("items:      " + rs.getInt("item_count"));
                        System.out.println("second sku: " + rs.getString("second_sku"));
                    }
                }
            }
        }
    }
}

Output:

id:         0192f3c4-5e6a-7b8c-9d0e-1f2a3b4c5d6e
ship_date:  2026-10-20
placed_at:  2026-10-17T12:30Z
as Instant: 2026-10-17T12:30:00Z
note:       Zażółć gęślą jaźń 😀
items:      2
second sku: MS-02

The order was placed at 14:30 at UTC+2 and comes back as 12:30 UTC, the same instant. details->>'$.note' extracts a JSON value as unquoted text (-> would return it as JSON, with quotes). The Polish text and the emoji survive because the table and the connection use utf8mb4.

Random (version 4) UUIDs as primary keys scatter inserts across the InnoDB clustered index and slow down large tables. Prefer time-ordered UUIDs (version 7), or an AUTO_INCREMENT key plus a separate unique UUID column for public ids.

Upserts and handling errors

INSERT ... ON DUPLICATE KEY UPDATE inserts a row, or updates it when a PRIMARY KEY or UNIQUE index would be violated. Since MySQL 8.0.19 you can name the new row with AS new and refer to its columns. This replaces the deprecated VALUES(col) function.

For this statement, the affected-row count tells you what happened: 1 for insert, 2 for update, 0 if the row already had the same values. By default Connector/J reports matched rows instead of changed ones, so an unchanged row also returns 1. Add useAffectedRows=true to get the real count:

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;

public class Upsert {
    static final String URL = "jdbc:mysql://localhost:3306/shop?useAffectedRows=true";

    static void upsert(Connection conn, String sku, String name, BigDecimal price) throws SQLException {
        String sql = """
            INSERT INTO products (sku, name, price) VALUES (?, ?, ?) AS new
            ON DUPLICATE KEY UPDATE name = new.name, price = new.price""";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, sku);
            ps.setString(2, name);
            ps.setBigDecimal(3, price);
            int n = ps.executeUpdate();
            String what = switch (n) {
                case 1 -> "inserted";
                case 2 -> "updated";
                default -> "unchanged";
            };
            System.out.println(sku + " -> " + what + " (affected rows = " + n + ")");
        }
    }

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection(URL, "app", "secret")) {
            upsert(conn, "KB-01", "Mechanical keyboard", new BigDecimal("79.99"));
            upsert(conn, "HS-04", "Headset", new BigDecimal("59.00"));
            upsert(conn, "HS-04", "Headset", new BigDecimal("59.00"));

            try (PreparedStatement ps = conn.prepareStatement(
                    "INSERT INTO products (sku, name, price) VALUES (?, ?, ?)")) {
                ps.setString(1, "KB-01");
                ps.setString(2, "Duplicate keyboard");
                ps.setBigDecimal(3, BigDecimal.TEN);
                ps.executeUpdate();
            } catch (SQLIntegrityConstraintViolationException e) {
                System.out.println(e.getClass().getSimpleName());
                System.out.println("  error code: " + e.getErrorCode());
                System.out.println("  SQLState:   " + e.getSQLState());
                System.out.println("  message:    " + e.getMessage());
            }
        }
    }
}

Output:

KB-01 -> updated (affected rows = 2)
HS-04 -> inserted (affected rows = 1)
HS-04 -> unchanged (affected rows = 0)
SQLIntegrityConstraintViolationException
  error code: 1062
  SQLState:   23000
  message:    Duplicate entry 'KB-01' for key 'products.sku'

Connector/J throws subclasses of SQLException you can catch by type: SQLIntegrityConstraintViolationException for duplicate keys and foreign key errors, SQLTransactionRollbackException for deadlocks, SQLTimeoutException for timeouts. For finer detail, check the MySQL error code with getErrorCode():

Error code SQLState Meaning
1062 23000 Duplicate entry for a unique key
1452 23000 Foreign key constraint fails (no parent row)
1451 23000 Can't delete a parent row that has children
1048 23000 Column cannot be null
1213 40001 Deadlock found (retry the transaction)
1205 HY000 Lock wait timeout exceeded (retry the transaction)
1146 42S02 Table doesn't exist

Prefer the error code over the SQLState: MySQL uses 23000 for all integrity violations.

Streaming large result sets

Connector/J reads the entire result of a query into memory before executeQuery() returns. That's fast for small results, but a query returning millions of rows can run out of heap. There are two ways around it:

Streaming looks like this:

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Stream {
    static final String URL = "jdbc:mysql://localhost:3306/shop";

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection(URL, "app", "secret")) {
            String sql = "SELECT sku, price FROM products ORDER BY id";
            try (PreparedStatement ps = conn.prepareStatement(sql,
                    ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
                ps.setFetchSize(Integer.MIN_VALUE); // stream rows one by one
                try (ResultSet rs = ps.executeQuery()) {
                    long rows = 0;
                    BigDecimal total = BigDecimal.ZERO;
                    while (rs.next()) {
                        rows++;
                        total = total.add(rs.getBigDecimal("price"));
                        if (rows == 1) {
                            // The connection is busy until the stream is fully read or closed
                            try (Statement other = conn.createStatement()) {
                                other.executeQuery("SELECT 1");
                            } catch (SQLException e) {
                                System.out.println("second query failed: " + e.getMessage());
                            }
                        }
                    }
                    System.out.println("rows = " + rows + ", total = " + total);
                }
            }
        }
    }
}

Output:

second query failed: Streaming result set com.mysql.cj.protocol.a.result.ResultsetRowsStreaming@3a4621bd is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.
rows = 10004, total = 50342.49

While a streaming result is open, the connection can't run any other statement. Read it to the end or close it first. Streaming also holds locks and a server thread until it finishes, so process rows quickly.

Connection pooling with HikariCP

Opening a MySQL connection takes a TCP handshake, often TLS, and authentication. Servers and web applications should reuse connections from a pool. HikariCP is the standard choice, and Spring Boot uses it by default:

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>7.1.0</version>
</dependency>

HikariCP passes addDataSourceProperty settings through to Connector/J. The prepared statement cache settings below are the ones HikariCP's documentation recommends for MySQL:

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import javax.sql.DataSource;

public class Pool {
    public static void main(String[] args) throws Exception {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/shop");
        config.setUsername("app");
        config.setPassword("secret");
        config.setMaximumPoolSize(4);
        config.setPoolName("shop-pool");
        // Recommended Connector/J settings for pooled use
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        config.addDataSourceProperty("useServerPrepStmts", "true");
        config.addDataSourceProperty("rewriteBatchedStatements", "true");
        config.addDataSourceProperty("connectionAttributes", "program_name:inventory-service");

        try (HikariDataSource ds = new HikariDataSource(config);
             ExecutorService pool = Executors.newVirtualThreadPerTaskExecutor()) {
            List<Future<String>> results = new ArrayList<>();
            for (String sku : List.of("KB-01", "MS-02", "MN-03", "HS-04", "CBB-0007")) {
                results.add(pool.submit(() -> priceOf(ds, sku)));
            }
            for (Future<String> f : results) {
                System.out.println(f.get());
            }
        }
    }

    static String priceOf(DataSource ds, String sku) throws SQLException {
        // close() returns the connection to the pool instead of closing the socket
        try (Connection conn = ds.getConnection();
             PreparedStatement ps = conn.prepareStatement(
                     "SELECT price FROM products WHERE sku = ?")) {
            ps.setString(1, sku);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? sku + " = " + rs.getBigDecimal(1) : sku + " missing";
            }
        }
    }
}

Output:

KB-01 = 79.99
MS-02 = 24.50
MN-03 = 279.00
HS-04 = 59.00
CBB-0007 = 4.99

Calling close() on a pooled connection returns it to the pool. Create one HikariDataSource per database for the lifetime of the application, and pass it around as a javax.sql.DataSource. Keep the pool small (10 connections is HikariCP's default and fine for most services). MySQL's max_connections defaults to 151, shared by all clients. MySQL closes idle connections after wait_timeout (8 hours by default), so set HikariCP's maxLifetime a little lower than that.

connectionAttributes labels the connections. The label shows up in performance_schema.session_connect_attrs, which helps when you're working out which service owns a slow query.

Best practices

Feedback about page:

Feedback:
Optional: your email if you want me to get back to you:



Table Of Contents