jOOQ

jOOQ is an internal DSL and source code generator, modelling the SQL language as a type safe Java API to help you write better SQL.

Its main features include:

• The source code generator
• The DSL API for type safe query construction and dynamic SQL

Secondary features include:

• DAOs
• Data export and import
• Data type conversion
• DDL statement support
• DML statement support
• Diagnostics
• Dialect agnosticity for 30+ RDBMS
• Embedded types
• Formatting and pretty printing
• Implicit joins
• Kotlin support
• Mapping
• Meta data API
• Mocking API for JDBC
• Model API for use in traversal and replacement
• MULTISET and ROW nested collections and records
• Multitenancy
• Parser (and translator)
• Pattern based transformation
• Plain SQL templating
• Policies
• Procedural logic API
• Reactive support via R2DBC
• Readonly columns
• Scala support
• Schema diff
• SQL transformation
• SQL translation
• Stored procedure support
• Transaction API
• UpdatableRecords for simplified CRUD, with opt-in optimistic locking
• And much more

Examples

Typesafe, embedded SQL

jOOQ’s main feature is typesafe, embedded SQL, allowing for IDE auto completion of SQL syntax…

… as well as of schema meta data:

This allows for preventing errors of various types, including typos of identifiers:

Or data type mismatches:

The examples are from the code generation blog post.

A more powerful example using nested collections

For many more examples, please have a look at the demo. A key example showing jOOQ’s various strengths is from the MULTISET operator announcement blog post:

Given these target DTOs:

record Actor(String firstName, String lastName) {}
record Film(
  String title,
  List<Actor> actors,
  List<String> categories
) {}

You can now write the following query to fetch films, their nested actors and their nested categorise in a single, type safe query:

List<Film> result =
dsl.select(
      FILM.TITLE,
      multiset(
        select(
          FILM.actor().FIRST_NAME, 
          FILM.actor().LAST_NAME)
        .from(FILM.actor())
      ).as("actors").convertFrom(r -> r.map(mapping(Actor::new))),
      multiset(
        select(FILM.category().NAME)
        .from(FILM.category())
      ).as("categories").convertFrom(r -> r.map(Record1::value1))
   )
   .from(FILM)
   .orderBy(FILM.TITLE)
   .fetch(mapping(Film::new));

The query is completely type safe. Change a column type, name, or the target DTO, and it will stop compiling! Trust only your own eyes:

And here you see the nested result in action from the logs:

How does it work? Look at this annotated example:

List<Film> result =
dsl.select(
      FILM.TITLE,

      // MULTISET is a standard SQL operator that allows for nesting collections
      // directly in SQL. It is either
      // - supported natively
      // - emulated using SQL/JSON or SQL/XML
      multiset(

        // Implicit path based joins allow for simpler navigation of foreign
        // key relationships.
        select(
          FILM.actor().FIRST_NAME, 
          FILM.actor().LAST_NAME)

        // Implicit correlation to outer queries allows for avoiding repetitive
        // writing of predicates.
        .from(FILM.actor())

      // Ad-hoc conversion allows for mapping structural Record2<String, String>
      // types to your custom DTO using constructor references
      ).as("actors").convertFrom(r -> r.map(mapping(Actor::new))),
      multiset(
        select(FILM.category().NAME)
        .from(FILM.category())
      ).as("categories").convertFrom(r -> r.map(Record1::value1))
   )
   .from(FILM)
   .orderBy(FILM.TITLE)
   .fetch(mapping(Film::new));

The generated SQL query might look like this, in PostgreSQL:

select
  film.title,
  (
    select coalesce(
      jsonb_agg(jsonb_build_object(
        'first_name', t.first_name,
        'last_name', t.last_name
      )),
      jsonb_build_array()
    )
    from (
      select
        alias_78509018.first_name, 
        alias_78509018.last_name
      from (
        film_actor
          join actor as alias_78509018
            on film_actor.actor_id = alias_78509018.actor_id
        )
      where film_actor.film_id = film.film_id
    ) as t
  ) as actors,
  (
    select coalesce(
      jsonb_agg(jsonb_build_object('name', t.name)),
      jsonb_build_array()
    )
    from (
      select alias_130639425.name
      from (
        film_category
          join category as alias_130639425
            on film_category.category_id = alias_130639425.category_id
        )
      where film_category.film_id = film.film_id
    ) as t
  ) as categories
from film
order by film.title

This particular example is explained more in detail in the MULTISET operator announcement blog post. For many more examples, please have a look at the demo.