七、数据库连接安全

7.1 连接加密

// SSL 连接配置
public class SSLConnectionDemo {

    public Connection getSSLConnection() throws SQLException {
        Properties props = new Properties();
        props.setProperty("user", "username");
        props.setProperty("password", "password");

        // MySQL SSL 配置
        props.setProperty("useSSL", "true");
        props.setProperty("requireSSL", "true");
        props.setProperty("verifyServerCertificate", "true");
        props.setProperty("trustCertificateKeyStoreUrl", "file:/path/to/truststore");
        props.setProperty("trustCertificateKeyStorePassword", "storepass");
        props.setProperty("clientCertificateKeyStoreUrl", "file:/path/to/keystore");
        props.setProperty("clientCertificateKeyStorePassword", "keypass");

        return DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", props);
    }
}

7.2 敏感信息保护

// 使用环境变量存储密码
public class SecureConnection {

    // 从环境变量读取
    private static final String DB_PASSWORD = System.getenv("DB_PASSWORD");

    // 从配置中心读取
    @Value("${database.password}")
    private String dbPassword;

    // 使用加密工具解密
    public String getDecryptedPassword() {
        String encrypted = "encrypted_password";
        return decrypt(encrypted);
    }

    private String decrypt(String encrypted) {
        // 实现解密逻辑
        return "decrypted";
    }
}

// 配置密码加密（Jasypt）
@Configuration
public class EncryptedConfig {

    @Bean
    public StringEncryptor stringEncryptor() {
        StandardPBEStringEncryptor encryptor = new StandardPBEStringEncryptor();
        encryptor.setPassword(System.getenv("JASYPT_PASSWORD"));
        return encryptor;
    }
}

7.3 SQL 注入防护

// 1. 使用 PreparedStatement（推荐）
public User findUser(String username) {
    String sql = "SELECT * FROM users WHERE username = ?";
    // 使用 PreparedStatement，自动转义
    // ...
}

// 2. 使用参数化查询（MyBatis）
// @Select("SELECT * FROM users WHERE username = #{username}")
// 使用 #{} 而非 ${}

// 3. 输入验证
public void validateInput(String username) {
    if (username == null || !username.matches("^[a-zA-Z0-9_]{3,20}$")) {
        throw new IllegalArgumentException("用户名格式错误");
    }
}

// 4. 使用 ORM 框架
// JPA/Hibernate 自动参数化

八、性能优化

8.1 连接池调优

@Component
public class ConnectionPoolTuner {

    // 根据业务特点调整连接池大小
    public DataSource tunedDataSource() {
        HikariConfig config = new HikariConfig();

        // 计算最佳连接数
        // 公式：连接数 = (核心数 * 2) + 有效磁盘数
        int optimalSize = calculateOptimalPoolSize();
        config.setMaximumPoolSize(optimalSize);

        // 根据响应时间要求设置超时
        config.setConnectionTimeout(calculateTimeout());

        // 根据连接使用模式设置生命周期
        config.setMaxLifetime(calculateMaxLifetime());

        return new HikariDataSource(config);
    }

    private int calculateOptimalPoolSize() {
        int cores = Runtime.getRuntime().availableProcessors();
        // 假设 I/O 密集型应用
        return Math.max(5, cores * 2);
    }

    private long calculateTimeout() {
        // 根据业务 SLA 设置
        return 30000; // 30秒
    }
}

8.2 查询优化

@Repository
public class OptimizedQueryDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    // 1. 只查询需要的字段
    public List<User> findUsersWithLimit() {
        String sql = "SELECT id, username FROM users LIMIT 100";  // 避免 SELECT *
        return jdbcTemplate.query(sql, (rs, rowNum) -> {
            User user = new User();
            user.setId(rs.getLong("id"));
            user.setUsername(rs.getString("username"));
            return user;
        });
    }

    // 2. 使用批处理
    public void batchInsertOptimized(List<User> users) {
        String sql = "INSERT INTO users (username, age) VALUES (?, ?)";
        jdbcTemplate.batchUpdate(sql, users, 1000, (ps, user) -> {
            ps.setString(1, user.getUsername());
            ps.setInt(2, user.getAge());
        });
    }

    // 3. 使用分页查询
    public List<User> findUsersByPage(int page, int size) {
        String sql = "SELECT * FROM users ORDER BY id LIMIT ? OFFSET ?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class), 
                                   size, (page - 1) * size);
    }

    // 4. 使用索引优化
    // 确保查询条件字段有索引
    public User findByEmail(String email) {
        // 确保 email 字段有唯一索引
        String sql = "SELECT * FROM users WHERE email = ?";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), email);
    }
}

8.3 读写分离

@Configuration
public class ReadWriteSplittingConfig {

    @Bean
    @Primary
    public DataSource routingDataSource() {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("write", writeDataSource());
        targetDataSources.put("read1", readDataSource1());
        targetDataSources.put("read2", readDataSource2());

        RoutingDataSource routingDataSource = new RoutingDataSource();
        routingDataSource.setDefaultTargetDataSource(writeDataSource());
        routingDataSource.setTargetDataSources(targetDataSources);

        return routingDataSource;
    }

    @Bean
    public DataSource writeDataSource() {
        // 主库配置
        return DataSourceBuilder.create()
            .url("jdbc:mysql://master:3306/mydb")
            .username("root")
            .password("password")
            .build();
    }

    @Bean
    public DataSource readDataSource1() {
        // 从库1配置
        return DataSourceBuilder.create()
            .url("jdbc:mysql://slave1:3306/mydb")
            .username("readonly")
            .password("readonly")
            .build();
    }

    // 动态数据源路由
    public static class RoutingDataSource extends AbstractRoutingDataSource {
        private static final ThreadLocal<String> context = new ThreadLocal<>();

        public static void setDataSource(String dataSource) {
            context.set(dataSource);
        }

        @Override
        protected Object determineCurrentLookupKey() {
            return context.get();
        }
    }
}

// 使用 AOP 实现读写分离
@Aspect
@Component
public class ReadWriteAspect {

    @Around("@annotation(Transactional)")
    public Object around(ProceedingJoinPoint pjp) throws Throwable {
        Transactional transactional = 
            ((MethodSignature) pjp.getSignature()).getMethod().getAnnotation(Transactional.class);

        if (transactional != null && transactional.readOnly()) {
            // 读操作，使用从库
            RoutingDataSource.setDataSource("read");
        } else {
            // 写操作，使用主库
            RoutingDataSource.setDataSource("write");
        }

        try {
            return pjp.proceed();
        } finally {
            RoutingDataSource.setDataSource(null);
        }
    }
}

九、监控与诊断

9.1 连接监控

@Component
public class ConnectionMonitor {

    private static final Logger log = LoggerFactory.getLogger(ConnectionMonitor.class);

    @Autowired
    private DataSource dataSource;

    // 监控连接池状态
    @Scheduled(fixedDelay = 30000)
    public void monitorConnectionPool() {
        if (dataSource instanceof HikariDataSource) {
            HikariDataSource hikari = (HikariDataSource) dataSource;
            HikariPoolMXBean pool = hikari.getHikariPoolMXBean();

            log.info("=== Connection Pool Statistics ===");
            log.info("Active Connections: {}", pool.getActiveConnections());
            log.info("Idle Connections: {}", pool.getIdleConnections());
            log.info("Total Connections: {}", pool.getTotalConnections());
            log.info("Threads Waiting: {}", pool.getThreadsAwaitingConnection());
            log.info("===================================");
        }
    }

    // 慢查询监控
    @Aspect
    @Component
    public static class SlowQueryMonitor {

        private static final long SLOW_THRESHOLD = 1000; // 1秒

        @Around("execution(* com.example.dao.*.*(..))")
        public Object monitor(ProceedingJoinPoint pjp) throws Throwable {
            long start = System.currentTimeMillis();
            try {
                return pjp.proceed();
            } finally {
                long duration = System.currentTimeMillis() - start;
                if (duration > SLOW_THRESHOLD) {
                    log.warn("慢查询: {}.{} - 耗时: {}ms", 
                             pjp.getTarget().getClass().getSimpleName(),
                             pjp.getSignature().getName(), duration);
                }
            }
        }
    }
}

9.2 性能指标收集

@Component
public class DatabaseMetrics {

    private final MeterRegistry meterRegistry;
    private final DataSource dataSource;

    public DatabaseMetrics(MeterRegistry meterRegistry, DataSource dataSource) {
        this.meterRegistry = meterRegistry;
        this.dataSource = dataSource;
        initMetrics();
    }

    private void initMetrics() {
        // 连接池指标
        if (dataSource instanceof HikariDataSource) {
            HikariDataSource hikari = (HikariDataSource) dataSource;
            HikariPoolMXBean pool = hikari.getHikariPoolMXBean();

            Gauge.builder("hikari.active.connections", pool, HikariPoolMXBean::getActiveConnections)
                .register(meterRegistry);

            Gauge.builder("hikari.idle.connections", pool, HikariPoolMXBean::getIdleConnections)
                .register(meterRegistry);

            Gauge.builder("hikari.total.connections", pool, HikariPoolMXBean::getTotalConnections)
                .register(meterRegistry);

            Gauge.builder("hikari.threads.waiting", pool, HikariPoolMXBean::getThreadsAwaitingConnection)
                .register(meterRegistry);
        }

        // 查询计数器
        Timer.Sample sample = Timer.start(meterRegistry);
        // 记录查询耗时
    }
}

十、故障排查与常见问题

10.1 连接超时问题

public class ConnectionTimeoutHandler {

    // 1. 检查网络连通性
    public boolean checkNetwork(String host, int port) {
        try (Socket socket = new Socket()) {
            socket.connect(new InetSocketAddress(host, port), 5000);
            return true;
        } catch (IOException e) {
            return false;
        }
    }

    // 2. 检查数据库是否可连接
    public boolean checkDatabase(DataSource dataSource) {
        try (Connection conn = dataSource.getConnection()) {
            return conn.isValid(5);
        } catch (SQLException e) {
            return false;
        }
    }

    // 3. 自动重连机制
    public <T> T executeWithRetry(Supplier<T> operation) {
        int maxRetries = 3;
        int retryDelay = 1000;

        for (int i = 0; i < maxRetries; i++) {
            try {
                return operation.get();
            } catch (Exception e) {
                if (i == maxRetries - 1) throw e;
                try {
                    Thread.sleep(retryDelay);
                } catch (InterruptedException ie) {
                    Thread.currentThread().interrupt();
                    throw new RuntimeException(ie);
                }
            }
        }
        throw new RuntimeException("操作失败");
    }
}

10.2 连接泄漏排查

@Component
public class LeakDetection {

    // 使用 ThreadLocal 追踪连接
    private static final ThreadLocal<Map<String, StackTraceElement[]>> CONNECTION_TRACE = 
        new ThreadLocal<>();

    @Autowired
    private DataSource dataSource;

    public Connection getConnection() throws SQLException {
        Connection conn = dataSource.getConnection();

        // 记录获取连接的位置
        Map<String, StackTraceElement[]> trace = CONNECTION_TRACE.get();
        if (trace == null) {
            trace = new HashMap<>();
            CONNECTION_TRACE.set(trace);
        }
        trace.put(conn.toString(), Thread.currentThread().getStackTrace());

        return new LeakAwareConnection(conn);
    }

    // 监控连接泄漏
    @Scheduled(fixedDelay = 60000)
    public void detectLeaks() {
        if (dataSource instanceof HikariDataSource) {
            HikariDataSource hikari = (HikariDataSource) dataSource;
            HikariPoolMXBean pool = hikari.getHikariPoolMXBean();

            // HikariCP 自带泄漏检测
            // 配置 leakDetectionThreshold 参数
        }
    }

    // 包装连接，自动记录关闭
    private static class LeakAwareConnection implements Connection {
        private final Connection delegate;
        private final long createTime;

        LeakAwareConnection(Connection delegate) {
            this.delegate = delegate;
            this.createTime = System.currentTimeMillis();
        }

        @Override
        public void close() throws SQLException {
            long lifeTime = System.currentTimeMillis() - createTime;
            if (lifeTime > 60000) {
                log.warn("连接使用时间过长: {}ms", lifeTime);
            }
            delegate.close();
        }

        // 其他方法委托给 delegate
    }
}

数据库连接的世界广阔而深刻，愿本文成为你后端开发之路上的重要指南。持续学习，深入实践，你一定能成为数据库连接方面的专家！
来源：
https://ljtgc.cn/