15

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?

Community
  • 1
  • 1
Gabriel Llamas
  • 18,244
  • 26
  • 87
  • 112
  • 8
    Closing and re-opening connections for each query is a bad idea. Making a connection is really, really expensive. –  Sep 02 '11 at 08:18
  • 1
    Then can I leave opened the PreparedStatement or should I close it for every query? – Gabriel Llamas Sep 02 '11 at 08:23
  • 1
    You can leave everything open until you're actually done using the DB. Closing a connection will track down and close all of the stuff (statements, resultsets) that it spawned. –  Sep 02 '11 at 08:27
  • You can probably just use Jakarta Commons DBUtils. It seems to do the same things as your wrapper. – Thilo Sep 02 '11 at 08:36
  • Possibility DUplicate : http://stackoverflow.com/q/1039419/668970 – developer Sep 02 '11 at 12:12

4 Answers4

16

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.

pete
  • 1,878
  • 2
  • 23
  • 43
boes
  • 2,835
  • 2
  • 23
  • 28
  • 1
    This is not correct. 1. You can have multiple `PreparedStatements` at the same time. 2. Closing the connection automatically closes all statements and result sets. 3. The `Connection` should be opened and closed as *frequently* as possible, and the performance mitigated by using a connection pool like Apache DBCP. – user207421 Jul 02 '18 at 00:27
8

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
helios
  • 13,574
  • 2
  • 45
  • 55
2

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.

developer
  • 9,116
  • 29
  • 91
  • 150
1

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

ssedano
  • 8,322
  • 9
  • 60
  • 98
  • The connection pool is created when I initialize the database. The connection that my database.open() method returns is a Connection from a pool because I'm using a datasource. – Gabriel Llamas Sep 02 '11 at 08:52