Comparisons of libraries for dynamic SQL in Java [closed]

Question

Consider a read-only service that needs to do real time searching of a backing database. The service needs to be able to generate fairly complex select queries to generate summaries, reports, and results based on multi-table objects. The priorities for the library/framework selection are performance (of the sql), maintainability, and longevity.

Hibernate - Seems to require "tricks" to get it to generate the correct SQL, The Criteria API looks promising but also has several limitations when it comes to arbitrary queries.

MyBatis - No Criteria-like API but it's clear and clean and often doesn't require the tricks and tweeks of Hibernate. Limited database abstraction.

Some other yet-to-be-evaluated solutions include: SQLBuilder, Squiggle, Querydsl, JOOQ, or a custom solution.

What have SO users found works best for making a fast versatile searching service.

[update] - Some of the tricks and issues I have faced with Hibernate are...

• https://hibernate.onjira.com/browse/HHH-879
• https://hibernate.onjira.com/browse/HHH-5291
• How to apply Where clause to secondary table
• https://forum.hibernate.org/viewtopic.php?f=1&t=973514

In general, joining to the same table twice seems to cause problems. I've even managed to trick hibernate into producing the correct SQL only to have it map the results wrong because it cached the first instance of the entity and assumed the columns from the second join where redundant. Conditional joins expressed via annotations is also painful. Not saying it's impossible but very cryptic and not intuitive.

To answer X-Zero below I am wanting to go from a specification to a result. Ie, the consumer tells me what they know, and I build a query that answers their question on-the-fly. In practice I intend to have some limits on what they can pass in.

Answer 1

For a query-oriented service I'd strongly recommend to stay away from Hibernate or similar JPA frameworks. MyBatis can be a good choice and it provides some basic scripting support for dynamically constructing queries from SQL fragments, but you'll have to deal with SQL dialects yourself.

Also, to add to your list, there are also Spring SQL Template classes and JDBI, though neither of them address issue with SQL dialects.

Answer 2

From your requirements, I think that jOOQ would be a good match. You also mentioned jOOQ as one of the potential frameworks, so let's consider your requirements from the perspective of jOOQ:

The service needs to be able to generate fairly complex select queries

jOOQ models SQL as an internal domain-specific language in Java. This means that SQL (or something that looks almost like SQL) is constructed using Java objects and methods. I have recently blogged about how jOOQ's DSL / fluent API is built and why it allows for typesafe construction of arbitrarily complex queries. See the blog post here (including a rather complex query):

http://blog.jooq.org/2012/01/05/the-java-fluent-api-designer-crash-course/

to generate summaries, reports, and results based on multi-table objects.

jOOQ embraces advanced OLAP features used to generate summaries, reports etc. Supported features include GROUPING SETS (CUBE(), ROLLUP()), pivot tables, hierarchical queries, window functions, and arbitrary SQL.

The priorities for the library/framework selection are performance (of the sql),

jOOQ generates SQL the way you write it.

maintainability

jOOQ ships with a source code generator modelling your database schema as Java code. This is similar to what Hibernate and/or QueryDSL are capable of doing. When your schema changes, your Java code changes (possibly resulting in compilation errors)

and longevity.

jOOQ is mature and has a yearly 10k downloads, growing. The API has been very stable through the past releases.

Hibernate - Seems to require "tricks" to get it to generate the correct SQL, The Criteria API looks promising but also has several limitations when it comes to arbitrary queries.

Hibernate generates HQL (or JPQL), not SQL. You cannot make full use of SQL features with Hibernate.

MyBatis - No Criteria-like API but it's clear and clean and often doesn't require the tricks and tweeks of Hibernate.

That's a good choice, too, for what you're aiming to do

Limited database abstraction.

jOOQ's API is the same for every underlying database. The generated SQL is integration tested against 13 major RDBMS. This includes a variety of functions, which are translated and/or simulated in other databases. An example of this is given in this blog post, where the simulation of MySQL's ON DUPLICATE KEY UPDATE clause in other RDBMS is described:

http://blog.jooq.org/2012/05/01/how-to-simulate-mysqls-insert-statement-extensions/

Or how tricky correct handling of DUAL tables can be:

http://blog.jooq.org/2011/10/16/sql-trouble-with-dummy-tables/

Disclaimer: I'm the developer of jOOQ, so this answer may be slightly biased.