SQL Zone is brought to you in partnership with:

I am the founder and CEO of Data Geekery GmbH, located in Zurich, Switzerland. With our company, we have been selling database products and services around Java and SQL since 2013. Ever since my Master's studies at EPFL in 2006, I have been fascinated by the interaction of Java and SQL. Most of this experience I have obtained in the Swiss E-Banking field through various variants (JDBC, Hibernate, mostly with Oracle). I am happy to share this knowledge at various conferences, JUGs, in-house presentations and on our blog. Lukas is a DZone MVB and is not an employee of DZone and has posted 224 posts at DZone. You can read more from them at their website. View Full User Profile

SQL Templating with jOOQ or MyBatis

07.17.2013
| 5149 views |
  • submit to reddit

Many people compare jOOQ with MyBatis. Both are seen as popular alternatives to Java’s persistence standard JPA, as both are much more SQL-focused than JPA itself. When comparing the two tools, the first obvious difference is this:

  • jOOQ is an internal domain-specific language modelling SQL througha Java fluent API.
  • MyBatis is an XML-based SQL templating and mapping engine where dynamic SQL can be created through an XML-DSL.

MyBatis’ current success is mostly based on it having provided a viable alternative to JPA in a time when JPA was still a controversial standard, and when JPA had to prove that it is better than JDO, which solves very similar problems. This provided alternative is something that many SQL-oriented users like to have:

  • The separation of Java and SQL code, extracting SQL code into external files. This allows DBA to patch SQL strings in productive environments, adding hints, and other tuning.
  • The automatic mapping of tabular result set data to objects. This too is achieved in the same XML DSL as the dynamic SQL specification.

Implementing SQL templating with jOOQ

These things can be achieved with jOOQ as well. But unlike MyBatis, jOOQ’s SQL templating (as will be introduced in jOOQ 3.2) will not make use of a proprietary templating language. You should be able to make your own choice of language, providing jOOQ with a very simple adapter to it. This will allow for using:

Let’s have a look at a Velocity template example. This example adds a dynamic list of ID parameters to the WHERE clause:

SELECT
  a.first_name,
  a.last_name,
  count(*)
FROM
  t_author a
LEFT OUTER JOIN
  t_book b ON a.id = b.author_id
WHERE
  1 = 0
#foreach ($param in $p)
  OR a.id = ?
#end
GROUP BY
  a.first_name,
  a.last_name
ORDER BY
  a.id ASC

The above template can be passed to the following jOOQ Template implementation, which uses arbitrary input objects to produce a concrete jOOQ QueryPart. A QueryPart is an object that can render SQL and bind variables:

class VelocityTemplate
implements org.jooq.Template {
 
  private final String file;
 
  public VelocityTemplate(String file) {
    this.file = file;
  }
 
  @Override
  public QueryPart transform(Object... input) {
 
    // Velocity code
    // -----------------------------------------
    URL url = this.getClass().getResource(
      "/org/jooq/test/_/templates/");
    File file = url.getFile();
 
    VelocityEngine ve = new VelocityEngine();
    ve.setProperty(RESOURCE_LOADER, "file");
    ve.setProperty(FILE_RESOURCE_LOADER_PATH,
      new File(file ).getAbsolutePath());
    ve.setProperty(FILE_RESOURCE_LOADER_CACHE,
      "true");
    ve.init();
 
    VelocityContext context = new VelocityContext();
    context.put("p", input);
 
    StringWriter writer = new StringWriter();
    ve.getTemplate(file, "UTF-8")
      .merge(context, writer);
 
    // jOOQ Code
    // -----------------------------------------
    return DSL.queryPart(writer.toString(), input);
  }
}

Very simple glue code. As you’re in full control of your template engine implementation adapter, you can also add caching, object pools, etc to your adapter.

The above template can then be used easily throughout the jOOQ API, wherever jOOQ allows for plain SQL. For example as a top-level query:

Template tpl = new VelocityTemplate(
    "authors-and-books.vm");
 
DSL.using(configuration)
   .resultQuery(tpl, 1, 2, 3)
   .fetch();

Or as a nested select, embedded in jOOQ’s typesafe DSL:

DSL.using(configuration)
   .select()
   .from(new TableSourceTemplate("my-table.vm"))
   .fetch();

Of course, you can also take advantage of jOOQ’s record mapping features, which allows you to implement your own custom table to object mapping algorithms. This may often be a better choice than relying on any hard-wired XML configuration, such as the one from MyBatis:

List<MyType> result =
DSL.using(configuration)
   .select()
   .from(new TableSourceTemplate("my-table.vm"))
   .fetch(new RecordMapper<Record, MyType>() {
      public MyType map(Record record) {
        // Custom mapping logic here
      }
   });

or with Java 8 : 

List<MyType> result =
DSL.using(configuration)
   .select()
   .from(new TableSourceTemplate("my-table.vm"))
   .fetch((Record) -> new MyType().init(record));

The possibilities are great

SQL templating is a powerful tool when you prefer simple, string-based SQL that can be tweaked every now and then with a little loop or if statement, to inject some dynamic SQL clause. There are a couple of SQL engines that try to solve this problem in one way or another:

Among the above, all tools ship with a simple, proprietary template language, but jOOQ is the only one encouraging you to use your template engine of choice and thus offering arbitrary templating extensibility in the future.

Published at DZone with permission of Lukas Eder, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)