JDBC Best practice

Question

i'm gonna create class that will operate on database. The class will have functions addRecord(), getAllRecords(), stuff like that. I'm looking for a good approach to design the class. Should i have to: 1) create new Connection for every function. Like this:

void readRecords(){
    try {
        Connection con = DriverManager.getConnection (connectionURL);

        Statement stmt = con.createStatement();

        ResultSet rs = stmd.executeQuery("select moviename, releasedate from movies");

        while (rs.next())
            System.out.println("Name= " + rs.getString("moviename") + " Date= " + rs.getString("releasedate");
    }
    catch (SQLException e) {
        e.printStackTrace();
    }
    catch (Exception e) {
        e.printStackTrace();
    }
    finally {
        con.close();
    }
}

or

2) it's better to have one connection as a memeber variable

class MyClass{
     private  Connection con;

     public MyClass(){
          con = DriverManager.getConnection (connectionURL);
     }
}

and create just the statement for every function.

3) or something else...

Answer 1

Both approaches are bad. The first one won't allow you to implement proper transaction management, since you can't call several methods inside the same transaction. The latter one requires unnecessary creation of multiple objects.

The best approach would be to introduce a notion of the current connection, which can be obtained from some kind of transactional context. Basically, it should look like this:

beginTransaction(...); // Opens connection and starts transaction

readRecords(...); // Uses the current connection
addRecord(...);
...

commitTransaction(...); // Commits transaction and closes connection

The simpliest but not very elegant implementation is to open a Connection inside the calling method (which defines boundaries of the transaction) and pass it to your methods as a parameter.

More sophisticated solution is to create a static ThreadLocal storage for the current Connection, place it there when you start a transaction and obtain it from that storage inside your methods. Some frameworks implement this approach implicitly, for example, Spring Framework.

Note that connection pooling is completely orthogonal to these matters.

Answer 2

If there are frequent regular jdbc calls, then use a database connection pool.

Answer 3

Connection pooling is the way to go. The biggest reason is that on average the time it takes for the DB access (DML etc) is much smaller than the time it takes to create a connection and then close the connection. Additionally, don't forget to close your ResultSet, PreparedStatement and Connection variables after the transaction is done.

You can use tomcat or apache connection pooling classes. THese classes are defined for example in the package

org.apache.commons.dbcp.*;

org.apache.tomcat.dbcp.dbcp.*;

where dbcp stands for database connection pooling.