So we ended the last post pondering on this method:

@Override
public double fetchBalance(
       @NotNull final String stashId)
       throws SQLException {

   String fetchBalanceSql = "select balance from mystash where id = ?";

   try (ResultSet resultSet = sqlExecutor.executeQuery(fetchBalanceSql, stashId)) {

       // Move to first row. Should get only one
       resultSet.next();

       return resultSet.getDouble("balance");
   }
}

…and how we would land up with a disposed ResultSet in the proposed implementation.  SQLExecutor, as shown below, would have closed the Statement & hence in turn closed the ResultSet.

private ResultSet executeQuery(
       String querySql,
       Connection conn,
       Object[] parameters)
       throws SQLException {

   try (PreparedStatement stmt = conn.prepareStatement(querySql)) {

       for (int index = 1; index <= parameters.length; index++) {
           stmt.setObject(index, parameters[index]);
       }

       return stmt.executeQuery();
   }
}

Believe it or not, at some point in your illustrious career you would come across code like this as a solution:

private Object executeQuery(
       String querySql,
       Connection conn,
       Object[] parameters)
       throws SQLException {

   try (PreparedStatement stmt = conn.prepareStatement(querySql)) {

       for (int index = 1; index <= parameters.length; index++) {
           stmt.setObject(index, parameters[index]);
       }

       ResultSet resultSet = stmt.executeQuery();
       resultSet.next();

       return resultSet.getObject("balance");
   }
}

What’s wrong with this?  Well it may be easy to spot now, reading this series.  Your mind is tuned to the the thought process in this series – maybe?  But back at work, you may not give this a second glance – when someone churns up code like this.

Always ensure knowledge of things is where it is supposed to be.  I cannot stress this more.  This may be a simple example, but you will encounter far subtler ones and you will not realize it.  Let’s personify, my favourite trick in the world of designing.

You be StashJDBCDAO & I shall be SQLExecutor.  Let’s list down what you know and what I know.

Spot the problem now?  Take a look below:

SQLExecutor has no knowledge of the concept of “balance”.  Think about who knows the structure of the database in this relationship (above code)?  Or to put it more simply, who knows the exact SQL?  It’s StashJDBCDao.  Not SQLExecutor.  SQLExecutor is simply a messenger boy in this scheme of things.  I know messenger boy is belittling him, but when it comes to concepts of the domain that is what he is.

So please ensure knowledge of concepts is placed in classes/methods it belongs in.  Do not let it seep into any other places inadvertently.

Following is a tad bit more acceptable:

But hardly scalable in terms of reuse.  This method can be used only by DAO(s) expecting to get only one row and the value of one column in it.

We could go all out and do this:

private List<Object[]> executeQuery(
       String querySql,
       Connection conn,
       Object[] parameters,
       List<String> returnColumns)
       throws SQLException {

   try (PreparedStatement stmt = conn.prepareStatement(querySql)) {

       for (int index = 1; index <= parameters.length; index++) {
           stmt.setObject(index, parameters[index]);
       }

       try (ResultSet resultSet = stmt.executeQuery()) {

           List<Object[]> resultToReturn = new ArrayList<>();
           while (resultSet.next()) {

               Object[] row = new Object[returnColumns.size()];
               for (int i = 0; i < returnColumns.size(); i++) {
                   row[i] = resultSet.getObject(returnColumns.get(i));
               }

               resultToReturn.add(row);
           }

           return resultToReturn;
       }
   }
}

Do you like what you see?  You think we are headed in the right direction?  Squinting a bit as you take in this code?  Let’s see what the client code i.e. the code in StashJDBCDao would look like with this change:

@Override
public double fetchBalance(
       @NotNull final String stashId)
       throws SQLException {

   String fetchBalanceSql = "select balance from mystash where id = ?";

   List<Object[]> result = sqlExecutor.executeQuery(
           fetchBalanceSql,
           Collections.singletonList("balance"),
           stashId);

   return (double) result.get(0)[0];
}

If you are starting off building the software, this form of the SQLExecutor.executeQuery(), will actually go a long way without you needing much of a change.  You could provide overloads of SQLExecutor.executeQuery() to move the complexity of the List of Arrays into SQLExecutor.  For example, you could add methods like the following, each one delegating to the other:

// This method expects the query to return a single row
// containing the specified column of a given type
public <T> T executeQueryReturnUniqueColumnValue(
       String querySql,
       String returnColumn,
       Class<T> columnType,
       Object... parameters)
       throws SQLException {

   return (T) executeQueryReturnUniqueColumnValue(
           querySql,
           returnColumn,
           parameters);
}

// This method expects the query to return a single row
// containing the specified column
public Object executeQueryReturnUniqueColumnValue(
       String querySql,
       String returnColumn,
       Object... parameters)
       throws SQLException {

   return executeQueryReturnUnique(
           querySql,
           Collections.singletonList(returnColumn),
           parameters)[0];
}

// This method expects the query to return a single row
// containing all the specified column
public Object[] executeQueryReturnUnique(
       String querySql,
       List<String> returnColumns,
       Object... parameters)
       throws SQLException {

   return executeQuery(
           querySql,
           returnColumns,
           parameters).get(0);
}

Using the first of the above methods, the fetchBalance() method is reduced to just a single line:

@Override
public double fetchBalance(
       @NotNull final String stashId)
       throws SQLException {

   return sqlExecutor.executeQueryReturnUniqueColumnValue(
           "select balance from mystash where id = ?",
           "balance", Double.class, stashId);
}

So everywhere in DAO(s) when you expect to fire a query which returns a single row and you are interested in a specific column’s value, you can use this method.

All good?  But what happened to the spies and agents we ended the last post on?  Call backs?  What happened to it?

Why did we head down this path from the initial implementation?  I recall, we were happy with what we had, except for the darn ResultSet getting closed and not waiting for a tab bit longer.  If we could solve that one piece of the puzzle, it is really up-to each DAO method – how they wish to iterate & interpret the fetched ResultSet.  Repeating the method(s) we started off with initially below:

StashJDBCDao:

@Override
public double fetchBalance(
       @NotNull final String stashId)
       throws SQLException {

   String fetchBalanceSql = "select balance from mystash where id = ?";

   try (ResultSet resultSet = sqlExecutor.executeQuery(
         fetchBalanceSql, stashId)) {

       // Move to first row. Should get only one
       resultSet.next();

       return resultSet.getDouble("balance");
   }
}

SQLExecutor:

private ResultSet executeQuery(
       String querySql,
       Connection conn,
       Object[] parameters)
       throws SQLException {

   try (PreparedStatement stmt = conn.prepareStatement(querySql)) {

       for (int index = 1; index <= parameters.length; index++) {
           stmt.setObject(index, parameters[index]);
       }

       return stmt.executeQuery();
   }
}

Okay, let’s use an analogy for what I am trying to get at….again personify the problem as always. You and me are back :-).

The problem statement is the following.  We speak different languages.  You talk the domain of finance and I talk the domain of database.  Think of world leaders who meet, but speak different languages.  How do they meet and have a discussion?  Yes, they have along with them an interpreter who speaks both languages – at least who is able to syntactically do the necessary translation.  Therein lies the proposal, an elegant solution if you will.  You would be astounded with the number of simple parallels you can draw with the real world when doing software design/programming.

So we enlist an interpreter.  Who knows what a ResultSet is and who knows the data of interest StashJDBCDao.fetchBalance() is interested in from the retrieved ResultSet.  So:

Clear?  Too cryptic?  Here it is in code.  First the interpreter:

Interpreter Interface:

public interface IResultSetInterpreter {

   void interpret(@NotNull final ResultSet resultSet)
           throws SQLException;
}

The implementation for fetchBalance():

private static class BalanceInterpreter 
   implements IResultSetInterpreter {

   private double lastInterpretedBalance;

   public double getLastInterpretedBalance() {
       return lastInterpretedBalance;
   }

   @Override
   public void interpret(ResultSet resultSet) throws SQLException {

       resultSet.next();
       lastInterpretedBalance = resultSet.getDouble("balance");
   }
}

SQLExecutor:

private void executeQuery(
       String querySql,
       Connection conn,
       Object[] parameters,
       IResultSetInterpreter resultSetInterpreter)
       throws SQLException {

   try (PreparedStatement stmt = conn.prepareStatement(querySql)) {

       for (int index = 1; index <= parameters.length; index++) {
           stmt.setObject(index, parameters[index]);
       }

       try (ResultSet resultSet = stmt.executeQuery()) {

           // Ask interpreter to do some interpreting
           resultSetInterpreter.interpret(resultSet);
       }
   }
}

As you can see above, SQLExecutor receives a IResultSetInterpreter from its client, fetches the ResultSet, hands it off to the IResultSetInterpreter for interpretation and then, once the guy is done, closes the ResultSet & Statement.

Now for the fetchBalance() method:

@Override
public double fetchBalance(
       @NotNull final String stashId)
       throws SQLException {

   // The interpreter
   BalanceInterpreter balanceInterpreter = new BalanceInterpreter();

   // Send it in along with executeQuery Call
   sqlExecutor.executeQuery(
           "select balance from mystash where id = ?",
           balanceInterpreter,
           stashId);

   // Get the balance from it and return
   return balanceInterpreter.getLastInterpretedBalance();
}

PS: I thought I would land up calling this interpreter a spy or and agent, but as it turns out interpreter is a more apt name for him.

When you compare this to the earlier solution, the client code, fetchBalance(), seems to be a little longer.  The earlier solution was just one line of client code, albeit more overloaded methods in SQLExecutor.  So which is better?

The earlier case had SQLExecutor become complex with all its overload(s) & in this one fetchBalance().  Can there be a best of both worlds?  If you notice carefully…the only reason fetchBalance() is complicated in the interpreter solution is because the interpreter & inturn SQLExecutor.executeQuery() have void return values.  So:

Interpreter Interface:

public interface IResultSetInterpreter<T> {

   T interpret(@NotNull final ResultSet resultSet)
           throws SQLException;
}

The implementation for fetchBalance():

private static class BalanceInterpreter 
   implements IResultSetInterpreter<Double> {

   @Override
   public Double interpret(ResultSet resultSet) throws SQLException {

       resultSet.next();
       return resultSet.getDouble("balance");
   }
}

SQLExecutor:

private <T> T executeQuery(
       String querySql,
       Connection conn,
       Object[] parameters,
       IResultSetInterpreter<T> resultSetInterpreter)
       throws SQLException {

   try (PreparedStatement stmt = conn.prepareStatement(querySql)) {

       for (int index = 1; index <= parameters.length; index++) {
           stmt.setObject(index, parameters[index]);
       }

       try (ResultSet resultSet = stmt.executeQuery()) {
           return resultSetInterpreter.interpret(resultSet);
       }
   }
}

And the one line fetchBalance() method would be:

@Override
public double fetchBalance(
       @NotNull final String stashId)
       throws SQLException {

   return sqlExecutor.executeQuery(
           "select balance from mystash where id = ?",
           new BalanceInterpreter(),
           stashId);
}

And if you did Lambda(s) you could eliminate the BalanceInterpreter class which is a essentially a functional interface analogous to Function.  But I leave that to you.