Java/JDBC/MySQL：如何解决 DriverManager.getConnection() 返回 NULL 的原因？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14898672/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Java/JDBC/MySQL: How do I troubleshoot why DriverManager.getConnection() is returning NULL?
提问by TACHEON
I am attempting to troubleshoot an issue with a Java application that is utilizing JDBC to connect to a MySQL database. The surface issue is that when connecting to a valid database, DriverManager.getConnection will sometimes return NULL, while just a few minutes later it will return a valid connection to the exact same database.
我正在尝试解决使用 JDBC 连接到 MySQL 数据库的 Java 应用程序的问题。表面问题是，当连接到有效的数据库时，DriverManager.getConnection 有时会返回 NULL，而几分钟后它会返回到完全相同的数据库的有效连接。
I am in the position of trying to troubleshoot this issue, but my understanding of where Java, JDBC, and MySQL meet is fairly limited. I've been doing lots of research on this, but have hit a wall and don't know where to go from here.
我正试图解决这个问题，但我对 Java、JDBC 和 MySQL 在哪里的理解相当有限。我一直在对此进行大量研究，但遇到了困难，不知道从哪里开始。
Here's what I've done so far:
这是我到目前为止所做的：
- On the Java end, I have traced the code all the way to DriverManager.getConnection(). I have determined that the NULL connection is coming from there, but I have no clue what goes on under the hood of getConnection. I've been struggling to find a thorough explanation of this online.
- On the MySQL end, I have verified that there are plenty of connections available to me (around 1000 free connections at times), so I know I'm not exceeding max connections there. Viewing logs, I was able to determine that there are a slightly higher number of connections being aborted during the time-frames where I'm having the most issues, but I don't know how to determine why these connections were aborted (did MySQL abort, JDBC, the Java application?) I'm not sure if there is anything else I need to be looking for on the MySQL end.
- In the middle, with JDBC, I'm pretty lost. I have been reading up on MySQL Connector/J at http://dev.mysql.com/doc/refman/5.1/en/connector-j.html, but am not certain if that info pertains to the JDBC driver being utilized by Java.
- 在Java端，我已经将代码一路追踪到DriverManager.getConnection()。我已经确定 NULL 连接来自那里，但我不知道 getConnection 引擎盖下发生了什么。我一直在努力在网上找到对此的彻底解释。
- 在 MySQL 端，我已经验证有很多连接可供我使用（有时大约 1000 个免费连接），所以我知道我没有超过那里的最大连接数。查看日志，我能够确定在我遇到最多问题的时间范围内中止的连接数量略多，但我不知道如何确定这些连接中止的原因（MySQL中止、JDBC、Java 应用程序？）我不确定在 MySQL 端是否还有其他需要寻找的东西。
- 在中间，使用 JDBC，我很迷茫。我一直在http://dev.mysql.com/doc/refman/5.1/en/connector-j.html上阅读 MySQL Connector/J ，但不确定该信息是否与正在使用的 JDBC 驱动程序有关爪哇。
Any direction as to where I can go from here would be much appreciated.
任何关于我可以从这里去哪里的方向将不胜感激。
Thanks!
谢谢！
EDIT - 2/15, 10:35am CSTI apologize for not being more specific. This application is a production application that normally works just fine. It's successfully processing tens of thousands of connections a day without any issues, it's just that this issue will crop up at random times during the day, and will persist for anywhere from 30 seconds to 5 minutes when it does happen.
编辑 - 2/15, 10:35am CST我很抱歉没有更具体。此应用程序是一个生产应用程序，通常可以正常工作。它每天成功处理数万个连接，没有任何问题，只是这个问题会在一天中的随机时间出现，并且会在发生时持续 30 秒到 5 分钟。
Here's the code that I've traced all the way down to DriverManager.getConnection:
这是我一直追踪到 DriverManager.getConnection 的代码：
var dbConn = DatabaseConnectionFactory.createDatabaseConnection('com.mysql.jdbc.Driver','jdbc:mysql://'+ serverName +':' + port + '/' + database, userName, password);
public static DatabaseConnection createDatabaseConnection(String driver, String address, String username, String password) throws SQLException {
try {
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
Properties info = new Properties();
info.setProperty("user", username);
info.setProperty("password", password);
// this property should only be set if it's for embedded database
info.setProperty("shutdown", "true");
return new DatabaseConnection(address, info);
}
public DatabaseConnection(String address, Properties info) throws SQLException {
logger.debug("creating new database connection: address=" + address + ", " + info);
this.address = address;
connection = DriverManager.getConnection(address, info);
}
I don't believe there is actually any issue with the code, but rather an issue somewhere in between getConnection() and MySQL.
我不相信代码实际上有任何问题，而是在 getConnection() 和 MySQL 之间的某个地方存在问题。
回答by Mark Rotteveel
An individual driver can return null for a connect request, the JDBC 4.1 specification says this:
单个驱动程序可以为连接请求返回 null，JDBC 4.1 规范是这样说的：
When the DriverManager is trying to establish a connection, it calls that driver's connect method and passes the driver the URL. If the Driver implementation understands the URL, it will return a Connection object or throw a SQLException if a connection cannot be maded to the database. If the Driver implementation does not understand the URL, it will return null.
当 DriverManager 尝试建立连接时，它会调用该驱动程序的 connect 方法并将 URL 传递给驱动程序。如果 Driver 实现理解 URL，它将返回一个 Connection 对象，或者如果无法与数据库建立连接，则抛出 SQLException。如果驱动程序实现不理解 URL，它将返回 null。
However, looking at the code of java.sql.DriverManager(in Java 7 Update 13), it will alwaysthrow an SQLExceptionwith message No suitable driver found for <url>when all available drivers have returned nullfor a connect(url, properties)call:
然而，在看的代码java.sql.DriverManager（在Java 7中更新13），它会永远抛出一个SQLException与消息没有合适的驱动程序找到<链接>当所有可用驱动程序已经返回null了connect(url, properties)电话：
// Worker method called by the public getConnection() methods.
private static Connection getConnection(
String url, java.util.Properties info, ClassLoader callerCL) throws SQLException {
// Removed some classloading stuff for brevity
if(url == null) {
throw new SQLException("The url cannot be null", "08001");
}
// Walk through the loaded registeredDrivers attempting to make a connection.
// Remember the first exception that gets raised so we can reraise it.
SQLException reason = null;
for(DriverInfo aDriver : registeredDrivers) {
// If the caller does not have permission to load the driver then
// skip it.
if(isDriverAllowed(aDriver.driver, callerCL)) {
try {
println(" trying " + aDriver.driver.getClass().getName());
Connection con = aDriver.driver.connect(url, info);
if (con != null) {
// Success!
println("getConnection returning " + aDriver.driver.getClass().getName());
return (con);
}
} catch (SQLException ex) {
if (reason == null) {
reason = ex;
}
}
} else {
println(" skipping: " + aDriver.getClass().getName());
}
}
// if we got here nobody could connect.
if (reason != null) {
println("getConnection failed: " + reason);
throw reason;
}
println("getConnection: no suitable driver found for "+ url);
throw new SQLException("No suitable driver found for "+ url, "08001");
}
In other words: what you describe cannot happen (at least not in Java 7 Update 13). A quick peek at the Java 5 Update 22 sources shows an almost identical implementation were it simply cannot return null.
换句话说：您所描述的不会发生（至少在 Java 7 Update 13 中不会）。快速浏览一下 Java 5 Update 22 源代码显示了一个几乎相同的实现，只是它不能返回 null。
More likely you are swallowing an exception and then attempting to use a Connectionvariable or field that has value null.
您更有可能吞下异常，然后尝试使用Connection值为 null的变量或字段。
Another possibility would be that you are not obtaining the connection with DriverManager.getConnection(url, ...), but with DriverManager.getDriver(url).connect(...)which can return nullbecause of the rules established above. If that is what you do, it probably points to a bug in the Connector/J driver if you are always using the exact same URL: a driver cannot decide at one point to return a connection for a specific URL and next return null. It should always either return a Connectionor throw an SQLExceptionfor the same URL.
另一种可能性是您没有获得与 的连接DriverManager.getConnection(url, ...)，但由于上面建立的规则，与的连接DriverManager.getDriver(url).connect(...)可以返回null。如果这就是您所做的，那么如果您始终使用完全相同的 URL，则它可能指向 Connector/J 驱动程序中的错误：驱动程序无法在某一时刻决定返回特定 URL 的连接，然后返回 null。它应该始终为相同的 URL返回一个Connection或抛出一个SQLException。
回答by duffymo
Yes, DriverManageris the class that gets the connection for you.
是的，DriverManager是为您获取连接的类。
It manages this using the JDBC driver classes that you get with the MySQL Connector-J JAR. That JAR has to be in your CLASSPATHwhen you start.
它使用您通过 MySQL Connector-J JAR 获得的 JDBC 驱动程序类来管理它。CLASSPATH当您开始时，该 JAR 必须在您的文件中。
Start by making sure you can connect to MySQL from the machine where you run the Java app. Log into the MySQL admin app successfully and you've gotten past the first hurdle.
首先确保您可以从运行 Java 应用程序的机器连接到 MySQL。成功登录 MySQL 管理应用程序，您已经克服了第一个障碍。
I'll offer you a class to doctor for your situation. The methods might be generally useful for you. Modify the connection, credentials, and query for your situation and try it out. I knowthis code works.
我会根据你的情况给你上一堂医生课。这些方法通常对您有用。根据您的情况修改连接、凭据和查询并尝试一下。我知道这段代码有效。
package persistence;
import java.sql.*;
import java.util.*;
/**
* util.DatabaseUtils
* User: Michael
* Date: Aug 17, 2010
* Time: 7:58:02 PM
*/
public class DatabaseUtils {
/*
private static final String DEFAULT_DRIVER = "oracle.jdbc.driver.OracleDriver";
private static final String DEFAULT_URL = "jdbc:oracle:thin:@host:1521:database";
private static final String DEFAULT_USERNAME = "username";
private static final String DEFAULT_PASSWORD = "password";
*/
/*
private static final String DEFAULT_DRIVER = "org.postgresql.Driver";
private static final String DEFAULT_URL = "jdbc:postgresql://localhost:5432/party";
private static final String DEFAULT_USERNAME = "pgsuper";
private static final String DEFAULT_PASSWORD = "pgsuper";
*/
private static final String DEFAULT_DRIVER = "com.mysql.jdbc.Driver";
private static final String DEFAULT_URL = "jdbc:mysql://localhost:3306/party";
private static final String DEFAULT_USERNAME = "party";
private static final String DEFAULT_PASSWORD = "party";
public static void main(String[] args) {
long begTime = System.currentTimeMillis();
String driver = ((args.length > 0) ? args[0] : DEFAULT_DRIVER);
String url = ((args.length > 1) ? args[1] : DEFAULT_URL);
String username = ((args.length > 2) ? args[2] : DEFAULT_USERNAME);
String password = ((args.length > 3) ? args[3] : DEFAULT_PASSWORD);
Connection connection = null;
try {
connection = createConnection(driver, url, username, password);
DatabaseMetaData meta = connection.getMetaData();
System.out.println(meta.getDatabaseProductName());
System.out.println(meta.getDatabaseProductVersion());
String sqlQuery = "SELECT PERSON_ID, FIRST_NAME, LAST_NAME FROM PERSON ORDER BY LAST_NAME";
System.out.println("before insert: " + query(connection, sqlQuery, Collections.EMPTY_LIST));
connection.setAutoCommit(false);
String sqlUpdate = "INSERT INTO PERSON(FIRST_NAME, LAST_NAME) VALUES(?,?)";
List parameters = Arrays.asList("Foo", "Bar");
int numRowsUpdated = update(connection, sqlUpdate, parameters);
connection.commit();
System.out.println("# rows inserted: " + numRowsUpdated);
System.out.println("after insert: " + query(connection, sqlQuery, Collections.EMPTY_LIST));
} catch (Exception e) {
rollback(connection);
e.printStackTrace();
} finally {
close(connection);
long endTime = System.currentTimeMillis();
System.out.println("wall time: " + (endTime - begTime) + " ms");
}
}
public static Connection createConnection(String driver, String url, String username, String password) throws ClassNotFoundException, SQLException {
Class.forName(driver);
if ((username == null) || (password == null) || (username.trim().length() == 0) || (password.trim().length() == 0)) {
return DriverManager.getConnection(url);
} else {
return DriverManager.getConnection(url, username, password);
}
}
public static void close(Connection connection) {
try {
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(Statement st) {
try {
if (st != null) {
st.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(ResultSet rs) {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void rollback(Connection connection) {
try {
if (connection != null) {
connection.rollback();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static List<Map<String, Object>> map(ResultSet rs) throws SQLException {
List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
try {
if (rs != null) {
ResultSetMetaData meta = rs.getMetaData();
int numColumns = meta.getColumnCount();
while (rs.next()) {
Map<String, Object> row = new HashMap<String, Object>();
for (int i = 1; i <= numColumns; ++i) {
String name = meta.getColumnName(i);
Object value = rs.getObject(i);
row.put(name, value);
}
results.add(row);
}
}
} finally {
close(rs);
}
return results;
}
public static List<Map<String, Object>> query(Connection connection, String sql, List<Object> parameters) throws SQLException {
List<Map<String, Object>> results = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = connection.prepareStatement(sql);
int i = 0;
for (Object parameter : parameters) {
ps.setObject(++i, parameter);
}
rs = ps.executeQuery();
results = map(rs);
} finally {
close(rs);
close(ps);
}
return results;
}
public static int update(Connection connection, String sql, List<Object> parameters) throws SQLException {
int numRowsUpdated = 0;
PreparedStatement ps = null;
try {
ps = connection.prepareStatement(sql);
int i = 0;
for (Object parameter : parameters) {
ps.setObject(++i, parameter);
}
numRowsUpdated = ps.executeUpdate();
} finally {
close(ps);
}
return numRowsUpdated;
}
}
After compiling, run it with this command:
编译后，使用以下命令运行它：
java -classpath .;<Connector-J driver path here> persistence.DatabaseUtils
