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