Lisp and Java

List users =

  rsMapQuery(conn,

             new RFMaker(User.class),

             "SELECT first_name, last_name, user_id " +

             "FROM users");

User and any other classes that are going to be loaded from the database need only define a constructor that takes a ResultSet as a param. At that point, you might want to put the SQL strings into the classes as well, so that all of the information about how to load, for instance, a User from the database is stored in one place.

Extending the First-Class Function Approach

"Why not use an object/relational bridge?" we hear you say. Yes, yes, yes, you could use one of the innumerable object/relational bridges out there, possibly specifying the table-to-class mapping via a set of XML files (and can we just say, ugh). Yes, it's possible that that would make some of the simple queries above disappear altogether. However, the really nice thing about the first-class function approach is its flexibility: for example, it can be easily extended to handle more complex queries (as we saw above for the newUsers), or even new kinds of loops.

To make this concrete, let's say you need to use a prepared statement for your SQL query. If you're still building Users, you can write a new mapping function, and use the same RFMaker constructor. This is where the power of having abstracted the User creation code away from the looping code really starts to shine. Et voila:

static List rsMapPrepared(Connection conn,

                          RowFunc f,

                          String query,

                          String[] vals)

  throws SQLException {



  PreparedStatement pStmt = conn.prepareStatement(query);

  for(int i = 0 ; i < vals.length ; i++) {

    pStmt.setString(i+1, vals[i]);  // JDBC is 1-indexed.

  }

  ResultSet rs = pStmt.executeQuery();

  ArrayList result = new ArrayList();

  while(rs.next()) {

    result.add(f.of(rs));

  }

  return result;  

}



List users =

  rsMapPrepared(conn,

                new RFMaker(User.class),

                "SELECT first_name, last_name, user_id " +

                "FROM users WHERE last_name = ?",

                new String[] { "Riemann" });

Or, you could decide that you'd like to create HashMaps from each row in your ResultSet, rather than building instances of a specific class. This can be a very nice approach, if all you're going to do is display the resulting list via a template system such as Velocity or Freemarker. Taking advantage of some ResultSetMetaData, we can write the following RowFunc:

static RowFunc makeHash = new RowFunc() {

    public Object of(ResultSet rs) throws SQLException {

      HashMap hm = new HashMap();

      ResultSetMetaData rsmd = rs.getMetaData();



      int columnCount = rsmd.getColumnCount();

      for(int i = 0 ; i < columnCount ; i++) {

        // Note that JDBC columns are 1-indexed

        String column = rsmd.getColumnName(i+1);

        hm.put(column, rs.getString(column));

      }

      return hm;

    }

  };

And now we can write any SQL queries we like and obtain a List of HashMaps from the result:

List usersAndDepts =

  rsMapQuery(conn,

             makeHash,

             "SELECT first_name, last_name, user_id, " +

             "department_name FROM users, departments" +

             "WHERE users.dept_id = departments.dept_id");

Note: The above code suffers from some inefficiency because it rereads all of the column names for each row. If this proved to be a problem, it would be fairly easy to create a variant of makeHash that was initialized with an array of column names once per ResultSet.

We've just scratched the surface of what you can do with first-class functions. As we confront problems in our programming practices, we should always be striving to write code that states what it does as clearly and simply as possible. The first-class function approach, by giving you new tools to capture patterns in your code, can lead to clear and elegant solutions to a wide variety of problems. The new abstractions that you can build will often let you step back and see your overall program in a new light. And that's something always worth learning.

Resources

Lisp has a long and complex history, with enough brilliant innovations, bitter rivalries, and failed startups to keep a team of historians in tenure. For some details on where the bodies are buried, check out Richard Gabriel and Guy Steele's excellent paper, "The Evolution of Lisp" (PDF).

Dan Milstein is an independent programmer and consultant in the Boston area.

Return to ONJava.com.