Questions on correctly closing database transactions and query execution

Question

I am working on a project update and I just want to know the best practices for these two questions:

• Should I continue to use Connections / Statementments / ResultSet to execute queries, or should I use something else?
• Is this the right way to commit / close connections / statements / result sets? I mean, am I committing / closing in the correct order using try / catch / finally blocks in the right places?

Please provide modified code examples of these fragments so that your point is understandable.

code snippet from a method of type select :

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

Counting code from a method of type update

 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 switch to PreparedStatement instead of simple expressions. The reason for this is that the JDBC driver in most cases sends an instruction to the database at creation, so it can be precompiled. Then you can associate your parameters with the instruction and execute. In addition to the benefits of precompiling performance, you also get a little protection against SQL injection attacks, since the way you configure parameters is more strongly typed. There is a good description of trained operators on the Oracle Web site.

If you use Spring (or want to take a jump to add it to your system), you can take a look at the JdbcTemplate and JdbcDaoSupport (both highlighted here ). The main advantage is that it takes care of the connection clearing code for you (so you don’t have to worry about missing a close call).

Similarly, if you add Spring to your project, you can use it to configure transactions (via annotations or from the Spring context file). This will allow you to take transaction management out of the actual implementation and make the code in your Dao a little cleaner.

As for your commit / close processing: you have to transfer your transactional statements from your finally blocks and to the main execution path. You should keep your closed statements in a finally block, although since you want them to happen, they happen no matter what.

An example of how your update code will look with PreparedStatements is as follows:

 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 used Spring JdbcDaoSuport, it would look like this:

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

Answer 2

• You should almost always prefer PreparedStatement for parameterized queries:

  • protects you from SQL injection

  • gets the precompiled database

• You do not need to reinitialize rs, stmt and con to null, as they go beyond the scope at the end of the method.

• You are almost always better off using some database access infrastructure. One example is Spring with it JdbcTemplate, another is Apache Commons DBUtils

• If you upgrade the system, upgrade it to java 7 - you will get trial resources that will free up your connections and instructions, 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; } 

Answer 3

The most important thing is to close the connection. Typically, you want to try / finally every resource (rs, stmt, conn) when closing.

also you do not want to commit in the finally block. you only want to commit on a happy path (inside the catch block).

Finally, never, never, never add "e.printStackTrace ()" to your code.

UPDATE:

Explanation for the previous warning about printStackTrace() : this is the smell of code, and this most likely means that the program is swallowing an exception. it is automatically inserted by the IDE or manually inserted by programmers, and to a large extent means that they actually do not handle the exception. someday, somewhere, they will want to see this exception, but it will be lost forever. (then there is the worst case scenario when the code accidentally continues, as an exception that never occurred, leaving the code in borked state). There are basically 2 options:

• if you want to continue working after an exception, write it to the appropriate logger (use java.util.Logger, if nothing else)
• if you cannot handle the exception, retype it

(technically, there is also a somewhat and far apart case where this is a really ignored exception, in which case put a comment as // i never, ever care if i get this exception ).

Answer 4

The answer to question 1: - if you are not very happy with the frameworks, and the business logic of interacting with the database is quite simple, you can continue to use this approach. Other alternatives: Hibernate, JPA, or Spring JDBC. They all do the same as your code, but are hidden in the implementation. Each of these structures has its own advantages / disadvantages. But again, a matter of preference / requirements / complexity.

The answer to question 2: - you are doing open / closed resources in the correct order, but there are some problems: 1. Try to catch a more specific exception, not an exception. SQLException should be hooked in your example. 2. It is better to use PreparedStatement instead of Statement to avoid SQL injection or if you execute the same query multiple times. 3. Production code should not use System.out.println or e.printStackTrace, but for spikes or using new things this is not a problem. (Regarding the previous answer).