您现在的位置：程式碼之家 >> 編程語言 > >> Java 程式設計 >> Java 相關知識

Java Web JDBC 連接 MySQL 完整指南：從原生 API 到連接池最佳實踐

編輯：Java 相關知識

JDBC 是 Java 訪問關係數據庫的標準 API。本文從驅動加載、連接獲取、Statement/PreparedStatement 防注入、事務控制、資源釋放，進階到連接池 (HikariCP/Druid) 與 Spring 整合，構建生產級數據訪問基礎設施。

環境準備

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.2.0</version>  <!-- JDK 17+ 推薦 8.x -->
</dependency>
<!-- 或舊版本 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.49</version>  <!-- JDK 8 兼容 -->
</dependency>

階段一：原生 JDBC 核心流程

1. 驅動加載 (JDBC 4.0+ SPI 自動加載，可省略)

// 老代碼顯式加載
Class.forName("com.mysql.cj.jdbc.Driver");  // 8.x
// Class.forName("com.mysql.jdbc.Driver");   // 5.x

2. 獲取連接

String url = "jdbc:mysql://localhost:3306/jdbctest?useSSL=false&serverTimezone=UTC&characterEncoding=UTF-8";
String user = "root";
String password = "root";

Connection conn = DriverManager.getConnection(url, user, password);

3. Statement vs PreparedStatement

特性	Statement	PreparedStatement
SQL 注入防護	無 (拼接字符串)	參數化查詢 (預編譯)
性能	每次編譯	預編譯緩存，批量執行快
可讀性	拼接難維護	佔位符 ? 清晰
適用場景	極簡/靜態 SQL	✅ 所有業務 SQL

4. 完整 CRUD 範例 (PreparedStatement)

public class UserDao {
    
    private static final String URL = "jdbc:mysql://localhost:3306/jdbctest?useSSL=false&serverTimezone=UTC";
    private static final String USER = "root";
    private static final String PASS = "root";
    
    public void insert(User user) {
        String sql = "INSERT INTO users(username, password, email, birthday) VALUES(?,?,?,?)";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getEmail());
            ps.setDate(4, Date.valueOf(user.getBirthday()));
            
            int rows = ps.executeUpdate();
            if (rows > 0) {
                try (ResultSet keys = ps.getGeneratedKeys()) {
                    if (keys.next()) user.setId(keys.getLong(1));
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("插入失敗", e);
        }
    }
    
    public List<User> findAll() {
        String sql = "SELECT id, username, password, email, birthday FROM users";
        List<User> list = new ArrayList<>();
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            
            while (rs.next()) {
                User u = new User();
                u.setId(rs.getLong("id"));
                u.setUsername(rs.getString("username"));
                u.setPassword(rs.getString("password"));
                u.setEmail(rs.getString("email"));
                u.setBirthday(rs.getDate("birthday").toLocalDate());
                list.add(u);
            }
        } catch (SQLException e) {
            throw new RuntimeException("查詢失敗", e);
        }
        return list;
    }
}

5. 事務控制

public void transfer(long fromId, long toId, double amount) {
    String sql1 = "UPDATE accounts SET balance = balance - ? WHERE id = ?";
    String sql2 = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
    
    try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {
        conn.setAutoCommit(false);  // 開啟事務
        try (PreparedStatement ps1 = conn.prepareStatement(sql1);
             PreparedStatement ps2 = conn.prepareStatement(sql2)) {
            
            ps1.setDouble(1, amount);
            ps1.setLong(2, fromId);
            ps2.setDouble(1, amount);
            ps2.setLong(2, toId);
            
            ps1.executeUpdate();
            ps2.executeUpdate();
            
            conn.commit();  // 提交
        } catch (SQLException e) {
            conn.rollback(); // 回滾
            throw new RuntimeException("轉賬失敗", e);
        }
    } catch (SQLException e) {
        throw new RuntimeException("連接失敗", e);
    }
}

階段二：工具類封裝 (DbUtils 風格)

1. 配置文件 (db.properties)

jdbc.url=jdbc:mysql://localhost:3306/jdbctest?useSSL=false&serverTimezone=UTC&characterEncoding=UTF-8
jdbc.username=root
jdbc.password=root
jdbc.driver=com.mysql.cj.jdbc.Driver

2. JdbcUtil 通用工具類

public class JdbcUtil {
    private static Properties props = new Properties();
    
    static {
        try (InputStream is = JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties")) {
            props.load(is);
            Class.forName(props.getProperty("jdbc.driver"));
        } catch (Exception e) {
            throw new ExceptionInInitializerError("JDBC 初始化失敗: " + e.getMessage());
        }
    }
    
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(
            props.getProperty("jdbc.url"),
            props.getProperty("jdbc.username"),
            props.getProperty("jdbc.password")
        );
    }
    
    public static void closeQuietly(Connection conn, Statement stmt, ResultSet rs) {
        closeQuietly(rs);
        closeQuietly(stmt);
        closeQuietly(conn);
    }
    
    public static void closeQuietly(AutoCloseable closeable) {
        if (closeable != null) {
            try { closeable.close(); } catch (Exception ignored) {}
        }
    }
    
    // 通用查詢模板
    public static <T> List<T> query(String sql, RowMapper<T> mapper, Object... params) {
        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            setParams(ps, params);
            try (ResultSet rs = ps.executeQuery()) {
                List<T> list = new ArrayList<>();
                while (rs.next()) list.add(mapper.mapRow(rs));
                return list;
            }
        } catch (SQLException e) {
            throw new RuntimeException("查詢失敗: " + sql, e);
        }
    }
    
    public static int update(String sql, Object... params) {
        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            setParams(ps, params);
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("更新失敗: " + sql, e);
        }
    }
    
    private static void setParams(PreparedStatement ps, Object... params) throws SQLException {
        for (int i = 0; i < params.length; i++) {
            ps.setObject(i + 1, params[i]);
        }
    }
    
    @FunctionalInterface
    public interface RowMapper<T> {
        T mapRow(ResultSet rs) throws SQLException;
    }
}

3. 業務代碼極簡調用

List<User> users = JdbcUtil.query(
    "SELECT * FROM users WHERE username LIKE ?", 
    rs -> new User(rs.getLong("id"), rs.getString("username"), ...),
    "%zhang%"
);

階段三：生產級連接池 (HikariCP - Spring Boot 默認)

Maven 依賴

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

獨立使用配置

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/jdbctest?useSSL=false&serverTimezone=UTC");
config.setUsername("root");
config.setPassword("root");
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setConnectionTimeout(30000);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000);
config.setConnectionTestQuery("SELECT 1");
config.setPoolName("MyPool");

HikariDataSource ds = new HikariDataSource(config);

// 使用
try (Connection conn = ds.getConnection()) { ... }

Spring Boot 自動配置 (application.yml)

spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/jdbctest?useSSL=false&serverTimezone=UTC
    username: root
    password: root
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000
      connection-test-query: SELECT 1
      pool-name: AppHikariPool

階段四：Druid 連接池 (國產強項、監控強)

依賴

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.18</version>
</dependency>

配置

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      initial-size: 5
      min-idle: 5
      max-active: 20
      max-wait: 60000
      validation-query: SELECT 1
      test-while-idle: true
      test-on-borrow: false
      filters: stat,wall,slf4j  # 監控、防火牆、日誌
      web-stat-filter:
        enabled: true
      stat-view-servlet:
        enabled: true
        url-pattern: /druid/*
        login-username: admin
        login-password: 123456

常見問題與避坑

問題	原因	解決
Public Key Retrieval is not allowed	MySQL 8 密碼插件緩存	URL 加 `allowPublicKeyRetrieval=true`
時區報錯 / 時間差 8 小時	serverTimezone 未設置	URL 加 `serverTimezone=UTC` 或 `Asia/Shanghai`
中文亂碼	字符集不一致	URL 加 `characterEncoding=UTF-8`、DB/表/列 utf8mb4
連接泄露 / 連接數耗盡	未正確關閉 / 池配置過小	try-with-resources、監控泄露、調大池
通信鏈路最後一包異常	連接空閒超過 MySQL wait_timeout	配置 `testWhileIdle`、`validationQuery`、`maxLifetime < wait_timeout`

總結

JDBC 從原生 API → 工具類封裝 → 連接池 (HikariCP/Druid) → Spring Data/JPA/MyBatis，是 Java 數據訪問的演進之路。核心鐵律：必須用 PreparedStatement 防注入、try-with-resources 保證資源釋放、生產環境必須用連接池、事務邊界清晰。掌握原生 JDBC 能看懂上層框架原理，排查疑難雜癥時游刃有餘。