Java 連接 MySQL 數據庫 Bean 組件設計與最佳實踐

本文提供一個封裝良好的 MySQLCon JavaBean 組件，實現 MySQL 連接管理、查詢執行、更新操作與資源釋放，並分析其設計優缺點與現代改進方向。

核心代碼實現

package com.example.db;

import java.sql.*;

public class MySQLCon {
    private String dbName = "";
    private String password = "";
    private String url = "";
    private Connection conn = null;
    private Statement stmt = null;
    private ResultSet rs = null;

    public void setDbName(String name) { this.dbName = name; }
    public void setPassword(String pass) { this.password = pass; }

    public void buildConnection() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver").newInstance(); // 現代驅動類名
        url = "jdbc:mysql://localhost/" + dbName +
              "?user=root&password=" + password +
              "&useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
        conn = DriverManager.getConnection(url);
        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    }

    public ResultSet selectLog(String sql) throws SQLException {
        buildConnection();
        rs = stmt.executeQuery(sql);
        return rs;
    }

    public void updateLog(String sql) throws SQLException {
        buildConnection();
        stmt.executeUpdate(sql);
    }

    public void close() throws SQLException {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
        if (conn != null) conn.close();
    }
}

設計亮點與隱患分析

現代化重構範例

// 使用連接池 + PreparedStatement + try-with-resources
public class ModernMySQLDao {
    private final DataSource dataSource; // 注入 HikariDataSource

    public List<Map<String, Object>> query(String sql, Object... params) {
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            for (int i = 0; i < params.length; i++) ps.setObject(i + 1, params[i]);
            try (ResultSet rs = ps.executeQuery()) {
                ResultSetMetaData meta = rs.getMetaData();
                int cols = meta.getColumnCount();
                List<Map<String, Object>> list = new ArrayList<>();
                while (rs.next()) {
                    Map<String, Object> row = new HashMap<>();
                    for (int i = 1; i <= cols; i++) row.put(meta.getColumnLabel(i), rs.getObject(i));
                    list.add(row);
                }
                return list;
            }
        } catch (SQLException e) {
            throw new RuntimeException("Query failed: " + sql, e);
        }
    }
}

部署建議

• Maven 依賴：mysql:mysql-connector-java:8.0.33 + com.zaxxer:HikariCP:5.0.1
• Spring Boot 配置：spring.datasource.hikari.* 微調池參數
• 生產環境必備：慢查詢日志、連接泄漏檢測、指標監控（Prometheus + Grafana）