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.
Like this:
Like Loading...
Great article Lukas and I appreciate the mention of JIRM. I’m big fan of SQL templating but always wanted a SQL templating language that was logic-less (ala mustache) and can be copied ‘n pasted back and forth between your SQL Query Tool and an classpath SQL resource.
Thus I came up with my own SQL templating language: https://github.com/agentgt/jirm/blob/master/jirm-core/README.md
You can do SQL parameterization like:
INSERT INTO test_bean
(string_prop, long_prop, timets)
VALUES (
'HELLO' -- {stringProp}
, 3000 -- {longProp}
, now() -- {timeTS}
)
(hint ‘HELLO’, 3000 and , now() will be replaced with ?).
You can also define partial blocks of SQL to be reused (ie so you don’t have to select on the same list of fields over and over).
The parser is handwritten and probably buggy but I think you get the idea of leveraging SQL comments for safe template logic.
Nice hearing from you Adam. I remember your idea of having “interoperable” SQL between your tool and an actual SQL interpreter, and I already found it quite clever at the time. I guess a more reliable (yet a bit more cumbersome) way to declare such variables would be using multi-line comments, e.g.
INSERT INTO test_bean (string_prop, long_prop, timets) VALUES ( /* {stringProp} */ 'HELLO' /* {/stringProp} */ , /* {longProp} */ 3000 /* {/longProp} */ , /* {timeTS} */ now() /* {/timeTS} */ )The nice thing about jOOQ 3.2’s templating capabilities is the fact that you can inject any templating behaviour into jOOQ. Maybe there’s even room for a jOOQ/JIRM integration, that way, if some users would like that!
Useful article! Your list of templating engines misses the best one out there (IMO), StringTemplate. http://www.stringtemplate.org/ (BTW I think it fits Adam’s requirement for “logic-less” if I correctly take that to be equivalent to ST’s strict view / model separation).
Absolutely! Will fix the article to include StringTemplate
Hi Lukas. Good article and nice feature for jOOQ 3.2
One of the MyBatis strongest points is a bigger limitation for others. Given that MyBatis externalizes SQL you cannot use Java for building dynamic queries (*). So you lose some quite important things like compile type checks, autocompletion.. So a templating engine fits well for this.
(*) MyBatis solves this partially with the “providers” and a query languaje (that Adam improved making it fluent for 3.2 :)) This is not the typical way of using MyBatis though.
But, to be fair, although very few people uses it now and I wont expect a massive use in the future, MyBatis introduced pluggable templating engines in 3.2 (what a concidence!). There is a velocity plugin already released: http://mybatis.github.io/velocity-scripting/
Eduardo, thanks for contacting me.
If typesafe, embedded SQL is really what you’re looking for, I guess you should use really use jOOQ. I have seen MyBatis’ approach, and I’m not so convinced that it would add much value to an application, even with Adam’s improvements… But of course, my opinion here is a bit biased :-)
Good to know about MyBatis’ Velocity support. I see that you’re in their project team? Maybe, we should create a couple of joint blog articles, making the Velocity SQL templating approach a bit more popular? I’ll certainly post about this topic again, and I’ll advertise the fact that MyBatis also supports Velocity. Eventually, I (or a jOOQ user) might be looking into supporting MyBatis XML files within jOOQ.
Nice Post. Just to note, there is also a mybatis-scala project with nice SQL embedding, look a this example:
https://code.google.com/p/mybatis/source/browse/sub-projects/scala/trunk/mybatis-scala-samples/src/main/scala/org/mybatis/scala/samples/select/SelectSample.scala
True, SQL embedding is easy in Scala as Scala supports multi-line strings. Are you guys at MyBatis planning to delve more into an improved Scala integration?