在尝试使用c3p0数据库连接池的时候,首先先导入了三个jar包:
然后写了c3p0-config.xml配置文件如下:
<c3p0-config>
<!-- 默认配置,如果没有指定则使用这个配置 -->
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/TestDB?useSSL=false</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="checkoutTimeout">30000</property>
<property name="idleConnectionTestPeriod">30</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
<user-overrides user="test-user">
<property name="maxPoolSize">10</property>
<property name="minPoolSize">1</property>
<property name="maxStatements">0</property>
</user-overrides>
</default-config>
<!-- 命名的配置 -->
<named-config name="itcast">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/TestDB?useSSL=false</property>
<property name="user">root</property>
<property name="password">123456</property>
<!-- 如果池中数据连接不够时一次增长多少个 -->
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">20</property>
<property name="minPoolSize">10</property>
<property name="maxPoolSize">40</property>
<property name="maxStatements">0</property>
<property name="maxStatementsPerConnection">5</property>
</named-config>
</c3p0-config>
编写代码使用测试:
package edu.xatu.datasource.c3p0;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
public class c3p0Dome01 {
public static void main(String[] args) throws SQLException {
//1.创建数据库连接池对象
DataSource ds = new ComboPooledDataSource();
//获取连接对象
Connection conn = ds.getConnection();
//3.打印连接对象
System.out.println(conn);
}
}
然后就报错如下:
三月 14, 2020 6:10:35 下午 com.mchange.v2.log.MLog <clinit>
信息: MLog clients using java 1.4+ standard logging.
Exception in thread "main" java.lang.NoClassDefFoundError: com/mchange/v2/cfg/MConfig
at com.mchange.v2.c3p0.cfg.C3P0Config.findLibraryMultiPropertiesConfig(C3P0Config.java:157)
at com.mchange.v2.c3p0.cfg.C3P0Config.<clinit>(C3P0Config.java:143)
at com.mchange.v2.c3p0.impl.PoolBackedDataSourceBase.<init>(PoolBackedDataSourceBase.java:54)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.<init>(AbstractPoolBackedDataSource.java:74)
at com.mchange.v2.c3p0.AbstractComboPooledDataSource.<init>(AbstractComboPooledDataSource.java:142)
at com.mchange.v2.c3p0.AbstractComboPooledDataSource.<init>(AbstractComboPooledDataSource.java:138)
at com.mchange.v2.c3p0.ComboPooledDataSource.<init>(ComboPooledDataSource.java:47)
at edu.xatu.datasource.c3p0.c3p0Dome01.main(c3p0Dome01.java:11)
Caused by: java.lang.ClassNotFoundException: com.mchange.v2.cfg.MConfig
at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:349)
at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
... 8 more
Process finished with exit code 1
通过网上查找资料发现,据说这是上面导入c3p0和commons的版本不相容导致的,然后我就按照一个博主的版本修改了我的mchange-commons-java-0.2.3.4.jar版本改成mchange-commons-java-0.2.11.jar,然后运行又报错如下:
三月 14, 2020 6:15:01 下午 com.mchange.v2.log.MLog
信息: MLog clients using java 1.4+ standard logging.
三月 14, 2020 6:15:02 下午 com.mchange.v2.c3p0.C3P0Registry
信息: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
三月 14, 2020 6:15:02 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 30000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hge138a81o2yfxw1hioeds|3b7951, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge138a81o2yfxw1hioeds|3b7951, idleConnectionTestPeriod -> 30, initialPoolSize -> 10, jdbcUrl -> jdbc:mysql://localhost:3306/TestDB?useSSL=false, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 30, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 100, maxStatements -> 200, maxStatementsPerConnection -> 0, minPoolSize -> 10, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {test-user={minPoolSize=1, maxStatements=0, maxPoolSize=10}}, usesTraditionalReflectiveProxies -> false ]
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Exception in thread "main" java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:690)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
at edu.xatu.datasource.c3p0.c3p0Dome01.main(c3p0Dome01.java:13)
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@1e4cb85 -- timeout at awaitAvailable()
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1467)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685)
... 2 more
突然想起来,mysql 8之后,应该用:com.mysql.cj.jdbc.Driver
将com.mysql.jdbc.Driver修改为com.mysql.cj.jdbc.Driver之后仍然在报错......
三月 14, 2020 6:19:06 下午 com.mchange.v2.resourcepool.BasicResourcePool
警告: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@1e4cb85 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
三月 14, 2020 6:19:06 下午 com.mchange.v2.resourcepool.BasicResourcePool
警告: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@1e4cb85 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
三月 14, 2020 6:19:06 下午 com.mchange.v2.resourcepool.BasicResourcePool
警告: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@2200d7 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception:
java.sql.SQLException: The server time zone value '???ú±ê×??±??' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:76)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835)
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:207)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value '???ú±ê×??±??' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
at sun.reflect.GeneratedConstructorAccessor5.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:85)
at com.mysql.cj.util.TimeUtil.getCanonicalTimezone(TimeUtil.java:132)
at com.mysql.cj.protocol.a.NativeProtocol.configureTimezone(NativeProtocol.java:2234)
at com.mysql.cj.protocol.a.NativeProtocol.initServerSession(NativeProtocol.java:2258)
at com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:1319)
at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:966)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:825)
... 12 more
再经过网上继续查找广大网友的相关问题之后,悲催的发现:在mysql 8之后,xml配置文件应该如下才对:
<c3p0-config>
<default-config>
<!-- 必要参数 -->
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/TestDB?useSSL=false&serverTimezone=UTC</property>
<property name="user">root</property>
<property name="password">123456</property>
<!-- 下面不是必要的参数 -->
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
</default-config>
</c3p0-config>
然后。。。我终于成功了,哭了。
三月 14, 2020 6:22:46 下午 com.mchange.v2.log.MLog
信息: MLog clients using java 1.4+ standard logging.
三月 14, 2020 6:22:47 下午 com.mchange.v2.c3p0.C3P0Registry
信息: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
三月 14, 2020 6:22:47 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hge138a81o38eynqoe0oj|3b7951, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.cj.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge138a81o38eynqoe0oj|3b7951, idleConnectionTestPeriod -> 0, initialPoolSize -> 10, jdbcUrl -> jdbc:mysql://localhost:3306/TestDB?useSSL=false&serverTimezone=UTC, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 30, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 100, maxStatements -> 200, maxStatementsPerConnection -> 0, minPoolSize -> 10, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
com.mchange.v2.c3p0.impl.NewProxyConnection@a71081 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@8f0181]
所以血的教训,一定要检查开发环境,以及不同版本之间的兼容性和不同版本所改动的特性!!!