Kết nối MySQL

git clone https://github.com/canhdominich/java-backend-fundamentals.git
cd java-backend-fundamentals/02-jdbc/02-ket-noi-mysql
docker compose up -d
mvn -q compile exec:exec -Dmain.class=dev.canhdominich.jdbc.HelloConnection

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

package dev.canhdominich.jdbc;

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 withUser(String newUser) {
        return new DbConfig(url, newUser, password);
    }

    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;

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

public class HelloConnection {

    public static void main(String[] args) throws SQLException {
        DbConfig config = DbConfig.fromEnvironment();

        try (Connection connection = DriverManager.getConnection(config.url(), config.user(), config.password())) {
            var meta = connection.getMetaData();
            System.out.println("Connected to " + meta.getDatabaseProductName() + " " + meta.getDatabaseProductVersion());
            System.out.println("Driver: " + meta.getDriverName() + " " + meta.getDriverVersion());
            System.out.println("Catalog: " + connection.getCatalog());
            System.out.println("isValid(2): " + connection.isValid(2));

            try (Statement statement = connection.createStatement();
                 ResultSet rs = statement.executeQuery("SELECT CONNECTION_ID(), CURRENT_USER()")) {
                rs.next();
                System.out.println("Server connection id: " + rs.getLong(1));
                System.out.println("Authenticated as: " + rs.getString(2));
            }
        }
    }
}

Connected to MySQL 8.4.9
Driver: MySQL Connector/J mysql-connector-j-26.7.0 (Revision: ...)
Catalog: shop
isValid(2): true
Server connection id: 15
Authenticated as: app@%

jdbc:mysql://<host>:<port>/<database>?<tùy chọn>=<giá trị>&...

package dev.canhdominich.jdbc;

import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectionErrors {

    public static void main(String[] args) {
        DbConfig good = DbConfig.fromEnvironment();

        report("Wrong password", good.withPassword("not-the-password"));
        report("Wrong port", good.withUrl(good.url().replace(":3307", ":3999")));
        report("Unknown database", good.withUrl(good.url().replace("/shop", "/no_such_db")));
        String rootPassword = System.getenv().getOrDefault("DB_ROOT_PASSWORD", "root_pw");
        report("Unknown database (administrator)", good.withUser("root").withPassword(rootPassword).withUrl(good.url().replace("/shop", "/no_such_db")));
        report("Wrong URL prefix", good.withUrl(good.url().replace("jdbc:mysql:", "jdbc:mysq:")));
    }

    static void report(String label, DbConfig config) {
        System.out.println("== " + label);
        try (var ignored = DriverManager.getConnection(config.url(), config.user(), config.password())) {
            System.out.println("connected (unexpected)");
        } catch (SQLException e) {
            System.out.println("class    : " + e.getClass().getName());
            System.out.println("SQLState : " + e.getSQLState());
            System.out.println("errorCode: " + e.getErrorCode());
            System.out.println("message  : " + firstLine(e.getMessage()));
            Throwable root = e;
            while (root.getCause() != null) {
                root = root.getCause();
            }
            System.out.println("root     : " + root.getClass().getName() + ": " + firstLine(root.getMessage()));
        }
    }

    private static String firstLine(String message) {
        if (message == null) {
            return "";
        }
        int newline = message.indexOf('\n');
        return newline < 0 ? message : message.substring(0, newline);
    }
}

package dev.canhdominich.jdbc;

import java.sql.DriverManager;
import java.sql.SQLException;

public class NetworkFailures {

    public static void main(String[] args) {
        DbConfig good = DbConfig.fromEnvironment();
        report("Unknown host", "jdbc:mysql://no-such-host.invalid:3307/shop", good, 0);
        report("Closed port", good.url().replace(":3307", ":3999"), good, 0);
        report("Unroutable address, connectTimeout=2000", "jdbc:mysql://10.255.255.1:3307/shop?connectTimeout=2000", good, 0);
        report("Unroutable address, loginTimeout=1 second", "jdbc:mysql://10.255.255.1:3307/shop", good, 1);
    }

    static void report(String label, String url, DbConfig credentials, int loginTimeoutSeconds) {
        DriverManager.setLoginTimeout(loginTimeoutSeconds);
        long start = System.nanoTime();
        try (var ignored = DriverManager.getConnection(url, credentials.user(), credentials.password())) {
            System.out.println(label + ": connected (unexpected)");
        } catch (SQLException e) {
            long ms = (System.nanoTime() - start) / 1_000_000;
            Throwable root = e;
            while (root.getCause() != null) {
                root = root.getCause();
            }
            System.out.printf("%-44s %s SQLState=%s root=%s: %s after %d ms%n", label, e.getClass().getSimpleName(), e.getSQLState(),
                    root.getClass().getSimpleName(), root.getMessage(), ms);
        } finally {
            DriverManager.setLoginTimeout(0);
        }
    }
}

Unknown host                                 CommunicationsException SQLState=08S01 root=UnknownHostException: no-such-host.invalid: Temporary failure in name resolution after 249 ms
Closed port                                  CommunicationsException SQLState=08S01 root=ConnectException: Connection refused after 9 ms
Unroutable address, connectTimeout=2000      CommunicationsException SQLState=08S01 root=SocketTimeoutException: Connect timed out after 2006 ms
Unroutable address, loginTimeout=1 second    CommunicationsException SQLState=08S01 root=SocketTimeoutException: Connect timed out after 1006 ms

package dev.canhdominich.jdbc;

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

public class UrlOptions {

    private static final String[] OPTIONS = {"", "useSSL=false", "sslMode=DISABLED", "sslMode=REQUIRED", "sslMode=VERIFY_CA", "useSSl=false"};

    public static void main(String[] args) {
        DbConfig config = DbConfig.fromEnvironment();
        for (String option : OPTIONS) {
            System.out.printf("%-20s %s%n", option.isEmpty() ? "(no option)" : option, cipher(config, option));
        }
    }

    static String cipher(DbConfig config, String option) {
        String url = option.isEmpty() ? config.url() : config.url() + "?" + option;
        try (Connection connection = DriverManager.getConnection(url, config.user(), config.password());
             Statement statement = connection.createStatement();
             ResultSet rs = statement.executeQuery("SHOW SESSION STATUS LIKE 'Ssl_cipher'")) {
            rs.next();
            String cipher = rs.getString(2);
            return cipher.isEmpty() ? "no encryption" : cipher;
        } catch (SQLException e) {
            Throwable root = e;
            while (root.getCause() != null) {
                root = root.getCause();
            }
            return "FAILED " + root.getClass().getSimpleName();
        }
    }
}

(no option)          TLS_AES_256_GCM_SHA384
useSSL=false         no encryption
sslMode=DISABLED     no encryption
sslMode=REQUIRED     TLS_AES_256_GCM_SHA384
sslMode=VERIFY_CA    FAILED CertPathValidatorException
useSSl=false         TLS_AES_256_GCM_SHA384

package dev.canhdominich.jdbc;

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

public class PublicKeyRetrieval {

    public static void main(String[] args) {
        DbConfig config = DbConfig.fromEnvironment();
        System.out.println("1. sslMode=DISABLED                           : " + attempt(config, "sslMode=DISABLED"));
        System.out.println("2. sslMode=DISABLED&allowPublicKeyRetrieval=true: " + attempt(config, "sslMode=DISABLED&allowPublicKeyRetrieval=true"));
        System.out.println("3. sslMode=DISABLED again                      : " + attempt(config, "sslMode=DISABLED"));
    }

    static String attempt(DbConfig config, String options) {
        try (Connection ignored = DriverManager.getConnection(config.url() + "?" + options, config.user(), config.password())) {
            return "connected";
        } catch (SQLException e) {
            return e.getClass().getSimpleName() + " SQLState=" + e.getSQLState() + " " + e.getMessage();
        }
    }
}

docker compose restart mysql
mvn -q exec:exec -Dmain.class=dev.canhdominich.jdbc.PublicKeyRetrieval

1. sslMode=DISABLED                           : SQLNonTransientConnectionException SQLState=08001 Public Key Retrieval is not allowed
2. sslMode=DISABLED&allowPublicKeyRetrieval=true: connected
3. sslMode=DISABLED again                      : connected

package dev.canhdominich.jdbc;

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.sql.Timestamp;
import java.time.Instant;

public class TimezoneDemo {

    private static final Instant MOMENT = Instant.parse("2026-10-16T03:00:00Z");

    public static void main(String[] args) throws SQLException {
        DbConfig config = DbConfig.fromEnvironment();

        System.out.println("JVM time zone : " + java.util.TimeZone.getDefault().getID());
        System.out.println("Stored moment : " + MOMENT + " (epoch " + MOMENT.getEpochSecond() + ")");
        for (String option : new String[]{"default", "connectionTimeZone=UTC", "serverTimezone=UTC", "serverTimezone=Foo/Bar",
                "connectionTimeZone=UTC&forceConnectionTimeZoneToSession=true"}) {
            try {
                store(config, option);
            } catch (SQLException e) {
                Throwable root = e;
                while (root.getCause() != null) {
                    root = root.getCause();
                }
                System.out.printf("%-24s FAILED %s: %s%n", option, root.getClass().getSimpleName(), root.getMessage());
            }
        }
    }

    static void store(DbConfig config, String option) throws SQLException {
        String url = option.equals("default") ? config.url() : config.url() + "?" + option;
        try (Connection connection = DriverManager.getConnection(url, config.user(), config.password());
             Statement statement = connection.createStatement()) {
            statement.execute("DROP TABLE IF EXISTS tz_demo");
            statement.execute("CREATE TABLE tz_demo (id INT PRIMARY KEY, created_at TIMESTAMP NOT NULL)");
            try (PreparedStatement insert = connection.prepareStatement("INSERT INTO tz_demo VALUES (1, ?)")) {
                insert.setTimestamp(1, Timestamp.from(MOMENT));
                insert.executeUpdate();
            }
            try (ResultSet rs = statement.executeQuery("SELECT CAST(created_at AS CHAR), UNIX_TIMESTAMP(created_at), @@session.time_zone FROM tz_demo")) {
                rs.next();
                long epoch = rs.getLong(2);
                System.out.printf("%-24s server shows %s, epoch %d, offset from truth %+d h, session time_zone %s%n",
                        option, rs.getString(1), epoch, (epoch - MOMENT.getEpochSecond()) / 3600, rs.getString(3));
            }
        }
    }
}

JVM time zone : Asia/Ho_Chi_Minh
Stored moment : 2026-10-16T03:00:00Z (epoch 1792119600)
default                  server shows 2026-10-16 10:00:00, epoch 1792144800, offset from truth +7 h, session time_zone SYSTEM
connectionTimeZone=UTC   server shows 2026-10-16 03:00:00, epoch 1792119600, offset from truth +0 h, session time_zone SYSTEM
serverTimezone=UTC       server shows 2026-10-16 03:00:00, epoch 1792119600, offset from truth +0 h, session time_zone SYSTEM
serverTimezone=Foo/Bar   FAILED ZoneRulesException: Unknown time-zone ID: Foo/Bar
connectionTimeZone=UTC&forceConnectionTimeZoneToSession=true server shows 2026-10-16 03:00:00, epoch 1792119600, offset from truth +0 h, session time_zone +00:00

package dev.canhdominich.jdbc;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedHashSet;
import java.util.Set;

public class PoolVsDirect {

    private static final int ROUNDS = 200;

    public static void main(String[] args) throws SQLException {
        DbConfig config = DbConfig.fromEnvironment();

        measureDirect(config);
        measurePooled(config);
    }

    static void measureDirect(DbConfig config) throws SQLException {
        Set<Long> ids = new LinkedHashSet<>();
        long start = System.nanoTime();
        for (int i = 0; i < ROUNDS; i++) {
            try (Connection connection = DriverManager.getConnection(config.url(), config.user(), config.password())) {
                ids.add(connectionId(connection));
            }
        }
        report("DriverManager", start, ids);
    }

    static void measurePooled(DbConfig config) throws SQLException {
        HikariConfig hikari = new HikariConfig();
        hikari.setJdbcUrl(config.url());
        hikari.setUsername(config.user());
        hikari.setPassword(config.password());
        hikari.setMaximumPoolSize(5);

        try (HikariDataSource dataSource = new HikariDataSource(hikari)) {
            Set<Long> ids = new LinkedHashSet<>();
            long start = System.nanoTime();
            for (int i = 0; i < ROUNDS; i++) {
                try (Connection connection = dataSource.getConnection()) {
                    ids.add(connectionId(connection));
                }
            }
            report("HikariCP", start, ids);
        }
    }

    static long connectionId(Connection connection) throws SQLException {
        try (Statement statement = connection.createStatement();
             ResultSet rs = statement.executeQuery("SELECT CONNECTION_ID()")) {
            rs.next();
            return rs.getLong(1);
        }
    }

    private static void report(String label, long startNanos, Set<Long> ids) {
        long millis = (System.nanoTime() - startNanos) / 1_000_000;
        System.out.printf("%-14s %d borrows in %d ms, distinct server connections: %d%n", label, ROUNDS, millis, ids.size());
    }
}

DriverManager  200 borrows in 5056 ms, distinct server connections: 200
HikariCP       200 borrows in 265 ms, distinct server connections: 1

HikariConfig hikari = new HikariConfig();
hikari.setJdbcUrl(config.url());
hikari.setUsername(config.user());
hikari.setPassword(config.password());
hikari.setPoolName("lab-pool");
hikari.setMaximumPoolSize(2);
hikari.setConnectionTimeout(1000);
hikari.setLeakDetectionThreshold(2000);

try (HikariDataSource dataSource = new HikariDataSource(hikari)) {
    try (Connection connection = dataSource.getConnection()) {
        connection.createStatement().execute("SELECT 1");
    }
}

package dev.canhdominich.jdbc;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.HikariPoolMXBean;
import java.sql.Connection;
import java.sql.SQLException;

public class PoolExhaustion {

    public static void main(String[] args) throws Exception {
        DbConfig config = DbConfig.fromEnvironment();

        HikariConfig hikari = new HikariConfig();
        hikari.setJdbcUrl(config.url());
        hikari.setUsername(config.user());
        hikari.setPassword(config.password());
        hikari.setPoolName("lab-pool");
        hikari.setMaximumPoolSize(2);
        hikari.setMinimumIdle(2);
        hikari.setConnectionTimeout(1000);
        hikari.setLeakDetectionThreshold(2000);

        try (HikariDataSource dataSource = new HikariDataSource(hikari)) {
            HikariPoolMXBean pool = dataSource.getHikariPoolMXBean();
            Connection first = dataSource.getConnection();
            Connection second = dataSource.getConnection();
            System.out.printf("borrowed 2: active=%d idle=%d waiting=%d%n", pool.getActiveConnections(), pool.getIdleConnections(), pool.getThreadsAwaitingConnection());

            long start = System.nanoTime();
            try (Connection third = dataSource.getConnection()) {
                System.out.println("third connection obtained (unexpected)");
            } catch (SQLException e) {
                System.out.printf("third borrow failed after %d ms%n", (System.nanoTime() - start) / 1_000_000);
                System.out.println("class  : " + e.getClass().getName());
                System.out.println("message: " + e.getMessage());
            }

            Thread.sleep(2500);
            first.close();
            second.close();
            System.out.printf("after close: active=%d idle=%d%n", pool.getActiveConnections(), pool.getIdleConnections());
        }
    }
}

borrowed 2: active=2 idle=0 waiting=0
third borrow failed after 1061 ms
class  : java.sql.SQLTransientConnectionException
message: lab-pool - Connection is not available, request timed out after 1001ms (total=2, active=2, idle=0, waiting=0)
after close: active=0 idle=2

WARN com.zaxxer.hikari.pool.ProxyLeakTask - Connection leak detection triggered for com.mysql.cj.jdbc.ConnectionImpl@6497b078 on thread main, stack trace follows
java.lang.Exception: Apparent connection leak detected
	at dev.canhdominich.jdbc.PoolExhaustion.main(PoolExhaustion.java:26)

package dev.canhdominich.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class MaxConnectionsDemo {

    public static void main(String[] args) throws SQLException {
        DbConfig app = DbConfig.fromEnvironment();
        String rootPassword = System.getenv().getOrDefault("DB_ROOT_PASSWORD", "root_pw");
        try (Connection admin = DriverManager.getConnection(app.url(), "root", rootPassword);
             Statement statement = admin.createStatement()) {
            statement.execute("SET GLOBAL max_connections = 8");
            List<Connection> opened = new ArrayList<>();
            try {
                for (int i = 1; i <= 20; i++) {
                    opened.add(DriverManager.getConnection(app.url(), app.user(), app.password()));
                }
            } catch (SQLException e) {
                System.out.println("opened " + opened.size() + " connections, then " + e.getClass().getSimpleName()
                        + " SQLState=" + e.getSQLState() + " errorCode=" + e.getErrorCode() + " message=" + e.getMessage());
            } finally {
                for (Connection connection : opened) {
                    connection.close();
                }
                statement.execute("SET GLOBAL max_connections = 151");
            }
        }
    }
}

opened 7 connections, then SQLNonTransientConnectionException SQLState=08004 errorCode=1040 message=Too many connections