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.

Vocalise

Mind elaborate more on the hibernate "tricks" you are referring to? Do your queries contain inner select clause(s)? A couple examples of complex queries will help too. – Locomobile

And how are you expecting to be able to create these queries, if you're not writing them yourself? Are you just going to have a frontend with checkboxes (for things like 'occurred in the past), or some sort of modeling view (the SQL I've seen from these is not usually all that great, and it may be difficult to do advanced things in such an interface). And I'm assuming that you're at least locking down the database-user privileges, if you're allowing user-specified dynamic SQL. – Preciado

@limic: see update please, it would be a rather long comment – Vocalise

@X-Zero: same for you, please see the updates – Vocalise

Given that I'm assuming that their results are also restricted (otherwise, you might have to create Java code on the fly), I'd probably create the dynamic SQL 'as normal' (ie through concatenation, direct or through an interface), then tell your JPA provider that the classes it expects are the result of the (prepared, executed) statement. – Preciado

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.

Monitory

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.

Hegelian