Applying the template pattern to eliminate boilerplate code

At one point in the enterprise application, we saw some code that looked like code we had already written before in the same application. That is actually boilerplate code. It is code that we often have to write again and again in the same application to accomplish common requirements in different parts of the application. Unfortunately, there are a lot of places where Java APIs involve a bunch of boilerplate code. A common example of boilerplate code can be seen when working with JDBC to query data from a database. If you've ever worked with JDBC, you've probably written something in code that deals with the following:

• Retrieving a connection from the connection pool
• Creating a PreparedStatement object
• Binding SQL parameters
• Executing the PreparedStatement object
• Retrieving data from the ResultSet object and populating data container objects
• Releasing all database resources

Let's look at the following code, it contains boilerplate code with the JDBC API of the Java:

    public Account getAccountById(long id) { 
      Connection conn = null; 
      PreparedStatement stmt = null; 
      ResultSet rs = null; 
      try { 
        conn = dataSource.getConnection(); 
        stmt = conn.prepareStatement( 
          "select id, name, amount from " + 
          "account where id=?"); 
        stmt.setLong(1, id); 
        rs = stmt.executeQuery(); 
        Account account = null; 
        if (rs.next()) { 
          account = new Account(); 
          account.setId(rs.getLong("id")); 
          account.setName(rs.getString("name")); 
          account.setAmount(rs.getString("amount")); 
        } 
        return account; 
      } catch (SQLException e) { 
      } finally { 
          if(rs != null) { 
            try { 
              rs.close(); 
            } catch(SQLException e) {} 
          } 
          if(stmt != null) { 
            try { 
              stmt.close(); 
            } catch(SQLException e) {} 
          } 
          if(conn != null) { 
            try { 
              conn.close(); 
            } catch(SQLException e) {} 
          } 
        } 
      return null; 
    } 

In the preceding code, we can see that the JDBC code queries the database for an account name and amount. For this simple task, we have to create a connection, then create a statement, and finally query for the results. We also have to catch SQLException, a checked exception, even though there's not a lot you can do if it's thrown. Lastly, we have to clean up the mess, closing down the connection statement and result set. This could also force it to handle JDBC's exception, so you must catch SQLException here as well. This kind of boilerplate code seriously hurts reusability.

Spring JDBC solves the problem of boilerplate code by using the Template Design pattern, and it makes life very easy by removing the common code in templates. This makes the data access code very clean and prevents nagging problems, such as connection leaks, because the Spring Framework ensures that all database resources are released properly.