SQL Combinators in Java

Posted on April 13, 2011 by Tommy McGuire

Labels: data structure, software development, java

Combinators are another technique common in functional programming which seems to be significantly less common in object-oriented languages. This is unfortunate, because combinators work very well in an object-oriented design. What I am going to present below is a very simple use of them that hopefully displays some of their features. Other common examples include parsers and various embedded domain specific languages.

Now, I will admit that I am a crazed neo-Luddite. I am proud to say that I hate persistent objects, believe remote procedure calls are evil, and regard most of the enterprise development infrastructure and practices with a considerable amount of disdain. But the bottom line is that I do not use Hibernate, JPA, or any of the other object/relational/database/mapper hoo-has. They, in the immortal words of Del, purport to make something easier that was not difficult in the first place. In doing so, they stack another layer of stuff on top of the actual solution; witness the fun when combining OSGi modularity with Hibernate.

Instead, I am perfectly happy writing SQL statements in my cheerfully modular code, performing just the operations that I need when I need them, and enjoying the control that I get. However, there are times when that is not enough, when plain SQL statements will not do the job. One such case that has come up several times in my career is a semi-general, ad hoc query based on a HTTP request.

For example, consider a web form that displays rows from a database table. This web form submits a number of parameters, and based on those parameters the server-side code formulates a query that returns the appropriate rows, which are then returned to the client. Now, there are two ways to formulate a SQL query: parameterized and non-parameterized. The latter involves building a text string including the SQL and the values to be selected; the values must be appropriately sanitized to avoid SQL injection. I prefer the parameterized option. The thing about the parameterized option is that it needs two passes: one to build the SQL, "SELECT * FROM table WHERE whosit = ?" and one to inject "Fred" to match ? number 1 (remembering the order that the ?'s came in, too). This is where the combinators enter the picture. These combinators provide a way of building the where-clause of a SQL statement in a semi-general way.

To demonstrate, this code selects all of the rows with NAMEs equal to "Doe".

Predicate p = Predicate.stringEquals("NAME", "Doe");
statement = connection.prepareStatement("SELECT * FROM people WHERE " + p.toSql());
p.parameterize(statement);
ResultSet rs = statement.executeQuery();

The following example selects all of the rows with NAMEs equal to "Doe" and ADDRESSes LIKE "Main Street".

Predicate p = Predicate.and();
p.add(Predicate.stringEquals("NAME", "Doe"));
p.add(Predicate.stringLike("ADDRESS", "Main Street"));
statement = connection.prepareStatement("SELECT * FROM people WHERE " + p.toSql());
p.parameterize(statement);
ResultSet rs = statement.executeQuery();

Finally, this code selects all of the rows with NAMES equal to "Doe" and orders the result by "CITY", ascending.

Predicate p = Predicate.orderedByAscending(Predicate.stringEquals("NAME", "Doe"), "CITY");
statement = connection.prepareStatement("SELECT * FROM people WHERE " + p.toSql());
p.parameterize(statement);
ResultSet rs = statement.executeQuery();

The primary abstract interface to a combinator is to evaluate it. (This is one of the reasons they work so well in functional languages.) For my SQL combinator, I wanted a little richer interface:

public abstract class Predicate
{
  /**
   * Return the SQL for the given predicate, including parameter markers (?).
   * @return A String.
   */
  public abstract String toSql();
  
  /**
   * Set the parameters in the {@link PreparedStatement} associated with the parameter markers from the toSQL string.
   * This method must be called after the statement is created from the string and before the query is executed.
   * @param statement {@link PreparedStatement} which should have the values injected into it.
   * @throws SQLException If the parameterization fails.
   */
  public void parameterize(PreparedStatement statement) throws SQLException
  {
    [...]
  }

  /**
   * Test whether the {@link Predicate} represents an empty WHERE clause.
   * 
   * @return True if the predicate represents an empty WHERE clause, false otherwise.
   */
  public boolean isEmpty() { return false; }

[...]

The two key methods are toSql(), which returns the SQL predicate clause, and parameterize(), which accepts a PreparedStatement generated from the SQL and inserts the values. A base-case combinator (ignoring the empty one) is something that compares a value with a column:

  public static class StringEquals extends Predicate
  {
    private final String column;
    private final String value;
    
    public StringEquals(String column, String value)
    {
      this.column = column;
      this.value = value;
    }

    @Override
    public String toSql()
    {
      return String.format("(%s = ?)", column, operator);
    }

    @Override
    protected int setParameter(int index, PreparedStatement statement) throws SQLException
    {
      statement.setString(index, value);
      return index + 1;
    }
  }

The parameterize() method of the Predicate calls setParameter() for the Predicate structure; each setParameter returns the next index value into the SQL statement.

  [...]
  public void parameterize(PreparedStatement statement) throws SQLException
  {
    this.setParameter(1, statement);
  }
  [...]

By ensuring that the order of the ?'s in the SQL is the same as the order that setParameter() is called, the values are associated with the appropriate clauses.

Another combinator class is used to search for timestamps appearing on a given day:

  public static class OnDate extends ColumnComparison
  {
    private final String column;
    private final Date date;
    
    protected OnDate(String column, Date date)
    {
      this.column = column;
      this.date = date;
    }

    @Override
    public String toSql()
    {
      return String.format("(%s BETWEEN ? AND ?)", column);
    }

    @Override
    protected int setParameter(int index, PreparedStatement statement) throws SQLException
    {
      final Calendar calendar = Calendar.getInstance();
      calendar.setTime(date);
      statement.setDate(index, new Date(calendar.getTimeInMillis()));
      calendar.roll(Calendar.DATE, true);
      statement.setDate(index + 1, new Date(calendar.getTimeInMillis()));
      return index + 2;
    }
  }

The interesting part of combinators (and the ad hoc query) is the ability to generate a higher-level Predicate from one or more more-basic predicates. In this case, that is the purpose of And and Or:

  private static class And extends Aggregate { public And() { super(" AND "); } }
  private static class Or  extends Aggregate { public Or() { super(" OR "); } }

which are based on a general Aggregate:

  public abstract static class Aggregate extends Predicate implements Collection
  {
    protected final String join;
    protected final List subPredicates = new ArrayList();
    
    protected Aggregate(String join)
    {
      this.join = join;
    }
    
    @Override
    public String toSql()
    {
      List sqls = new ArrayList(subPredicates.size());
      // Some hot mapping action here.
      for (Predicate sub : subPredicates)
      {
        sqls.add(sub.toSql());
      }
      return StringUtils.join(sqls, join);
    }
    
    @Override
    protected int setParameter(int index, PreparedStatement statement) throws SQLException
    {
      for (Predicate sub : subPredicates)
      {
        index = sub.setParameter(index, statement);
      }
      return index;
    }
  [...]
}

The final piece is the capability of ordering the results of the query.

  public static class Ordered extends Predicate
  {
    private final String sortColumn;
    private final Predicate predicate;
    private final boolean ascending;
    
    protected Ordered(Predicate predicate, boolean ascending, String column)
    {
      this.predicate = predicate;
      this.sortColumn = column;
      this.ascending = ascending;
    }
    
    @Override
    protected int setParameter(int index, PreparedStatement statement) throws SQLException
    {
      return predicate.setParameter(index, statement);
    }

    @Override
    public String toSql()
    {
      // Default: ASC
      final String asc = ascending ? "" : " DESC";
      return predicate.toSql() + " ORDER BY " + sortColumn + asc;
    }
  }

There is a small problem here; once an Ordered has been wrapped around a Predicate, the result is no longer really a Predicate; it admits the same interface, but an Ordered instance cannot legitimately be added to an And or Or collection. I will leave the fix as an exercise for the reader.

To make using Predicates easier, I included a number of static methods that can be used to create individual instances.

and() returns an And aggregate which will join its sub-predicates with AND.
or() returns an Or aggregate which will join its sub-predicates with OR.
stringEquals(String, String) returns a simple StringEquals predicate joining a column with a String via '='; this comparison is case sensitive.
stringEqualsIgnoreCase(String, String) returns a simple predicate joining a column with a String via '='; this comparison is case-insensitive.
stringLike(String, String) returns a simple predicate joining a column with a SQL pattern expression via 'LIKE'; this comparison is case-insensitive.
onDate(String, Date) returns a simple predicate joining a column with a Date; BETWEEN is used to allow any time between DATE and the next day.
orderedBy(Predicate, boolean, String) returns an Ordered predicate, ordered as described by the flag and the column names.

The examples I gave are not as syntactically nice as some of the DSLs implementable in Haskell, say. (But in Java, what is?) However, it does allow me to cleanly build up a predicate in stages