在多线程系统中使用静态java.sql.Connection实例是否安全？ [英] Is it safe to use a static java.sql.Connection instance in a multithreaded system?

问题描述

我在Tomcat上运行Web应用程序。我有一个类，处理所有的DB查询。
此类包含 Connection 对象和返回查询结果的方法。

这是连接对象：

  private static Connection conn = null; 
  

它只有一个实例（单例）。

此外，我有执行查询的方法，例如在db中搜索用户：

  public static ResultSet searchUser（String user，String pass）throws SQLException 
  

此方法使用静态连接对象。我的问题是，是我在静态连接中使用对象线程安全吗？或者当很多用户调用 searchUser 方法时会导致问题？

解决方案

$ b >绝对不是！

这样，所有用户发送的所有请求都将共享连接，因此所有查询都会相互干扰。但threadafety不是你唯一的问题，资源泄漏也是你的其他问题。您在整个应用程序的生命周期内保持单个连接打开。平均数据库会在连接打开太久时回收连接，通常在30分钟到8小时之间，具体取决于数据库的配置。因此，如果您的网络应用程序运行时间超过此时间，则连接会丢失，您将无法再执行查询。

这些问题也适用于作为一个重复使用多次的类实例的非 - static 实例变量。

始终获取并关闭最短可能范围中的连接，语句和结果集，最好在非常相同的 try-with-resources 块作为根据以下JDBC惯用语执行查询的位置：

public class 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 这里。如果你没有使用Java 7，那么使用一个 try-finally 块，其中你手动关闭可关闭资源的顺序与你获得它们相反。 p>

如果你担心连接性能，那么你应该使用连接池。这是内置到许多Java EE应用程序服务器中，甚至是tomcat支持的servletcontainers。只需在服务器本身创建一个JNDI数据源，让你的webapp抓取它 DataSource 。

另请参阅：

• 我应该如何从Eclipse连接到MySQL数据源？


• 当我的应用程序失去连接时，我应该如何恢复？


• 我是否使用JDBC连接池？


• 在HTML中显示JDBC ResultSet< table>在JSP页面中使用MVC模式


• 使用JDBC的DAO教程

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.

This is the connection object:

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

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?

解决方案

is my use in static Connection object thread safe?

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.

This problem also applies when those resources are held as a non-static instance variable of a class instance which is reused multiple times.

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;
}

Note that you should not return a ResultSet here. 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.

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.

See also:

• How should I connect to a MySQL data source from Eclipse?
• When my app loses connection, how should I recover it?
• Am I Using JDBC Connection Pooling?
• Show JDBC ResultSet in HTML <table> in JSP page using MVC pattern
• DAO tutorial with JDBC

这篇关于在多线程系统中使用静态java.sql.Connection实例是否安全？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！