MyBatis’ Alternative Transaction Management

On the jOOQ user group, we’re often being asked how to perform transaction management with jOOQ. And we have an easy answer ready: You don’t do that with jOOQ. You choose your favourite transaction management API, be it:

And the above list is far from being exhaustive. Transaction management is something very delicate, and it certainly should not be imposed by a library whose main purpose is not transaction management, because any such library / framework will provide you with at most a very leaky abstraction of its transaction model. In other words, if you just slightly want to deviate from “the standard” model (e.g. as imposed by Hibernate), you will suffer greatly, as soon as you want to run 2-3 queries outside of Hibernate – e.g. batch or reporting statements through jOOQ.

MyBatis’ Alternative Transaction Management

MyBatis is a SQL templating engine that provides a couple of features on top of alternative templating engines, such as Velocity, or StringTemplate. One of these features built on top of templating is precisely transaction management, as can be seen in the docs.

From what we can read in the docs, it looks as though MyBatis’ transaction managers can be overriden by Spring, for instance. However, it is not easy to see how this is done. In fact, given that MyBatis also solves Connection pooling (for which there are also very viable alternatives, such as c3p0 and DBCP), and mapping (which could be solved more easily with custom transformers, such as offered by Spring’s JdbcTemplate, or jOOQ’s RecordMapper).

As many frameworks, MyBatis tries to solve problems outside its core scope, which is SQL templating. While this may be a good thing as you only rely on a single dependency, it is also quite a lock-in, in case you have a more complex model. In the case of transaction management, we believe that this was not a good idea by MyBatis.

Thoughts from MyBatis users?

SQL Templating with jOOQ or MyBatis

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 through a 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.

High Complexity and Low Throughput. Reasons for Using an ORM.

I’ve recently stumbled upon an interesting blog post about when to use an ORM. I found it to be well-written and quite objective, specifically with respect to its model complexity and throughput diagram:

Original image taken from this blog post: http://mikehadlow.blogspot.ca/2012/06/when-should-i-use-orm.html
Original image taken from this blog post: http://mikehadlow.blogspot.ca/2012/06/when-should-i-use-orm.html

The ORM or not ORM topic will probably never stop showing up on blogs. Some of them are more black and white, such as Jeff Atwood’s Object-Relational Mapping is the Vietnam of Computer Science others are more “50 shades of data access”, such as Martin Fowler’s ORM Hate.

I’m personally impressed by the work ORMs have done for us in times when repetitive SQL started to get boring and CRUD was not yet established. But ORMs do have their caveats as they are indeed leaky abstractions.

The aforementioned article shows in what situations ORMs can pull their weight, and in what situations you better keep operating on a SQL level, using tools like jOOQ, MyBatis, Apache DbUtils, or just simply JDBC.

Read the original blog post here:
http://mikehadlow.blogspot.ca/2012/06/when-should-i-use-orm.html

Other related articles:

MyBatis’ Wicked Statement Builders

Now here’s one of the most wicked API’s I’ve seen in a while!

MyBatis is well-known as a database abstraction framework on top of JDBC, allowing for externalising SQL into files, loading them at appropriate places in your Java code. For those of you who like this approach, you may be used to statements similar to this one here, taken from the MyBatis documentation:

<select id="selectPerson" parameterType="int" resultType="hashmap">
  SELECT * FROM PERSON WHERE ID = #{id}
</select>

This is MyBatis alright, you think? You haven’t seen everything. MyBatis also has statement builders. Not just any type of statement builders. They make your Java code look like SQL. Check this out:

private String selectPersonSql() {
  BEGIN(); // Clears ThreadLocal variable
  SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME");
  SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON");
  FROM("PERSON P");
  FROM("ACCOUNT A");
  INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");
  INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID");
  WHERE("P.ID = A.ID");
  WHERE("P.FIRST_NAME like ?");
  OR();
  WHERE("P.LAST_NAME like ?");
  GROUP_BY("P.ID");
  HAVING("P.LAST_NAME like ?");
  OR();
  HAVING("P.FIRST_NAME like ?");
  ORDER_BY("P.ID");
  ORDER_BY("P.FULL_NAME");
  return SQL();
}

… jeez, that is one of the funniest ideas I’ve ever seen. A ThreadLocal to keep the SQL statement currently being rendered!! And an API just … err … adding Strings to an internal StringBuilder? Wow.

You can decide for yourself whether you like this or not. But you can’t say these guys aren’t creative ;-) Be sure to read the full docs to learn also about how to write INSERT, UPDATE and DELETE:

http://www.mybatis.org/core/statement-builders.html

jOOQ compared to iBatis

Nice feedback from a jOOQ user comparing it with iBATIS:

jOOQ rocks! We implemented a large scale ETL service with it, and couldn’t be happier. I’ve used iBATIS, and jOOQ gives all the same advantages of low level SQL without the reams of XML and lack of type safety.

[…] one of the best features of jOOQ: Complex and/or dynamically generated queries can be assembled in a controlled way and verified by the Java compiler, saving you from all those ambiguous syntax errors databases throw, and providing compile-time regression coverage for when the schema changes under you.

Feedback by Stack Overflow user dacc on Stack Overflow:

http://stackoverflow.com/questions/296587/light-weight-alternative-to-hibernate/4522144#comment12069361_4522144