在多线程系统中使用静态 java.sql.Connection 实例是否安全? [英] Is it safe to use a static java.sql.Connection instance in a multithreaded system?
问题描述
我正在 Tomcat 上运行 Web 应用程序.我有一个处理所有数据库查询的类.此类包含返回查询结果的 Connection 对象和方法.
I'm running a web application on Tomcat. I have a class that handles all DB queries.
This class contains the Connection object and methods that returns query results.
这是连接对象:
private static Connection conn = null;
它只有一个实例(单例).
It has only one instance (singleton).
此外，我还有执行查询的方法，例如在数据库中搜索用户:
In addition, I have methods that execute queries, such as search for a user in the db:
public static ResultSet searchUser(String user, String pass) throws SQLException
此方法使用静态Connection 对象.我的问题是，我在静态 Connection 对象线程中的使用是否安全?或者当很多用户会调用searchUser方法时会导致问题吗?
This method uses the static Connection object. My question is, is my use in static Connection object thread safe? Or can it cause problems when a lot of users will call the searchUser method?
推荐答案
我在静态 Connection 对象线程中使用安全吗?
绝对不会！
这种方式将在所有用户发送的所有请求之间共享连接，因此所有查询将相互干扰.但是线程安全不是您唯一的问题，资源泄漏也是您的另一个问题.在整个应用程序的整个生命周期中，您都保持打开一个连接.一般数据库会在打开时间过长时回收连接，通常在 30 分钟到 8 小时之间，具体取决于数据库的配置.因此，如果您的 Web 应用程序运行时间超过此时间，连接就会丢失，您将无法再执行查询.
Absolutely not!
This way the connection going to be shared among all requests sent by all users and thus all queries will interfere with each other. But threadsafety is not your only problem, resource leaking is also your other problem. You're keeping a single connection open during the entire application's lifetime. The average database will reclaim the connection whenever it's been open for too long which is usually between 30 minutes and 8 hours, depending on DB's configuration. So if your web application runs longer than that, the connection is lost and you won't be able to execute queries anymore.
当这些资源作为多次重用的类实例的非static 实例变量持有时，此问题也适用.
This problem also applies when those resources are held as a non-static instance variable of a class instance which is reused multiple times.
您应该总是在尽可能短的范围中获取和关闭连接、语句和结果集，最好在完全相同的try-with-resources 块 作为您根据以下 JDBC 习语:
You should always acquire and close the connection, statement and resultset in the shortest possible scope, preferably inside the very same try-with-resources block as where you're executing the query according the following JDBC idiom:
public User find(String username, String password) throws SQLException {
User user = null;
try (
Connection connection = dataSource.getConnection();
PreparedStatement statement = connection.prepareStatement("SELECT id, username, email FROM user WHERE username=? AND password=md5(?)");
) {
statement.setString(1, username);
statement.setString(2, password);
try (ResultSet resultSet = statement.executeQuery()) {
if (resultSet.next()) {
user = new User();
user.setId(resultSet.getLong("id"));
user.setUsername(resultSet.getString("username"));
user.setEmail(resultSet.getString("email"));
}
}
}
return user;
}
请注意，您应该不要在此处返回 ResultSet.您应该立即读取它并将其映射到非 JDBC 类，然后返回它，以便可以安全地关闭 ResultSet.
Note that you should not return a ResultSet here. You should immediately read it and map it to a non-JDBC class and then return it, so that the ResultSet can safely be closed.
如果您还没有使用 Java 7，那么使用 try-finally 块，您可以在其中手动关闭可关闭资源，与您获得它们时的顺序相反.你可以在这里找到一个例子:应该多久JDBC中的Connection、Statement和ResultSet要关闭吗?
If you're not on Java 7 yet, then use a try-finally block wherein you manually close the closeable resources in the reverse order as you've acquired them. You can find an example here: How often should Connection, Statement and ResultSet be closed in JDBC?
如果您担心连接性能，那么您应该改用连接池.这是内置在许多 Java EE 应用程序服务器中的，甚至像 Tomcat 这样的准系统 servletcontainer 也支持它.只需在服务器本身中创建一个 JNDI 数据源，并让您的 Web 应用程序将其作为 DataSource 抓取.它显然已经是一个连接池.您可以在下面列表的第一个链接中找到示例.
If you worry about connecting performance, then you should be using connection pooling instead. This is built-in into many Java EE application servers and even barebones servletcontainers like Tomcat supports it. Just create a JNDI datasource in the server itself and let your webapp grab it as DataSource. It's transparently already a connection pool. You can find an example in the first link of the list below.
- 我应该如何连接到 JDBC基于 servlet 的应用程序中的数据库/数据源?
- 当我的应用失去连接时，如何我应该恢复它吗?
- 我在使用 JDBC 连接池吗?
- 使用 MVC 和 DAO 模式在 JSP 页面中以 HTML 格式显示 JDBC ResultSet
- 使用 JDBC 的 DAO 教程
这篇关于在多线程系统中使用静态 java.sql.Connection 实例是否安全?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
