是否可以结合MyBatis和QueryDSL / jOOQ？ [英] Is it possible to combine MyBatis and 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 的一些胶水代码/适配器执行。
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:
- 我考虑使用QueryDSL生成SQL查询字符串，并在MyBatis中使用它们的'@SelectProvider'注释，但它似乎已经死了结束：MyBatis在其SQL字符串中需要$ {xxx}内容，但QueryDSL仅根据实际的Java类型生成查询，因此即使对于ID也不会起作用。
- MyBatis Generator 作为QueryDSL / jOOQ的替代品：非常差的替代方案，因为它实际上生成了一个样板代码，你会以后必须维护和扩展
- MyBatis SQL助洗剂作为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之间的实际差异，在讨论的这一点上只是两者在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结果映射到您的域
- Type safe embedded SQL
- Caching
- Logging
- Mapping of denormalised SQL results to your domain
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级别上的日志记录
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，btw也是如此，它也实现了默认POJO的映射，你可以以任何你想要的方式覆盖。但很像缓存，映射并不是一个应该在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(",\n"))
);
System.out.println(");");
}
);
这是这篇文章，它展示了如何查询H2 <$ c所有表的$ c> 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？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
