Oct 1, 2015

JOOQ Worthy


Introduction

After getting some favorable reviews from colleagues concerning JOOQ, touted as “the easiest way to write SQL in Java” I wanted to play around with it and see what it is all about. I should state that I am inherently biased because I have never been a fan of Object-Relational Mapping (ORM) tools because I never wanted to be isolated from writing SQL queries. My experiences with libraries that generate SQL for you, such as Hibernate, have been that they cause you performance headaches. As a counterbalance, I also never been a huge fan of writing all of an application’s database interactions using JDBC because it can be tedious work. Still, I have often avoided ORM unless the client was already using it or expressed an expectation about using one. Through these experiences, I have sensed that there has long been a gap in the all-important “accessing data through code” arena, which is why JOOQ is so interesting.

What Do Have We Here?

JOOQ is a DSL that removes some of the headaches involved in adding database support to a JVM-based software project.

The above example inserts a record into the Book table and then demonstrates the equivalent of a “select *” to retrieve all the data from the Book table. The insert statement has a “good feel” because the code reads much like creating a Map. In fact, JOOQ will let us do even better:

By using the override of the set method that accepts a map, you can easily iterate over a list of maps containing your data and bulk insert that into your table. Of course, you can do the same thing with JDBC, but readability and concision suffer:

Here is a JDBC update statement:

Here is the JOOQ equivalent:


Selling Points of JOOQ

Data first

Your focus stays on the data, not on classes that map over the data. JOOQ’s concept of “data first” involves placing the design of the database schema back in the database realm. In Functional Programming, the concept of putting ‘data first’ is about focusing on the data processing goal as well as favoring composition over inheritance. If doing ‘the work you came here for’ is an attractive concept to you, then you will find JOOQ fits right in with your style.
The JOOQ documentation includes examples of how it can inter-operate with Flyway, a popular database script management system. This places your work directly in line with the expectations of what DBAs expect as far as code artifacts go.

Finer control over query Lifecycle

Anyone who has performed any complex data retrieval (e.g. joins) using Hibernate has run into the problem of Hibernate’s innate ability to encumber application performance through its reliance on generating safe, but often non-performant “kitchen sink” queries. Many of us have spent considerable time fixing performance issues in applications by analyzing what Hibernate was doing and then writing a better query to replace it. JOOQ is far more transparent in its query generation lifecycle, allowing developers to control the end product.

Typesafe SQL

A major selling point of ORM technology is its ability to enforce type safety. JDBC queries are just strings that have placeholders for data values merged in at the last moment. Developers have to know tell it what types are being passed in and what types can be expected in the result set, but there is no way to enforce it other than at runtime. Like Hibernate, JOOQ queries are compiled code.

It’s Not ORM

While few developers make choices by running away from a technology, it is nice to see a viable alternative arrive. I admit that I have a bias and hostility towards ORM, but I feel that it is deserved. Ted Neward famously referred to ORM as the Vietnam of Computer Science. We understand he was poking fun, but ORM can be a frustrating quagmire in which every hour of increased productivity is offset by many hours of swearing and determined debugging of annotations and configuration problems.

While ORM technology has improved over the years, it will never abstract away the DBMS nor should it. Any complex schema is managed by a DBA and DBAs are not going to generate tables and relations using Hibernate. DBAs work with DDL and DML and most do not use ORM technologies in their day to day work. As a result, DBAs will not offer much in the way of assistance when you inevitably run into problems with your ORM solution. They don’t grok ORM, and the best they can do is turn on tracing on their side to see what the ORM is producing in the actual database. We should be building bridges with the keepers of the all-important data, not shutting them out.

Support for Stored Procedures

Nobody really likes stored procedures. They are vendor lock-in to the max. Despite that, most places that have any kind of database utilize stored procedures in some capacity. Stored procedures were once a popular solution for certain database problems, especially common when performance problems were encountered or complex transactions were needed. JOOQ allows developers to embed stored procedure function calls within their queries.


Other Cool Things About JOOQ

Code Generation

JOOQ is capable of generating its own POJOs. Unlike Hibernate, there are no customized classes to create for doing specific joins because the data returned by a JOOQ query is not unmarshaled into objects.

Active Records

In order to alleviate the work of basic CRUD operations, JOOQ maps each table as a typesafe record. Each record can store, update, and delete itself using the API. These active records employ optimistic locking, they can navigate foreign keys, and they can be mapped to other custom POJO types as needed.

Multi-Tenancy

Having multi-tenancy just means that JOOQ is easy to configure per environment (e.g. LOCAL, DEV, QA, STAGE, and PROD) and that it can perform schema and table rewriting at both code generation and runtime, depending on your project’s needs. To me, this is not that compelling a point because any decent-sized project will want to maintain .sql DDL and DML files and utilize a tool such as Flyway, perhaps coupled with an automated build script, in order to achieve multi-tenancy. It makes sense for JOOQ to include such a capability, however, because the product competes with frameworks such as Grails (GORM), and of course, Hibernate, both of which offer quick, automated database nuke and pave capabilities.


What Else Have I Worked With?

SpringJDBCTemplate with Groovy

My old standby is Spring JDBCTemplate. Spring improved the PreparedStatement situation by introducing named parameters in the query string and de-clutters the code by pulling a bean from its application context. As a result, all the connection setup code is encapsulated elsewhere and re-used as needed, and you get the following:

As you can see, Spring JDBCTemplate is helpful, but JOOQ offers quite a bit more.


Parting Shot

JOOQ is free to use with open source databases such as Postgres and MySQL. If you want to use it with Oracle or other commercial databases, there is a sliding annual subscription fee based on size of company. That there is a fee for commercial databases may be a detractor to some, however, a cost vs. benefits analysis could be performed to justify the savings in time and defects that translate into dollars saved on a project. If JOOQ simplifies writing complex queries without sacrificing performance, then there is a definite value argument to be made in favor of its adoption.

Aside from the possible hurdles involved in convincing a client to license JOOQ, it looks like a worthwhile product with great potential for changing the status quo in Java-based database interactions.

About the Author

Object Partners profile.

One thought on “JOOQ Worthy

  1. Scott says:

    Have you used Groovy’s SQL library? Granted, there is not much for type safety, but I find it’s quite hard to beat, for straight sql operations.
    http://docs.groovy-lang.org/latest/html/api/groovy/sql/Sql.html

  2. Terry Kilshaw says:

    So “JOOQ is a DSL”. How helpful that is! Did you know -spoiler alert – that a snark was a boojum!

    So what is a DSL, in this context?

Leave a Reply

Your email address will not be published.

Related Blog Posts
Natively Compiled Java on Google App Engine
Google App Engine is a platform-as-a-service product that is marketed as a way to get your applications into the cloud without necessarily knowing all of the infrastructure bits and pieces to do so. Google App […]
Building Better Data Visualization Experiences: Part 2 of 2
If you don't have a Ph.D. in data science, the raw data might be difficult to comprehend. This is where data visualization comes in.
Unleashing Feature Flags onto Kafka Consumers
Feature flags are a tool to strategically enable or disable functionality at runtime. They are often used to drive different user experiences but can also be useful in real-time data systems. In this post, we’ll […]
A security model for developers
Software security is more important than ever, but developing secure applications is more confusing than ever. TLS, mTLS, RBAC, SAML, OAUTH, OWASP, GDPR, SASL, RSA, JWT, cookie, attack vector, DDoS, firewall, VPN, security groups, exploit, […]