java - 在中型 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. 删除公共(public)getConnection 方法，如果它在Database类之外使用，您确实有设计问题
2. 删除提交 方法。我想这没有意义，因为从未调用过 connection.setAutoCommit(false) 并且我没有看到 rollback 方法
3. 移除实例变量connection，而是每次调用获取一个连接
4. 并在每次调用的finally block 中正确关闭此连接

免责声明:目前不知道您的交易处理是如何进行的，所以我对 #2 的看法可能是错误的。

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

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

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