BaseDao 設計目標
- 泛型支援任意實體類別
- 反射自動映射欄位/屬性
- 動態生成預編譯 SQL 防注入
- 統一資源管理（Connection/Statement/ResultSet）
核心工具類：JdbcUtil
public class JdbcUtil {
private static final String URL = "jdbc:mysql://localhost:3306/db?useSSL=false";
private static final String USER = "root";
private static final String PASS = "123456";
private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
static { try { Class.forName(DRIVER); } catch (Exception e) { throw new RuntimeException(e); } }
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, USER, PASS);
}
public static void close(ResultSet rs, Statement st, Connection conn) {
closeQuietly(rs);
closeQuietly(st);
closeQuietly(conn);
}
private static void closeQuietly(AutoCloseable ac) {
if (ac != null) try { ac.close(); } catch (Exception ignore) {}
}
}
BaseDao 完整實作
public class BaseDao<T> {
private final JdbcUtil jdbcUtil = new JdbcUtil();
private final Class<T> entityClass;
@SuppressWarnings("unchecked")
public BaseDao() {
// 取得泛型實際類型：BaseDao → User.class
ParameterizedType type = (ParameterizedType) getClass().getGenericSuperclass();
this.entityClass = (Class<T>) type.getActualTypeArguments()[0];
}
/** 通用：取得所有 getter 方法 */
private List<Method> getGetters(T entity) {
List<Method> list = new ArrayList<>();
for (Method m : entity.getClass().getDeclaredMethods()) {
if (m.getName().startsWith("get") && m.getParameterCount() == 0) {
list.add(m);
}
}
return list;
}
/** 通用：取得 setter 方法（用於查找 ID） */
private List<Method> getSetters(T entity) {
List<Method> list = new ArrayList<>();
for (Method m : entity.getClass().getDeclaredMethods()) {
if (m.getName().startsWith("set") && m.getParameterCount() == 1) {
list.add(m);
}
}
return list;
}
/** 保存：INSERT INTO table (col1,col2) VALUES (?,?) */
public int save(T entity) throws Exception {
Connection conn = jdbcUtil.getConnection();
List<Method> getters = getGetters(entity);
Object[] params = new Object[getters.size()];
StringBuilder sql = new StringBuilder("INSERT INTO ")
.append(entityClass.getSimpleName().toLowerCase()).append(" (");
StringBuilder placeholders = new StringBuilder();
for (int i = 0; i < getters.size(); i++) {
Method m = getters.get(i);
String col = m.getName().substring(3).toLowerCase(); // getName → name
sql.append(col).append(",");
placeholders.append("?,");
Object val = m.invoke(entity);
// 日期類型格式化
if (val instanceof java.util.Date) {
params[i] = new java.sql.Timestamp(((java.util.Date)val).getTime());
} else {
params[i] = val;
}
}
sql.setCharAt(sql.length()-1, ')'); // 替換最後逗號
sql.append(" VALUES (").append(placeholders).setCharAt(placeholders.length()-1, ')');
return executeUpdate(conn, sql.toString(), params);
}
/** 刪除：DELETE FROM table WHERE id = ? */
public int deleteById(Object id) throws Exception {
Connection conn = jdbcUtil.getConnection();
T entity = entityClass.newInstance();
Method idSetter = findIdSetter(entity);
String idCol = idSetter.getName().substring(3).toLowerCase(); // setId → id
String sql = "DELETE FROM " + entityClass.getSimpleName().toLowerCase()
+ " WHERE " + idCol + " = ?";
return executeUpdate(conn, sql, id);
}
/** 更新：UPDATE table SET col1=?,col2=? WHERE id=? */
public int update(T entity) throws Exception {
Connection conn = jdbcUtil.getConnection();
List<Method> getters = getGetters(entity);
List<Object> params = new ArrayList<>();
StringBuilder sql = new StringBuilder("UPDATE ")
.append(entityClass.getSimpleName().toLowerCase()).append(" SET ");
Method idGetter = null;
Object idVal = null;
for (Method m : getters) {
String col = m.getName().substring(3).toLowerCase();
Object val = m.invoke(entity);
// 識別 ID 欄位（慣例：getId 或 getXxxId）
if (col.equals("id") || col.equals(entityClass.getSimpleName().toLowerCase() + "id")) {
idGetter = m;
idVal = val;
continue; // ID 不參與 SET，放到 WHERE
}
sql.append(col).append("=?,");
if (val instanceof java.util.Date) {
params.add(new java.sql.Timestamp(((java.util.Date)val).getTime()));
} else {
params.add(val);
}
}
sql.setCharAt(sql.length()-1, ' '); // 移除最後逗號
sql.append("WHERE ").append(idGetter.getName().substring(3).toLowerCase()).append(" = ?");
params.add(idVal);
return executeUpdate(conn, sql.toString(), params.toArray());
}
/** 查詢單筆：SELECT * FROM table WHERE id = ? */
public T findById(Object id) throws Exception {
Connection conn = jdbcUtil.getConnection();
T entity = entityClass.newInstance();
Method idSetter = findIdSetter(entity);
String idCol = idSetter.getName().substring(3).toLowerCase();
String sql = "SELECT * FROM " + entityClass.getSimpleName().toLowerCase()
+ " WHERE " + idCol + " = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
setParameter(ps, 1, id);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
return mapRow(rs, entity);
}
}
}
return null;
}
/** ResultSet → 實體映射 */
private T mapRow(ResultSet rs, T entity) throws Exception {
List<Method> setters = getSetters(entity);
for (Method m : setters) {
String col = m.getName().substring(3).toLowerCase();
Class<?> paramType = m.getParameterTypes()[0];
try {
Object val = null;
if (paramType == String.class) val = rs.getString(col);
else if (paramType == Integer.class || paramType == int.class) val = rs.getInt(col);
else if (paramType == Long.class || paramType == long.class) val = rs.getLong(col);
else if (paramType == Double.class || paramType == double.class) val = rs.getDouble(col);
else if (paramType == java.util.Date.class) val = rs.getTimestamp(col);
else if (paramType == Boolean.class || paramType == boolean.class) val = rs.getBoolean(col);
if (val != null) m.invoke(entity, val);
} catch (SQLException e) {
// 欄位不存在於結果集，忽略
}
}
return entity;
}
/** 找出 ID setter */
private Method findIdSetter(T entity) {
for (Method m : getSetters(entity)) {
String name = m.getName().substring(3);
if (name.equalsIgnoreCase("id") || name.equalsIgnoreCase(entityClass.getSimpleName() + "Id")) {
return m;
}
}
throw new IllegalStateException("找不到 ID setter");
}
/** 通用更新執行 */
private int executeUpdate(Connection conn, String sql, Object... params) throws Exception {
try (PreparedStatement ps = conn.prepareStatement(sql)) {
for (int i = 0; i < params.length; i++) {
setParameter(ps, i+1, params[i]);
}
return ps.executeUpdate();
}
}
/** 參數綁定（支援常見類型） */
private void setParameter(PreparedStatement ps, int idx, Object val) throws SQLException {
if (val == null) { ps.setNull(idx, Types.NULL); return; }
if (val instanceof String) ps.setString(idx, (String)val);
else if (val instanceof Integer) ps.setInt(idx, (Integer)val);
else if (val instanceof Long) ps.setLong(idx, (Long)val);
else if (val instanceof Double) ps.setDouble(idx, (Double)val);
else if (val instanceof java.util.Date) ps.setTimestamp(idx, new java.sql.Timestamp(((java.util.Date)val).getTime()));
else if (val instanceof Boolean) ps.setBoolean(idx, (Boolean)val);
else ps.setObject(idx, val);
}
}
實體類別規範（POJO 慣例）
public class User {
private Long id;
private String name;
private String email;
private java.util.Date createTime;
// 必須提供無參建構子
public User() {}
// Getter/Setter 命名慣例
public Long getId() { return id; }
public void setId(Long id) { this.id = id; }
public String getName() { return name; }
public void setName(String name) { this.name = name; }
// ...
}
使用範例
public class UserDao extends BaseDao {
// 繼承即擁有 save/deleteById/update/findById
}
public class Demo {
public static void main(String[] args) throws Exception {
UserDao dao = new UserDao();
// 新增
User u = new User();
u.setName("張三");
u.setEmail("zhang@example.com");
u.setCreateTime(new Date());
dao.save(u);
// 查詢
User found = dao.findById(1L);
System.out.println(found.getName());
// 修改
found.setEmail("new@example.com");
dao.update(found);
// 刪除
dao.deleteById(1L);
}
}
擴展建議
| 功能 | 實作方向 |
|---|
| 條件查詢 | 新增 findByExample(T example)，反射解析非空屬性組裝 WHERE |
| 分頁查詢 | 整合 PageHelper 或手寫 LIMIT/OFFSET |
| 批次操作 | batchSave(List<T>) 使用 addBatch() |
| 事務管理 | ThreadLocal 綁定 Connection，提供 begin/commit/rollback |
| 快取支援 | 整合 Ehcache/Caffeine，findById 先查快取 |
| SQL 審計 | AOP 記錄執行 SQL、耗時、參數 |
注意事項與限制
- 表名/欄位名慣例：類名→表名，屬性名→欄位名（駝峰轉下劃線需自行處理）
- 複雜關聯：不支援 Join、嵌套物件，需手寫 SQL 或升級 MyBatis
- 效能：反射有開銷，高頻調用建議快取 Method 對象
- 資料庫移植：SQL 方言差異（分頁、自增 ID）需抽象適配
