Introduction to jOOQ

jooq.org states that jOOQ generates Java code from your database and lets you build type safe SQL queries through its fluent API.

jOOQ (Java Object-Oriented Querying) is a library in Java that provides an expressive and type-safe way to build SQL queries.

Why would you use jOOQ?

First the library matches your database schema (table, columns and records) with Java classes which means:

• Reduced risks of mismatches of SQL syntaxes ;
• Reduced runtime errors;
• Reduction of boilerplate code;

Besides that jOOQ also have a gamma of advanced sql features hands-on like sorting, stored procedures, common table expressions and so on.

This can be especially useful for complex queries that go beyond what typical ORMs can handle.

And nonetheless it supports multiple SQL dialects, like MySQL, Postgres, Oracle, etc.

With this library you focus more on working with the SQL rather than abstracted ORM models.

jOOQ can be imported using Ant, Maven and Graddle.

To give a bit of taste on what jOOQ is about let’s see query example.

This is an example of a select:

var result = create.select(BOOK.TITLE, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
      .from(BOOK)
      .join(AUTHOR)
      .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
      .where(BOOK.PUBLISHED_IN.eq(1948))
      .fetch();

This example would translate to SQL:

SELECT title, first_name, last_name 
  FROM book 
  JOIN author ON book.author_id = author.id 
 WHERE book.published_in = 1984

Through this select example you can see that using jOOQ library to make the query reduce many risks and also open window for performance optimizations under the hood. Simple right?

But don’t be fooled by the library, at first thing seems very simple but jOOQ is robust and can be complex, so be aware of the learning curve and the fact the this library can be heavier than others.

Overall this an excelent tool and everyone should give it a try.

To get to know more about it you can learn through jooq.org

—

That’s it! If there is anything thing else to discuss feel free to drop a comment, if I missed anything let me know so I can update accordingly.

Until next post! :)