java - 如何修复错误：java.sql.SQLException: Cannot create PoolableConnectionFactory (Could not create connection to database server.)？
问题描述
我正在使用带有 JSP + Servlets 的 Maven 项目。
在主类中，我通过以下方式连接：
Connection connection = ConnectionFactory.getInstance().getConnection();
ConnectionFactory类的逻辑是：
public class ConnectionFactory {
static final Logger logger = Logger.getLogger(ConnectionFactory.class);
private static final String PATH_TO_PROPERTIES = RootPathUtil.getRootApplicationClassPath("database.properties");
private static final Properties properties;
static {
InputStream inputStream;
Properties prop = new Properties();
try {
inputStream = new FileInputStream(PATH_TO_PROPERTIES);
prop.load(inputStream);
logger.info("Connection Factory Property loaded.\n\t\tProperty file location: " + PATH_TO_PROPERTIES);
} catch (FileNotFoundException e) {
logger.error("Property file \"database.properties\" didn't found. Database main properties didn't initialize.");
e.printStackTrace();
} catch (IOException e) {
logger.error("IO exception during property file \"database.properties\" loading. Database main properties didn't initialize.");
e.printStackTrace();
}
properties = prop;
}
private static ConnectionFactory instance = new ConnectionFactory();
private BasicDataSource ds;
private ConnectionFactory() {
if (properties.isEmpty()){
logger.error("Property instance is empty. Database main properties didn't initialize. ConnectionFactory creation failed.");
throw new PropertyLoadException("ConnectionFactory creation failed. Property didn't load.");
}
ds = new BasicDataSource();
ds.setDriverClassName(properties.getProperty("dbDriver"));
ds.setUsername(properties.getProperty("userName"));
ds.setPassword(properties.getProperty("password"));
ds.setUrl(properties.getProperty("connectionUrl"));
ds.setMinIdle(Integer.parseInt(properties.getProperty("connection_pull.min_idle")));
ds.setMaxIdle(Integer.parseInt(properties.getProperty("connection_pull.max_idle")));
ds.setMaxOpenPreparedStatements(Integer.parseInt(properties.getProperty("connection_pull.max_open_prepared_statements")));
}
public static ConnectionFactory getInstance() {
if (instance == null) {
instance = new ConnectionFactory();
return instance;
} else {
return instance;
}
}
public Connection getConnection() throws SQLException {
return this.ds.getConnection();
}
}
当我运行 Tomcat 服务器时，我总是遇到如下问题：
java.sql.SQLException: Cannot create PoolableConnectionFactory (Could not create connection to database server.)
at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2294)
at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2039)
at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1533)
at com.test.project.carrental.data.service.ConnectionFactory.getConnection(ConnectionFactory.java:69)
at com.test.project.carrental.data.service.DataBaseUtil.getConnection(DataBaseUtil.java:21)
at com.test.project.carrental.controller.filter.JDBCFilter.doFilter(JDBCFilter.java:63)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at com.test.project.carrental.controller.filter.LogSessionIdFilter.doFilter(LogSessionIdFilter.java:39)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:690)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
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:404)
at com.mysql.jdbc.Util.getInstance(Util.java:387)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:917)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2330)
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.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:404)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:410)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:328)
at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:39)
at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:256)
at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:2304)
at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2290)
... 27 more
Caused by: java.lang.NullPointerException
at com.mysql.jdbc.ConnectionImpl.getServerCharset(ConnectionImpl.java:2997)
at com.mysql.jdbc.MysqlIO.sendConnectionAttributes(MysqlIO.java:1936)
at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1865)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1228)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2253)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2284)
... 41 more
为了解决这个问题，我尝试了不同的mysql-connector-java依赖版本，例如：
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.20</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>
5.1.41使用或5.1.46的替代修复对我也没有帮助。
同时，我尝试将各种连接属性用于：
dbDriver=com.mysql.jdbc.Driver
connectionUrl=jdbc:mysql://localhost:3306/db?verifyServerCertificate=false&useSSL=true
userName=...
password=...
connection_pull.min_idle=5
connection_pull.max_idle=20
connection_pull.max_open_prepared_statements=180
我已经测试了不同的变体，coonectionUrl例如：
#connectionUrl=jdbc:mysql://127.0.0.1:3306/db?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
#connectionUrl=jdbc:mysql://localhost:3306/db?autoReconnect=true
#connectionUrl=jdbc:mysql://localhost:3306/db?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
#connectionUrl=jdbc:mysql://127.0.0.1:3306/db?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
#connectionUrl=jdbc:mysql://127.0.0.1:3306/db?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
#connectionUrl=jdbc:mysql://127.0.0.1:3306/db?useUnicode=true&serverTimezone=UTC&useSSL=false
一些联系是基于this & this & this answer。
升级版：
我添加了我的 POM 文件，因为正如下面评论中提到的，MySQL Connector/J8.0.11包含连接字符串的两种变体。
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>CarRental</groupId>
<artifactId>CarRental</artifactId>
<version>1.0-SNAPSHOT</version>
<name>Car Rental Service</name>
<packaging>war</packaging>
<properties>
<java.version>1.8</java.version>
<junit.version>4.12</junit.version>
<mokito.version>1.10.19</mokito.version>
<slf4j.version>1.7.21</slf4j.version>
</properties>
<dependencies>
<!-- SERVLETS AND VIEWS-->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>jsp-api</artifactId>
<version>2.2</version>
</dependency>
<!-- DATDBASE DRIVER -->
<!--<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.39</version>
</dependency>-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.1.1</version>
</dependency>
<!-- TESTING -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>${junit.version}</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.mockito</groupId>
<artifactId>mockito-all</artifactId>
<version>${mokito.version}</version>
<scope>test</scope>
</dependency>
<!-- LOGGING -->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>${slf4j.version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>${slf4j.version}</version>
</dependency>
</dependencies>
<build>
<finalName>CarRentalService</finalName>
<plugins>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<encoding>UTF-8</encoding>
<source>${java.version}</source>
<target>${java.version}</target>
</configuration>
</plugin>
<plugin>
<artifactId>maven-resources-plugin</artifactId>
<version>3.0.1</version>
<configuration>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
<plugin>
<artifactId>maven-war-plugin</artifactId>
<version>3.0.0</version>
<configuration>
<failOnMissingWebXml>false</failOnMissingWebXml>
</configuration>
</plugin>
<plugin>
<groupId>org.jacoco</groupId>
<artifactId>jacoco-maven-plugin</artifactId>
<version>0.7.7.201606060606</version>
<configuration>
<destFile>${basedir}/target/coverage-reports/jacoco-unit.exec</destFile>
<dataFile>${basedir}/target/coverage-reports/jacoco-unit.exec</dataFile>
</configuration>
<executions>
<execution>
<id>jacoco-initialize</id>
<goals>
<goal>prepare-agent</goal>
</goals>
</execution>
<execution>
<id>jacoco-site</id>
<phase>package</phase>
<goals>
<goal>report</goal>
</goals>
</execution>
</executions>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.19.1</version>
<configuration>
<systemPropertyVariables>
<jacoco-agent.destfile>${basedir}/target/coverage-reports/jacoco-unit.exec</jacoco-agent.destfile>
</systemPropertyVariables>
</configuration>
</plugin>
</plugins>
</build>
</project>
我尝试在没有 Intelij IDEA战争文件的情况下运行 - 同样的问题。
解决方案
我花了几天时间来解决这个问题。我测试了许多在不同答案中提到的方法，但没有一个有效。
我的具体问题的原因是在dbDriver=com.mysql.jdbc.Driver. 为了修复它，我已更改为dbDriver=com.mysql.cj.jdbc.Driver.
例如，可以在此处阅读更多信息。
注意：我使用的是MySQL Server 8.0.20 & OS Windows 10。
UPD：
事实证明，我没有target用 jars 检查 WEB-INF/lib 所在的文件夹。
一旦我在那里清理了不必要的版本，程序也开始了dbDriver=com.mysql.jdbc.Driver。解压缩提示也可以作为解决方案。
推荐阅读
- odata - 如何在不刷新模型的情况下批量设置属性（）？
- esri - arcgis：加载 kml 文件，总计 80 兆字节（每个文件 10 兆字节）
- ubuntu-16.04 - 如何让 bazel 自动完成以重新开始工作？
- r - 查找文档的余弦相似度及其从 R 数据框中的删除
- ajax - Ajax / 如何使附加列表项“被选中”？
- android - 具有网格布局管理器的 Recyclerview 的相等填充
- batch-file - 使用批处理脚本检查网络适配器是否设置为静态或 dhcp
- java - 在java中将列表转换为二维字符串数组
- typescript - 如何使用 webpack 捆绑 puppeteer 进行生产部署？
- ruby-on-rails - Cancancan 无法与用户和管理员一起使用