Comparing Querydsl, jOOQ, JEQUEL, activejdbc, iciql and other query DSLs

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.

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.