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. 保持代码可维护性