Good practices: JDBC Connection [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

when to close Connection, Statement, PreparedStatement and ResultSet in JDBC

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

Notes:

• 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?

Usage:

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 ();

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?

---

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().

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

---

Connection pooling

Use a connection pool. Each transaction in your app will get a connection from this pool, execute all the things needed, rollback or commit and close the connection (it will return the conn to the pool).

You could give you Query object a reference to the pool and open will get the connection and close will close it (returning it in fact).

Prepared Statement

Try to reuse your prepared statement for similar queries. That way the DB will reuse the previous query plan and it will be faster. It has a lot of sense if you are executing a lot of queries of the same form.

How?

• Maintain last PS open
• If you close the connection or whatever, close it
• if you query for the same query string than the previous reuse the PS you saved
• if it's not the same query string... close it and create a new one

---

best paectise is : use single Connection object for all queries if those queires are part of same method and for each query close the PreparedStatement, after its been used.

---

Use a Connection pool. So you do not keep creating.