Is it possible to combine MyBatis and QueryDSL/jOOQ?

Question

MyBatis provides the mapping, local cache, and logging out of the box.
QueryDSL / jOOQ provide compile-time check of SQL statements and IDE auto-completion as a result.
Is it possible to combine them?

In other words, I would like to create a query with either QueryDSL or jOOQ, and then execute it with some glue code/adapters with MyBatis.

What I have already checked:

• I considered to generate SQL query strings with QueryDSL and use them in MyBatis with its '@SelectProvider' annotation, but it seems to be a dead end: MyBatis requires "${xxx}" stuff in its SQL strings, but QueryDSL only generates queries based on the actual Java types, so it will not work even for IDs.
• MyBatis Generator as an alternative to QueryDSL/jOOQ: quite poor alternative, since it actually generates a boilerplate code which you will later have to maintain and extend
• MyBatis SQL Builder as an alternative to QueryDSL/jOOQ: much weaker than QueryDSL or jOOQ, e.g. it does not offer compile-time check of column names, it is more cumbersome, and it relies on the '@SelectProvider' which complicates the code

Answer 1

I'm going to answer from a high level - so I'm not delving into the actual differences between QueryDSL and jOOQ, which at this point of the discussion "just" both provide type safe embedded SQL in Java. However, I'll give the answer from a jOOQ perspective, as I know that API much better.

Disclaimer: I'm giving this answer from the perspective of someone who works for the company behind jOOQ.

Yes you can:

Yes you can combine jOOQ with MyBatis. For instance, you can extract a query string from a jOOQ query like this:

Select<?> query =
DSL.using(configuration)
   .select(TABLE.A, TABLE.B)
   .from(TABLE)
   .where(TABLE.C.eq(1));

String sql = query.getSQL();
List<Object> bindvalues = query.getBindValues();

Many people actually use this technique to then run the query with Spring JDBC instead of with jOOQ. The main reasaon why they do this is because they have already widely used Spring JDBC, and they don't want to have two types of query execution on their stack.

But why would you:

Every API that you add to your stack will also add complexity and a set of rules to the way your stack behaves. On the other hand, you have a set of features that you want to have implemented from those APIs. Let's identify those features:

1. Type safe embedded SQL
2. Caching
3. Logging
4. Mapping of denormalised SQL results to your domain

1) type safe embedded SQL

This is a no-brainer. You probably don't want to use MyBatis for that. That implementation (as you've discovered) was more of a proof of concept. So you came to the choice of jOOQ

2) caching

This is something that I personally think you jumped to conclusions for too quickly. MyBatis has a lot of very simple implementations for things, but in the case of caching, I'm pretty sure you want to implement a much more general caching strategy, e.g. using the new JSR-107 caching support, like the one from Spring simply because caching is not really that tightly coupled to SQL.

3) logging

jOOQ for instance implements easy to hook into logging hooks, but you could also use logging on a JDBC level using a JDBC trace logging library or your JDBC drivers' capabilities

4) mapping

The same that I've said for caching is true here. MyBatis has a simple default implementation for your mapping algorithms, which might quickly not be enough as you move on to more complex mapping scenarios. The same is true for jOOQ, btw, which also implements default mapping for POJOs, which you can override any way you want. But much like caching, mapping is not really something that should be solved on a SQL level. You will find much better tools out there for the mapping per se - e.g. Model Mapper (which has built-in jOOQ support, btw). Or if you're in a Java 8 environment, you could just use regular functional programming techniques to map stuff, e.g. like this:

DSL.using(configuration)
   .select(
       COLUMNS.TABLE_NAME,
       COLUMNS.COLUMN_NAME,
       COLUMNS.TYPE_NAME
   )
   .from(COLUMNS)
   .orderBy(
       COLUMNS.TABLE_CATALOG,
       COLUMNS.TABLE_SCHEMA,
       COLUMNS.TABLE_NAME,
       COLUMNS.ORDINAL_POSITION
   )
   .fetch()

Above: jOOQ code. Below: Java 8 mapping code

   .stream()
   .collect(groupingBy(
       r -> r.getValue(COLUMNS.TABLE_NAME),
       LinkedHashMap::new,
       mapping(
           r -> new Column(
               r.getValue(COLUMNS.COLUMN_NAME),
               r.getValue(COLUMNS.TYPE_NAME)
           ),
           toList()
       )
   ))
   .forEach(
       (table, columns) -> {
            System.out.println(
                "CREATE TABLE " + table + " (");

            System.out.println(
                columns.stream()
                       .map(col -> "  " + col.name +
                                    " " + col.type)
                       .collect(Collectors.joining(",\n"))
            );

           System.out.println(");");
       }
  );

This is the example from the bottom of this article, and it shows how to query the H2 INFORMATION_SCHEMA for all tables, and map results into CREATE TABLE statements

Conclusion:

Many APIs tend to lure you into using them because of their non-core "features", such as caching or mapping, which are really non-core features for a SQL API. You will get up and running quickly with simple use cases and pet/cat/dog or author/book applications, but you'll be stuck with more complex ones. In your use-case, the type safe embedded SQL feature is the reason you wanted to use something like jOOQ. The other features you're looking for are all not very compelling reasons to use MyBatis (whose core feature is external SQL files, which is completely the opposite of jOOQ, which embeds SQL).

So, my advice to you is: Identify the best tool for each one of the features you want on your stack, and see if those tools are easy to combine. We've taken extreme care when implementing jOOQ to allow for any third party application to plug in very easily into jOOQ (e.g. caching, logging, mapping) because those features are not jOOQ's core features.

Answer 2

Just like to add a point to Lukas very complete answer.

From my benchmark MyBatis has some serious perfomance issue on the mapping, Jooq as a much nicer performance curve. That's with Jooq on mapper.

https://github.com/arnaudroger/SimpleFlatMapper#local-mysql

Also SimpleFlatMapper as an integration point with querydsl and spring data. I did not know you could integrate your own mapping with Jooq and will add that to the backlog.