好的做法：JDBC连接 [英] Good practices: JDBC Connection

问题描述

可能重复：

何时关闭JDBC中的Connection，Statement，PreparedStatement和ResultSet

我为JDBC连接编写了一个简单的包装器并且它可以工作，但我希望尽可能地使用最佳实践来改进它。它基本上有像 open（）， close（）， isOpened（）等方法， select（）， insert（）， update（）， delete（）和 batch（）。为简单起见，我将仅在此处发布前4个方法。

I've written a simple wrapper for a JDBC connection and it works but I want to improve it with the best practices as possible. It basically has methods like open(), close(), isOpened(), select(), insert(), update(), delete() and batch(). For simplicity I will only post here the first 4 methods.

public class Query{
    private Connection con;
    private PreparedStatement ps;
    private ResultSet rs;

    //Database.open() returns a Connection ready to use
    public void open (Database database) throws DatabaseException, SQLException{
        if (!isOpened ()){
            con = database.open ();
        }
    }

    public void close () throws SQLException{
        if (isOpened ()){
            if (ps != null) ps.close ();
            con.close ();
            con = null;
        }
    }

    public boolean isOpened (){
        return con != null;
    }

    //The query string is the query without the word "select" and can use placeholders (?)
    //The args param it's just an array owith the values of this placeholders
    public ResultSet select (String query, Object[] args) throws SQLException{
        if (ps != null) ps.close ();

        if (isOpened ()){
            ps = con.prepareStatement ("select " + query);
            if (args != null){
                for (int i=0; i<args.length; i++){
                    ps.setObject (i+1, args[i]);
                }
            }
            rs = ps.executeQuery ();
        }

        return rs;
    }
}

注意：

• 可以重复使用相同的查询对象，例如打开和关闭
  ，再次打开后。


• 我'我没有关闭每个查询的连接，我只是关闭
  准备好的语句（这是正确的，或者我可以打开准备好的
  语句，因为Connection对象将关闭它？）


• 当我关闭连接时，所有 PreparedStatement s和
  他们的 ResultSet s也关闭了，对吗？

• The same query object can be reused, for example opening and closing it, and after opening again.
• I'm not closing the connection for every query, i'm just closing the prepared statement (this is correct or I can leave the prepared statement opened because the Connection object will close it?)
• When I close the Connection, all the PreparedStatements and their ResultSets are also closed, right?

用法：

Database database;

//Database initialization

Query query = new Query ();


query.open (database);

ResultSet rs = query.select ("* from user where name=?", new String[]{ "MyName" });
doSomethingWithResult1 (rs);

//Connection is not closed here

ResultSet rs = query.select ("coordx from point where coordy=? and coordz=?", new Float[]{ 0.1, 0.2 });
doSomethingWithResult2 (rs);

query.close ();


query.open (database);

ResultSet rs = query.select ("* from user where name=?", new String[]{ "MyName" });
doSomethingWithResult1 (rs);

//Connection is not closed here

ResultSet rs = query.select ("coordx from point where coordy=? and coordz=?", new Float[]{ 0.1, 0.2 });
doSomethingWithResult2 (rs);

query.close ();

您如何看待？我应该在每次查询后关闭并打开连接吗？我可以在同一连接上的每次查询后打开PreparedStatement吗？这是一个很好的设计？

What do you think? Should I close and open the connection after every query? Can I leave opened the PreparedStatement after every query on the same connection? It's a good design?

推荐答案

完成后，在创建新的PreparedStatement之前，你必须关闭它。在同一个连接上。我遇到了严重问题，因为我没有关闭PreparedStatements。事实证明，在数据库服务器上，分配的资源仅在显式调用PreparedStatement.close（）后才被释放。

You have to close the PreparedStatement after you're done with it and before you create a new one on the same connection. I have had serious problems because I did not close PreparedStatements. It turned out that on the database server there were resources allocated that are only freed after an explicit call of PreparedStatement.close().

如bdares所述，连接应该是不经常打开和关闭。

As bdares commented, the Connection should be opened and closed as infrequently as possible.

这篇关于好的做法：JDBC连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！