jOOQ Deep Dive: CTE, MULTISET, and SQL Pipelines

 

Transcript: 

If you want type-safe SQL in Java without N+1 and manual grouping hell, you're in the right place. In this video, we will use jOOQ to build clean and reliable CRUD and stuff that ORMs hate: nested payloads, CTE pipelines, and conditional expressions.

jOOQ, or Java Object Oriented Querying, is the library that helps you build type-safe SQL in Java. Unlike ORMs that handle SQL and mapping based on your Java entities, jOOQ generates Java classes based on the database schema and also provides you with an API to write SQL as if Java natively supported it. And the SQL is type-safe. jOOQ can use the Java compiler to check the SQL queries at compile time. So if there is an error in your SQL, the code won't compile.

Plus, jOOQ enables you to use powerful SQL features that ORMs struggle with: window functions, complex joins, CTEs, recursive CTEs, and so on. But jOOQ is very versatile. You can use it for everything, or in combination with Hibernate, where Hibernate is used for basic CRUD and jOOQ for complex queries, or you can use jOOQ for building queries and JDBC or Spring Data for executing them.

Okay, without further ado, let's wire it up with a Spring Boot project and see what jOOQ is capable of. The code for the demo I will show is available on GitHub. The link is in the description. Go ahead and clone the repo to follow along.

For this project, I'm using Spring Boot 4, PostgreSQL, Flyway for migrations, Testcontainers for testing, Docker Compose for spinning up the database container instance, and jOOQ. Of course, I'm also using JDK 25. My runtime of choice is Liberica JDK, recommended by Spring.

Let's look at the schema we are using. The project is called Neon Care. This is the app for a distributed healthcare system. We have hospitals, mobile points, and labs. Patients get triaged and assigned to available slots based on the capacity, required specialty, and the facility with the necessary equipment. The schema and test data are in the db subdirectory of resources.

Okay, we have a database schema but no Java classes yet. Let's leave that to jOOQ and its code generation function. For that, we need to configure the jOOQ codegen Maven plugin. Here we need to specify the generate goal, database properties, and the generator for the PostgreSQL database. We also want jOOQ to generate classes from all tables except the Flyway housekeeping ones. Then we specify the output directory and package name. And finally, we add the dependency on the PostgreSQL JDBC driver.

Apart from other standard configs, we need to specify jOOQ's dialect in application properties. Now we can spin up the database, run Flyway migrations, and finally run the goal of generating classes with jOOQ. The classes, entities, their records, enums, and also keys and indices are going to be generated in the output directory we specified.

Great. Now it's time to write some queries. We are using Spring Boot, so we don't need to establish the connection to the database using the DriverManager class. Just create a repository class annotated with Repository and you're golden.

So I have the appointment slot repository for basic CRUD. Here we need to inject jOOQ's DSLContext as a bean. DSLContext is jOOQ's main entry point. It's the fluent query factory used for building and executing SQL with the DSL. We need to inject it so that every query runs with the right dialect, the one that we specified in properties, transaction, and settings from Spring Boot.

Under the hood, it turns the DSL you write into database-specific SQL. Also, it binds parameters safely, executes it via JDBC, and maps the result back into jOOQ records. You can use your custom DTOs or DTOs generated by jOOQ to map jOOQ records directly to them. For instance, we have this SlotDto. For the demo's sake, I use one DTO for request and response.

Let's start with finding a slot by ID. Here we are using the context select to build a select that pulls exactly the columns we specify. Then we use the methods from and join to join the slot to its facility and specialty using the foreign key columns. We are referencing the tables via classes generated by jOOQ. The where method filters to one row or none by primary key. fetchOptional executes the query and returns an empty optional if there are no rows, or maps the single row into your slot via the lambda.

So why exactly is jOOQ so good here? Well, first, we get type-safe SQL. Appointment slot ID, facility name, and so on are generated fields. So typos and wrong column names fail at compile time, not in production. Then there's no mess of string queries. We are not assembling a string query hoping that we didn't miss a comma. And also, we get clean mapping. We get a strongly typed record back and map it directly to our DTO without any reflection magic.

Now let's create a slot. The createSlot method inserts a new row into the appointment slot and returns the generated primary key all in one go. So context insertInto and set build an insert statement using your generated type-safe column references and fill them from the slot. returningResult for the appointment slot ID tells jOOQ to use PostgreSQL's returning feature. So the database sends back the new row's ID. This is instead of us doing a second query. fetchOne getValue executes the insert via JDBC, reads the single returned row, and extracts the generated ID as a Long. Again, no handwritten SQL strings, no select last inserted id, no race conditions, and the insert and key retrieval stay atomic and portable through jOOQ's dialect support.

Okay, before we write update and delete methods, let's set some guardrails. Appointment slots can have active bookings in the status Reserved or Confirmed. So we need to make sure that we don't delete the slots or update them with a capacity less than the existing bookings. While we do all these checks, the bookings may change. So we need to lock the required appointment slot for the duration of the transaction.

Okay, to avoid repeating code, let's add some helper logic. Here we have the record SlotLockState with the data on the locked slot, whether it exists, a boolean, and if yes, how many active bookings it has. Now let's lock the slot and count the active bookings. Of course, we can do that directly in our update and delete methods, but we don't want to repeat ourselves. So we create a separate method for that.

The method lockSlotAndCountActiveBookings selects the required record by ID and then locks it with the for update clause. This is important. After that, we can use fetchCount to count the number of bookings with the Reserved and Confirmed status for a given slot ID.

Now let's write the update method. Here we use context transactionResult, that runs everything inside one database transaction and gives us a transaction-scope configuration. DSL using configuration creates a DSLContext bound to that transaction. So all reads and writes share the same connection and locks. lockSlotAndCountActiveBookings is our locking and counting logic part. And then we do routine Java-style checks, and if they are satisfactory, we update the slot with update, set, and execute, which returns the affected row count.

With deleting, the logic is similar. We acquire the configuration, perform checks, and delete a slot if the checks are successful, all within a single transaction.

Suppose we want to search for some appointment slots with filters that may be applied or not applied by the user. Well, that's easy. Let's build a dynamic SQL query with optional conditional expressions.

Let's write the findSlots method that accepts the slot filter and returns the list of slot details. The first part of the query is nothing new. We are selecting required fields from the appointment slot, joining the required tables, and doing the left join on the booking table to get active bookings and later show only slots with remaining free capacity.

The part we are most interested in starts after the where clause. So we have two booleans for facility and district conditions, and hasFacility conditionally adds a facility filter. If hasFacility is false, noCondition is a neutral do-nothing predicate. So the SQL is not affected. And hasDistrict does the same for district. We filter by district when present, otherwise we don't constrain the result set. As a result, we get composable, type-safe dynamic SQL, and the final generated query only contains the predicates that matter.

We have already seen how to use joins with jOOQ explicitly. Here's another example of that. But jOOQ also lets us write implicit joins. We can traverse foreign keys with path expressions and let jOOQ inject the joins. That is a very convenient feature of jOOQ that makes it a little bit familiar to Hibernate in terms of convenience, of course.

So the previous query can be rewritten in the following way. The select is pulling fields not just from Staff, but also from tables that Staff is related to by foreign keys, without you writing any join calls. And when jOOQ sees that we reference staff, facility, district, and name, it knows that it must join facility and district to make that column available. So it injects those joins in the generated SQL automatically. The benefit is less boilerplate and less risk of mixing up joins, but we still keep everything type-safe and discoverable.

Are you ready for a big one? Imagine that we need to extract a lot of data on the triage case. We need nested bookings, nested lab orders, and these orders will also have nested lab results, if any. This is the N+1 minefield for Hibernate, but we are using jOOQ, so we are in control of our SQL.

Standard SQL and jOOQ support the multiset value constructor. It allows us to collect the results of a non-scalar subquery into a single nested collection with multiset semantics.

Okay, let's write the findTriageCase. First, we need to alias facilities for two different roles: the facility that hosts an appointment slot and the facility that processes the lab order. Aliases keep those joins unambiguous.

Then we define a nested collection field for booking details. For each triage case row in the outer query, PostgreSQL will run the subquery filtered by the case's ID and produce the list of bookings for that case. convertFrom maps the nested result set into the list of BookingDto, but we are not executing any SQL here yet.

Then we do the same for lab result detail. For each lab order row, we fetch its lab results and map them to LabResultDetail, and this becomes the reusable nested field.

Then we move on to LabOrderDto. This is the nested-inside-nested part. Each lab order row includes the result collection, so your final DTO gets lab orders, and each lab order contains results.

After that, we can use prepared subqueries in our parent query for fetching the triage case. And this is just one round trip to the database. We are asking the database for one case with nested bookings and nested orders with results, and jOOQ gives us exactly that as typed Java collections. And this is still SQL under the hood. No ORM magic. It is just one well-defined SQL statement where the database does what it's good at, namely aggregating, filtering, joining, and jOOQ just maps the structured result.

Okay, here comes some really heavy machinery: common table expressions. A common table expression, or CTE, is a named query that you define at the top of the SQL statement and then reuse it like it's a temporary table. They are great when your query has multiple steps, because they let you build SQL in layers. First you compute A, then you use A to compute B, then you use B to compute C. It's readable, debuggable, and saves you from writing nested subqueries.

jOOQ is a great tool for that because it lets you build those layered SQL queries in Java without turning them into unreadable strings. So you get type compatibility because CTEs are treated as objects, and code that still maps directly to the SQL you want.

So how can we use a CTE in our code? For example, given a triage case ID, we want to suggest appointment slots that match the triage case's specialty, and we only want slots that have remaining capacity. So we will build three CTEs. We will get the triage case context, namely the specialty we need. We will find all slots for that specialty. And then we will compute the remaining capacity per slot and filter to slots that have capacity left.

Okay. Here we have a separate repository for that, SchedulingRepository. Again, we inject DSLContext. And then you can see here several string constants like slot ID field and facility name field. They are going to be used as explicit column names when we define the CTEs. I know, I know, it looks a little verbose, but trust me, it will prevent column naming chaos once we start turning CTEs into tables and fields.

Okay, the first CTE that we will write is called caseContextCte. So here we are creating a CTE with two columns, triage case ID and required specialty ID, and then we are filling it by selecting these columns from the triage case table for the given triage case ID. So caseContext is going to be a one-row context object that we can join against later.

And then we create the table from caseContextCte. It means that we are treating the CTE as a table and aliasing it as cc in this case, but the name can be any you like. And then we extract the typed field from it, the required specialty ID. Okay, this is important because we will join appointment slots against it.

Now the second CTE that we will write is called candidateSlotsCte. This one is going to be our slot finder. So we start from the caseContextCte table, as we called it, and join into appointment slots where the slot specialty matches the required specialty from the triage case. And then we join facility, district, and specialty to get some names.

At the end of the CTE, we will get a list of candidate slots with a slot ID, start and end, facility name, district name, specialty name, and slot capacity. We will convert this CTE into a table as well. Let's call it cs. And we will also extract a couple of fields: slot ID and capacity. So now slot ID is a real jOOQ field that can be referenced in later queries.

Now we need to calculate the active bookings per slot. This is the correlated subquery that we turn into a field. So for each candidate slot, we count how many bookings exist, but only if the booking status is Reserved or Confirmed. So basically, here we count how many spots are already taken. The asField part here is important because it turns the subquery into a value that we can use like a column.

Now we can calculate the remaining capacity. Here we also have the field remainingCapacity. The greatest function with inline zero prevents negative values. Now we have the very important value: remaining capacity.

Finally, we will write another CTE called slotsWithRemainingCapacityCte. Here we take all columns that we need from the candidate slots, add the calculated remaining capacity, and expose the result as the new named CTE. Then with this CTE, we do the same as with the previous one. So we create the table swc in this case and extract the field for remaining capacity, and now we can write our final query.

This is where our CTE labor will pay off. So with caseContextCte adds case context, with candidateSlotsCte adds candidate slots, and with slotsWithRemainingCapacityCte adds slots with capacity. And then the real query is going to be super clean. So we select from slots with capacity, we keep only the rows where the remaining capacity is greater than zero, we sort by start time, and we take the first 50 slots. And finally, with fetch, we map each row into SlotSuggestionDetail.

So the output is the list of suggested slots that match the triage case ID to the required specialty, belong to a specific facility and district, have remaining capacity, and are ordered by the earliest start time.

Of course, we can do a lot more with jOOQ. Basically, whatever you can do with SQL, you can do with jOOQ. The latter gives you type safety, a database-agnostic approach, and a DSL API reducing the risk of syntactic errors in your code.

Now, how do we test all of that? There are two approaches to testing jOOQ code. You can use the JooqTest annotation. This is the Spring Boot testing annotation that is used for testing jOOQ components in isolation. It autoconfigures the DSLContext and loads only the persistence layer components.

Alternatively, you can use the SpringBootTest annotation. It loads the whole application context. I'm using the second approach here. The Flyway migrations are run automatically, so there's no need to configure them explicitly. I'm also using Transactional and Rollback annotations. This way, we will run each test method inside a transaction. Each test is rolled back afterwards. So if we are doing any inserts and updates during the test, we don't pollute the database.

Then we autowire the slot repository here. So we are injecting a real bean from the Spring context to test the actual jOOQ code that we wrote. And then we declare the PostgreSQL container that the tests will use. And after that, you can write tests as usual.

I hope I convinced you to try out jOOQ and maybe integrate it into your next or existing project. If this video was useful to you, don't forget to like it, subscribe to our channel, and until next time.