Java Remove repeated try, catch, finally boilerplate from DAO

Question

I have a DAO class with many methods that have a lot of repeated code along the lines of: -

public void method1(...) {
  Connection conn = null;
  try {
      //custom code here
  } catch (SQLException e) {
     LOG.error("Error accessing the database.", e);
     throw new DatabaseException();
  } catch (QueryNotFoundException e) {
     LOG.error("Error accessing the database.", e);
     throw new DatabaseException();
  } finally {
     if (conn != null)
        connectionPool.returnConnection(conn);
  } 

public void method2(...) {
  Connection conn = null;
  try {
      //different custom code here
  } catch (SQLException e) {
     LOG.error("Error accessing the database.", e);
     throw new DatabaseException();
  } catch (QueryNotFoundException e) {
     LOG.error("Error accessing the database.", e);
     throw new DatabaseException();
  } finally {
     if (conn != null)
        connectionPool.returnConnection(conn);
  } 

I would like to restructure this class to put the try, catch, finally in one place to avoid repetation. How would I accomplish this?

Answer 1

Create an interface for ex. Executable:

 public interface Executable() {

   void exec() throws SqlException();

 }

Refactor each of your dao method to :

public void method1() {
   execute(new Executable() {

     @Override
     public void exec() throws SqlException() {
          // your code here
     }
  });
  } 

Create the following method execute in your DAO:

private void execute(Executor ex) {
    try {
      ex.exec();
    } catch(...) {
      ...
    } finally {
       ...
    }
}

Answer 2

I think what you should use is Spring-JDBC's JdbcTemplate

Even if you don't use spring to manage your application, you could programmatically use JdbcTemplate to abstract away all the connection management and error handling.

Example code:

List<Actor> actors = this.jdbcTemplate.query(
        "select first_name, last_name from t_actor",
        new RowMapper<Actor>() {
            public Actor mapRow(ResultSet rs, int rowNum)
            throws SQLException {
                Actor actor = new Actor();
                actor.setFirstName(rs.getString("first_name"));
                actor.setLastName(rs.getString("last_name"));
                return actor;
            }
        });

As you can see, you deal with the actual query only, not with the infrastructure stuff around it.