Using Groovy to externalize SQL queries

A very common use case for enterprise applications is to run complex SQL queries. Usually these queries are large and hard to write inside Java code. SQL queries are big Strings that should span into multiple lines so we can understand what they do. The following example shows a SQL statement inside Java code:

String sqlText =
"  select "
"    user.name "+
"    ,user.age " +
"    ,address.city "+
"  from " +
"    user, address "+
"  where " +
"    user.address_id = address.id " +
"    and user.name like ? " +
"    and address.city like ? ";

This SQL statement is far from the real world queries but it is large enough to illustrate how hard is to write these queries inside Java code.

To make things a little more complicated, imagine the query parameters are optional. If the user doesn't provide the user name or the address city the query must adapt dynamically removing the parameter from the SQL where clause. In pure Java we have one option: split the query String creation so we can test for the presence of the parameters. Something like this:

StringBuffer sqlBuffer = new StringBuffer("  select " +
"    user.name "+
"    ,user.age " +
"    ,address.city "+
"  from " +
"    user, address "+
"  where " +
"    user.address_id = address.id ");

if(paramName != null){
  sqlBuffer.append("    and user.name like ? ");
}

if(paramCity != null){
  sqlBuffer.append("    and address.city like ? ");
}

String sqlText = sqlBuffer.toString();

Now imagine large queries with lots of optional parameters: a real pain. The main problems here are:

• Java has no support to multi line strings so we need to use ugly and error prone string concatenation.
• When the SQL has dynamic parameters the code becomes bigger and confuse.
• It is hard to copy and paste the SQL to test it in query tools like Oracle SQL plus.

There are some good options to solve this problem. Template engines like Velocity and Freemarker or the excellent iBatis could help. In this post I will show how Groovy can be used to create smart query strings.

Groovy provides multi line strings support so we can write queries this way:

def sqlText =
"""
select
  user.name
  ,user.age
  ,address.city
from
  user, address
where
  user.address_id = address.id
  and user.name like ?
  and address.city like ?
"""

This solves the string concatenation problem but we still have to deal with optional parameters. Thanks to groovy embedded template engine. Assuming the query parameters are provided as a java.util.Map, we can rewrite the query like this:

def sqlText =
"""
select
  user.name
  ,user.age
  ,address.city
from
  user, address
where
  user.address_id = address.id
  <% if(userName){ %>
    and user.name like ?
  <% } %>
  <% if(city){ %>
    and address.city like ?
  <% } %>
"""

As Groovy is a JVM language, we can compile Groovy classes to binary Java byte code and use them like regular Java classes. The query templates can be implemented as Groovy classes that inherits from a common base class. The base groovy class provides common template processing logic. The complete Groovy code is provided below:

import groovy.text.*

/**
* Base class for query templates
*/
public abstract class AbstractQueryTemplate{

  /**
  * This method must be implemented by subclasses to provide the SQL template text
  */
  protected abstract String getSqlTemplate();

  public String getSqlText(Map parameters){
    return translateSqlText(parameters)
  }

  private String translateSqlText(Map parameters){
    def engine = new GStringTemplateEngine()
    def template = engine.createTemplate(getSqlTemplate()).make(parameters)

    return template.toString()
  }

}

The query templates implementations inherits from the base class:

public class UserQuery extends AbstractQueryTemplate{

  def static SQL_TEXT =
  """
  select
    user.name
    ,user.age
    ,address.city
  from
    user, address
  where
    user.address_id = address.id
    <% if(userName){ %>
      and user.name like ?
    <% } %>
    <% if(city){ %>
      and address.city like ?
    <% } %>
  """

  protected String getSqlTemplate(){
    return SQL_TEXT
  }
}

After compiling the Groovy classes and putting them into our app classpath, we can use them like in the following Java snippet:

  UserQuery query = new UserQuery();
  Map parameters = new HashMap();
  parameters.put("userName", "John Doe");
  parameters.put("city", "Taipei");
  String sqlText = query.getSqlText(parameters);

As you can see, Groovy is a nice option to simplify the way we write complex SQL queries into enterprise applications.