是否可以结合 MyBatis 和 QueryDSL/jOOQ?

本文介绍了是否可以结合 MyBatis 和 QueryDSL/jOOQ?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

MyBatis 提供开箱即用的映射、本地缓存和注销.
QueryDSL/jOOQ 提供 SQL 语句的编译时检查和 IDE 自动完成功能.
可以合并吗?

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?

换句话说，我想用 QueryDSL 或 jOOQ 创建一个查询，然后用 MyBatis 的一些胶水代码/适配器执行它.

我已经检查过的内容:

What I have already checked:

我考虑过用 QueryDSL 生成 SQL 查询字符串，并在 MyBatis 中使用它的 '@SelectProvider' 注释，但这似乎是一个死胡同:MyBatis 在它的 SQL 字符串中需要 "${xxx}" 的东西，但是 QueryDSL仅根据实际 Java 类型生成查询，因此即使是 ID 也不起作用.
MyBatis Generator 作为 QueryDSL/jOOQ 的替代品:相当糟糕的替代品，因为它实际上会生成一个您以后必须维护和扩展的样板代码
MyBatis SQL Builder 作为 QueryDSL/jOOQ 的替代品:很多比 QueryDSL 或 jOOQ 弱，例如它不提供列名的编译时检查，更麻烦，并且它依赖于使代码复杂化的@SelectProvider"

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

推荐答案

我将从高层次回答——所以我不会深入研究 QueryDSL 和 jOOQ 之间的实际区别，在讨论的这一点上"just" 都在 Java 中提供类型安全的嵌入式 SQL.不过，我会从 jOOQ 的角度给出答案，因为我知道那个 API 要好得多.

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.

免责声明:我是从为 jOOQ 背后的公司工作的人的角度给出这个答案的.

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

是的，您可以将 jOOQ 与 MyBatis 结合使用.例如，您可以像这样从 jOOQ 查询中提取查询字符串:

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();

许多人实际上使用这种技术然后使用 Spring JDBC 而不是 jOOQ 运行查询.他们这样做的主要原因是他们已经广泛使用 Spring JDBC，并且他们不希望在他们的堆栈上执行两种类型的查询.

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.

您添加到堆栈中的每个 API 也会增加堆栈行为方式的复杂性和一组规则.另一方面，您有一组想要通过这些 API 实现的功能.让我们确定这些特征:

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:

类型安全的嵌入式 SQL
缓存
日志记录
将非规范化的 SQL 结果映射到您的域

1) 类型安全的嵌入式 SQL

这很简单.你可能不想为此使用 MyBatis.该实现(如您所见)更像是概念证明.所以你来到了jOOQ的选择

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) 缓存

我个人认为您过早下结论.MyBatis 有很多非常简单的实现，但是在缓存的情况下，我很确定你想要实现更通用的缓存策略，例如使用新的 JSR-107 缓存支持，就像 Spring 中的那样，只是因为缓存与 SQL 的耦合并不是那么紧密.

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) 日志记录

jOOQ 例如实现易于挂钩到日志记录挂钩，但您也可以使用 JDBC 级别的日志记录使用 JDBC 跟踪日志记录库或 JDBC 驱动程序的功能

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) 映射

我所说的缓存在这里也是如此.MyBatis 为您的映射算法提供了一个简单的默认实现，当您转向更复杂的映射场景时，这可能很快就不够了.jOOQ 也是如此，顺便说一句，它也实现了 defaultPOJO 的映射，你可以以任何你想要的方式覆盖它.但就像缓存一样，映射并不是真正应该在 SQL 级别上解决的问题.您会发现更好的工具用于映射本身 - 例如模型映射器(具有内置 jOOQ 支持，顺便说一句).或者，如果您在 Java 8 环境中，您可以使用常规函数式编程技术来映射东西，例如像这样:

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()

上图:jOOQ 代码.下图:Java 8 映射代码

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(",
"))
            );

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

这是这篇文章，它展示了如何查询所有表的H2 INFORMATION_SCHEMA，并将结果映射到CREATE TABLE语句

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

许多 API 倾向于诱使您使用它们，因为它们的非核心功能"(例如缓存或映射)对于 SQL API 来说确实是非核心功能.您将使用简单的用例和宠物/猫/狗或作者/书籍应用程序快速启动并运行，但您将被更复杂的应用程序卡住.在您的用例中，类型安全的嵌入式 SQL 功能是您想要使用类似 jOOQ 的原因.您正在寻找的其他功能都不是使用 MyBatis 的非常有说服力的理由(其核心功能是外部 SQL 文件，这与嵌入 SQL 的 jOOQ 完全相反).

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).

因此，我对您的建议是:为您希望堆栈中的每个功能确定最佳工具，并查看这些工具是否易于组合.我们在实现 jOOQ 时非常小心，允许任何第三方应用程序非常容易地插入到 jOOQ 中(例如缓存、日志记录、映射)，因为这些功能不是 jOOQ 的核心功能.

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.

这篇关于是否可以结合 MyBatis 和 QueryDSL/jOOQ?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！