c3p0 配置连接MySQL异常：java.sql.SQLException: Connections could not be acquired from the underlying database!

在Idea上配置c3p0连接MySQL时报如下错误：

警告: com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@4e745ea8 -- 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 (2). Last acquisition attempt exception:
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.

java.sql.SQLException: Connections could not be acquired from the underlying database!

Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.

 

使用的配置为：

spring的applicationContext.xml：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-4.2.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-4.2.xsd">

    <context:property-placeholder location="classpath:jdbc.properties"/>

    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <!--配置连接池属性-->
        <property name="driverClass" value="${jdbc.driver}"/>
        <property name="jdbcUrl" value="${jdbc.url}"/>
        <property name="user" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>

        <!--c3p0连接池的私有属性-->
        <property name="maxPoolSize" value="30"/>
        <property name="minPoolSize" value="10"/>
        <!--关闭连接后不自动commit-->
        <property name="autoCommitOnClose" value="false"/>
        <!--获取连接超时时间-->
        <property name="checkoutTimeout" value="10000"/>
        <!--设置获取连接失败重试次数-->
        <property name="acquireRetryAttempts" value="2"/>
    </bean>
</beans>

jdbc.properties文件：

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/xiaoyanger?useUnicode=true&characterEncoding=utf-8&useSSL=false
jdbc.username=root
jdbc.password=1234

pom文件中关于mysql的依赖：

<!--数据库-->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.37</version>
</dependency>
<dependency>
  <groupId>c3p0</groupId>
  <artifactId>c3p0</artifactId>
  <version>0.9.1.2</version>
</dependency>

Main.java代码：

import javax.sql.DataSource;
import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        ApplicationContext ioc=new ClassPathXmlApplicationContext("META-INF/applicationContext.xml");
        DataSource bean= (DataSource) ioc.getBean("dataSource");
        try {
            System.out.println(bean.getConnection());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("容器启动完成。。。");
    }
}

 

排查：

1，驱动配置有误：driver=com.mysql.jdbc.Driver
2，数据库连接地址有误：url=jdbc:mysql://localhost:3306/test?3useUnicode=true&characterEncoding=utf8
3，密码或帐号有误：username=root  password=root

(上面三条一般都写在配置文件中，如果是因为修改了配置文件后导致该错误，建议重写一遍配置文件，因为有时候开发工具就是很蛋疼，表面没有错误，程序运行却提示报错)

4，数据库未启动或无权访问

5，项目未引入对应的驱动jar包mysql-connector-java-5.1.6-bin.jar

6,mysql root没有远程访问的权限，需要增加权限，增加权限的步骤如下：
进入mysql数据库：
grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
flush privileges;

7.在普通java项目中使用 mybatis+C3p0+spring时也遇见上述问题。
问题原因是：在xml配置文件中显示声明了自动装载  <beans default-autowire="byName"> 几经折腾，把自动装载设置去掉，就没有问题了，应该使用默认的 byType。

经过以上方式排查均未解决问题，还是报错！！

解决该问题办法：

修改pom文件中mysql驱动的版本，改为8.0.11：

<!--数据库-->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.11</version>
</dependency>
<dependency>
  <groupId>c3p0</groupId>
  <artifactId>c3p0</artifactId>
  <version>0.9.1.2</version>
</dependency>

将jdbc.properties的jdbc.driver=com.mysql.jdbc.Driver改为jdbc.driver=com.mysql.cj.jdbc.Driver

再次运行发现报如下错误

　　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

　　很明显是时区的问题，证明已经连接上了MySQL

解决时区问题

1、在连接url后加serverTimezone=UTC(UTC是世界均衡时间)，即修改jdbc.properties的jdbc.url为：

jdbc.url=jdbc:mysql://localhost:3306/xiaoyanger?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC

2、进入mysql命令行模式执行语句：

set global time_zone='+8:00'

我采用的是方法1，再次执行，无报错，问题解决。