问题描述
我有一个要连接到 MySQL 数据库的应用程序.它在半夜失去连接，然后喷出关于 null 连接和 JDBC 在 X 秒内没有收到消息.
I have an app that I'm connecting to a MySQL database. It loses connection in the middle of the night and then spouts about null connections and JDBC hasn't received messages in X seconds.
我在执行任何需要与 SQL 服务器通信的操作之前调用 getConnection().
I call getConnection() before I do anything that requires communication with the SQL server.
这是我的 getConnection() 方法:
private Connection getConnection() {
try {
if (connection != null) {
if (connection.isClosed() || !connection.isValid(10000)) {
this.initializeRamsesConnection();
}
} else {
this.initializeRamsesConnection();
}
} catch (Exception e) {
debug("Connection failed: " + e);
}
return connection;
}
在 initializeRamsesConnection() 方法中，我将密码等信息放入字符串中，然后以标准 JDBC 方式创建连接.
In the initializeRamsesConnection() method I put the password and so on information into a string and then I create the connection in the standard JDBC way.
然后我调用这个方法:
private Connection getConnectionFromConnectionString() {
Connection con = null;
String driver = "com.mysql.jdbc.Driver";
try {
Class.forName(driver);//jdbc sorcery
//if there is no connection string
if (getConnectionString() == null) {
HMIDatabaseAdapter.debug("No connection string");
}
//makes a string out of the values of db/host
String str = getConnectionString();
//if there is no driver
if (driver == null) {
debug("" + ": " + "No driver");
}
//Tries to make a connection from the connection string, username, and the password.
con = DriverManager.getConnection(str, username, password);
//if for some reason the connection is null
if (con == null) {
HMIDatabaseAdapter.debug("CONNECTION IS NULL, WHAT?");
}
} catch (Exception ex) {
HMIDatabaseAdapter.debug("getConnection() " + ex);
}
return con;
}
我可以在这两种方法中的任何一种中更改什么以适应丢失的连接?
What can I change in either of these methods to accommodate losing connection?
推荐答案
这不是检索连接的正确方法.您正在检索连接并将其分配为类的实例(或更糟，静态)变量.基本上，您将永远保持连接打开并为所有查询重用单个连接.如果查询由不同的线程执行，这可能会导致灾难.此外，当它保持打开时间过长时，DB 会回收它，因为它假定它已死/泄漏.
This is not the correct way of retrieving a connection. You're retrieving the connection and assigning it as an instance (or worse, static) variable of the class. Basically, you're keeping the connection open forever and reusing a single connection for all queries. This may end up in a disaster if the queries are executed by different threads. Also, when it's been kept open for too long, the DB will reclaim it because it assumes that it's dead/leaked.
您应该获取并关闭尽可能短的范围中的连接.IE.在与执行查询相同的 try 块中.像这样:
You should acquire and close the connection in the shortest possible scope. I.e. in the very same try block as where you're executing the query. Something like this:
public Entity find(Long id) throws SQLException {
Entity entity = null;
try (
Connection connection = dataSource.getConnection(); // This should return a NEW connection!
PreparedStatement statement = connection.prepareStatement(SQL_FIND);
) {
statement.setLong(1, id);
try (ResultSet resultSet = preparedStatement.executeQuery()) {
if (resultSet.next()) {
entity = new Entity(
resultSet.getLong("id"),
resultSet.getString("name"),
resultSet.getInt("value")
);
}
}
}
return entity;
}
如果您担心连接性能并希望重用连接，那么您应该使用连接池.你可以自己种植一个，但我强烈反对这样做，因为你似乎对这些东西很陌生.只需使用现有的连接池，例如 BoneCP、C3P0 或 DBCP.请注意，您应该不要更改 JDBC 惯用法，如上例所示.您仍然需要在尽可能短的范围内获取和关闭连接.连接池本身会担心实际重用、测试和/或关闭连接.
If you worry about connecting performance and want to reuse connections, then you should be using a connection pool. You could homegrow one, but I strongly discourage this as you seem to be pretty new to the stuff. Just use an existing connection pool like BoneCP, C3P0 or DBCP. Note that you should not change the JDBC idiom as shown in the above example. You still need to acquire and close the connection in the shortest possible scope. The connection pool will by itself worry about actually reusing, testing and/or closing the connection.
这篇关于当我的应用程序失去连接时，我应该如何恢复它?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！