Giới thiệu JDBC và JDBC Drivers

01-gioi-thieu-jdbc/
  docker-compose.yml           MySQL 8.4, database shop, user app
  pom.xml                      thêm hai profile đóng fat jar: fatjar-broken, fatjar-fixed
  src/main/java/dev/canhdominich/jdbc/intro/
    DbConfig.java              đọc URL, user, mật khẩu từ biến môi trường
    Schema.java                tạo bảng product và hai dòng mẫu
    DriverDiscovery.java       ServiceLoader, DriverManager.getDrivers, getDriver theo URL
    WhatDriverSends.java       bật general_log và in các câu lệnh server nhận được
    ApiTour.java               lớp cài đặt, DatabaseMetaData, NULL, đóng tài nguyên, SQLException

<?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-gioi-thieu</artifactId>
    <version>1.0.0</version>
    <name>JDBC - Gioi thieu JDBC</name>

    <properties>
        <maven.compiler.release>17</maven.compiler.release>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <main.class>dev.canhdominich.jdbc.intro.DriverDiscovery</main.class>
        <mysql.connector.version>26.7.0</mysql.connector.version>
        <h2.version>2.5.252</h2.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>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>${h2.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>-classpath</argument>
                        <classpath/>
                        <argument>${main.class}</argument>
                    </arguments>
                </configuration>
            </plugin>
        </plugins>
    </build>

    <profiles>
        <profile>
            <id>fatjar-broken</id>
            <build>
                <finalName>lab-broken</finalName>
                <plugins>
                    <plugin>
                        <groupId>org.apache.maven.plugins</groupId>
                        <artifactId>maven-shade-plugin</artifactId>
                        <version>3.6.1</version>
                        <executions>
                            <execution>
                                <phase>package</phase>
                                <goals>
                                    <goal>shade</goal>
                                </goals>
                                <configuration>
                                    <createDependencyReducedPom>false</createDependencyReducedPom>
                                    <filters>
                                        <filter>
                                            <artifact>*:*</artifact>
                                            <excludes>
                                                <exclude>META-INF/*.SF</exclude>
                                                <exclude>META-INF/*.DSA</exclude>
                                                <exclude>META-INF/*.RSA</exclude>
                                                <exclude>META-INF/MANIFEST.MF</exclude>
                                            </excludes>
                                        </filter>
                                    </filters>
                                </configuration>
                            </execution>
                        </executions>
                    </plugin>
                </plugins>
            </build>
        </profile>
        <profile>
            <id>fatjar-fixed</id>
            <build>
                <finalName>lab-fixed</finalName>
                <plugins>
                    <plugin>
                        <groupId>org.apache.maven.plugins</groupId>
                        <artifactId>maven-shade-plugin</artifactId>
                        <version>3.6.1</version>
                        <executions>
                            <execution>
                                <phase>package</phase>
                                <goals>
                                    <goal>shade</goal>
                                </goals>
                                <configuration>
                                    <createDependencyReducedPom>false</createDependencyReducedPom>
                                    <transformers>
                                        <transformer implementation="org.apache.maven.plugins.shade.resource.ServicesResourceTransformer"/>
                                    </transformers>
                                    <filters>
                                        <filter>
                                            <artifact>*:*</artifact>
                                            <excludes>
                                                <exclude>META-INF/*.SF</exclude>
                                                <exclude>META-INF/*.DSA</exclude>
                                                <exclude>META-INF/*.RSA</exclude>
                                                <exclude>META-INF/MANIFEST.MF</exclude>
                                            </excludes>
                                        </filter>
                                    </filters>
                                </configuration>
                            </execution>
                        </executions>
                    </plugin>
                </plugins>
            </build>
        </profile>
    </profiles>
</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.intro;

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.intro;

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

final class Schema {

    private Schema() {
    }

    static void reset(Connection connection) throws SQLException {
        try (Statement statement = connection.createStatement()) {
            statement.execute("DROP TABLE IF EXISTS product");
            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 NULL
                    )""");
            statement.execute("INSERT INTO product(sku, name, price, stock) VALUES ('KB-01', 'Keyboard', 49.90, 12), ('MS-02', 'Mouse', 19.50, NULL)");
        }
    }
}

package dev.canhdominich.jdbc.intro;

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

public final class ApiTour {

    private ApiTour() {
    }

    public static void main(String[] args) throws SQLException {
        DbConfig config = DbConfig.fromEnvironment();
        try (Connection connection = DriverManager.getConnection(config.url(), config.user(), config.password())) {
            Schema.reset(connection);
            implementations(connection);
            capabilities(connection);
            nullHandling(connection);
            closeCascade(config);
            exceptionAnatomy(connection);
        }
    }

    static void implementations(Connection connection) throws SQLException {
        System.out.println("-- implementations");
        System.out.println("Connection        " + connection.getClass().getName());
        try (PreparedStatement ps = connection.prepareStatement("SELECT id, price FROM product")) {
            System.out.println("PreparedStatement " + ps.getClass().getName());
            try (ResultSet rs = ps.executeQuery()) {
                System.out.println("ResultSet         " + rs.getClass().getName());
                System.out.println("ResultSetMetaData " + rs.getMetaData().getClass().getName());
                System.out.println("price column type " + rs.getMetaData().getColumnTypeName(2) + " -> " + rs.getMetaData().getColumnClassName(2));
            }
        }
    }

    static void capabilities(Connection connection) throws SQLException {
        DatabaseMetaData meta = connection.getMetaData();
        System.out.println("-- capabilities");
        System.out.println("product        " + meta.getDatabaseProductName() + " " + meta.getDatabaseProductVersion());
        System.out.println("driver         " + meta.getDriverName() + " " + meta.getDriverVersion());
        System.out.println("JDBC version   " + meta.getJDBCMajorVersion() + "." + meta.getJDBCMinorVersion());
        System.out.println("transactions   " + meta.supportsTransactions());
        System.out.println("batch updates  " + meta.supportsBatchUpdates());
        System.out.println("isolation      " + connection.getTransactionIsolation() + " (REPEATABLE_READ=" + Connection.TRANSACTION_REPEATABLE_READ + ")");
        System.out.println("autoCommit     " + connection.getAutoCommit());
    }

    static void nullHandling(Connection connection) throws SQLException {
        System.out.println("-- null handling for the Mouse row (stock is NULL)");
        try (Statement statement = connection.createStatement();
             ResultSet rs = statement.executeQuery("SELECT stock FROM product WHERE sku = 'MS-02'")) {
            rs.next();
            int asInt = rs.getInt("stock");
            System.out.println("getInt     = " + asInt + ", wasNull = " + rs.wasNull());
            System.out.println("getObject  = " + rs.getObject("stock"));
            System.out.println("getObject(Integer.class) = " + rs.getObject("stock", Integer.class));
        }
    }

    static void closeCascade(DbConfig config) throws SQLException {
        System.out.println("-- closing the connection closes what it created");
        Connection connection = DriverManager.getConnection(config.url(), config.user(), config.password());
        Statement statement = connection.createStatement();
        ResultSet rs = statement.executeQuery("SELECT 1");
        connection.close();
        System.out.println("statement.isClosed = " + statement.isClosed());
        System.out.println("resultSet.isClosed = " + rs.isClosed());
    }

    static void exceptionAnatomy(Connection connection) throws SQLException {
        System.out.println("-- anatomy of an SQLException");
        try (PreparedStatement ps = connection.prepareStatement("INSERT INTO product(sku, name, price) VALUES (?, ?, ?)")) {
            ps.setString(1, "KB-01");
            ps.setString(2, "Duplicate keyboard");
            ps.setBigDecimal(3, new java.math.BigDecimal("10.00"));
            ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println("class      = " + e.getClass().getName());
            System.out.println("superclass = " + e.getClass().getSuperclass().getName());
            System.out.println("SQLState   = " + e.getSQLState());
            System.out.println("errorCode  = " + e.getErrorCode());
            System.out.println("message    = " + e.getMessage());
        }
        for (String sql : new String[]{"SELECT * FROM missing_table", "SELEC 1"}) {
            try (Statement statement = connection.createStatement()) {
                statement.executeQuery(sql);
            } catch (SQLException e) {
                System.out.println(sql + " -> " + e.getClass().getSimpleName() + " SQLState=" + e.getSQLState() + " errorCode=" + e.getErrorCode() + " message=" + e.getMessage());
            }
        }
    }
}

-- implementations
Connection        com.mysql.cj.jdbc.ConnectionImpl
PreparedStatement com.mysql.cj.jdbc.ClientPreparedStatement
ResultSet         com.mysql.cj.jdbc.result.ResultSetImpl
ResultSetMetaData com.mysql.cj.jdbc.result.ResultSetMetaData
price column type DECIMAL -> java.math.BigDecimal
-- capabilities
product        MySQL 8.4.9
driver         MySQL Connector/J mysql-connector-j-26.7.0 (Revision: 559f62df01d4f618440da174ff45ba87f7b3b2a8)
JDBC version   4.2
transactions   true
batch updates  true
isolation      4 (REPEATABLE_READ=4)
autoCommit     true
-- null handling for the Mouse row (stock is NULL)
getInt     = 0, wasNull = true
getObject  = null
getObject(Integer.class) = null
-- closing the connection closes what it created
statement.isClosed = true
resultSet.isClosed = true
-- anatomy of an SQLException
class      = java.sql.SQLIntegrityConstraintViolationException
superclass = java.sql.SQLNonTransientException
SQLState   = 23000
errorCode  = 1062
message    = Duplicate entry 'KB-01' for key 'product.sku'
SELECT * FROM missing_table -> SQLSyntaxErrorException SQLState=42S02 errorCode=1146 message=Table 'shop.missing_table' doesn't exist
SELEC 1 -> SQLException SQLState=S1009 errorCode=0 message=Statement.executeQuery() cannot issue statements that do not produce result sets.

package dev.canhdominich.jdbc.intro;

import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ServiceLoader;

public final class DriverDiscovery {

    private static final String[] URLS = {
            "jdbc:mysql://localhost:3307/shop",
            "jdbc:h2:mem:lab",
            "jdbc:postgresql://localhost:5432/shop",
            "mysql://localhost:3307/shop"
    };

    private DriverDiscovery() {
    }

    public static void main(String[] args) {
        System.out.println("ServiceLoader finds:");
        for (Driver driver : ServiceLoader.load(Driver.class)) {
            System.out.println("  " + driver.getClass().getName());
        }
        System.out.println("DriverManager.getDrivers():");
        DriverManager.drivers().forEach(driver -> System.out.println("  " + driver.getClass().getName()));
        for (String url : URLS) {
            System.out.println("getDriver(" + url + ")");
            System.out.println("  " + describe(url));
        }
    }

    static String describe(String url) {
        try {
            return DriverManager.getDriver(url).getClass().getName();
        } catch (SQLException e) {
            return e.getClass().getSimpleName() + " SQLState=" + e.getSQLState() + " message=" + e.getMessage();
        }
    }
}

ServiceLoader finds:
  com.mysql.cj.jdbc.Driver
  org.h2.Driver
DriverManager.getDrivers():
  com.mysql.cj.jdbc.Driver
  org.h2.Driver

<transformers>
    <transformer implementation="org.apache.maven.plugins.shade.resource.ServicesResourceTransformer"/>
</transformers>

mvn -q -DskipTests -Pfatjar-broken package
java -cp target/lab-broken.jar dev.canhdominich.jdbc.intro.DriverDiscovery

mvn -q -DskipTests -Pfatjar-fixed package
java -cp target/lab-fixed.jar dev.canhdominich.jdbc.intro.DriverDiscovery

package dev.canhdominich.jdbc.intro;

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.util.ArrayList;
import java.util.List;

public final class WhatDriverSends {

    private WhatDriverSends() {
    }

    public static void main(String[] args) throws SQLException {
        DbConfig app = DbConfig.fromEnvironment();
        DbConfig root = new DbConfig(app.url(), "root", System.getenv().getOrDefault("DB_ROOT_PASSWORD", "root_pw"));
        for (String line : trace(app, root)) {
            System.out.println(line);
        }
    }

    static List<String> trace(DbConfig app, DbConfig root) throws SQLException {
        List<String> lines = new ArrayList<>();
        try (Connection admin = DriverManager.getConnection(root.url(), root.user(), root.password())) {
            try (Statement statement = admin.createStatement()) {
                statement.execute("SET GLOBAL log_output = 'TABLE'");
                statement.execute("SET GLOBAL general_log = 'ON'");
            }
            long connectionId;
            try (Connection connection = DriverManager.getConnection(app.url(), app.user(), app.password())) {
                connectionId = connectionId(connection);
                try (PreparedStatement ps = connection.prepareStatement("SELECT name FROM product WHERE sku = ?")) {
                    ps.setString(1, "KB-01");
                    try (ResultSet rs = ps.executeQuery()) {
                        rs.next();
                    }
                }
            }
            try (Statement statement = admin.createStatement()) {
                statement.execute("SET GLOBAL general_log = 'OFF'");
                try (ResultSet rs = statement.executeQuery(
                        "SELECT command_type, CONVERT(argument USING utf8mb4) FROM mysql.general_log WHERE thread_id = " + connectionId + " ORDER BY event_time")) {
                    while (rs.next()) {
                        lines.add(rs.getString(1) + "  " + rs.getString(2));
                    }
                }
                statement.execute("TRUNCATE mysql.general_log");
            }
        }
        return lines;
    }

    private 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);
        }
    }
}

Connect  app@172.21.0.1 on shop using SSL/TLS
Query    /* mysql-connector-j-26.7.0 ... */SELECT @@session.auto_increment_increment AS ..., @@sql_mode AS sql_mode, @@time_zone AS time_zone, ...
Query    SET character_set_results = NULL
Query    SET autocommit=1
Query    SELECT CONNECTION_ID()
Query    SELECT name FROM product WHERE sku = 'KB-01'
Quit

try (Connection connection = DriverManager.getConnection(url, user, password);
     PreparedStatement ps = connection.prepareStatement("SELECT id, price FROM product WHERE price > ?")) {
    ps.setBigDecimal(1, new BigDecimal("10.00"));
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            long id = rs.getLong("id");
            BigDecimal price = rs.getBigDecimal("price");
        }
    }
}

getInt     = 0, wasNull = true
getObject  = null
getObject(Integer.class) = null