java - 使用 Tomcat 进行连接池时出现 SQLNestedException

Question

这是 mysql 的 jdbc 连接池 的 Resource 配置。

<Resource 
        abandonWhenPercentageFull="50" 
        auth="Container" 
        closeMethod="close" 
        driverClassName="com.mysql.jdbc.Driver" 
        global="jdbc/gisdata" 
        initialSize="34" 
        logAbandoned="true" 
        maxActive="377" 
        maxIdle="233" 
        maxWait="10000" 
        minEvictableIdleTimeMillis="55000" 
        minIdle="89" name="jdbc/gisdata" 
        password="temp12345" 
        removeAbandoned="true" 
        removeAbandonedTimeout="60" 
        testOnBorrow="true" 
        timeBetweenEvictionRunsMillis="34000" 
        type="javax.sql.DataSource" 
        url="jdbc:mysql://URL:3306/zmaperp_final?allowMultiQueries=true" 
        username="root" 
        validationInterval="34000" 
        validationQuery="SELECT 1"
/>  

我收到以下错误:

WARNING: Unexpected exception resolving reference
org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot load JDBC driver class 'com.mysql.jdbc.Driver'
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1429)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1371)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getLogWriter(BasicDataSource.java:1098)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory.createDataSource(BasicDataSourceFactory.java:350)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory.getObjectInstance(BasicDataSourceFactory.java:156)
    at org.apache.naming.factory.ResourceFactory.getObjectInstance(ResourceFactory.java:141)
    at javax.naming.spi.NamingManager.getObjectInstance(Unknown Source)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:842)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:153)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:830)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:167)
    at org.apache.catalina.core.NamingContextListener.addResource(NamingContextListener.java:1103)
    at org.apache.catalina.core.NamingContextListener.createNamingContext(NamingContextListener.java:682)
    at org.apache.catalina.core.NamingContextListener.lifecycleEvent(NamingContextListener.java:271)
    at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:117)
    at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:90)
    at org.apache.catalina.core.StandardServer.startInternal(StandardServer.java:731)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
    at org.apache.catalina.startup.Catalina.start(Catalina.java:689)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:294)
    at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:428)
Caused by: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1420)

我在我的系统上使用 Tomcat 实例，并按照建议添加了 mysql-connector jar 文件:

更新:我已经按照@Jan 的回答建议更新了jar 文件。错误已更改为如下所示:

WARNING: Unexpected exception resolving reference
org.apache.tomcat.dbcp.dbcp.SQLNestedException: Error preloading the connection pool
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1398)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getLogWriter(BasicDataSource.java:1098)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory.createDataSource(BasicDataSourceFactory.java:350)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory.getObjectInstance(BasicDataSourceFactory.java:156)
    at org.apache.naming.factory.ResourceFactory.getObjectInstance(ResourceFactory.java:141)
    at javax.naming.spi.NamingManager.getObjectInstance(Unknown Source)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:842)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:153)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:830)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:167)
    at org.apache.catalina.core.NamingContextListener.addResource(NamingContextListener.java:1103)
    at org.apache.catalina.core.NamingContextListener.createNamingContext(NamingContextListener.java:682)
    at org.apache.catalina.core.NamingContextListener.lifecycleEvent(NamingContextListener.java:271)
    at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:117)
    at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:90)
    at org.apache.catalina.core.StandardServer.startInternal(StandardServer.java:731)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
    at org.apache.catalina.startup.Catalina.start(Catalina.java:689)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:294)
    at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:428)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    at com.mysql.jdbc.Util.getInstance(Util.java:384)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:973)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1112)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2506)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2539)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2321)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:832)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:417)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:344)
    at org.apache.tomcat.dbcp.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
    at org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
    at org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.addObject(GenericObjectPool.java:1679)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1395)
    ... 23 more

更新

更新了连接属性:

<Resource 
            abandonWhenPercentageFull="50" 
            auth="Container" 
            closeMethod="close" 
            driverClassName="com.mysql.jdbc.Driver" 
            global="jdbc/gisdata" 
            initialSize="10" 
            logAbandoned="true" 
            maxActive="25" 
            maxIdle="10" 
            maxWait="10000" 
            minEvictableIdleTimeMillis="55000" 
            minIdle="5" 
            name="jdbc/gisdata" 
            password="temp12345" 
            removeAbandoned="true" 
            removeAbandonedTimeout="60" 
            testOnBorrow="true" 
            timeBetweenEvictionRunsMillis="34000" 
            type="javax.sql.DataSource" 
            url="jdbc:mysql:SERVER/zmaperp_final?allowMultiQueries=true" 
            username="root" 
            validationInterval="34000" validationQuery="SELECT 1;"
        />

我现在尝试删除 allowMultiQueries 选项，但连接仍然给我一个错误。

Answer 1

您必须添加Driver，而不是Sources :-)

检查驱动程序的文件名。你会想要

 mysql-connector-java-5.1.37-bin.jar

或突出显示:你有 mysql-connector-java-5.1.37- source .jar，你需要 mysql-connector-java-5.1.37-bin 。 jar 祝你好运:-)

编辑/您的问题已更改

message from server: "Too many connections"

您需要将最大/初始连接数减少到适中的程度。我推荐

    maxActive="25" 
    maxIdle="10" 
    maxWait="10000" 
    minEvictableIdleTimeMillis="55000" 
    minIdle="5"

对于初学者来说。