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:

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 :)