使用spring连接mysql数据库出错
最近在学习spring框架,但是在学到JdbcTemplate时连接数据库一直报错,百度谷歌各种查找都能没有解决问题,简直要癫狂,报错信息如下:
org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Connections could not be acquired from the underlying database! at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:390) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:534) at cn.itcast.a_jdbctemplate.Demo.fun1(Demo.java:38) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75) at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86) at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84) at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:254) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:89) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70) at org.junit.runners.ParentRunner.run(ParentRunner.java:363) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:193) at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:678) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192) Caused by: java.sql.SQLException: Connections could not be acquired from the underlying database! at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:529) at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128) at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111) at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77) ... 32 more Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source. at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1319) at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557) at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525) ... 35 more
最后的原因是我使用的数据库是Mysql8,太新了,相对以前我使用的Mysql5需要在url参数上添加一些参数,(吐血。。。。。),调了三天,期间换了IDE,MySQL等等,最后解决方法是把url换成这样:
jdbc:mysql:///spring?useSSL=false&serverTimezone=UTC&characterEncoding=utf8
重点是设置:useSSL=false
还有一点,最新的mysql驱动类应当是:com.mysql.cj.jdbc.Driver,以前的 com.mysql.cj.jdbc.Driver提示被废弃了。
最后完整的测试代码供君借鉴:
@Test public void test05() throws Exception { // 创建数据源 ComboPooledDataSource dataSource = new ComboPooledDataSource(); //设置连接相关参数 dataSource.setDriverClass("com.mysql.cj.jdbc.Driver"); dataSource.setJdbcUrl("jdbc:mysql:///spring?useSSL=false&serverTimezone=UTC&characterEncoding=utf8"); dataSource.setUser("root"); dataSource.setPassword("123456"); //创建模板对象 JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); //测试相关代码是否执行 System.out.println("Hello"); String sql = "insert into jdbc_template values(null,'Nick') "; jdbcTemplate.update(sql); System.out.println("!!!!!!!!!!!OK!!!!!!!!!!!!!!!!"); }
数据库创建代码:
CREATE DATABASE spring; USE spring; CREATE TABLE jdbc_template( id INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(255) NOT NULL ); INSERT INTO jdbc_template VALUES(NULL,'rose') ; SELECT * FROM jdbc_template;