JDBC 与 PreparedStatement

第 8 章 · JDBC 与 PreparedStatement

本章目标：掌握 JDBC 标准 API 访问 shop_db；使用 PreparedStatement 完成完整 CRUD；理解 try-with-resources 自动关闭连接/语句/结果集；在 shop-db 中封装 ProductRepository；理解 slug + is_published + price（分） 在 Java 层的读写约定；对照 java-dev ch11 并针对 shop-db schema 扩展。

学时建议：5～6 小时（含 2.5 小时跟练）

前置：完成 java-database ch07（shop_db DDL 已就绪）；java-dev ch11（JDBC 入门）。

8.1 场景说明：不用 ORM 也要会访问数据库

在 shop-db 运维脚本、数据迁移小工具、单元测试夹具中，常需轻量直连 MySQL，而不引入 JPA/Spring。本章用纯 JDBC 完成：

路径：~/learn-java/shop-db；数据库：shop_db。域名 api.example.com 仅为占位。

8.2 对照 java-dev ch11：从 toolkit_db 到 shop_db

java-dev ch11 使用虚构库 toolkit_db，字段为 sku + DECIMAL price。shop-db 升级为：

JDBC API 本身不变：Connection、PreparedStatement、ResultSet 用法与 ch11 相同，本章重点在业务字段映射与完整 Repository 封装。

8.3 Maven 依赖与环境

pom.xml：

<properties>
    <maven.compiler.release>21</maven.compiler.release>
    <mysql.version>8.3.0</mysql.version>
</properties>

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

连接 URL（勿硬编码生产密码）：

jdbc:mysql://127.0.0.1:3306/shop_db?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8

DbConfig.java：

package com.example.shopdb.jdbc;

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

public final class DbConfig {

    private static final String URL = System.getenv()
        .getOrDefault("DB_URL",
            "jdbc:mysql://127.0.0.1:3306/shop_db?useSSL=false&serverTimezone=Asia/Shanghai");
    private static final String USER = System.getenv().getOrDefault("DB_USER", "shop_user");
    private static final String PASS = System.getenv().getOrDefault("DB_PASS", "shop_pass");

    private DbConfig() {}

    public static Connection openConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASS);
    }
}

8.4 try-with-resources 深入

java-dev ch11 §11.3 已介绍 Connection 的 try-with-resources。本章扩展三层嵌套关闭顺序：

// 推荐：Connection + PreparedStatement + ResultSet 均实现 AutoCloseable
String sql = """
    SELECT id, title, slug, price, stock, is_published
    FROM products
    WHERE is_published = 1
    ORDER BY created_at DESC
    LIMIT ?
    """;

try (Connection conn = DbConfig.openConnection();
     PreparedStatement ps = conn.prepareStatement(sql)) {

    ps.setInt(1, 20);

    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.printf("%s ¥%.2f%n",
                rs.getString("slug"),
                rs.getLong("price") / 100.0);
        }
    }
} catch (SQLException e) {
    throw new RuntimeException("查询上架商品失败", e);
}

反模式：只关 Connection 而不关 ResultSet——在部分驱动下可能泄漏服务器端资源。

8.5 价格（分）工具类

对照 python-database ch08 的 money.py：

package com.example.shopdb.util;

import java.math.BigDecimal;
import java.math.RoundingMode;

public final class MoneyUtils {

    private MoneyUtils() {}

    /** "59.90" → 5990 */
    public static long yuanToCents(String yuan) {
        BigDecimal bd = new BigDecimal(yuan.trim());
        return bd.multiply(BigDecimal.valueOf(100))
                 .setScale(0, RoundingMode.HALF_UP)
                 .longValueExact();
    }

    /** 5990 → "59.90" */
    public static String centsToYuan(long cents) {
        return BigDecimal.valueOf(cents, 2).toPlainString();
    }
}

JDBC 写入：ps.setLong(4, MoneyUtils.yuanToCents("59.90"));

8.6 PreparedStatement 查询

package com.example.shopdb.jdbc;

import com.example.shopdb.model.ProductRow;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

public class ProductQuery {

    public List<ProductRow> findPublishedByCategory(Connection conn, long categoryId)
            throws SQLException {
        String sql = """
            SELECT id, category_id, title, slug, price, stock, is_published
            FROM products
            WHERE category_id = ? AND is_published = 1
            ORDER BY created_at DESC
            """;
        List<ProductRow> list = new ArrayList<>();

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setLong(1, categoryId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(mapRow(rs));
                }
            }
        }
        return list;
    }

    public Optional<ProductRow> findBySlug(Connection conn, String slug) throws SQLException {
        String sql = "SELECT * FROM products WHERE slug = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, slug);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? Optional.of(mapRow(rs)) : Optional.empty();
            }
        }
    }

    private ProductRow mapRow(ResultSet rs) throws SQLException {
        return new ProductRow(
            rs.getLong("id"),
            rs.getLong("category_id"),
            rs.getString("title"),
            rs.getString("slug"),
            rs.getLong("price"),
            rs.getInt("stock"),
            rs.getBoolean("is_published")
        );
    }
}

ProductRow 用 Java record 承载只读行数据：

package com.example.shopdb.model;

public record ProductRow(
    long id,
    long categoryId,
    String title,
    String slug,
    long priceCents,
    int stock,
    boolean published
) {}