SqlClosure

Internally q2o makes heavy use of SqlClosure, a more technical class.

We'll work from simple to complex. In the first examples, the savings in code will not seem that great, but as we go through the examples you'll notice the code using q2o vs. pure Java/JDBC gets more and more compact.

q2o provides you with two important classes. Let's look at the first, which has nothing to do with Java objects or persistence. This class just makes your life easier when writing raw SQL (JDBC). It is called SqlClosure.

Typical Java pure JDBC with [mostly] correct resource cleanup:

public int getUserCount(String usernameWildcard) throws SQLException {
   Connection connection = null;
   try {
      connection = dataSource.getConnection();
      PreparedStatement stmt = connection.prepareStatement("SELECT COUNT(*) FROM users WHERE username LIKE ?");
      stmt.setString(1, usernameWildcard);

      int count = 0;
      ResultSet resultSet = stmt.executeQuery();
      if (resultSet.next() {
         count = resultSet.getInt(1);
      }
      resultSet.close();
      stmt.close();
      return count;
   }
   finally {
      if (connection != null) {
         try {
            connection.close();
         }
         catch (SQLException e) {
            // ignore
         }
      }
   }
}

Now the same code using q2o's SqlClosure (with completely correct resource cleanup):

public int getUserCount(final String usernameWildcard) {
   return new SqlClosure<Integer>() {
      public Integer execute(Connection conn) {
          PreparedStatement stmt = conn.prepareStatement("SELECT COUNT(*) FROM users WHERE username LIKE ?");
          stmt.setString(1, usernameWildcard);
          ResultSet resultSet = stmt.executeQuery();
          return (resultSet.next() ? resultSet.getInt(1) : 0;
      }
   }.execute();
}

Important points:

• The SqlClosure class is a generic (templated) class
• The SqlClosure class will call your execute(Connection) method with a provided connection
  • The provided connection will be closed quietly automatically (i.e. exceptions in connection.close() will be eaten)
• SqlExceptions thrown from the body of the execute() method will be wrapped in a RuntimeException

Now with a Java 8 Lambda

public int getUserCount(final String usernameWildcard) {
   return SqlClosure.sqlExecute(connection -> {
      PreparedStatement stmt = connection.prepareStatement("SELECT COUNT(*) FROM users WHERE username LIKE ?"));
      stmt.setString(1, usernameWildcard);
      ResultSet resultSet = stmt.executeQuery();
      return (resultSet.next() ? resultSet.getInt(1) : 0;
   });
}

Note that the lambda automatically closes Statement and ResultSet resources.

SqlClosure is typically constructed as an anonymous class, and you must provide the implementation of the execute(Connection connection) method. Invoking the execute() method (no parameters) will create a Connection and invoke your overridden method, cleaning up resources in a finally, and returning the value returned by the overridden method. Of course you don't have to execute the closure right away; you could stick it into a queue for later execution, pass it to another method, etc. But typically you'll run execute it right away.

Let's look at an example of returning a complex type:

public Set<String> getAllUsernames() {
   return new SqlClosure<Set<String>>() {
      public Set<String> execute(Connection connection) {
         Set<String> usernames = new HashSet<>();
         Statement statement = connection.createStatement();
         ResultSet resultSet = statement.executeQuery("SELECT username FROM users");
         while (resultSet.next()) {
            usernames.add(resultSet.getString("username"));
         }
         return usernames;
      }
   }.execute();
}

And again with Java 8 Lambda

public Set<String> getAllUsernames() {
   return SqlClosure.sqlExecute(connection -> {
      Set<String> usernames = new HashSet<>();
      Statement statement = connection.createStatement();
      ResultSet resultSet = statement.executeQuery("SELECT username FROM users");
      while (resultSet.next()) {
         usernames.add(resultSet.getString("username"));
      }
      return usernames;
   });
}

Even if you use no other features of q2o, the SqlClosure class alone can really help to cleanup and simplify your code.