設計目標
- 簡化 JDBC 樣板碼（連線、PreparedStatement、ResultSet 關閉）
- 支援屬性檔配置資料庫連線資訊
- 利用反射實現實體類與資料表的自動映射（基本 CRUD）
- 作為學習 ORM 原理的教學範例，非生產級替代品
1. 設定檔與 Config 類
# dbconfig.properties
CLASS_NAME=com.mysql.cj.jdbc.Driver
DATABASE_URL=jdbc:mysql
SERVER_IP=localhost
SERVER_PORT=3306
DATABASE_SID=employees
USERNAME=root
PASSWORD=123456
public class Config {
private static final Properties PROP = new Properties();
static {
try (InputStream is = Config.class.getResourceAsStream("/dbconfig.properties")) {
PROP.load(is);
} catch (IOException e) {
throw new ExceptionInInitializerError("載入 dbconfig.properties 失敗", e);
}
}
public static final String CLASS_NAME = PROP.getProperty("CLASS_NAME");
public static final String URL = PROP.getProperty("DATABASE_URL") + "://" +
PROP.getProperty("SERVER_IP") + ":" + PROP.getProperty("SERVER_PORT") + "/" +
PROP.getProperty("DATABASE_SID");
public static final String USERNAME = PROP.getProperty("USERNAME");
public static final String PASSWORD = PROP.getProperty("PASSWORD");
}
2. 底層連線工具 DBConn
public class DBConn {
private Connection conn;
private PreparedStatement pstmt;
private ResultSet rs;
public Connection getConnection() throws SQLException {
if (conn == null || conn.isClosed()) {
Class.forName(Config.CLASS_NAME);
conn = DriverManager.getConnection(Config.URL, Config.USERNAME, Config.PASSWORD);
}
return conn;
}
public void closeAll() {
closeQuietly(rs);
closeQuietly(pstmt);
closeQuietly(conn);
rs = null; pstmt = null; conn = null;
}
public int executeUpdate(PreparedStatement ps) {
try { return ps.executeUpdate(); }
catch (SQLException e) { throw new RuntimeException(e); }
}
public ResultSet executeQuery(PreparedStatement ps) {
try { return ps.executeQuery(); }
catch (SQLException e) { throw new RuntimeException(e); }
}
private void closeQuietly(AutoCloseable c) {
if (c != null) try { c.close(); } catch (Exception ignored) {}
}
}
3. 通用 BaseDAO（反射自動映射）
public abstract class BaseDAO<T> {
protected final DBConn db = new DBConn();
protected final Class<T> entityClass;
@SuppressWarnings("unchecked")
protected BaseDAO() {
this.entityClass = (Class<T>) ((ParameterizedType) getClass().getGenericSuperclass())
.getActualTypeArguments()[0];
}
// 取得所有 getter 方法（用於 INSERT/UPDATE）
private List<Method> getGetters(T entity) {
return Arrays.stream(entity.getClass().getMethods())
.filter(m -> m.getName().startsWith("get") && m.getParameterCount() == 0)
.collect(Collectors.toList());
}
// 取得所有 setter 方法（用於查詢映射）
private List<Method> getSetters(T entity) {
return Arrays.stream(entity.getClass().getMethods())
.filter(m -> m.getName().startsWith("set") && m.getParameterCount() == 1)
.collect(Collectors.toList());
}
// 找出主鍵欄位（慣例：getId 或 get+類名+Id）
private Method findIdGetter(T entity) {
return getGetters(entity).stream()
.filter(m -> "getId".equals(m.getName()) ||
("get" + entityClass.getSimpleName() + "Id").equalsIgnoreCase(m.getName()))
.findFirst().orElseThrow(() -> new IllegalArgumentException("找不到主鍵 getter"));
}
public void save(T entity) {
List<Method> getters = getGetters(entity);
// 排除主鍵（假設自增）
Method idGetter = findIdGetter(entity);
getters.remove(idGetter);
String sql = "INSERT INTO " + tableName() + " (" +
getters.stream().map(m -> columnName(m)).collect(Collectors.joining(",")) +
") VALUES (" + getters.stream().map(m -> "?").collect(Collectors.joining(",")) + ")";
try (Connection conn = db.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
int i = 1;
for (Method m : getters) {
setParameter(ps, i++, m.invoke(entity));
}
db.executeUpdate(ps);
} catch (Exception e) { throw new RuntimeException(e); }
}
public void update(T entity) {
List<Method> getters = getGetters(entity);
Method idGetter = findIdGetter(entity);
getters.remove(idGetter);
String sql = "UPDATE " + tableName() + " SET " +
getters.stream().map(m -> columnName(m) + "=?").collect(Collectors.joining(",")) +
" WHERE " + columnName(idGetter) + "=?";
try (Connection conn = db.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
int i = 1;
for (Method m : getters) setParameter(ps, i++, m.invoke(entity));
setParameter(ps, i, idGetter.invoke(entity));
db.executeUpdate(ps);
} catch (Exception e) { throw new RuntimeException(e); }
}
public void delete(T entity) {
Method idGetter = findIdGetter(entity);
String sql = "DELETE FROM " + tableName() + " WHERE " + columnName(idGetter) + "=?";
try (Connection conn = db.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
setParameter(ps, 1, idGetter.invoke(entity));
db.executeUpdate(ps);
} catch (Exception e) { throw new RuntimeException(e); }
}
public T findById(Object id) {
Method idGetter = findIdGetter(entityClass);
String sql = "SELECT * FROM " + tableName() + " WHERE " + columnName(idGetter) + "=?";
try (Connection conn = db.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
setParameter(ps, 1, id);
try (ResultSet rs = db.executeQuery(ps)) {
if (rs.next()) return mapRow(rs);
}
} catch (Exception e) { throw new RuntimeException(e); }
return null;
}
protected T mapRow(ResultSet rs) throws Exception {
T entity = entityClass.getDeclaredConstructor().newInstance();
List<Method> setters = getSetters(entity);
for (Method setter : setters) {
String col = columnName(setter);
Class<?> paramType = setter.getParameterTypes()[0];
Object val = getResult(rs, col, paramType);
if (val != null) setter.invoke(entity, val);
}
return entity;
}
// 輔助方法：駝峰轉下劃線、參數綁定、結果取值等略...
}
4. 具體 DAO 繼承
public class EmployeeDAO extends BaseDAO<Employee> {
// 直接繼承即可使用 save/update/delete/findById
// 可額外加入 findAll、findByName 等自定義查詢
}
5. 單元測試範例
public class EmployeeDAOTest {
private EmployeeDAO dao = new EmployeeDAO();
@Test
public void testCRUD() {
Employee e = new Employee();
e.setName("張三"); e.setDept("研發"); e.setSalary(80000);
dao.save(e);
assertNotNull(e.getId());
e.setSalary(85000);
dao.update(e);
Employee found = dao.findById(e.getId());
assertEquals(85000, found.getSalary());
dao.delete(e);
assertNull(dao.findById(e.getId()));
}
}
局限性與改進方向
- 無連線池、無事務管理、無批次操作、無樂觀鎖、無關聯映射。
- 生產環境請直接使用成熟框架：MyBatis、JdbcTemplate、Hibernate、JPA。
- 本範例適合理解「反射 + JDBC + 註解/慣例」如何構建簡易 ORM 原型。
