比较Querydsl，jOOQ，JEQUEL，activejdbc，iciql和其他查询DSL [英] Comparing Querydsl, jOOQ, JEQUEL, activejdbc, iciql and other query DSLs

问题描述

有人可以向我介绍一些有关Java可用的不同查询DSL库之间的性能比较的资源，例如: jOOQ ， JEQUEL ， activejdbc ， 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批注).但是我们至少需要修改实体
• 没有简单/直接的方法来获取查询字符串

(所有观察值我对它们的了解都不多；如果其中任何一个不正确，请更正)

基于上述所有内容，我都会坚持写命名查询:(但是，正如Lukas Eder的回答似乎解释了我最初对帖子的关注(表现)一样，我已经接受了他.

解决方案

在现代JVM中，您不必担心SQL字符串串联过多.任何数据库抽象层可能产生的实际开销(与往返数据库的相对较长的往返时间相比)通常归因于二级缓存，这是在Hibernate/JPA中完成的.或通过将对象模型低效率地映射到SQL，从而无法使用索引或进行常规查询转换.

与此相比，即使对于具有多个UNIONs，嵌套的SELECTs，JOINs，semi-JOINs，anti-JOINs等的复杂SQL构造，字符串连接实际上也可以忽略不计.您提到的框架以类似的方式执行，因为它们使您可以控制SQL.

另一方面，某些框架或这些框架中的使用模式实际上可能会将整个结果集提取到内存中.如果结果集很大，那可能会引起问题，这还因为使用Java的泛型，大多数原始类型(int，long等)可能已映射到其相应的包装器(Integer，Long).

对于 jOOQ (我是开发人员)，我之前曾使用 YourKit事件探查器，用于执行大量查询.批量工作始终在数据库中完成，而不是在查询构造中完成. jOOQ为每个查询使用单个StringBuilder.我想象(未验证) QueryDSL 和 iciql ，它是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.

解决方案

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, nested SELECTs, 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 StringBuilder for 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.

这篇关于比较Querydsl，jOOQ，JEQUEL，activejdbc，iciql和其他查询DSL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！