java - 该代码是否会对 MySQL 可用连接产生任何影响？

Question

我开发了一个 Java Web 应用程序，它使用 JDBC 连接器连接到 MySQL 数据库并建立 2 个连接池。应用部署在tomcat服务器上。

我的问题是，如果我多次部署应用程序并且代码在我关闭 tomcat 时没有任何行来关闭可用连接，那么这段代码是否会影响 MySQL 的可用连接？ tomcat 会在重新启动时负责关闭连接吗？

连接实用程序:

import javax.sql.DataSource;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.impl.GenericObjectPool;

public class MySQLConnectionPool {


     public static DataSource setUpPool(){

         GenericObjectPool gPool = null;
         String dbName = "DBName";
         String userName = "Username";
         String password = "Password";
         String hostname = "Host";
         String port = "Port";
         try {
             Class.forName("com.mysql.jdbc.Driver");
            // Creates an Instance of GenericObjectPool That Holds Our Pool of Connections Object!
                gPool = new GenericObjectPool();
                gPool.setMaxActive(2);

                // Creates a ConnectionFactory Object Which Will Be Use by the Pool to Create the Connection Object!
                ConnectionFactory cf = new DriverManagerConnectionFactory("jdbc:mysql://" + hostname + ":" + port + "/" + dbName, userName, password);

                // Creates a PoolableConnectionFactory That Will Wraps the Connection Object Created by the ConnectionFactory to Add Object Pooling Functionality!
                PoolableConnectionFactory pcf = new PoolableConnectionFactory(cf, gPool, null, null, false, true);
         }catch (Exception e) {
            // TODO: handle exception
             System.out.println("Error: "+e.toString());
        }

            return new PoolingDataSource(gPool);
     }

}

道:

@Override
public ArrayList<User> getUserDetails(String environment, String MySQLQuery) {
    // TODO Auto-generated method stub
    ArrayList<User> users = new ArrayList<User>();
    Connection connObj = null;
    Statement stmt = null;
    ResultSet result = null;
    try {   
        DataSource dataSource = MySQLConnectionPool.setUpPool();

        // Performing Database Operation!
        System.out.println("\n=====Making A New Connection Object For Db Transaction=====\n");
        connObj = dataSource.getConnection();

        stmt = connObj.createStatement();
        result = stmt.executeQuery(MySQLQuery);
        while(result.next()) {
            //Some code here
        }

        System.out.println("\n=====Releasing Connection Object To Pool=====\n");            
    } catch(Exception sqlException) {

    } finally {
        try {
            // Closing ResultSet Object
            if(result != null) {
                result.close();
            }
            // Closing Statement Object
            if(stmt != null) {
                stmt.close();
            }
            // Closing Connection Object
            if(connObj != null) {
                connObj.close();
            }
        } catch(Exception sqlException) {

        }
    }
    return users;
}

Answer 1

如果您停止整个 tomcat，那么您与数据库的连接将关闭，因为运行 Tomcat 的 java 可执行文件将在停止时释放连接。

即使您不停止服务器，如果 Java 决定不再使用您的 Connection 对象，那么它将被垃圾收集。你只是不知道什么时候。

另一个笔记:

• 您可能应该寻找将DataSource 嵌入到服务器并从服务器获取DataSource 的方法:this page可能有帮助。
• 您应该重写您的代码以使用try with resources (Java 7++)，它负责正确关闭您的资源(您的代码是错误的)。

尝试使用资源:

DataSource dataSource = MySQLConnectionPool.setUpPool();
try (Connection connObj = dataSource.getConnection()) {   
  try (Statement stmt = connObj.createStatement()) {
    try (ResultSet result = stmt.executeQuery(MySQLQuery)) {
      while(result.next()) {
        //Some code here
      }
    }
  } 
} catch (SQLException sqlException) {
  // do something
}

或者:

DataSource dataSource = MySQLConnectionPool.setUpPool();
try (Connection connObj = dataSource.getConnection();
     Statement stmt = connObj.createStatement();
     ResultSet result = stmt.executeQuery(MySQLQuery)
    ) {
  while(result.next()) {
    //Some code here
  }
} catch (SQLException sqlException) {
  // do something
}

正如我上面所说，您的代码是错误的，因为您在关闭 ResultSet 或 Statement 时可能会出现异常(至少是 SQLException) ，所以后面的对象永远不会被你的代码释放:

    try {
        // Closing ResultSet Object
        if(result != null) {
            result.close(); // if it fails, stmt and connObj are not closed.
        }
        // Closing Statement Object
        if(stmt != null) {
            stmt.close(); // if it fails, connObj is not closed.
        }
        // Closing Connection Object
        if(connObj != null) {
            connObj.close();
        }
    } catch(Exception sqlException) {

    }

如果您不能使用try with resources(Java 6 大概几十年来不再受支持，但谁知道呢)，那么您的代码应该如下所示:

Connection connObj = null;
Statement stmt = null;
ResultSet result = null;
try {
  // ... do whatever is needed
} finally {
  if(result != null) {
    try {result.close();} catch (Exception ignored) {}
  }
  if(stmt != null) {
    try {stmt.close();} catch (Exception ignored) {}
  }
  if(connObj != null) {
    try {connObj .close();} catch (Exception ignored) {}
  }
}