比较 Querydsl、jOOQ、JEQUEL、activejdbc、iciql 和其他查询 DSL
有人可以向我指出一些关于可用于 Java 的不同 Query DSL 库之间的性能比较的资源，例如： Querydsl，jOOQ，JEQUEL、activejdbc、 <强><一href="http://iciql.com/" rel="noreferrer" title="iciql">iciql 等...
背景：我正在使用 Spring JDBC 模板，但这仍然需要以纯字符串格式编写查询。虽然我在编写直接查询时没有问题，但我担心对数据库表名称的直接依赖。我不想使用任何 ORM 框架，如 Hibernate 或 JPA/EclipseLink。我需要尽可能高的原始性能（IMO，它们适合更多以 CRUD 为中心的应用程序）。我可以为这些 DSL 承担一些轻微的开销（我相信，这主要是 StringBuilder/String 连接！）。
我已经考虑过使用在某些 xml 中外部化的命名查询。但只是尝试评估不同 Query DSL 库提供的价值。
编辑：更多关于我的要求： 我想知道使用它们的 API 方法构建中等复杂查询时它们之间的性能比较。我所需要的只是使用这些查询 DSL 库生成一个查询字符串并将其传递给 Spring JDBC 模板。因此，我想知道添加此中间步骤是否会导致相当大的性能损失，我想使用命名查询或构建自己的库，该库仅使用 StingBuilder 或类似方法
更新我对 jOOQ、iciql、QueryDSL 的体验：
虽然我在原来的帖子中没有提到这一点，但我也很喜欢它们的易用性和易用性。我的实体类中需要的开销（例如是否需要任何附加注释或实现）。
jOOQ：
- 需要将实体属性更改为库特定方式
- 可以返回 SQL 查询字符串
Iciql：
- 实体可以在没有或很少更改的情况下进行映射（可以使用总共 3 种方式进行映射），
- 但它仅限于选择查询（用于更新/删除/...需要再次更改实体）
QueryDSL：
- 将实体与表绑定的多种方式（除了库特定方式之外，支持使用 JPA 注释）。但我们需要修改实体，至少
- 没有简单/直接的方法来获取查询字符串
（所有观察结果我对这些知之甚少；如果其中任何一个不正确，请更正）
有了上述所有内容，我坚持编写命名查询:(但是卢卡斯·埃德尔的回答似乎解释了我最初的帖子关注点（性能），我已经接受了他的。
Can someone point me to some resources about the performance comparison among the different Query DSL libraries available for using with Java, like: Querydsl, jOOQ, JEQUEL, activejdbc, iciql and etc...
Background: I m using Spring JDBC template, but that still required the queries to be written in plain string format. Although I don't have issues in writing the direct queries, but I m concerned having direct dependency on DB table names. I don't want to use any ORM framework like Hibernate or JPA/EclipseLink. I need the raw performance as high as possible (IMO, they are good for more CRUD centric applications). I can afford some slight overhead for the these DSLs only if that is a little (I believe, it'll be mostly StringBuilder/String concatenations!)
I've considered using named queries externalised in some xml. But just trying to evaluate the value different Query DSL libraries provide.
Edit: more on my requirement:
I want to know the performance comparison among these when building a moderately complex query using their API methods. All I need is to generate a query string using any of these query DSL libraries and pass that to Spring JDBC template. So, I want to know if addition of this intermediate step incurs considerable performance penalty, I want to use named queries or build my own library which just uses StingBuilder or similar approach
update my experience with jOOQ, iciql, QueryDSL:
All though I missed to mention this in my original post, I m also keen about the ease of use & the overhead I need to have in my entity classes (like if any additional annotations or implementations required).
jOOQ:
- requires changing the entity properties to the library specific way
- can return SQL query string
Iciql:
- entity can be mapped with no or little changes (can be mapped using total 3 ways)
- but with that it limits to only select queries (for update/delete/... requires entity changes again)
QueryDSL:
- multiple ways to bind entities with table (other than library specific ways, using JPA annotations is supported). but we need to modify the entities at least
- no simple/direct way to get the query string
(all observations are with little knowledge I've on these; if any of these are incorrect, please correct)
With all of the above, I m sticking with writing named queries :( But as the Lukas Eder answer seems explains about my original post concern (performance), I've accepted his.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
在现代 JVM 中，您不必太担心 SQL 字符串连接。任何数据库抽象层可能产生的真正开销（与相对较高的数据库往返时间相比）通常是由于在 Hibernate/JPA 中完成的二级缓存造成的。或者通过将对象模型低效地映射到 SQL，使得使用索引或一般查询转换变得不可能。
相比之下，字符串连接实际上可以忽略不计，即使对于具有多个 UNION、嵌套 SELECT、JOIN、半连接的复杂 SQL 结构也是如此。 JOINs、
anti-JOINs等，所以我猜测您提到的所有框架都以类似的方式执行，因为它们允许您保持对 SQL 的控制。另一方面，某些框架或这些框架中的使用模式实际上可能会将整个结果集提取到内存中。如果结果集很大，这可能会导致问题，因为使用 Java 的泛型，大多数原始类型（
int、long等）可能会映射到其相应的包装器（<代码>整数，长整型）。至于 jOOQ （我是其开发人员），我之前已经用 YourKit Profiler 用于大规模查询执行。大量工作始终在数据库中完成，而不是在查询构造中完成。 jOOQ 对每个查询使用一个
StringBuilder。我想象（未验证）， QueryDSL 和 JEQUEL 做同样的事情...至于iciql，这是一个JaQu，他们使用 Java 工具来反编译 自然语法。但我想如果这意味着影响太大的话，可以省略。
In modern JVM's you shouldn't be worrying about SQL string concatenation too much. The true overhead any database abstraction layer may produce (compared to the relatively high round-trip time to the database and back), is usually due to second-level caching, which is done in Hibernate/JPA. Or by inefficiently mapping object models to SQL in a way that using indexes or general query transformation becomes impossible.
Compared to that, string concatenation is really negligible, even for complex SQL constructs with several
UNIONs, nestedSELECTs,JOINs,semi-JOINs,anti-JOINs, etc, so I'm guessing all of the frameworks you mentioned perform in a similar manner, as they allow you to keep control over your SQL.On the other hand, some frameworks or usage modes in those frameworks may actually fetch the whole result set into memory. That can cause issues if your result sets are large, also because with Java's generics, most primitive types (
int,long, etc) are probably mapped to their corresponding wrappers (Integer,Long).As for jOOQ (of which I'm the developer), I have previously profiled the library with YourKit Profiler for massive query execution. The bulk work was always done in the database, not in query construction. jOOQ uses a single
StringBuilderfor every query. I imagine (not verified), that QueryDSL and JEQUEL do the same...As for iciql, which is a fork of JaQu, there might be some additional impact by the fact that they use Java instrumentation to decompile their natural syntax. But I guess that can be omitted, if it means too much impact.
您还应该查看MyBatis 语句生成器。
是一种映射技术，但它确实有一个声明构建器 DSL，它似乎与 MyBatis 解耦（也就是说，您不需要 MyBatis 中的任何其他内容来使用构建器......令人烦恼的是，它不属于它自己的）罐子）。我不喜欢它，因为它使用 ThreadLocals。
You should also look at MyBatis Statement Builder.
While MyBatis is clearly a mapping technology it does have a Statement builder DSL that seems to be decoupled from MyBatis (that is you don't need anything else from MyBatis to use the builders... annoyingly its not in its own jar). I don't like it because it uses ThreadLocals.
我不能代表其他框架，但我对性能进行了原始分析，以比较 ActiveJDBC 和 Hibernate 。该测试是在一台配备 8G RAM、SSD 驱动器的笔记本电脑上针对 MySQL 进行的。表 PEOPLE 具有一些简单的列和代理 ID PK。
一个测试是插入 50K 记录作为对象，另一个测试是一次从表中读取 50K 对象（在内存中）。在这两项测试中，ActiveJDBC 的性能比 Hibernate 提高了 40%。无论哪种情况，生成的查询都是简单的插入和选择，彼此非常相似。
希望这有帮助，
伊戈尔
I cannot speak for other frameworks, but I performed a primitive analysis of performance to compare ActiveJDBC and Hibernate. The test was on a laptop with 8G RAM, SSD drive against MySQL. Table PEOPLE with a few simple columns and a surrogate ID PK.
One test was to insert 50K records as objects, and the other was to read the 50K objects from table at once (in memory). In both tests ActiveJDBC showed 40% performance improvement over Hibernate. In either case, the queries generated were simple insert and select, closely resembling each other.
hope this helps,
Igor
用于以编程方式创建 SQL 查询的轻量级、无依赖库是 OpenHMS SQL Builder 库：
https://openhms.sourceforge.io/sqlbuilder/
可作为 Maven 依赖项：
https://mvnrepository.com/artifact/com.healthmarketscience.sqlbuilder/sqlbuilder
A light-weight, no-dependency library for programmatic SQL query creation is the OpenHMS SQL Builder library:
https://openhms.sourceforge.io/sqlbuilder/
Available as Maven dependency:
https://mvnrepository.com/artifact/com.healthmarketscience.sqlbuilder/sqlbuilder