SQL Zone is brought to you in partnership with:

I am the founder and CEO of Data Geekery GmbH, located in Zurich, Switzerland. With our company, we have been selling database products and services around Java and SQL since 2013. Ever since my Master's studies at EPFL in 2006, I have been fascinated by the interaction of Java and SQL. Most of this experience I have obtained in the Swiss E-Banking field through various variants (JDBC, Hibernate, mostly with Oracle). I am happy to share this knowledge at various conferences, JUGs, in-house presentations and on our blog. Lukas is a DZone MVB and is not an employee of DZone and has posted 249 posts at DZone. You can read more from them at their website. View Full User Profile

jOOQ’s Reason for Being

02.22.2013
| 7122 views |
  • submit to reddit

The below paragraphs were taken from the jOOQ preface from the manual. It is worth thinking about why you should (or should not) use jOOQ in a given project. Specifically, you might be choosing between jOOQ and JPA, jOOQ and Hibernate, or jOOQ and SLICK (in a Scala context). here’s some guidance (slightly biased towards jOOQ, of course…):

jOOQ’s reason of being – compared to JPA

Java and SQL have come a long way. SQL is an “ancient”, yet established and well-understood technology. Java is a legacy too, although its platform JVM allows for many new and contemporary languages built on top of it. Yet, after all these years, libraries dealing with the interface between SQL and Java have come and gone, leaving JPA to be a standard that is accepted only with doubts, short of any surviving options.

So far, there had been only few database abstraction frameworks or libraries, that truly respected SQL as a first class citizen among languages. Most frameworks, including the industry standards JPA, EJB, Hibernate, JDO, Criteria Query, and many others try to hide SQL itself, minimising its scope to things called JPQL, HQL, JDOQL and various other inferior query languages

jOOQ has come to fill this gap.

jOOQ’s reason of being – compared to LINQ

Other platforms incorporate ideas such as LINQ (with LINQ-to-SQL), or Scala’s SLICK to better integrate querying as a concept into their respective language. By querying, they understand querying of arbitrary targets, such as SQL, XML, Collections and other heterogeneous data stores. jOOQ claims that this is going the wrong way too.

In more advanced querying use-cases (more than simple CRUD and the occasional JOIN), people will want to profit from the expressivity of SQL. Due to the relational nature of SQL, this is quite different from what object-oriented and partially functional languages such as C#, Scala, or Java can offer.

It is very hard to formally express and validate joins and the ad-hoc table expression types they create. It gets even harder when you want support for more advanced table expressions, such as pivot tables, unnested cursors, or just arbitrary projections from derived tables. With a very strong object-oriented typing model, these features will probably stay out of scope.

In essence, the decision of creating an API that looks like SQL or one that looks like C#, Scala, Java is a definite decision in favour of one or the other platform. While it will be easier to evolve SLICK in similar ways as LINQ, SQL feature scope that clearly communicates its underlying intent will be very hard to add, later on (e.g. how would you model Oracle’s partitioned outer join syntax? How would you model ANSI/ISO SQL:1999 grouping sets? How can you support scalar subquery caching? etc…).

jOOQ has come to fill this gap.

jOOQ is different

SQL was never meant to be abstracted. To be confined in the narrow boundaries of heavy mappers, hiding the beauty and simplicity of relational data. SQL was never meant to be object-oriented. SQL was never meant to be anything other than… SQL!

 

Published at DZone with permission of Lukas Eder, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Robert Greathouse replied on Fri, 2013/02/22 - 5:12pm

So why not just write SQL?

Lukas Eder replied on Sat, 2013/02/23 - 4:46am in response to: Robert Greathouse

SQL can be written as plain text and passed through the JDBC API. Over the years, people have become wary of this approach for many reasons:

  • No typesafety
  • No syntax safety
  • Verbose SQL string concatenation
  • Boring bind value indexing techniques
  • Verbose resource and exception handling in JDBC
For these many reasons, other frameworks have tried to abstract JDBC away in the past in one way or another. Unfortunately, many have completely abstracted SQL away as well

Oleksandr Alesinskyy replied on Wed, 2013/02/27 - 8:54am in response to: Lukas Eder

Last two issues are very neatly addressed by the Spring JDBC template/named JDBC template.

A string concatenation is needed just in a few percents of the cases (and even then it often may be replaced by kind if templating).

I hardly believe that JOOQ would be able to provide a lot of "syntax safety" - especially, if take into account subtle differences between SQL dialects.


Lukas Eder replied on Wed, 2013/02/27 - 10:30am in response to: Oleksandr Alesinskyy

Last two issues are very neatly addressed by the Spring JDBC template/named JDBC template

Yes, that's true. And by many other little tools, like Apache DbUtils.

A string concatenation is needed just in a few percents of the cases (and even then it often may be replaced by kind if templating).

I've seen enough complex SQL statements where, depending on the filter criteria, new filters, joins, subqueries, semi- and anti-joins were added. The string concatenation mess produced by this kind of dynamic SQL can get really bad.

I hardly believe that JOOQ would be able to provide a lot of "syntax safety" - especially, if take into account subtle differences between SQL dialects.

Try it for yourself :-) Yes, there are an awful lot of subtle differences between the 14 dialects currently supported by jOOQ. jOOQ simulates things as much as possible. An example can be seen here, where REPEAT() and RPAD(), LPAD() are simulated in SQLite:

http://blog.jooq.org/2012/07/19/funky-string-function-simulation-in-sqlite/ 

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.