影楼网站制作/seo自动工具

一、BasicDAO 设计理念

BasicDAO 是基于 DButils 的通用数据访问层抽象，旨在提供：

1. 常见CRUD操作的默认实现
2. 类型安全的查询方法
3. 统一的异常处理机制
4. 可扩展的模板方法

二、基础实现方案

1. 核心抽象类设计

public abstract class BasicDAO<T, ID> {protected final QueryRunner runner;protected final Class<T> entityClass;public BasicDAO(DataSource dataSource, Class<T> entityClass) {this.runner = new QueryRunner(dataSource);this.entityClass = entityClass;}// 获取表名（可被子类覆盖）protected String getTableName() {return entityClass.getSimpleName().toLowerCase();}// 获取主键字段名（默认"id"）protected String getIdColumn() {return "id";}
}

2. 完整CRUD实现

// 插入操作
public ID insert(T entity) throws DataAccessException {try {Map<String, Object> fieldMap = BeanUtils.describe(entity);fieldMap.remove(getIdColumn()); // 排除ID字段String[] columns = fieldMap.keySet().toArray(new String[0]);Object[] values = fieldMap.values().toArray();// 构建动态SQLString sql = String.format("INSERT INTO %s (%s) VALUES (%s)",getTableName(),String.join(",", columns),String.join(",", Collections.nCopies(columns.length, "?")));// 执行插入并返回生成的主键return runner.insert(sql, new ScalarHandler<ID>(), values);} catch (Exception e) {throw new DataAccessException("Insert operation failed", e);}
}// 更新操作
public int update(T entity) throws DataAccessException {try {Map<String, Object> fieldMap = BeanUtils.describe(entity);Object idValue = fieldMap.remove(getIdColumn());String setClause = fieldMap.keySet().stream().map(col -> col + "=?").collect(Collectors.joining(","));String sql = String.format("UPDATE %s SET %s WHERE %s=?",getTableName(),setClause,getIdColumn());// 合并参数值Object[] params = Stream.concat(fieldMap.values().stream(),Stream.of(idValue)).toArray();return runner.update(sql, params);} catch (Exception e) {throw new DataAccessException("Update operation failed", e);}
}// 按ID查询
public Optional<T> findById(ID id) throws DataAccessException {String sql = String.format("SELECT * FROM %s WHERE %s=?",getTableName(),getIdColumn());try {T entity = runner.query(sql, new BeanHandler<>(entityClass), id);return Optional.ofNullable(entity);} catch (SQLException e) {throw new DataAccessException("Find by ID failed", e);}
}// 删除操作
public int delete(ID id) throws DataAccessException {String sql = String.format("DELETE FROM %s WHERE %s=?",getTableName(),getIdColumn());try {return runner.update(sql, id);} catch (SQLException e) {throw new DataAccessException("Delete operation failed", e);}
}

三、高级功能扩展

1. 动态条件查询

// 条件查询构建器
public List<T> findByCriteria(Criteria criteria) {StringBuilder sql = new StringBuilder("SELECT * FROM ").append(getTableName()).append(" WHERE 1=1");List<Object> params = new ArrayList<>();// 处理条件criteria.getConditions().forEach((field, value) -> {sql.append(" AND ").append(field).append("=?");params.add(value);});// 处理排序if (!criteria.getSorts().isEmpty()) {sql.append(" ORDER BY ").append(criteria.getSorts().entrySet().stream().map(e -> e.getKey() + " " + e.getValue()).collect(Collectors.joining(",")));}// 处理分页if (criteria.getPageSize() > 0) {sql.append(" LIMIT ? OFFSET ?");params.add(criteria.getPageSize());params.add(criteria.getOffset());}try {return runner.query(sql.toString(), new BeanListHandler<>(entityClass),params.toArray());} catch (SQLException e) {throw new DataAccessException("Query by criteria failed", e);}
}// 使用示例
List<User> users = userDAO.findByCriteria(new Criteria().addCondition("status", 1).addSort("create_time", "DESC").setPage(1, 20)
);

2. 批量操作增强

// 批量插入
public int[] batchInsert(List<T> entities) {if (entities.isEmpty()) {return new int[0];}// 获取字段列表（排除ID）Map<String, Object> sample = BeanUtils.describe(entities.get(0));sample.remove(getIdColumn());String[] columns = sample.keySet().toArray(new String[0]);// 构建SQLString sql = String.format("INSERT INTO %s (%s) VALUES (%s)",getTableName(),String.join(",", columns),String.join(",", Collections.nCopies(columns.length, "?")));// 准备参数Object[][] params = entities.stream().map(entity -> {try {Map<String, Object> fieldMap = BeanUtils.describe(entity);return Arrays.stream(columns).map(fieldMap::get).toArray();} catch (Exception e) {throw new RuntimeException(e);}}).toArray(Object[][]::new);try {return runner.batch(sql, params);} catch (SQLException e) {throw new DataAccessException("Batch insert failed", e);}
}// 使用存储过程的批量更新
public void batchUpdateStatus(List<ID> ids, int status) {String sql = "{call batch_update_status(?, ?)}";Object[][] params = ids.stream().map(id -> new Object[]{id, status}).toArray(Object[][]::new);try {runner.batch(sql, params);} catch (SQLException e) {throw new DataAccessException("Batch update failed", e);}
}

3. 关联查询处理

// 一对多关联查询
public <R> List<T> findWithRelations(ID id, String relationSql,ResultSetHandler<List<R>> relationHandler,BiConsumer<T, List<R>> relationSetter) {// 查询主对象T mainEntity = findById(id).orElseThrow(() -> new EntityNotFoundException("Entity not found with id: " + id));// 查询关联对象List<R> relations = runner.query(relationSql, relationHandler, id);// 设置关联关系relationSetter.accept(mainEntity, relations);return Collections.singletonList(mainEntity);
}// 使用示例
List<Order> orders = orderDAO.findWithRelations(orderId,"SELECT * FROM order_items WHERE order_id=?",new BeanListHandler<>(OrderItem.class),(order, items) -> order.setItems(items)
);

四、事务管理增强

1. 注解式事务支持

// 事务注解
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface Transactional {int timeout() default 30; // 秒Propagation propagation() default Propagation.REQUIRED;
}// 事务切面
public class TransactionAspect {private DataSource dataSource;public Object manageTransaction(ProceedingJoinPoint pjp) throws Throwable {Method method = ((MethodSignature)pjp.getSignature()).getMethod();Transactional transactional = method.getAnnotation(Transactional.class);Connection conn = null;boolean existingTransaction = TransactionSynchronizationManager.hasConnection(dataSource);try {if (!existingTransaction) {conn = dataSource.getConnection();conn.setAutoCommit(false);conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);TransactionSynchronizationManager.bindConnection(dataSource, conn);}Object result = pjp.proceed();if (!existingTransaction) {conn.commit();}return result;} catch (Exception e) {if (!existingTransaction && conn != null) {conn.rollback();}throw e;} finally {if (!existingTransaction && conn != null) {TransactionSynchronizationManager.unbindConnection(dataSource);DbUtils.closeQuietly(conn);}}}
}// 使用示例
@Transactional
public void transferMoney(Long fromId, Long toId, BigDecimal amount) {accountDAO.decreaseBalance(fromId, amount);accountDAO.increaseBalance(toId, amount);
}

五、性能优化方案

1. 缓存集成

// 带缓存的DAO实现
public class CachedBasicDAO<T, ID> extends BasicDAO<T, ID> {private final Cache<ID, T> cache;public CachedBasicDAO(DataSource dataSource, Class<T> entityClass, Cache<ID, T> cache) {super(dataSource, entityClass);this.cache = cache;}@Overridepublic Optional<T> findById(ID id) {// 先查缓存T cached = cache.getIfPresent(id);if (cached != null) {return Optional.of(cached);}// 缓存未命中则查询数据库Optional<T> result = super.findById(id);result.ifPresent(entity -> cache.put(id, entity));return result;}@Overridepublic int update(T entity) {try {int affected = super.update(entity);if (affected > 0) {// 获取ID值ID id = (ID) PropertyUtils.getProperty(entity, getIdColumn());cache.invalidate(id); // 失效缓存}return affected;} catch (Exception e) {throw new DataAccessException("Update failed", e);}}
}

2. 分页查询优化

// 高性能分页实现
public PageResult<T> queryPage(PageQuery query) {// 主查询String dataSql = String.format("SELECT * FROM %s WHERE %s ORDER BY %s LIMIT ? OFFSET ?",getTableName(),query.getWhereClause(),query.getSortClause());// 计数查询String countSql = String.format("SELECT COUNT(*) FROM %s WHERE %s",getTableName(),query.getWhereClause());try {// 并行执行查询和计数Future<List<T>> dataFuture = executor.submit(() -> runner.query(dataSql, new BeanListHandler<>(entityClass),query.getParametersWithPaging()));Future<Long> countFuture = executor.submit(() -> runner.query(countSql, new ScalarHandler<>(), query.getParametersWithoutPaging()));return new PageResult<>(dataFuture.get(),countFuture.get(),query.getPageNumber(),query.getPageSize());} catch (Exception e) {throw new DataAccessException("Page query failed", e);}
}

六、实战应用示例

1. 用户管理完整实现

// 用户实体
@Data
public class User {private Long id;private String username;private String email;private Integer status;private Date createTime;
}// 用户DAO扩展
public interface UserDAO extends BasicDAO<User, Long> {// 自定义查询方法List<User> findByStatus(int status);// 复杂查询List<User> searchUsers(String keyword, Date startDate, Date endDate);// 批量更新状态int batchUpdateStatus(List<Long> ids, int status);
}// 实现类
public class UserDAOImpl extends BasicDAO<User, Long> implements UserDAO {public UserDAOImpl(DataSource dataSource) {super(dataSource, User.class);}@Overrideprotected String getTableName() {return "sys_user"; // 自定义表名}@Overridepublic List<User> findByStatus(int status) {String sql = "SELECT * FROM sys_user WHERE status=? ORDER BY create_time DESC";try {return runner.query(sql, new BeanListHandler<>(entityClass), status);} catch (SQLException e) {throw new DataAccessException("Query by status failed", e);}}@Overridepublic List<User> searchUsers(String keyword, Date startDate, Date endDate) {StringBuilder sql = new StringBuilder("SELECT * FROM sys_user WHERE 1=1");List<Object> params = new ArrayList<>();if (StringUtils.isNotBlank(keyword)) {sql.append(" AND (username LIKE ? OR email LIKE ?)");params.add("%" + keyword + "%");params.add("%" + keyword + "%");}if (startDate != null) {sql.append(" AND create_time >= ?");params.add(new java.sql.Date(startDate.getTime()));}if (endDate != null) {sql.append(" AND create_time <= ?");params.add(new java.sql.Date(endDate.getTime()));}try {return runner.query(sql.toString(), new BeanListHandler<>(entityClass),params.toArray());} catch (SQLException e) {throw new DataAccessException("User search failed", e);}}@Overridepublic int batchUpdateStatus(List<Long> ids, int status) {String sql = "UPDATE sys_user SET status=? WHERE id IN (" + String.join(",", Collections.nCopies(ids.size(), "?")) + ")";Object[] params = new Object[ids.size() + 1];params[0] = status;for (int i = 0; i < ids.size(); i++) {params[i + 1] = ids.get(i);}try {return runner.update(sql, params);} catch (SQLException e) {throw new DataAccessException("Batch update status failed", e);}}
}

七、最佳实践总结

1. 分层设计：保持DAO层纯净，只做数据访问操作
2. 合理抽象：通过BasicDAO提供通用实现，特殊需求通过子类扩展
3. 异常转换：将SQLException转换为业务异常体系
4. 性能考量：对高频操作添加缓存支持
5. 事务控制：复杂业务使用声明式事务管理
6. SQL安全：始终使用参数化查询防止注入
7. 资源管理：确保所有JDBC资源正确释放

这种DButils+BasicDAO的组合方案特别适合中小型项目，在保持轻量级的同时提供了足够的灵活性，能够满足大多数业务场景的数据访问需求。