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.

Mapas em Java - Reduzindo o sofrimento

O aparecimento de linguagens modernas como o Groovy e o Ruby me levou a uma triste constatação: Java é uma linguagem completamente defasada que pouco colabora para a produtividade do programador. Antes que alguém comece a jogar pedras, quero deixar claro que estou falando da linguagem, não da JVM, mesmo porque outras linguagens como Groovy e JRuby rodam na JVM. Quando comecei a estudar Groovy, o recurso que mais me impressionou foi o excelente suporte a coleções e mapas. Estas estruturas são de uso muito frequente, mas o Java simplesmente não tem suporte nativo para elas, já que a manipulação de listas e mapas é feita via API do JDK. Se você nunca usou listas e mapas numa linguagem como Groovy ou Ruby, você provavelmente está relativamente satisfeito com a API de collections do Java. É mais ou menos como alguém que sempre teve um carro 1.0 e não sabe o quanto ele é fraco até o dia que dirige um carro 1.8. Nada melhor que um exemplo para ilustrar:

Em Groovy:

// Definindo e inicializando um mapa em Groovy
def errorMap = [
 1:"Database is down"
 ,2:"Network is down"
 ,3:"Something else"
]

Em Java:

//Mesmo mapa em Java
Map errorMap = new HashMap();

errorMap.put(1, "Database is down");
errorMap.put(2, "Network is down");
errorMap.put(3, "Something else");

Nos dois exemplos anteriores temos quatro linhas de código Java contra apenas uma do Groovy. Se o mapa for atribuído a uma variável estática, que aliás é muito comum no caso de mapas "constantes", a vantagem do Groovy fica ainda maior.

Em Groovy:

// Definindo e inicializando um mapa estático em Groovy
def static errorMap = [
 1:"Database is down"
 ,2:"Network is down"
 ,3:"Something else"
]

Em Java:

//Mesmo mapa estático em Java
static Map errorMap = new HashMap();

static{
 errorMap.put(1, "Database is down");
 errorMap.put(2, "Network is down");
 errorMap.put(3, "Something else");
}

Resumindo, o Java não permite criar e inicializar um mapa ao mesmo tempo, o que acaba levando o programador a escrever muito código simplesmente para criar e preencher um mapa. Esta deficiência em particular poderia não existir se o método Map.put() retornasse a instância do próprio Map, como faz o método StringBuffer.append(), mas infelizmente o retorno do put() é void :(

Para melhorar esta triste realidade, podemos utilizar um MapBuilder, que usa o mesmo pattern do StringBuffer. O método put() do MapBuilder retorna a própria instância do builder, o que permite encadear chamadas ao put numa única linha de código. O HashMap é criado internamente e pode ser obtido através do método toMap().

public class MapBuilder{
  private Map workMap;

  public MapBuilder(){
     workMap = new HashMap();
  }

  public MapBuilder put(Object key, Object value){
     assert key != null;

     workMap.put(key, value);

     return this;
  }

  public toMap(){
     return workMap;
  }
}

A implementação acima é muito simples e pode ser melhorada, mas o objetivo é mostrar o conceito de um map builder. O trecho de código a seguir mostra um exemplo de uso:

static Map errorMap = new MapBuilder()
 .put(1, "Database is down")
 .put(2, "Network is down")
 .put(3, "Something else")
.toMap();

O MapBuilder é uma idéia simples que pode ajudar a reduzir volume e melhorar a legibilidade do código.