Return to site

☕🗄️ jOOQ: 🔟 INSIGHTS on TYPE-SAFE SQL, DATABASES & AI

What if writing SQL in Java felt like writing SQL itself?

· java

🔸 TL;DR

In A Bootiful Podcast, Josh Long interviews Lukas Eder, the creator of jOOQ (Java Object Oriented Querying).

They explore how jOOQ brings type-safe SQL into Java, supports dynamic query transformations, integrates database features, and even benefits from AI-assisted debugging.

Section image

Here are 🔟 insights from their conversation.

🔸 🔟 VERBATIM FROM THE INTERVIEW

1️⃣ [jOOQ started off as a type safe embedded domain specific language that models SQL as a language in Java.]

jOOQ uses a Domain-Specific Language (DSL) to express SQL queries directly in Java, combining SQL's expressiveness with Java's type system.

#DSL

2️⃣ [So you can just type check your SQL queries directly in your Java code and you can use auto completion and everything.]

Java compilation and IDE autocompletion help detect query mistakes earlier, reducing reliance on runtime SQL errors.

import static com.example.jooq.tables.Customer.CUSTOMER;

// CUSTOMER is generated from your database schema
List<String> names = dsl
    .select(CUSTOMER.NAME)       // ✅ IDE autocompletion
    .from(CUSTOMER)
    .where(CUSTOMER.ID.eq(42))   // ✅ Type-safe Integer
    .fetch(CUSTOMER.NAME);       // ✅ List<String>

// CUSTOMER.ID.eq("42");        // ❌ Type mismatch
// CUSTOMER.UNKNOWN_COLUMN;     // ❌ Compilation error 

#TypeSafety

3️⃣ [You're actually not really writing the SQL code itself, but you're writing the expression tree that generates SQL.]

jOOQ builds an expression tree representing the query, then renders SQL for the target database dialect.

import static com.example.jooq.tables.Customer.CUSTOMER;

// 1. Build SQL expressions as Java objects
Condition condition = CUSTOMER.ID.eq(42);

// 2. Compose the SQL expression tree
Select<?> query = dsl
    .select(CUSTOMER.NAME)
    .from(CUSTOMER)
    .where(condition);

// 3. Render the tree into SQL
String sql = dsl.renderInlined(query);

// Example generated SQL (dialect-dependent):
// SELECT customer.name
// FROM customer
// WHERE customer.id = 42 

#SQL

4️⃣ [You can have transformations that add predicates, that add joins dynamically depending on whatever you have in your session.]

Query transformations allow jOOQ to modify SQL at runtime, enabling reusable rules and context-dependent query behavior.

import static com.example.jooq.tables.Customer.CUSTOMER;
import static com.example.jooq.tables.Orders.ORDERS;

// 1. Session-dependent parameters
int tenantId = 7;
boolean requirePaidOrder = true;

// 2. Build the initial query
Condition filter = CUSTOMER.TENANT_ID.eq(tenantId);

SelectJoinStep<?> query = dsl
    .select(CUSTOMER.ID, CUSTOMER.NAME)
    .from(CUSTOMER);

// 3. Dynamically add JOIN and predicate
if (requirePaidOrder) {
    query = query
        .join(ORDERS)
        .on(ORDERS.CUSTOMER_ID.eq(CUSTOMER.ID));

    filter = filter.and(ORDERS.STATUS.eq("PAID"));
}

// 4. Execute the composed query
var customers = query.where(filter).fetch();

#DynamicSQL

5️⃣ [It's always going to be added automatically to all of your SQL statements, including writing statements.]

jOOQ's policy feature can inject tenant-specific predicates into queries, helping enforce row-level access restrictions.

import org.jooq.*;
import org.jooq.impl.DefaultPolicyProvider;

import static com.example.jooq.tables.Customer.CUSTOMER;

// 1. Define a tenant-specific policy
int tenantId = 42;

configuration.set(new DefaultPolicyProvider()
    .append(CUSTOMER, CUSTOMER.TENANT_ID.eq(tenantId))
);

DSLContext dsl = DSL.using(configuration);

// 2. SELECT: tenant filter added automatically
dsl.selectFrom(CUSTOMER).fetch();

// 3. UPDATE: tenant filter added automatically
dsl.update(CUSTOMER)
   .set(CUSTOMER.NAME, "Alice")
   .where(CUSTOMER.ID.eq(10))
   .execute();

// 4. DELETE: tenant filter added automatically
dsl.deleteFrom(CUSTOMER)
   .where(CUSTOMER.ID.eq(10))
   .execute();

// All statements are restricted to TENANT_ID = 42 

#DataSecurity

6️⃣ [SQL has so many constructs that change nullability very implicitly.]

LEFT JOINs and other SQL constructs can introduce nullable results. This makes guaranteeing nullability through Java types particularly challenging.

import static com.example.jooq.tables.Customer.CUSTOMER;
import static com.example.jooq.tables.Orders.ORDERS;

// ORDERS.ID is declared NOT NULL in the database

var results = dsl
    .select(CUSTOMER.NAME, ORDERS.ID)
    .from(CUSTOMER)
    .leftJoin(ORDERS)
    .on(ORDERS.CUSTOMER_ID.eq(CUSTOMER.ID))
    .fetch();

// LEFT JOIN can introduce NULL values
for (var row : results) {
    String name = row.get(CUSTOMER.NAME);
    Integer orderId = row.get(ORDERS.ID);

    if (orderId == null) {
        System.out.println(name + " has no orders");
    } else {
        System.out.println(name + " ordered " + orderId);
    }
}

#NullSafety

7️⃣ [You want to really push your operation inside of the database with whatever technology the database offers.]

Process data close to where it lives. SQL operations and stored procedures can avoid unnecessary transfers and client-side computations.

CREATE PROCEDURE mark_order_paid(IN p_order_id INT)
LANGUAGE SQL
AS $$
    UPDATE orders
    SET status = 'PAID'
    WHERE id = p_order_id;
$$;
import com.example.jooq.routines.MarkOrderPaid;

// 1. Instantiate the generated procedure
MarkOrderPaid procedure = new MarkOrderPaid();

// 2. Set the input parameter
procedure.setPOrderId(101);

// 3. Execute the procedure in the database
procedure.execute(dsl.configuration());

// Alternative: generated convenience method
Routines.markOrderPaid(dsl.configuration(), 101);

#Performance

8️⃣ [jOOQ will pick up the view in the code generator, make it available like all the other tables.]

Database views can encapsulate complex, static SQL while remaining accessible through generated Java types.

CREATE VIEW paid_orders_summary AS
SELECT
    customer_id,
    SUM(total_amount) AS total_revenue
FROM orders
WHERE status = 'PAID'
GROUP BY customer_id;
import static com.example.jooq.tables
    .PaidOrdersSummary.PAID_ORDERS_SUMMARY;

import java.math.BigDecimal;

// jOOQ generates PAID_ORDERS_SUMMARY
// from the database view

var results = dsl
    .select(
        PAID_ORDERS_SUMMARY.CUSTOMER_ID,
        PAID_ORDERS_SUMMARY.TOTAL_REVENUE
    )
    .from(PAID_ORDERS_SUMMARY)
    .where(PAID_ORDERS_SUMMARY.TOTAL_REVENUE
        .gt(new BigDecimal("1000")))
    .fetch();

results.forEach(System.out::println);

#CodeGeneration

9️⃣ [Then you can call. It's just a Java function, a Java method.]

jOOQ generates Java stubs for stored procedures and functions, allowing developers to invoke database logic through typed Java APIs.

CREATE PROCEDURE activate_customer(IN p_customer_id INT)
LANGUAGE SQL
AS $$
    UPDATE customer
    SET active = TRUE
    WHERE id = p_customer_id;
$$;
import static com.example.jooq.Routines.activateCustomer;

// jOOQ generates the Java method from the procedure

// 1. Call the stored procedure as a Java method
activateCustomer(dsl.configuration(), 10);

// 2. Alternative: use the generated Routine class
ActivateCustomer procedure = new ActivateCustomer();

procedure.setPCustomerId(10);
procedure.execute(dsl.configuration());

#StoredProcedures

🔟 [And you can ask it to write a test, you know.]

Lukas describes how Claude uncovered subtle R2DBC rollback exception-handling bugs and helped generate regression tests using the jOOQ API.

#AIEngineering

🔸 TAKEAWAYS

▪️ SQL is not the enemy: jOOQ embraces SQL instead of hiding its capabilities.

▪️ Type safety matters: Generated Java APIs make database interactions easier to maintain.

▪️ Dynamic SQL is powerful: Runtime transformations enable sophisticated database behavior.

▪️ Database-first performance: Push computation to the database when appropriate.

▪️ AI can assist experts: Even experienced framework authors can benefit from AI-supported debugging and regression testing.

▪️ Abstractions have limits: SQL nullability, dialect differences, and database semantics still require expertise.

💬 Have you used jOOQ in production, or do you prefer JPA/Hibernate or plain JDBC?

#Java #jOOQ #SQL #SpringBoot #Database #TypeSafety #SoftwareEngineering #JavaDevelopment #AI #BackendDevelopment

Go further with Java certification:

Java👇

https://bit.ly/javaOCP

Spring👇

SpringBook👇

JavaFullstackBook👇