Trying jOOQ out in a real world example

In an earlier post I talked about the state of two SQL Java libraries that do not follow the ORM approach. One of them was JOOQ. JOOQ has been around since about 2010 (version 1.0), but I never really paid any attention to it. On first glance it looked too much like enterprise software to me. But after evaluating it some more it started to look more interesting. There are a couple of things that I like about it:

• Most features can be used independently from each other e.g.:
  • Code Generation (produces Constants (like table names) and Classes representing individual database records)
  • SQL DSL Query Building (one could just it to generate a SQL query String and not bother with the rest of the library)
  • POJOs and DAO generation (with these Classes generated it feels a bit more “ORM-like”)
• The pricing model and history show that it seems pretty stable. You basically pay for support/maintenance and when you need support for commercial or cloud databases
• It supports modern features like Java 16 records, Streams and Optionals quite well
• It covers quite a lot of SQL including specific dialects of most available databases
• It does not try to solve the object graph problem like an ORM, but gives you powerful tools like SQL multisets and integrates them into java

Looking at a simple example

The jOOQ documentation is well written albeit a bit dry and overwhelming especially on your first encounter. It contains sum gems which (in my opinion) should be front and center advertised on the main page (the jOOQ blog does a better job). This example combines two new (well “new” is relative here …) features of Java and SQL:

1. SQL Multisets which boil down to (depending on the database) JSON or XML
2. Java 16 Records

// the capital constants are generated by the code generator
// source:
// https://blog.jooq.org/jooq-3-15s-new-multiset-operator-will-change-how-you-think-about-sql/
record Actor(String firstName, String lastName) {}
record Film(
  String title,
  List<Actor> actors,
  List<String> categories
) {}


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

Achieving this kind of nesting without either multiple single queries or a single messy big one with gigantic redundancy is a big win in my book! It is basically the SQL DSL I always wished for (now there is truely no excuse anymore not to write SQL for me ;) )

Another new feature in the pipleline for the next jOOQ release (3.16) is support for GIS extensions e.g. POSTGIS which is described in this blog post. That is useful for querying certain aspects of geometry for instance if a point is inside a polygon or not. The future looks bright :)