mysql - Spring 启动:GenericJDBCException: Unable to acquire JDBC Connection

Question

我通过使用以下属性动态创建数据源来连接到 mysql 数据库，它工作正常，但一段时间后它一直给我错误“无法获取 JDBC 连接”。

 package com.test.db;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;

import java.util.HashMap;

@Configuration
@EnableJpaRepositories(basePackages = "com.test.master", entityManagerFactoryRef = "userMasterEntityManager", transactionManagerRef = "userMasterTransactionManager")
public class MasterDBConfig {

    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean userMasterEntityManager() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource());
        em.setPackagesToScan(new String[] { "com.test.master" });

        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);

        HashMap<String, Object> properties = new HashMap<>();
        properties.put("hibernate.hbm2ddl.auto", "create");
        properties.put("hibernate.id.new_generator_mappings", "false");
        properties.put("hibernate.show_sql", "true");
        properties.put("hibernate.dialect", "org.hibernate.dialect.MySQL5Dialect");
        properties.put("hibernate.testOnBorrow", "true");
        properties.put("hibernate.validationQuery", "SELECT 1");
        properties.put("hibernate.testWhileIdle", "true");
//        properties.put("hibernate.timeBetweenEvictionRunsMillis", "3600000");
        properties.put("hibernate.connection.autoReconnect", "true");
        properties.put("hibernate.connection.autoReconnectForPools", "true");

        em.setJpaPropertyMap(properties);

        return em;
    }

    @Primary
    @Bean(name = "dataSource")
    public DataSourceRouter dataSource() {
        return new DataSourceRouter();
    }

    @Primary
    @Bean
    public PlatformTransactionManager userMasterTransactionManager() {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(userMasterEntityManager().getObject());
        return transactionManager;
    }
}

它在 Tomcat 环境中托管在 AWS 中。 DB 在 AWS 的 RDS 中。

更新:
原因:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Too many connections
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:873)
    at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1710)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1226)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2253)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2284)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2083)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:806)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at sun.reflect.GeneratedConstructorAccessor39.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:410)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:328)
    at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:310)
    at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:203)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:732)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:664)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:479)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:154)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:118)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:107)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:131)

Answer 1

您遇到连接过多导致无法建立任何新连接的问题。解决方案非常简单——您必须设置连接池(c3p0 非常流行)并指定连接池。

参见How to setup c3p0 connection也不要忘记 include dependency