BaseDao 通用查詢方法設計與實現
一、通用查詢方法設計思路
1. 核心查詢功能矩陣
| 查詢類型 | 方法名 | 功能說明 | 復雜度 |
|---|
| 主鍵查詢 | findById() | 根據主鍵獲取單個實體 | ? |
| 全量查詢 | findAll() | 獲取全部實體 | ? |
| 條件查詢 | findByCondition() | 動態條件查詢 | ??? |
| 分頁查詢 | findPage() | 分頁結果集 | ???? |
| 排序查詢 | findWithOrder() | 帶排序的結果集 | ?? |
| 投影查詢 | findProjection() | 返回指定字段 | ??? |
| 聚合查詢 | executeAggregate() | 執行聚合函數 | ???? |
2. 類關系設計
二、完整實現代碼
1. Page 分頁對象
public class Page<T> {private int pageNum; private int pageSize; private int total; private List<T> content; public Page(int pageNum, int pageSize, int total, List<T> content) {this.pageNum = pageNum;this.pageSize = pageSize;this.total = total;this.content = content;}public int getTotalPages() {return (int) Math.ceil((double) total / pageSize);}public boolean isFirst() {return pageNum == 1;}public boolean isLast() {return pageNum >= getTotalPages();}
}
2. BaseDao 通用查詢實現
import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;public abstract class BaseDao<T, ID> {protected final DataSource dataSource;protected final Class<T> entityClass;protected final String tableName;protected final String primaryKey;@SuppressWarnings("unchecked")public BaseDao(DataSource dataSource) {this.dataSource = dataSource;this.entityClass = (Class<T>) ((java.lang.reflect.ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[0];this.tableName = resolveTableName();this.primaryKey = resolvePrimaryKey();}public T findById(ID id) {String condition = primaryKey + " = ?";List<T> result = findByCondition(condition, id);return result.isEmpty() ? null : result.get(0);}public List<T> findAll() {return findByCondition(null);}public List<T> findByCondition(String condition, Object... params) {String sql = buildSelectSql(null, condition);try (Connection conn = dataSource.getConnection();PreparedStatement pstmt = conn.prepareStatement(sql)) {setParameters(pstmt, params);try (ResultSet rs = pstmt.executeQuery()) {List<T> result = new ArrayList<>();while (rs.next()) {result.add(mapRowToEntity(rs));}return result;}} catch (SQLException e) {throw new RuntimeException("Query by condition failed", e);}}public Page<T> findPage(int pageNum, int pageSize) {return findPageByCondition(pageNum, pageSize, null);}public Page<T> findPageByCondition(int pageNum, int pageSize, String condition, Object... params) {int offset = (pageNum - 1) * pageSize;String sql = buildSelectSql(null, condition) + " LIMIT " + pageSize + " OFFSET " + offset;List<T> content;try (Connection conn = dataSource.getConnection();PreparedStatement pstmt = conn.prepareStatement(sql)) {setParameters(pstmt, params);try (ResultSet rs = pstmt.executeQuery()) {content = new ArrayList<>();while (rs.next()) {content.add(mapRowToEntity(rs));}}} catch (SQLException e) {throw new RuntimeException("Paged query failed", e);}int total = countByCondition(condition, params);return new Page<>(pageNum, pageSize, total, content);}public List<Map<String, Object>> findProjection(String[] columns, String condition, Object... params) {String sql = buildSelectSql(columns, condition);try (Connection conn = dataSource.getConnection();PreparedStatement pstmt = conn.prepareStatement(sql)) {setParameters(pstmt, params);try (ResultSet rs = pstmt.executeQuery()) {List<Map<String, Object>> result = new ArrayList<>();ResultSetMetaData metaData = rs.getMetaData();int columnCount = metaData.getColumnCount();while (rs.next()) {Map<String, Object> row = new LinkedHashMap<>();for (int i = 1; i <= columnCount; i++) {String columnName = metaData.getColumnLabel(i);row.put(columnName, rs.getObject(i));}result.add(row);}return result;}} catch (SQLException e) {throw new RuntimeException("Projection query failed", e);}}public <R> R executeAggregate(String function, String column, String condition, Class<R> resultType,Object... params) {String sql = "SELECT " + function + "(" + column + ") FROM " + tableName;if (condition != null && !condition.trim().isEmpty()) {sql += " WHERE " + condition;}try (Connection conn = dataSource.getConnection();PreparedStatement pstmt = conn.prepareStatement(sql)) {setParameters(pstmt, params);try (ResultSet rs = pstmt.executeQuery()) {if (rs.next()) {return resultType.cast(rs.getObject(1));}return null;}} catch (SQLException e) {throw new RuntimeException("Aggregate query failed", e);}}public int countByCondition(String condition, Object... params) {return executeAggregate("COUNT", primaryKey, condition, Integer.class, params);}protected String buildSelectSql(String[] columns, String condition) {String selectedColumns = "*";if (columns != null && columns.length > 0) {selectedColumns = String.join(", ", columns);}StringBuilder sql = new StringBuilder("SELECT ").append(selectedColumns).append(" FROM ").append(tableName);if (condition != null && !condition.trim().isEmpty()) {sql.append(" WHERE ").append(condition);}return sql.toString();}protected void setParameters(PreparedStatement pstmt, Object... params) throws SQLException {if (params != null) {for (int i = 0; i < params.length; i++) {pstmt.setObject(i + 1, params[i]);}}}protected T mapRowToEntity(ResultSet rs) throws SQLException {try {T entity = entityClass.getDeclaredConstructor().newInstance();ResultSetMetaData metaData = rs.getMetaData();int columnCount = metaData.getColumnCount();for (int i = 1; i <= columnCount; i++) {String columnName = metaData.getColumnLabel(i);Object value = rs.getObject(i);Field field = findFieldForColumn(columnName);if (field != null) {field.setAccessible(true);if (value instanceof java.sql.Timestamp && field.getType() == java.time.LocalDateTime.class) {value = ((java.sql.Timestamp) value).toLocalDateTime();} else if (value instanceof java.sql.Date && field.getType() == java.time.LocalDate.class) {value = ((java.sql.Date) value).toLocalDate();}field.set(entity, value);}}return entity;} catch (Exception e) {throw new SQLException("Failed to map row to entity", e);}}protected Field findFieldForColumn(String columnName) {try {return entityClass.getDeclaredField(columnName);} catch (NoSuchFieldException e1) {String camelCaseName = snakeToCamel(columnName);try {return entityClass.getDeclaredField(camelCaseName);} catch (NoSuchFieldException e2) {for (Field field : entityClass.getDeclaredFields()) {if (field.getName().equalsIgnoreCase(columnName) || field.getName().equalsIgnoreCase(camelCaseName)) {return field;}}return null;}}}protected String resolveTableName() {if (entityClass.isAnnotationPresent(Table.class)) {return entityClass.getAnnotation(Table.class).name();}return camelToSnake(entityClass.getSimpleName());}protected String resolvePrimaryKey() {for (Field field : entityClass.getDeclaredFields()) {if (field.isAnnotationPresent(Id.class)) {return field.getName();}}return "id";}protected String camelToSnake(String str) {return str.replaceAll("([a-z])([A-Z])", "$1_$2").toLowerCase();}protected String snakeToCamel(String str) {StringBuilder result = new StringBuilder();boolean nextUpper = false;for (int i = 0; i < str.length(); i++) {char c = str.charAt(i);if (c == '_') {nextUpper = true;} else {if (nextUpper) {result.append(Character.toUpperCase(c));nextUpper = false;} else {result.append(Character.toLowerCase(c));}}}return result.toString();}
}
3. 注解定義
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface Table {String name();
}@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTentionPolicy.RUNTIME)
public @interface Id {}
4. 實體類示例
@Table(name = "user_info")
public class User {@Idprivate Long id;private String userName;private String email;private LocalDateTime createTime;private Integer status;
}
三、使用示例
1. 基礎查詢
HikariDataSource dataSource = new HikariDataSource();
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
dataSource.setUsername("user");
dataSource.setPassword("password");
UserDao userDao = new UserDao(dataSource);
User user = userDao.findById(1L);
System.out.println("User: " + user.getUserName());
List<User> allUsers = userDao.findAll();
System.out.println("Total users: " + allUsers.size());
List<User> activeUsers = userDao.findByCondition("status = ? AND create_time > ?", 1, LocalDateTime.now().minusMonths(1)
);
System.out.println("Active users: " + activeUsers.size());
2. 分頁查詢
Page<User> userPage = userDao.findPage(2, 10);
System.out.println("Page " + userPage.getPageNum() + " of " + userPage.getTotalPages());
System.out.println("Records: " + userPage.getContent().size());
Page<User> activeUserPage = userDao.findPageByCondition(1, 20, "status = ?", 1
);
System.out.println("Active users: " + activeUserPage.getTotal());
3. 投影查詢
List<Map<String, Object>> userProjections = userDao.findProjection(new String[]{"user_name", "email"}, "status = ?", 1
);userProjections.forEach(projection -> {System.out.println(projection.get("user_name") + ": " + projection.get("email"));
});
4. 聚合查詢
Integer activeCount = userDao.executeAggregate("COUNT", "id", "status = ?", Integer.class, 1
);
System.out.println("Active users: " + activeCount);
LocalDateTime lastRegistration = userDao.executeAggregate("MAX", "create_time", null, LocalDateTime.class
);
System.out.println("Last registration: " + lastRegistration);
四、高級查詢功能擴展
1. 動態排序支持
public List<T> findByConditionWithOrder(String condition, String orderBy, Object... params) {String sql = buildSelectSql(null, condition);if (orderBy != null && !orderBy.trim().isEmpty()) {sql += " ORDER BY " + orderBy;}return executeQuery(sql, params);
}private List<T> executeQuery(String sql, Object... params) {try (Connection conn = dataSource.getConnection();PreparedStatement pstmt = conn.prepareStatement(sql)) {setParameters(pstmt, params);try (ResultSet rs = pstmt.executeQuery()) {List<T> result = new ArrayList<>();while (rs.next()) {result.add(mapRowToEntity(rs));}return result;}} catch (SQLException e) {throw new RuntimeException("Query failed", e);}
}
2. 聯表查詢支持
public List<T> findBySql(String sql, Object... params) {return executeQuery(sql, params);
}
protected T mapRowToEntityWithJoins(ResultSet rs) throws SQLException {return mapRowToEntity(rs);
}
3. 緩存集成
public class CachedBaseDao<T, ID> extends BaseDao<T, ID> {private final Cache<ID, T> cache;public CachedBaseDao(DataSource dataSource, Cache<ID, T> cache) {super(dataSource);this.cache = cache;}@Overridepublic T findById(ID id) {T cached = cache.get(id);if (cached != null) {return cached;}T entity = super.findById(id);if (entity != null) {cache.put(id, entity);}return entity;}@Overridepublic void update(T entity) {super.update(entity);ID id = (ID) ReflectionUtils.getFieldValue(entity, primaryKey);cache.put(id, entity);}
}
五、性能優化策略
1. 查詢性能優化矩陣
| 場景 | 優化策略 | 實現方式 |
|---|
| 頻繁查詢 | 緩存結果 | 集成Caffeine/Redis |
| 大結果集 | 流式處理 | 使用ResultSet流式讀取 |
| 復雜查詢 | 預編譯SQL | 緩存PreparedStatement |
| 字段映射 | 反射緩存 | 緩存Field元數據 |
| 批量查詢 | IN查詢優化 | 使用JOIN代替多個OR |
2. 流式查詢實現
public void streamByCondition(String condition, Consumer<T> consumer, Object... params) {String sql = buildSelectSql(null, condition);try (Connection conn = dataSource.getConnection();PreparedStatement pstmt = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {pstmt.setFetchSize(Integer.MIN_VALUE); setParameters(pstmt, params);try (ResultSet rs = pstmt.executeQuery()) {while (rs.next()) {consumer.accept(mapRowToEntity(rs));}}} catch (SQLException e) {throw new RuntimeException("Stream query failed", e);}
}
3. 預編譯語句緩存
private final Map<String, PreparedStatement> statementCache = new ConcurrentHashMap<>();protected PreparedStatement prepareStatement(Connection conn, String sql) throws SQLException {return statementCache.computeIfAbsent(sql, key -> {try {return conn.prepareStatement(key);} catch (SQLException e) {throw new RuntimeException("Failed to prepare statement", e);}});
}
六、安全注意事項
1. 查詢安全防護
| 風險 | 防護措施 | 實現方式 |
|---|
| SQL注入 | 參數化查詢 | 使用PreparedStatement |
| 敏感數據 | 字段過濾 | 投影查詢指定字段 |
| 批量查詢 | 結果集限制 | 添加MAX_ROWS限制 |
| 日志泄露 | 脫敏處理 | 不記錄完整結果集 |
| 權限控制 | 行級權限 | 基礎條件自動附加 |
2. 自動條件附加
protected String applySecurityConditions(String originalCondition) {String userId = SecurityContext.getCurrentUserId();if (userId == null) {throw new SecurityException("Unauthorized access");}String securityCondition = "user_id = '" + userId + "'";if (originalCondition == null || originalCondition.isEmpty()) {return securityCondition;}return "(" + originalCondition + ") AND " + securityCondition;
}
七、總結與最佳實踐
1. BaseDao查詢方法使用場景
| 查詢類型 | 適用場景 | 性能建議 |
|---|
| findById | 單條記錄獲取 | 添加緩存 |
| findAll | 小型表全量查詢 | 避免大表使用 |
| findByCondition | 動態條件查詢 | 確保條件字段索引 |
| findPage | 列表展示 | 優化分頁SQL |
| findProjection | 報表生成 | 只查詢必要字段 |
| executeAggregate | 統計分析 | 數據庫聚合優于內存計算 |
2. 設計原則檢查表
- [ ] 接口與實現分離
- [ ] 支持動態條件查詢
- [ ] 分頁查詢獨立封裝
- [ ] 安全參數綁定
- [ ] 類型安全結果映射
- [ ] 支持投影查詢
- [ ] 提供聚合函數支持
- [ ] 異常統一處理
- [ ] 擴展點開放（如自定義映射）
3. 性能優化檢查表
- [ ] 大結果集使用流式處理
- [ ] 頻繁查詢添加緩存
- [ ] 預編譯語句重用
- [ ] 反射元數據緩存
- [ ] 避免N+1查詢問題
- [ ] 分頁查詢優化（Keyset分頁）
最佳實踐總結：BaseDao的通用查詢方法為數據訪問層提供了強大的基礎能力，但在實際項目中需根據具體需求進行擴展和優化。對于復雜查詢場景，建議結合使用MyBatis等專業ORM框架，同時注意查詢性能和安全防護。
)
