Questions about properly closing/committing database transactions and executing queries

Question

I'm working on updating a project and I just want to know best practices of these two questions:

1. Should I continue to use Connections/Statements/ResultSet for executing queries or should I be using something else?
2. Is this the correct way to commit/close Connections/Statements/ResultsSets? I mean am I doing the commits/closes in the correct order with the try/catch/finally blocks in the right places?

Please provide modified code examples of these snippets to make your point clear.

A snippet of code from a select type method:

public  ArrayList getMethod() {

    ArrayList a = new ArrayList();

    Connection con = null;
    ResultSet rs = null;
    Statement stmt = null;

    try {

        con = BoneCPConnection.getConnectionPool().getConnection();     // get a connection
        con.setAutoCommit(true);            
        stmt = con.createStatement();

        String query = "select * from example";

        rs = stmt.executeQuery(query);

        while(rs.next()) {
            System.out.println("Result: "+ rs.getInt(1));
        }

    } catch (Exception e) {
        System.out.println("Issue with getMethod");
        e.printStackTrace();
    } finally {

        try {
            rs.close();
            stmt.close();
            con.close();
        } catch (Exception e) {
            con = null;
        }

        rs = null;
        stmt = null;
        con = null;
    }

    return a;
}

A snippet of code from an update type method

public void updateMethod() {

    ArrayList a = new ArrayList();

    Connection con = null;
    Statement stmt = null;
    int updateCount = null;

    try {

        con = BoneCPConnection.getConnectionPool().getConnection();     // get a connection     
        stmt = con.createStatement();

        String query = "update example set id = 1";

        updateCount = stmt.executeUpdate(query);

        System.out.println("Result: "+ updateCount);    

    } catch (Exception e) {
        System.out.println("Issue with updateMethod");
        e.printStackTrace();
    } finally {

        try {
            con.commit();
            stmt.close();
            con.close();
        } catch (Exception e) {
            con = null;
        }

        stmt = null;
        con = null;
    }
}

Answer 1

At a minimum, you should probably switch to PreparedStatement instead of plain Statements. The reason for this is that the JDBC driver will, in most cases, send the statement to the database upon creation so it can be precompiled. You can then bind your parameters to the statement and execute. Beyond the performance benefits of precompilation, you also get a little bit of protection against SQL injection attacks since way in which you set the parameters is more strongly typed. There is a good description of prepared statements on Oracle's site.

If you're using Spring (or want to take the leap to add it to your system), you can take a look at the JdbcTemplate and JdbcDaoSupport classes (both outlined here). The main advantage is that it takes care of the connection cleanup code for you (so you don't have to worry as much about missing a close call).

Similarly, if you add Spring to your project, you can look at using it to configure your transactions (either via annotations or from with the Spring context file). This will allow you to pull the transaction management out of the actual implementation and make the code within your Dao a bit cleaner.

As for your handling of commit/close: you should move your commit statements out of your finally blocks and into the main execution path. You should keep your close statements in the finally block, though since you want to ensue they occur no matter what.

An example of what your update code would look like using PreparedStatements is:

public void updateMethod() {
    Connection con = null;
    PreparedStatement stmt = null;
    int updateCount = null;

    try {
        con = BoneCPConnection.getConnectionPool().getConnection();
        stmt = con.prepareStatement("update example set id = ?");        
        stmt.setInt(1,1);
        updateCount = stmt.executeUpdate(query);
        con.commit();
    } catch (Exception e) {
       if(con != null){
        con.rollback();
       }
    } finally {

        try {
          if(stmt != null){
            stmt.close();
          }
          if(con != null){                
            con.close();
          }
        } catch (Exception e) {
            con = null;
        }        
    }
}

If you were using Spring's JdbcDaoSuport, it would look like:

public class YourDao extends JdbcDaoSupport{

  public void updateMethod(){
    String sql = "update example set id = ?";
    getJdbcTemplate().update(sql, new Object[] { new Integer(1)});           
  }

}

Answer 2

1. You should almost always prefer PreparedStatement for parametrized queries as it:

   • protects you from sql injection

   • gets precompiled by database

2. You don't need to reinitialize rs, stmt and con to null as they go out of scope at the end of the method

3. You're almost always better off using some db access framework. One example is Spring with it's JdbcTemplate, another is Apache Commons DBUtils

4. If you're updating the system, update it to java 7 - you'll get try-with-resources that will close your connections and statements for free and your code will look like this:

    public void updateMethod() {
        int updateCount = 0;

        String query = "update example set id = ?";
        try (Connection con = BoneCPConnection.getConnectionPool().getConnection();                         
            PreparedStatement stmt = con.prepareStatement(query)) {
                stmt.setInt(1, 1);

                updateCount = stmt.executeUpdate();

                System.out.println("Result: " + updateCount);

        } catch (Exception e) {
            System.out.println("Issue with updateMethod");
            e.printStackTrace();
        }
    }

    public ArrayList getMethod() {

        ArrayList a = new ArrayList();
        String query = "select * from example";

        try (Connection con = BoneCPConnection.getConnectionPool().getConnection(); 
            Statement stmt = con.createStatement()) {

            con.setAutoCommit(false);

            try (
                ResultSet rs = stmt.executeQuery(query)) {

                while (rs.next()) {
                    System.out.println("Result: " + rs.getInt(1));
                }

            }

        } catch (Exception e) {
            System.out.println("Issue with getMethod");
            e.printStackTrace();
        }

        return a;

    }