本文通過一個完整的員工管理示例，展示如何結合 DAO 模式、代理模式、工廠模式 構建清晰、可維護、事務可控的數據訪問層。核心分層：
Emp - 數據載體DatabaseConnection - 封裝 JDBC 連接獲取與關閉IEmpDAO - 定義數據操作契約EmpDAOImpl - 純粹的 SQL 執行邏輯EmpDAOProxy - 事務控制、連接生命周期管理、業務校驗DAOFactory - 統一對象創建入口，解耦調用方
import java.util.Date;
public class Emp {
private int empno;
private String ename;
private String job;
private Date hiredate;
private float sal;
// 全屬性構造器、Getter/Setter 省略...
@Override
public String toString() {
return "Emp{empno=" + empno + ", ename='" + ename + "', job='" + job + "', hiredate=" + hiredate + ", sal=" + sal + '}';
}
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DatabaseConnection {
private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost:3306/mldn?useSSL=false&serverTimezone=UTC";
private static final String USER = "root";
private static final String PASSWORD = "root";
private Connection conn;
public DatabaseConnection() throws ClassNotFoundException, SQLException {
Class.forName(DRIVER);
this.conn = DriverManager.getConnection(URL, USER, PASSWORD);
}
public Connection getConnection() {
return conn;
}
public void close() throws SQLException {
if (conn != null && !conn.isClosed()) {
conn.close();
}
}
}
import java.util.List;
public interface IEmpDAO {
boolean doCreate(Emp emp) throws Exception;
List<Emp> findAll(String keyword) throws Exception;
Emp findById(int empno) throws Exception;
}
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class EmpDAOImpl implements IEmpDAO {
private Connection conn;
private PreparedStatement pstmt;
public EmpDAOImpl(Connection conn) {
this.conn = conn;
}
@Override
public boolean doCreate(Emp emp) throws SQLException {
String sql = "INSERT INTO emp(empno, ename, job, hiredate, sal) VALUES(?, ?, ?, ?, ?)";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, emp.getEmpno());
pstmt.setString(2, emp.getEname());
pstmt.setString(3, emp.getJob());
pstmt.setDate(4, new java.sql.Date(emp.getHiredate().getTime()));
pstmt.setFloat(5, emp.getSal());
int rows = pstmt.executeUpdate();
pstmt.close();
return rows > 0;
}
@Override
public List<Emp> findAll(String keyword) throws SQLException {
List<Emp> list = new ArrayList<>();
String sql = "SELECT empno, ename, job, hiredate, sal FROM emp WHERE ename LIKE ? OR job LIKE ?";
pstmt = conn.prepareStatement(sql);
String pattern = "%" + keyword + "%";
pstmt.setString(1, pattern);
pstmt.setString(2, pattern);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
Emp emp = new Emp();
emp.setEmpno(rs.getInt("empno"));
emp.setEname(rs.getString("ename"));
emp.setJob(rs.getString("job"));
emp.setHiredate(rs.getDate("hiredate"));
emp.setSal(rs.getFloat("sal"));
list.add(emp);
}
rs.close();
pstmt.close();
return list;
}
@Override
public Emp findById(int empno) throws SQLException {
String sql = "SELECT empno, ename, job, hiredate, sal FROM emp WHERE empno = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, empno);
ResultSet rs = pstmt.executeQuery();
Emp emp = null;
if (rs.next()) {
emp = new Emp();
emp.setEmpno(rs.getInt("empno"));
emp.setEname(rs.getString("ename"));
emp.setJob(rs.getString("job"));
emp.setHiredate(rs.getDate("hiredate"));
emp.setSal(rs.getFloat("sal"));
}
rs.close();
pstmt.close();
return emp;
}
}
public class EmpDAOProxy implements IEmpDAO {
private DatabaseConnection dbc;
private EmpDAOImpl dao;
public EmpDAOProxy() throws Exception {
this.dbc = new DatabaseConnection();
this.dao = new EmpDAOImpl(dbc.getConnection());
}
@Override
public boolean doCreate(Emp emp) throws Exception {
boolean flag = false;
try {
// 業務校驗：ID 唯一性
if (dao.findById(emp.getEmpno()) == null) {
flag = dao.doCreate(emp);
}
} catch (Exception e) {
throw e;
} finally {
// 統一關閉連接，確保資源釋放
dbc.close();
}
return flag;
}
@Override
public List<Emp> findAll(String keyword) throws Exception {
List<Emp> list = null;
try {
list = dao.findAll(keyword);
} catch (Exception e) {
throw e;
} finally {
dbc.close();
}
return list;
}
@Override
public Emp findById(int empno) throws Exception {
Emp emp = null;
try {
emp = dao.findById(empno);
} catch (Exception e) {
throw e;
} finally {
dbc.close();
}
return emp;
}
}
public class DAOFactory {
public static IEmpDAO getIEmpDAOInstance() throws Exception {
return new EmpDAOProxy();
}
}
public class TestDAOInsert {
public static void main(String[] args) throws Exception {
for (int x = 0; x < 5; x++) {
Emp emp = new Emp();
emp.setEmpno(1000 + x);
emp.setEname("測試員工 - " + x);
emp.setJob("開發工程師 - " + x);
emp.setHiredate(new java.util.Date());
emp.setSal(5000 + x * 100);
// 僅依賴接口與工廠，完全屏蔽實現細節
DAOFactory.getIEmpDAOInstance().doCreate(emp);
System.out.println("插入成功: " + emp.getEname());
}
}
}
| 職責 | 實現類 | 說明 |
|---|---|---|
| 連接生命周期管理 | EmpDAOProxy | 構造時創建，finally 中關閉，杜絕連接泄漏 |
| 事務邊界控制 | EmpDAOProxy | 可在此層添加 conn.setAutoCommit(false)/commit()/rollback() |
| 業務規則校驗 | EmpDAOProxy | 如 doCreate 前檢查 ID 唯一性，屬於業務邏輯而非數據操作 |
| 異常統一轉換 | EmpDAOProxy | 將 SQLException 包裝為業務異常，上層無需依賴 JDBC API |
EmpDAOImpl、EmpDAOProxy、DatabaseConnection）
// 替換 DatabaseConnection 為 HikariCP
HikariDataSource ds = new HikariDataSource();
ds.setJdbcUrl(URL);
ds.setUsername(USER);
ds.setPassword(PASSWORD);
// 代理類中獲取連接：ds.getConnection()
// 關閉連接實際歸還池中，而非物理關閉
public class EmpDAOProxy implements IEmpDAO {
// ThreadLocal 綁定連接，支持多方法調用共用一個事務
private static final ThreadLocal TL_CONN = new ThreadLocal<>();
public void startTransaction() throws SQLException {
Connection conn = dataSource.getConnection();
conn.setAutoCommit(false);
TL_CONN.set(conn);
}
public void commit() throws SQLException {
Connection conn = TL_CONN.get();
if (conn != null) {
conn.commit();
conn.close();
TL_CONN.remove();
}
}
// DAO 方法使用 TL_CONN.get() 獲取連接
}
本文通過手寫 DAO+Proxy+Factory 完整演示了經典三層架構中數據訪問層的構建藝術。雖然現代開發多使用 ORM 框架，但理解底層原理對於排查連接泄漏、事務失效、SQL 注入等疑難雜症至關重要。建議初學者親手實現一遍，體會「關注點分離」與「依賴倒置」的設計智慧。
