Posts Tagged: DSL


18
Jul 09

Groovy SQL Builder

I’m currently working on a project which requires some pretty heavy SQL to OO stuff. JPA of course can’t handle the full load, so my Repository pattern abstractions abstract away JPA as well as raw JDBC stuff. This architectures actually works out pretty well, except when it comes time to build queries. Java’s string interpolation/comprehension is not present and even if it was there, who wants to build queries using strings. I used to do it years ago, now that I had to come back to doing it (for non-trivial large queries), I definitely think it’s a big design smell. Even abstracting it away behind some sort of a sql variant, it’s just not as nice as I’m used to programming with DDD.

I also had to question simple sql variant implementation which I had. SQLVariant is nice when you know that you’ll always have only one query per operation (in each db you have to support) and building queries is pretty straightforward, not requiring a lot of logic.

So I added this concept of a SQLExecutor, which returned anonymous inner classes that represented query or update operations interface. The client can then execute this (psudo-closure) to perform the operation. And all query stuff is complete abstracted away in the returned closure’s execution logic. Worked great, but I’m still stuck with building queries. Java just sucks when it comes to this, ah, but what about groovy?

So I realized that I can use java and groovy interchangeably in the absolute same code base. Both are compiled into JVM bytecode and IntelliJ supports both file types rather nicely, allowing for cross compilation and all the other IDE goodies.

So why not build a sql builder in groovy? Groovy’s metaprogramming facilities allow for that level of dynamic logic way more than java. So in the last 2 hours I put together something very simple, which currently allows me to at least support my query building tasks in a cleaner fashion. Groovy’s FactoryBuilderSupport is a rather nice abstraction that allows to build builder DSLs on top of. Mind, this is 2 hours of work, many features I need (besides inserts and updates as well) are not yet there. I need to add column validation (to not allow to select or have included in the predicates columns and/or tables that were not defined as a part of the query or joins). There are numerous other things I’ll add with time, but thought someone might be interested in at least how easy it is to build a simple groovy DSL.

If I ever get it to the point of where I feel it will be helpful to anyone other than me, I’ll release it in some near future. Right now, I’m just glad that I can write simple DSLs and get rid of the SQL building crud.

Here is a small use case for a select statement (that’s all I can build at this point).

  @Test
  void buildSelectSql() {
    def sql = new SqlBuilder();
    Select select = sql.select ("table1") {
      join("table2", type: "INNER") {
        using(table1: "col1", table2: "col1")
      }
      join("table3", type: "OUTER") {
        using(table1: "col2", table2: "col2")
        using(table1: "col3", table2: "col3")
      }
      where("table1.col1 = 'test'")
      groupBy(table1: "col1", table2: "col1")
      orderBy(table1: "col1", table2: "col1")
    }
    assertNotNull select, "Select object should not be null"
    println "SELECT: " + select.getSql()
    assertNotNull select.getSql(), "Generated SQL SELECT expression should not be null"
    assertFalse select.getSql().trim() == "", "Generated SQL SELECT expression should not be empty"
  }

Here is the code:

/**
 * SqlBuilder is a DSL to build portable SQL using groovy internal DSL facilities.  It allows you to specify
 * SQL queries using groovy expressions vs. string concatenation/interpolation as it's usually done.
 *
 *
 * @author Ilya Sterin
 */
public class SqlBuilder extends FactoryBuilderSupport {

  // Register expression factories
  {
    registerFactory('select', new SelectFactory())
    registerFactory('join', new JoinFactory())
    registerFactory('using', new UsingFactory())
    registerFactory('where', new WhereFactory())
    registerFactory('groupBy', new GroupAndOrderFactory())
    registerFactory('orderBy', new GroupAndOrderFactory())
  }
}

/**
 * Select class encapsulates the select clause node of the SQL query
 * and acts as a parent node to all select queries.
 */
private class Select {

  def table,        // Name of the main select table
      tables  = [], // All tables join tables and select table
      joins   = [], // Collection of all joins
      where,        // Where clause
      groupBy = [], // Collection of group by columns
      orderBy = []  // Collection of order by columns

  /**
   * Builds and returns the SQL query based on the DSL expressions
   */
  String getSql() {
    def sql = "SELECT * FROM $table"
    joins.each {Join join ->
      sql += " ${join.type} JOIN ${join.table} ON"
      join.using.eachWithIndex {Using using, idx ->
        if (idx > 0) sql += " AND"
        sql += " ${using.lhs} ${using.op} ${using.rhs}"
      }
    }
    where.eachWithIndex {Where where, i ->
      if (i == 0) sql += " WHERE ";
      sql += "${where.clause}"
    }
    if (groupBy) sql += " GROUP BY ${groupBy.join(', ')}";
    if (orderBy) sql += " ORDER BY ${orderBy.join(', ')}";
    return sql
  }

}

///////// Private classes to support the builder functionality /////////////

private class Join {
  def table
  def type = "INNER"
  def using = []
}

private class Using {
  def lhs, rhs, op = "="

  def methodMissing(String name, args) {
    println "Looking for ${name}"
  }
}

private class Where {
  def clause
}

///////// Factories that construct the appropriate objects based on the DSL expressions //////////////

private class SelectFactory extends AbstractFactory {

  public Object newInstance(FactoryBuilderSupport factoryBuilderSupport, name, value, Map map) {
    return new Select(table: value, tables: [value])
  }

  public void setChild(FactoryBuilderSupport factoryBuilderSupport, Object parent, Object child) {
    println "Child ${child}"
    if (child instanceof Join) {
      println "Adding a join"
      parent.joins << child
    }
    if (child instanceof Where) {
      println "Adding a where"
      parent.where = child
    }
  }
}

private class JoinFactory extends AbstractFactory {

  public Object newInstance(FactoryBuilderSupport factoryBuilderSupport, name, value, Map map) {
    Join join = new Join(table: value)
    return join
  }

  public void setChild(FactoryBuilderSupport factoryBuilderSupport, Object parent, Object child) {
    super.setChild(factoryBuilderSupport, parent, child)
    if (child instanceof Using) {
      parent.using << child
    }
  }

  public void setParent(FactoryBuilderSupport factoryBuilderSupport, Object parent, Object child) {
    super.setParent(factoryBuilderSupport, parent, child);
    parent.tables << child.table
  }
}

private class UsingFactory extends AbstractFactory {

  public Object newInstance(FactoryBuilderSupport factoryBuilderSupport, name, op, Map map) {
    Using using = new Using()
    if (op) using.op = op
    map.each {k, v ->
      if (!using.lhs) {
        using.lhs = "${k}.${v}"
      }
      else if (!using.rhs) {
        using.rhs = "${k}.${v}"
      }
    }
    map.clear()
    return using
  }

  public boolean onHandleNodeAttributes(FactoryBuilderSupport factoryBuilderSupport, Object o, Map map) {
    return true;
  }

  public boolean isLeaf() {
    return true;
  }
}

private class WhereFactory extends AbstractFactory {

  public Object newInstance(FactoryBuilderSupport factoryBuilderSupport, name, value, Map map) {
    return new Where(clause: value)
  }

  public boolean isLeaf() {
    return true;
  }
}


private class GroupAndOrderFactory extends AbstractFactory {

  public Object newInstance(FactoryBuilderSupport factoryBuilderSupport, name, value, Map map) {
    def fqCols = [];
    map.each {k, v ->
      fqCols << "${k}.${v}"
    }
    return [name: name, cols: fqCols];
  }

  public void setParent(FactoryBuilderSupport factoryBuilderSupport, Object parent, Object child) {
    super.setParent(factoryBuilderSupport, parent, child);
    if (child.name == "groupBy") {
      parent.groupBy += child.cols
    }
    if (child.name == "orderBy") {
      parent.orderBy += child.cols
    }
  }

  public boolean onHandleNodeAttributes(FactoryBuilderSupport factoryBuilderSupport, Object o, Map map) {
    return true;
  }

  public boolean isLeaf() {
    return true;
  }
}