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框架，同时注意查询性能和安全防护。
)
