在中型 Web 应用程序中处理数据库连接的正确方法

Question

我目前正在维护一个中小型 Java Web 应用程序（仅使用普通 JSP/Servlet），该应用程序是一名实习生为公司内部使用而制作的，但我在连接方面遇到了一些问题。

有时，我们会突然收到“语句已关闭”或“连接已关闭”等错误，然后整个应用程序将停止工作，并且必须重新启动服务器。

我没有很多经验，也没有人指导或教我最佳实践、设计模式等，但我很确定这不是正确的方法。我读过有关 DAL、DAO 和 DTO 等内容的内容。我们的应用程序没有这些。

整个 Web 应用程序（即 servlet）基本上充满了类似于以下内容的调用：

Database db = Database.getInstance();
db.execute("INSERT INTO SomeTable VALUES (a, b, c)");
db.execute("UPDATE SomeTable SET Col = Val");

SELECT 的完成方式如下：

ArrayList<Model> results = Model.fetch("SELECT * FROM SomeTable");

其中 Model 是一个扩展 HashMap 的类，代表表中的单个行。

这是 Database.java 的代码，想知道是否有人可以指出明显的错误（我很确定有很多）、可以完成的任何快速修复以及有关数据库最佳实践的一些资源连接/连接处理。

package classes;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public final class Database {

    public static Database getInstance() {
        if (Database.instance == null) {
            Database.instance = new Database();
        }
        return Database.instance;
    }

    // Returns the results for an SQL SELECT query.
    public ArrayList<HashMap<String, Object>> fetch(String sql) {

        ArrayList<HashMap<String, Object>> results = new ArrayList<HashMap<String, Object>>();

        try {

            PreparedStatement stmt = this.connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
            ResultSet rs = stmt.executeQuery();
            this.doFetch(rs, results);
            stmt.close();

        } catch (SQLException e) {
            this.handleException(e, sql);
        }

        return results;
    }

    public ArrayList<HashMap<String, Object>> fetch(String sql, ArrayList<Object> parameters) {

        ArrayList<HashMap<String, Object>> results = new ArrayList<HashMap<String, Object>>();

        try {

            // Bind parameters to statement.
            PreparedStatement pstmt = this.connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
            for (int i=0; i<parameters.size(); i++) {
                pstmt.setObject(i+1, parameters.get(i));
            }

            ResultSet rs = pstmt.executeQuery();
            this.doFetch(rs, results);
            pstmt.close();

        } catch (SQLException e) {
            this.handleException(e, sql, parameters);
        }

        return results;
    }

    public int execute(String sql) {
        int result = 0;
        try {
            Statement stmt = this.connection.createStatement();
            result = stmt.executeUpdate(sql);
            stmt.close();
        } catch (SQLException e) {
            this.handleException(e, sql);
        }
        return result;
    }

    public int execute(String sql, ArrayList<Object> parameters) {
        int result = 0;
        try {
            PreparedStatement pstmt = this.connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
            for (int i=0; i<parameters.size(); i++) {
                if (parameters.get(i) == null) {
                    pstmt.setNull(i+1, java.sql.Types.INTEGER);
                } else {
                    pstmt.setObject(i+1, parameters.get(i));
                }
            }
            result = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            this.handleException(e, sql, parameters);
        }
        return result;
    }

    public void commit() {
        try {
            this.connection.commit();
        } catch (SQLException e) {
            System.out.println("Failed to commit transaction.");
        }
    }

    public Connection getConnection() {
        return this.connection;
    }


    private static Database instance;
    private static DataSource dataSource = null;
    private Connection connection;

    private Database() {
        this.connect();
        this.execute("SET SCHEMA " + Constant.DBSCHEMA);
    }

    private void connect() {
        Connection connection = null;
        if (dataSource == null) {
            try {
                InitialContext initialContext = new InitialContext();
                dataSource = (DataSource)initialContext.lookup(
                        Constant.DEPLOYED ? Constant.PROD_JNDINAME : Constant.TEST_JNDINAME);
            } catch (NamingException e) {
                e.printStackTrace();
            }
        }
        try {
            connection = dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        this.connection = connection;
    }

    // Fetches the results from the ResultSet into the given ArrayList.

    private void doFetch(ResultSet rs, ArrayList<HashMap<String, Object>> results) throws SQLException {
        ResultSetMetaData rsmd = rs.getMetaData();

        ArrayList<String> cols = new ArrayList<String>();           
        int numCols = rsmd.getColumnCount();

        for (int i=1; i<=numCols; i++) {
            cols.add(rsmd.getColumnName(i));
        }

        while (rs.next()) {
            HashMap<String, Object> result = new HashMap<String, Object>();
            for (int i=1; i<=numCols; i++) {
                result.put(cols.get(i-1), rs.getObject(i));
            }
            results.add(result);
        }

        rs.close();
    }

    private void handleException(SQLException e, String sql) {
        System.out.println("SQLException " + e.getErrorCode() + ": " + e.getMessage());
        System.out.println("Statement: " + sql);
        ExceptionAdapter ea = new ExceptionAdapter(e);
        ea.setSQLInfo(e, sql);
        throw ea;
    }

    private void handleException(SQLException e, String sql, ArrayList<Object> parameters) {
        if (parameters.size() < 100) {
            System.out.println("SQLException " + e.getErrorCode() + ": " + e.getMessage());
            System.out.println("PreparedStatement: " + sql.replace("?", "[?]"));
            System.out.println("Parameters: " + parameters.toString());
        }
        ExceptionAdapter ea = new ExceptionAdapter(e);
        ea.setSQLInfo(e, sql, parameters);
        throw ea;
    }
}

谢谢！

Answer 1

该类永远不会关闭连接：this.connection.close()。由于Database是一个Singleton，应用程序不使用连接池（数据源）。所有传入请求仅使用一个连接。

经验法则：每个方法（可能是每个 SQL 语句）获取一个连接。 dataSource.getConnection() 并不昂贵。

这就是我重构该类的方式：

1. 删除公共 getConnection 方法，如果它在 Database 类之外使用，那么您确实存在设计问题，
2. 删除 commit方法。我认为它没有意义，因为 connection.setAutoCommit(false) 从未被调用，并且我没有看到 rollback 方法
3. 删除实例变量 connection code>，而是在每次调用时获取一个连接
4. ，并在每次调用的 finally 块中正确关闭此连接

免责声明：目前不知道您的事务处理是如何工作的，所以我的#2 可能是错误的。

获取连接的方法的示例代码：

Connection c = null;
try {
    c = this.dataSource.getConnection();
    c.executeStatement("select * from dual");
} catch (SQLException e) {
    // handle...
} finally {
    closeConnection(c);
}

有趣的是这个应用程序如何工作:-)

Answer 2

对于简单的应用程序来说，这样做并没有什么错。但是，如果您的应用程序稍微复杂，您可能需要考虑一个简单的框架，例如 iBatis。

不过，有几件事我肯定会做。其一，当语句关闭方式引发异常时，应用程序可能会泄漏连接。所有关闭语句都应移至finally 块内。

因此，不要：

try {
            Statement stmt = this.connection.createStatement();
            result = stmt.executeUpdate(sql);
            stmt.close();
        } catch (SQLException e) {
            this.handleException(e, sql);
        }

这样做：

Statement stmt = null;
try {
        stmt = this.connection.createStatement();
        result = stmt.executeUpdate(sql);
    } catch (SQLException e) {
        this.handleException(e, sql);
    } finally {
        if (stmt != null) stmt.close();
    }

另一件事是我会确保您正在为数据源使用数据库连接池。如果您在 Tomcat 中运行它，希望在 tomcat 安装中定义了一个连接池，并且您的应用程序正在使用它。

编辑：再次查看代码后，我也没有看到数据库连接实际上在哪里关闭。这可能就是您耗尽连接的原因。您需要向Database 类添加一个close 方法，该方法调用connection.close()。并确保在完成查询后调用它。再次，在 try/finally 块中。

Answer 3

您正在以非常不安全的方式使用 JDBC 连接。它可以从多个线程访问，并且不是线程安全的。这是一个 Web 应用程序，多个请求可以同时来自不同的用户。您的应用程序没有更频繁地崩溃真是一个小奇迹。您可以使用多种策略来解决这个问题。您可以将连接存储在 ThreadLocal 或堆栈中。如果要在堆栈上保留连接，则必须在每个方法调用中打开和关闭它们。为了获得一定的性能，您必须使用连接池。连接池在任何情况下都不会造成伤害。

Answer 4

为了回答您有关设计原则的问题，该对象本质上是一个 DAO 对象，它只是不使用命名约定，而且大型应用程序也会有多个此类对象用于不同类型的数据（可能使用它们所使用的基本 DAO 对象）全部继承自）。

大致的想法是，DAO 是处理数据库连接的中心位置，这样您就不必在 Controller 对象中拥有所有代码。

除了其他人已经指出的缺点之外，这是由非常了解面向对象编程的人编写的一些可靠的代码。我的建议是将对象从单例更改为使用连接池管理数据库连接（正如其他人已经提到的）。

这似乎是一个高度抽象的对象，它返回映射（键值对）的数组列表，可用于不同的数据类型，然后在模型对象或数据类型中使用返回的信息来构建 java 对象。