JAVAWeb期末复习之Dao层设计

DAO和JDBC积木文档

目录

  1. 数据库连接积木
  2. 基础CRUD积木
  3. 事务管理积木
  4. 连接池管理积木
  5. SQL构建积木
  6. 参数处理积木
  7. 结果集处理积木
  8. 批处理积木

数据库连接积木

1. 基础连接积木

// 数据库连接工具
public class DatabaseConnection {
    private static final String URL = "jdbc:mysql://localhost:3306/dbname";
    private static final String USERNAME = "username";
    private static final String PASSWORD = "password";
    
    // 获取连接
    public static Connection getConnection() throws SQLException {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            return DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (ClassNotFoundException e) {
            throw new SQLException("数据库驱动加载失败", e);
        }
    }
    
    // 关闭连接
    public static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

2. 连接包装积木

// 数据库连接包装器
public class ConnectionWrapper implements AutoCloseable {
    private final Connection connection;
    
    public ConnectionWrapper(Connection connection) {
        this.connection = connection;
    }
    
    public Connection getConnection() {
        return connection;
    }
    
    @Override
    public void close() {
        DatabaseConnection.closeConnection(connection);
    }
    
    // 使用示例
    public static void useConnection() {
        try (ConnectionWrapper wrapper = new ConnectionWrapper(
                DatabaseConnection.getConnection())) {
            // 使用连接进行操作
            Connection conn = wrapper.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

基础CRUD积木

1. 通用DAO接口积木

// 通用DAO接口
public interface BaseDao<T> {
    // 插入数据
    int insert(T entity) throws SQLException;
    
    // 根据ID更新
    int update(T entity) throws SQLException;
    
    // 根据ID删除
    int deleteById(Long id) throws SQLException;
    
    // 根据ID查询
    T findById(Long id) throws SQLException;
    
    // 查询所有
    List<T> findAll() throws SQLException;
}

// 通用DAO实现
public abstract class BaseDaoImpl<T> implements BaseDao<T> {
    protected final Connection connection;
    
    public BaseDaoImpl(Connection connection) {
        this.connection = connection;
    }
    
    // 获取表名
    protected abstract String getTableName();
    
    // 获取实体字段
    protected abstract String[] getFields();
    
    // 实体转换
    protected abstract T mapResultSetToEntity(ResultSet rs) throws SQLException;
}

2. 实体DAO实现积木

// 用户DAO实现示例
public class UserDaoImpl extends BaseDaoImpl<User> {
    private static final String TABLE_NAME = "users";
    private static final String[] FIELDS = {"id", "username", "email", "password"};
    
    public UserDaoImpl(Connection connection) {
        super(connection);
    }
    
    @Override
    public int insert(User user) throws SQLException {
        String sql = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)";
        try (PreparedStatement stmt = connection.prepareStatement(sql, 
                Statement.RETURN_GENERATED_KEYS)) {
            stmt.setString(1, user.getUsername());
            stmt.setString(2, user.getEmail());
            stmt.setString(3, user.getPassword());
            return stmt.executeUpdate();
        }
    }
    
    @Override
    public User findById(Long id) throws SQLException {
        String sql = "SELECT * FROM users WHERE id = ?";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setLong(1, id);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return mapResultSetToEntity(rs);
                }
            }
        }
        return null;
    }
    
    @Override
    protected User mapResultSetToEntity(ResultSet rs) throws SQLException {
        User user = new User();
        user.setId(rs.getLong("id"));
        user.setUsername(rs.getString("username"));
        user.setEmail(rs.getString("email"));
        user.setPassword(rs.getString("password"));
        return user;
    }
}

事务管理积木

1. 事务管理器积木

// 事务管理器
public class TransactionManager {
    private final Connection connection;
    
    public TransactionManager(Connection connection) {
        this.connection = connection;
    }
    
    // 开始事务
    public void beginTransaction() throws SQLException {
        connection.setAutoCommit(false);
    }
    
    // 提交事务
    public void commit() throws SQLException {
        connection.commit();
        connection.setAutoCommit(true);
    }
    
    // 回滚事务
    public void rollback() {
        try {
            connection.rollback();
            connection.setAutoCommit(true);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

2. 事务模板积木

// 事务操作模板
public class TransactionTemplate {
    private final TransactionManager transactionManager;
    
    public TransactionTemplate(TransactionManager transactionManager) {
        this.transactionManager = transactionManager;
    }
    
    // 执行事务操作
    public <T> T execute(TransactionCallback<T> action) throws SQLException {
        try {
            transactionManager.beginTransaction();
            T result = action.doInTransaction();
            transactionManager.commit();
            return result;
        } catch (Exception e) {
            transactionManager.rollback();
            throw e;
        }
    }
    
    // 事务回调接口
    @FunctionalInterface
    public interface TransactionCallback<T> {
        T doInTransaction() throws SQLException;
    }
}

连接池管理积木

1. 简单连接池积木

// 简单数据库连接池
public class SimpleConnectionPool {
    private static final int MAX_POOL_SIZE = 10;
    private final BlockingQueue<Connection> pool;
    
    public SimpleConnectionPool() {
        pool = new LinkedBlockingQueue<>(MAX_POOL_SIZE);
        initializePool();
    }
    
    // 初始化连接池
    private void initializePool() {
        for (int i = 0; i < MAX_POOL_SIZE; i++) {
            try {
                pool.offer(createConnection());
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    // 获取连接
    public Connection getConnection() throws SQLException {
        try {
            Connection conn = pool.poll(5, TimeUnit.SECONDS);
            return conn != null ? conn : createConnection();
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
            throw new SQLException("获取连接超时");
        }
    }
    
    // 归还连接
    public void releaseConnection(Connection conn) {
        if (conn != null) {
            pool.offer(conn);
        }
    }
}

2. 连接池监控积木

// 连接池监控器
public class ConnectionPoolMonitor {
    private final AtomicInteger activeConnections = new AtomicInteger(0);
    private final AtomicInteger totalConnections = new AtomicInteger(0);
    
    // 跟踪连接获取
    public void trackConnectionAcquired() {
        activeConnections.incrementAndGet();
        totalConnections.incrementAndGet();
    }
    
    // 跟踪连接释放
    public void trackConnectionReleased() {
        activeConnections.decrementAndGet();
    }
    
    // 获取连接池状态
    public ConnectionPoolStats getStats() {
        return new ConnectionPoolStats(
            activeConnections.get(),
            totalConnections.get()
        );
    }
}

SQL构建积木

1. SQL构建器积木

// SQL构建器
public class SqlBuilder {
    private final StringBuilder sql = new StringBuilder();
    private final List<Object> params = new ArrayList<>();
    
    // SELECT语句构建
    public SqlBuilder select(String... columns) {
        sql.append("SELECT ");
        sql.append(String.join(", ", columns));
        return this;
    }
    
    // FROM子句构建
    public SqlBuilder from(String table) {
        sql.append(" FROM ").append(table);
        return this;
    }
    
    // WHERE条件构建
    public SqlBuilder where(String condition, Object... values) {
        sql.append(" WHERE ").append(condition);
        Collections.addAll(params, values);
        return this;
    }
    
    // AND条件构建
    public SqlBuilder and(String condition, Object... values) {
        sql.append(" AND ").append(condition);
        Collections.addAll(params, values);
        return this;
    }
    
    // 获取SQL和参数
    public PreparedStatement buildPreparedStatement(Connection conn) 
            throws SQLException {
        PreparedStatement stmt = conn.prepareStatement(sql.toString());
        for (int i = 0; i < params.size(); i++) {
            stmt.setObject(i + 1, params.get(i));
        }
        return stmt;
    }
}

2. 动态SQL构建积木

// 动态SQL构建器
public class DynamicSqlBuilder {
    private final List<String> conditions = new ArrayList<>();
    private final List<Object> params = new ArrayList<>();
    
    // 添加条件
    public DynamicSqlBuilder addConditionIfNotNull(String condition, 
                                                 Object value) {
        if (value != null) {
            conditions.add(condition);
            params.add(value);
        }
        return this;
    }
    
    // 构建WHERE子句
    public String buildWhereClause() {
        if (conditions.isEmpty()) {
            return "";
        }
        return " WHERE " + String.join(" AND ", conditions);
    }
    
    // 获取参数
    public Object[] getParams() {
        return params.toArray();
    }
}

参数处理积木

1. 参数设置积木

// 参数设置工具
public class ParameterSetter {
    // 设置基本类型参数
    public static void setParameters(PreparedStatement stmt, Object... params) 
            throws SQLException {
        for (int i = 0; i < params.length; i++) {
            setParameter(stmt, i + 1, params[i]);
        }
    }
    
    // 设置单个参数
    public static void setParameter(PreparedStatement stmt, int index, 
                                  Object value) throws SQLException {
        if (value == null) {
            stmt.setNull(index, Types.NULL);
        } else if (value instanceof String) {
            stmt.setString(index, (String) value);
        } else if (value instanceof Integer) {
            stmt.setInt(index, (Integer) value);
        } else if (value instanceof Long) {
            stmt.setLong(index, (Long) value);
        } else if (value instanceof Date) {
            stmt.setTimestamp(index, new Timestamp(((Date) value).getTime()));
        } else {
            stmt.setObject(index, value);
        }
    }
}

2. 参数验证积木

// 参数验证器
public class ParameterValidator {
    // 验证非空参数
    public static void validateNotNull(Object value, String paramName) {
        if (value == null) {
            throw new IllegalArgumentException(
                String.format("参数 %s 不能为空", paramName));
        }
    }
    
    // 验证字符串长度
    public static void validateStringLength(String value, String paramName, 
                                         int minLength, int maxLength) {
        if (value != null) {
            int length = value.length();
            if (length < minLength || length > maxLength) {
                throw new IllegalArgumentException(
                    String.format("参数 %s 长度必须在 %d 到 %d 之间", 
                        paramName, minLength, maxLength));
            }
        }
    }
}

结果集处理积木

1. 结果集映射积木

// 结果集映射器
public class ResultSetMapper<T> {
    private final Class<T> type;
    
    public ResultSetMapper(Class<T> type) {
        this.type = type;
    }
    
    // 映射单个对象
    public T mapRow(ResultSet rs) throws SQLException {
        T instance = createInstance();
        for (Field field : type.getDeclaredFields()) {
            mapField(instance, field, rs);
        }
        return instance;
    }
    
    // 映射列表
    public List<T> mapList(ResultSet rs) throws SQLException {
        List<T> list = new ArrayList<>();
        while (rs.next()) {
            list.add(mapRow(rs));
        }
        return list;
    }
    
    // 映射字段
    private void mapField(T instance, Field field, ResultSet rs) 
            throws SQLException {
        try {
            field.setAccessible(true);
            String columnName = getColumnName(field);
            Object value = rs.getObject(columnName);
            if (value != null) {
                field.set(instance, convertValue(value, field.getType()));
            }
        } catch (IllegalAccessException e) {
            throw new SQLException("映射字段失败", e);
        }
    }
}

2. 结果集转换积木

// 结果集转换器
public class ResultSetConverter {
    // 转换为Map
    public static Map<String, Object> toMap(ResultSet rs) throws SQLException {
        Map<String, Object> map = new HashMap<>();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        
        for (int i = 1; i <= columnCount; i++) {
            String columnName = metaData.getColumnName(i);
            Object value = rs.getObject(i);
            map.put(columnName, value);
        }
        
        return map;
    }
    
    // 转换为JSON
    public static String toJson(ResultSet rs) throws SQLException {
        ObjectMapper mapper = new ObjectMapper();
        try {
            return mapper.writeValueAsString(toMap(rs));
        } catch (JsonProcessingException e) {
            throw new SQLException("转换JSON失败", e);
        }
    }
}

批处理积木

1. 批量操作积木

// 批量操作执行器
public class BatchExecutor {
    private static final int BATCH_SIZE = 1000;
    
    // 批量插入
    public static <T> void batchInsert(Connection conn, String sql, 
                                     List<T> items, 
                                     BatchPreparedStatementSetter<T> setter) 
            throws SQLException {
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            int count = 0;
            for (T item : items) {
                setter.setValues(stmt, item);
                stmt.addBatch();
                if (++count % BATCH_SIZE == 0) {
                    stmt.executeBatch();
                }
            }
            if (count % BATCH_SIZE != 0) {
                stmt.executeBatch();
            }
        }
    }
    
    // 批量更新
    public static <T> int[] batchUpdate(Connection conn, String sql, 
                                      List<T> items,
                                      BatchPreparedStatementSetter<T> setter) 
            throws SQLException {
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            for (T item : items) {
                setter.setValues(stmt, item);
                stmt.addBatch();
            }
            return stmt.executeBatch();
        }
    }
}

2. 批量处理监控积木

// 批量处理监控器
public class BatchProcessMonitor {
    private final int totalItems;
    private final AtomicInteger processedItems = new AtomicInteger(0);
    private final long startTime;
    
    public BatchProcessMonitor(int totalItems) {
        this.totalItems = totalItems;
        this.startTime = System.currentTimeMillis();
    }
    
    // 更新进度
    public void updateProgress(int processed) {
        processedItems.addAndGet(processed);
        logProgress();
    }
    
    // 记录进度
    private void logProgress() {
        int processed = processedItems.get();
        double percentage = (processed * 100.0) / totalItems;
        long elapsed = System.currentTimeMillis() - startTime;
        
        System.out.printf("处理进度: %.2f%% (%d/%d) 耗时: %d ms%n",
            percentage, processed, totalItems, elapsed);
    }
}

使用说明

  1. 根据需求选择合适的数据库操作方式
  2. 注意连接管理和资源释放
  3. 合理使用事务
  4. 优化批量操作性能
  5. 做好参数验证和安全处理

注意事项

  1. 及时释放数据库资源
  2. 正确处理SQL注入
  3. 优化大数据量操作
  4. 合理使用连接池
  5. 处理好并发访问
  6. 注意事务隔离级别
  7. 做好异常处理
  8. 保持代码可维护性
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值