时间戳

首页 > 解决方案 > Facing Too many connection issue on HIkariCP

jdbc - Facing Too many connection issue on HIkariCP

问题描述

I have a Java JDBC application, after 100 queries of Select Jetty server crashed and return below error:

     ERROR com.zaxxer.hikari.pool.HikariPool - dev-server - Exception during pool initialization.
java.sql.SQLNonTransientConnectionException: Too many connections
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:526)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115)
        at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:1606)
        at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:633)
        at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:347)
        at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:219)
        at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:95)
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:358)
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:201)
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:443)
        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:514)
        at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:111)
        at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:72)
        at com.jrg.platform.commons.hikari.HikariDataSourceSupport.getDataSource(HikariDataSourceSupport.java:70)
        at com.jrg.platform.commons.hikari.HikariDataSourceSupport.getConnection(HikariDataSourceSupport.java:82)
        at com.jrg.platform.services.internal.pcms.data.campaign.CampaignAlertDaoImpl.getCampaignAlerts(CampaignAlertDaoImpl.java:40)
        at com.jrg.platform.services.internal.pcms.service.CampaignAlertServiceImpl.campaignAlerts(CampaignAlertServiceImpl.java:43)
        at com.jrg.platform.services.internal.pcms.resource.CampaignAlertResource.getCampaignAlerts(CampaignAlertResource.java:52)
        at com.jrg.platform.services.internal.pcms.resource.CampaignAlertResource_$$_jvstf5a_6._d5getCampaignAlerts(CampaignAlertResource_$$_jvstf5a_6.java)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

Unable to find the solution. I am just getting the connection from HikariDataSourceSupport and performing the query. the sample code of getting connection is given below:

default HikariDataSource getDataSource(E e, boolean readOnly) throws IOException {
        String key = this.getKey(e, readOnly);
        Map<String, HikariDataSource> sources = this.getDataSources();
        if(!sources.containsKey(key)) {
            synchronized(this.getMonitor()) {
                if(!sources.containsKey(key)) {
                    if(logger.isDebugEnabled()) {
                        logger.debug("creating new DataSource for {}", key);
                    }

                    Config config = this.getConfig(e, readOnly);
                    if(!config.hasPathOrNull("jdbcUrl")) {
                        throw new EnvironmentNotConfigured(e, readOnly);
                    }

                    sources.put(key, new HikariDataSource(new HikariConfig(ConfigUtils.asProperties(config))));
                }
            }
        }

        return (HikariDataSource)sources.get(key);
    }

    default Connection getConnection(E env, boolean readOnly) throws SQLException, IOException {
        return this.getDataSource(env, readOnly).getConnection();
    }

标签： jdbchikaricp

解决方案

配置 HikariCP 时出现问题。当请求生成并尝试获取连接时，create connection 方法正在创建新的数据源，因为由于 bean 的视图范围，最后一个状态被破坏。

现在我已经将该类转换为 singalton 并创建了该接口的实现以注入代码。它正在保存先前数据源连接的状态。现在它可以在最少的空闲连接下完美运行。

代码可以在这里看到：APP.java

 bind(HikariLucktasticDataSourceSupportImpl.class)
                        .to(HikariDataSourceSupport.class)
                        .named("hdsSupport")
                        .in(Singleton.class);

在 DAO 层：

   @Inject
    @Named("hdsSupport")
    private HikariDataSourceSupport hdsSupport;

推荐阅读