HikariCP连接池案例

本文目录导读：

1. 基础配置案例
2. 多数据源配置案例
3. 自定义连接池监控案例
4. 生产环境最佳实践案例
5. 使用HikariCP的DAO层案例
6. 性能优化配置案例
7. 常见问题排查

我来为你提供几个HikariCP连接池的实战案例,从基础配置到高级用法。

基础配置案例

Spring Boot 配置

# application.yml
spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    url: jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver
    hikari:
      # 池名称
      pool-name: MyHikariPool
      # 最小空闲连接数
      minimum-idle: 5
      # 最大连接池大小
      maximum-pool-size: 20
      # 空闲连接超时时间
      idle-timeout: 300000
      # 连接最大生命周期
      max-lifetime: 1800000
      # 连接超时时间
      connection-timeout: 30000
      # 自动提交
      auto-commit: true
      # 连接测试查询
      connection-test-query: SELECT 1
      # 连接初始化SQL
      connection-init-sql: SET NAMES utf8mb4
      # 统计监控
      metrics-enabled: true

Java 配置类

@Configuration
public class DataSourceConfig {
    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.hikari")
    public DataSource dataSource() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test?useSSL=false");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        // HikariCP 配置
        dataSource.setPoolName("MyHikariPool");
        dataSource.setMinimumIdle(5);
        dataSource.setMaximumPoolSize(20);
        dataSource.setIdleTimeout(300000);
        dataSource.setMaxLifetime(1800000);
        dataSource.setConnectionTimeout(30000);
        dataSource.setAutoCommit(true);
        dataSource.setConnectionTestQuery("SELECT 1");
        // 自定义配置
        HikariConfig config = new HikariConfig();
        config.setInitializationFailTimeout(1);
        config.setConnectionInitSql("SET NAMES utf8mb4");
        return dataSource;
    }
}

多数据源配置案例

多数据源配置

@Configuration
public class MultiDataSourceConfig {
    // 主数据源
    @Bean(name = "primaryDataSource")
    @Primary
    @ConfigurationProperties("spring.datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create()
            .type(HikariDataSource.class)
            .build();
    }
    // 从数据源
    @Bean(name = "secondaryDataSource")
    @ConfigurationProperties("spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create()
            .type(HikariDataSource.class)
            .build();
    }
    // JdbcTemplate 配置
    @Bean(name = "primaryJdbcTemplate")
    public JdbcTemplate primaryJdbcTemplate(
            @Qualifier("primaryDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
    @Bean(name = "secondaryJdbcTemplate")
    public JdbcTemplate secondaryJdbcTemplate(
            @Qualifier("secondaryDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

自定义连接池监控案例

HikariCP 监控

@Component
public class HikariPoolMonitor {
    private static final Logger logger = LoggerFactory.getLogger(HikariPoolMonitor.class);
    private final HikariDataSource hikariDataSource;
    public HikariPoolMonitor(HikariDataSource hikariDataSource) {
        this.hikariDataSource = hikariDataSource;
    }
    @Scheduled(fixedDelay = 60000) // 每分钟执行一次
    public void monitorPool() {
        HikariPoolMXBean poolMXBean = hikariDataSource.getHikariPoolMXBean();
        logger.info("========== HikariCP 池状态 ==========");
        logger.info("池名称: {}", hikariDataSource.getPoolName());
        logger.info("活动连接数: {}", poolMXBean.getActiveConnections());
        logger.info("空闲连接数: {}", poolMXBean.getIdleConnections());
        logger.info("等待连接线程数: {}", poolMXBean.getThreadsAwaitingConnection());
        logger.info("总连接数: {}", poolMXBean.getTotalConnections());
        logger.info("=====================================");
        // 检查连接池健康状态
        checkPoolHealth(poolMXBean);
    }
    private void checkPoolHealth(HikariPoolMXBean poolMXBean) {
        int activeConnections = poolMXBean.getActiveConnections();
        int totalConnections = poolMXBean.getTotalConnections();
        // 使用率超过80%时发送告警
        if (totalConnections > 0 && 
            (double) activeConnections / totalConnections > 0.8) {
            logger.warn("连接池使用率过高: {}/{}", 
                activeConnections, totalConnections);
            // 发送告警邮件或消息
            sendAlert();
        }
        // 等待连接线程过多时告警
        if (poolMXBean.getThreadsAwaitingConnection() > 10) {
            logger.error("连接池等待线程过多: {}", 
                poolMXBean.getThreadsAwaitingConnection());
        }
    }
    private void sendAlert() {
        // 实现告警逻辑
        logger.info("发送告警通知...");
    }
}

生产环境最佳实践案例

生产级配置

@Configuration
public class ProductionDataSourceConfig {
    @Bean
    @ConfigurationProperties("spring.datasource.hikari")
    public DataSource dataSource() {
        HikariConfig config = new HikariConfig();
        // 基础配置
        config.setPoolName("ProductionDB");
        config.setJdbcUrl("jdbc:mysql://db-server:3306/prod_db");
        config.setUsername("prod_user");
        config.setPassword("prod_password");
        // 连接池核心配置
        config.setMaximumPoolSize(50);
        config.setMinimumIdle(10);
        config.setIdleTimeout(300000);  // 5分钟
        config.setMaxLifetime(1200000); // 20分钟
        // 性能优化
        config.setConnectionTimeout(3000);  // 3秒连接超时
        config.setValidationTimeout(3000);  // 3秒验证超时
        config.setLeakDetectionThreshold(60000); // 1分钟泄漏检测
        // 性能配置
        config.setReadOnly(false);
        config.setIsolateInternalQueries(true);
        // 数据库特定优化
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        config.addDataSourceProperty("useServerPrepStmts", "true");
        config.addDataSourceProperty("useLocalSessionState", "true");
        config.addDataSourceProperty("rewriteBatchedStatements", "true");
        config.addDataSourceProperty("cacheResultSetMetadata", "true");
        config.addDataSourceProperty("cacheServerConfiguration", "true");
        config.addDataSourceProperty("elideSetAutoCommits", "true");
        config.addDataSourceProperty("maintainTimeStats", "false");
        return new HikariDataSource(config);
    }
    // 连接池健康检查
    @Component
    public static class PoolHealthChecker {
        private final DataSource dataSource;
        public PoolHealthChecker(@Qualifier("dataSource") DataSource dataSource) {
            this.dataSource = dataSource;
        }
        @Scheduled(fixedDelay = 30000) // 每30秒检查
        public void healthCheck() {
            try (Connection conn = dataSource.getConnection()) {
                try (Statement stmt = conn.createStatement()) {
                    try (ResultSet rs = stmt.executeQuery("SELECT 1")) {
                        if (rs.next() && rs.getInt(1) == 1) {
                            logger.debug("数据库连接正常");
                        }
                    }
                }
            } catch (SQLException e) {
                logger.error("数据库连接异常: ", e);
                // 触发告警
                triggerAlert(e);
            }
        }
    }
}

使用HikariCP的DAO层案例

用户数据访问对象

@Repository
public class UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    private static final String SELECT_USER_BY_ID = 
        "SELECT * FROM users WHERE id = ?";
    private static final String INSERT_USER = 
        "INSERT INTO users (username, email, created_at) VALUES (?, ?, ?)";
    private static final String UPDATE_USER = 
        "UPDATE users SET email = ? WHERE id = ?";
    private static final String DELETE_USER = 
        "DELETE FROM users WHERE id = ?";
    // 使用连接池获取连接
    public User getUserById(Long id) {
        return jdbcTemplate.queryForObject(
            SELECT_USER_BY_ID,
            new Object[]{id},
            (resultSet, rowNum) -> mapUser(resultSet)
        );
    }
    // 批量操作示例
    public void batchInsert(List<User> users) {
        jdbcTemplate.batchUpdate(
            INSERT_USER,
            users,
            100,
            (ps, user) -> {
                ps.setString(1, user.getUsername());
                ps.setString(2, user.getEmail());
                ps.setTimestamp(3, Timestamp.from(LocalDateTime.now()
                    .toInstant(ZoneOffset.UTC)));
            }
        );
    }
    // 事务处理示例
    @Transactional
    public void updateUserInTransaction(Long userId, String email) {
        jdbcTemplate.update(UPDATE_USER, email, userId);
        // 其他数据库操作...
    }
    // 使用编程式事务
    public void programmableTransaction() {
        TransactionTemplate transactionTemplate = 
            new TransactionTemplate(transactionManager);
        transactionTemplate.execute(status -> {
            try {
                // 业务逻辑
                jdbcTemplate.update(UPDATE_USER, "new@email.com", 1L);
                return null;
            } catch (Exception e) {
                status.setRollbackOnly();
                throw e;
            }
        });
    }
    private User mapUser(ResultSet rs) throws SQLException {
        User user = new User();
        user.setId(rs.getLong("id"));
        user.setUsername(rs.getString("username"));
        user.setEmail(rs.getString("email"));
        user.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
        return user;
    }
}

性能优化配置案例

根据不同场景调整配置

public class PoolPerformanceConfigurator {
    // 读多写少场景
    public static HikariConfig configureReadHeavy() {
        HikariConfig config = new HikariConfig();
        // 较大连接池，因为读操作通常需要更多并发连接
        config.setMaximumPoolSize(100);
        config.setMinimumIdle(20);
        // 缩短空闲超时，快速释放空闲连接
        config.setIdleTimeout(180000); // 3分钟
        // 优化缓存性能
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "500");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "4096");
        return config;
    }
    // 写多读少场景
    public static HikariConfig configureWriteHeavy() {
        HikariConfig config = new HikariConfig();
        // 较小连接池，减少锁竞争
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(5);
        // 批处理优化
        config.addDataSourceProperty("rewriteBatchedStatements", "true");
        config.addDataSourceProperty("useLocalSessionState", "true");
        return config;
    }
    // OLAP场景
    public static HikariConfig configureOLAP() {
        HikariConfig config = new HikariConfig();
        // 大量复杂查询，需要更多内存和连接
        config.setMaximumPoolSize(80);
        config.setMinimumIdle(10);
        // 优化大结果集
        config.addDataSourceProperty("useCursorFetch", "true");
        config.addDataSourceProperty("defaultFetchSize", "100");
        return config;
    }
    // 高并发瞬时报文场景
    public static HikariConfig configureHighConcurrency() {
        HikariConfig config = new HikariConfig();
        // 连接池大小按峰值需求配置
        config.setMaximumPoolSize(200);
        config.setMinimumIdle(50);
        // 极短超时，快速失败
        config.setConnectionTimeout(1000);  // 1秒
        config.setValidationTimeout(1000);  // 1秒
        // 快速回收空闲连接
        config.setIdleTimeout(60000);  // 1分钟
        config.setMaxLifetime(300000); // 5分钟
        return config;
    }
}

常见问题排查

连接池泄漏检测

@Component
public class ConnectionLeakDetector {
    @Autowired
    private HikariDataSource dataSource;
    @Scheduled(fixedDelay = 120000) // 每2分钟检查
    public void detectLeaks() {
        // 获取当前活动连接信息
        String leakLog = dataSource.getHikariConfigMXBean().toString();
        // 检查活动连接数是否异常
        int active = dataSource.getHikariPoolMXBean().getActiveConnections();
        if (active > dataSource.getMaximumPoolSize() * 0.9) {
            logger.error("连接池接近满载! 活动连接: {}", active);
            // 记录当前所有活动连接
            logActiveConnections();
        }
    }
    private void logActiveConnections() {
        // 通过JMX或内部方法获取连接信息
        logger.info("当前连接池状态: {}", hikariDataSource.toString());
    }
}

这些案例涵盖了HikariCP的常见使用场景,从基础配置到高级特性，在实际使用时，需要根据具体的业务需求和系统负载来调整配置参数。