萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

Java JDBC 通用 DAO 基類設計:多資料庫支援、資源管理、參數化增刪改查封裝

一個 BaseDao 走天下:MySQL/SQL Server/Oracle 無縫切換

企業級應用需要屏蔽資料庫差異。本文提供一個**生產就緒的 `BaseDao` 抽象基類**,支援三大主流資料庫驅動自動識別、連線池整合、參數化 CRUD、資源安全釋放、異常統一轉換。

BaseDao 完整實現 (Java 8+)

package dao;

import javax.sql.DataSource;
import java.sql.*;
import java.util.*;

public abstract class BaseDao {
    // ===== 配置區 (建議外部化到屬性檔/環境變數) =====
    private static final String DB_TYPE = "mysql"; // mysql / sqlserver / oracle
    private static final String JDBC_URL = "jdbc:mysql://127.0.0.1:3306/school?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
    private static final String USER = "root";
    private static final String PASS = "123456";
    // ============================================

    private static DataSource dataSource; // 生產環境注入 HikariDataSource

    static {
        // 簡易內建連線池 (演示用,生產請替換為 HikariCP)
        try {
            Class.forName(getDriverClass());
            // 這裡簡化為單例 Connection,實際必須用連線池
        } catch (ClassNotFoundException e) {
            throw new ExceptionInInitializerError("JDBC Driver not found: " + e.getMessage());
        }
    }

    private static String getDriverClass() {
        switch (DB_TYPE.toLowerCase()) {
            case "mysql": return "com.mysql.cj.jdbc.Driver"; // 8.0+ 驅動類名
            case "sqlserver": return "com.microsoft.sqlserver.jdbc.SQLServerDriver";
            case "oracle": return "oracle.jdbc.OracleDriver";
            default: throw new IllegalArgumentException("Unsupported DB_TYPE: " + DB_TYPE);
        }
    }

    // 獲取連線 (生產從 DataSource.getConnection())
    protected Connection getConnection() throws SQLException {
        if (dataSource != null) return dataSource.getConnection();
        return DriverManager.getConnection(JDBC_URL, USER, PASS);
    }

    // 統一資源關閉 (逆序、逐個 try-catch、吞掉關閉異常)
    protected void closeAll(Connection conn, PreparedStatement ps, ResultSet rs) {
        closeQuietly(rs);
        closeQuietly(ps);
        closeQuietly(conn);
    }
    private void closeQuietly(AutoCloseable c) {
        if (c != null) try { c.close(); } catch (Exception ignored) {}
    }

    // ===== 核心執行模板 =====
    // 增/刪/改: 返回影響行數
    protected int executeUpdate(String sql, Object... params) {
        Connection conn = null; PreparedStatement ps = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            bindParams(ps, params);
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new DaoException("SQL: " + sql, e);
        } finally {
            closeAll(conn, ps, null);
        }
    }

    // 單行查詢映射
    protected  T queryForObject(String sql, RowMapper mapper, Object... params) {
        Connection conn = null; PreparedStatement ps = null; ResultSet rs = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            bindParams(ps, params);
            rs = ps.executeQuery();
            if (rs.next()) return mapper.mapRow(rs);
            return null;
        } catch (SQLException e) {
            throw new DaoException("SQL: " + sql, e);
        } finally {
            closeAll(conn, ps, rs);
        }
    }

    // 列表查詢映射
    protected  List queryForList(String sql, RowMapper mapper, Object... params) {
        Connection conn = null; PreparedStatement ps = null; ResultSet rs = null;
        List list = new ArrayList<>();
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            bindParams(ps, params);
            rs = ps.executeQuery();
            while (rs.next()) list.add(mapper.mapRow(rs));
            return list;
        } catch (SQLException e) {
            throw new DaoException("SQL: " + sql, e);
        } finally {
            closeAll(conn, ps, rs);
        }
    }

    // 參數綁定 (支援 null)
    private void bindParams(PreparedStatement ps, Object... params) throws SQLException {
        if (params != null) {
            for (int i = 0; i < params.length; i++) {
                if (params[i] == null) ps.setObject(i + 1, null);
                else ps.setObject(i + 1, params[i]);
            }
        }
    }

    // RowMapper 函數式介面
    @FunctionalInterface
    public interface RowMapper {
        T mapRow(ResultSet rs) throws SQLException;
    }

    // 自定義異常
    public static class DaoException extends RuntimeException {
        public DaoException(String sql, Throwable cause) { super("DAO Error executing: " + sql, cause); }
    }

    // ===== 使用範例 (繼承類) =====
    /*
    public class UserDao extends BaseDao {
        public User findById(int id) {
            return queryForObject("SELECT id, username, email FROM users WHERE id=?", 
                rs -> new User(rs.getInt("id"), rs.getString("username"), rs.getString("email")), id);
        }
        public int insert(User u) {
            return executeUpdate("INSERT INTO users(username,email) VALUES(?,?)", u.getUsername(), u.getEmail());
        }
    }
    */
}

生產環境必做增強 (清單)

  1. 注入 HikariCP DataSource**:Spring `@Bean` 或手動構建 `HikariDataSource`,設定 `maximumPoolSize`、`connectionTimeout`、`idleTimeout`、`leakDetectionThreshold`
  2. SQL 方言抽象**:分頁 (`LIMIT` vs `OFFSET/FETCH` vs `ROWNUM`)、自增鍵返回 (`LAST_INSERT_ID()` vs `SCOPE_IDENTITY()` vs `SEQUENCE`)、關鍵字轉義
  3. 事務模板**:`@Transactional` / `TransactionTemplate` / 手動 `conn.setAutoCommit(false)` + `commit/rollback`
  4. 慢查詢日誌/監控**:P6Spy / datasource-proxy / Micrometer 記錄執行時間、參數、行數
  5. 優雅關閉**:JVM ShutdownHook 關閉 DataSource

測試驗證

public static void main(String[] args) {
    BaseDao dao = new BaseDao() {}; // 匿名內部類測試
    try (Connection conn = dao.getConnection()) {
        System.out.println("連線成功! 關閉狀態: " + conn.isClosed());
        System.out.println("資料庫: " + conn.getMetaData().getDatabaseProductName());
        System.out.println("驅動: " + conn.getMetaData().getDriverName());
    } catch (SQLException e) {
        e.printStackTrace();
    }
}
copyright © 萬盛學電腦網 all rights reserved