jOOQ Object Oriented Querying

Introduction

In this blog, I aim to offer an in-depth look at jOOQ—a powerful library that enables fluent SQL query construction and type-safe database interaction in Java.

jOOQ provides a distinctive approach, bridging the gap between SQL and Java. It is ideal for developers seeking greater control over their database interactions without sacrificing readability or maintainability.

This blog will walk you through jOOQ’s features, compare it with other popular libraries, and showcase real-world applications, including how we use jOOQ in one of our company's projects, Conac. I hope it gives you valuable insights into jOOQ and presents a helpful tool to consider for your own project needs

What is jOOQ

jOOQ (Java Object Oriented Querying) is a widely-used Java library that enables developers to write SQL queries using a type-safe, fluent API while fully harnessing the power of SQL.

By generating code directly from your database schema, jOOQ allows you to interact with database tables, columns, and records in a strongly typed way.

Key feature of jOOQ

• Type-Safe SQL: jOOQ ensures that SQL queries are type-checked at compile time, minimizing the risk of runtime errors.

• Fluent API: jOOQ provides a fluent API for constructing SQL queries, enhancing readability and maintainability in your code.

• Generated Code: jOOQ generates Java classes based on your database schema—such as tables, columns, and keys—offering a type-safe way to interact with your database.

• SQL as First-Class Citizen: Unlike ORMs like Hibernate that abstract SQL, jOOQ emphasizes generating SQL specific to your database, allowing direct interaction with SQL as a primary feature.

Pros

This section is based on my experience with jOOQ and other ORM libraries, along with reference documentation, so it may reflect a subjective perspective.

Query creation

Like all ORM libraries, jOOQ aims to return query results as Java objects. However, the approach each library takes to building queries can vary.

For very simple queries, jOOQ may lose some advantages to auto-generated query libraries like JPA.

But when it comes to building complex queries, we typically encounter three main solutions.

Let’s consider two use cases, one simple and one more complex, and walk through some of the popular solutions.

• Simple use case:

 Find authors who published fewer than ten books in 2020 and then retrieve all books by those authors.

• Complex use case:

Query books that have been published by authors in 2024, and we want to apply the following:
- We need to get the total number of books published by each author.
- We want to rank the authors by the number of books they've published in 2024.
- We need to filter the books to include only those from authors whose total number of books in 2024 is above a certain threshold (e.g., 5).

First solution: Using a query builder based on Java objects, which is a common approach in many libraries. For example:

• JPQL

  Simple use case:

SELECT b 
FROM Book b 
WHERE b.author IN (
    SELECT b2.author 
    FROM Book b2 
    WHERE b2.publishedIn = 2020 
    GROUP BY b2.author 
    HAVING COUNT(b2) < 10
)

      Complex use case:

... We are unable to achieve this using JPQL.

• QueryDSL

  Simple use case:

queryFactory
    .selectFrom(book)
    .where(book.author.in(
        JPAExpressions.select(book.author)
            .from(book)
            .where(book.publishedIn.eq(2020))
            .groupBy(book.author)
            .having(book.count().lt(10))
    ))
    .fetch();

      Complex use case:

val query = JPAQuery<Void>(entityManager)
    .select(qAuthor)
    .from(qAuthor)
    .leftJoin(qAuthor.books, qBook)
    .where(qBook.publishYear.eq(2024))  // Filter books published in 2024
    .groupBy(qAuthor.id)  // Group by author
    .having(qBook.count().gt(minBooksPublished))  // Apply threshold filter
    .orderBy(qBook.count().desc())  // Order by number of books published in descending order
    .fetch()

query
    .select(qBook)
    .from(qBook)
    .where(qBook.author.`in`(query))  // Filter books of the top authors
    .fetch()

• Criteria
  Simple use case:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();

// Step 1: Subquery to find authors with fewer than ten books in 2020
CriteriaQuery<Author> authorSubQuery = cb.createQuery(Author.class);
Root<Book> bookRoot = authorSubQuery.from(Book.class);
authorSubQuery.select(bookRoot.get("author"))
    .where(cb.equal(bookRoot.get("publicationYear"), 2020))
    .groupBy(bookRoot.get("author"))
    .having(cb.lt(cb.count(bookRoot), 10));

// Step 2: Main query to retrieve all books by authors from the subquery
CriteriaQuery<Book> mainQuery = cb.createQuery(Book.class);
Root<Book> mainBookRoot = mainQuery.from(Book.class);
Join<Book, Author> authorJoin = mainBookRoot.join("author");

// Correlate main query with subquery
Predicate authorInSubQuery = cb.in(authorJoin).value(authorSubQuery);

mainQuery.select(mainBookRoot).where(authorInSubQuery);

TypedQuery<Book> query = entityManager.createQuery(mainQuery);
return query.getResultList();

      Complex use case:

val criteriaBuilder = entityManager.criteriaBuilder
val criteriaQuery = criteriaBuilder.createQuery(Book::class.java)
val rootBook = criteriaQuery.from(Book::class.java)
val rootAuthor = rootBook.get<Author>("author")  // Assuming there's an 'author' field in Book

// Step 1: Create the subquery to get the count of books per author published in 2024
val subQuery = criteriaQuery.subquery(Long::class.java)
val subRoot = subQuery.from(Book::class.java)
val authorPath = subRoot.get<Author>("author")

subQuery.select(criteriaBuilder.count(subRoot))
subQuery.where(
    criteriaBuilder.equal(authorPath, rootAuthor),  // Group by author
    criteriaBuilder.equal(subRoot.get<Int>("publishYear"), 2024)  // Only books published in 2024
)

// Step 2: Main query for books published by authors with more than the threshold number of books
criteriaQuery.select(rootBook)
criteriaQuery.where(
    criteriaBuilder.greaterThanOrEqualTo(
        subQuery, minBooksPublished
    )
)

// Step 3: Group by author and order by the number of books in descending order
val orderBy = criteriaBuilder.desc(subQuery)
criteriaQuery.orderBy(orderBy)

// Execute the query
entityManager.createQuery(criteriaQuery)
    .resultList

• jOOQ

  Simple use case:

List<BookRecord> result = ctx.selectFrom(b)
    .where(b.AUTHOR_ID.in(
        ctx.select(b2.AUTHOR_ID)
            .from(b2)
            .where(b2.PUBLISHED_IN.eq(2020))
            .groupBy(b2.AUTHOR_ID)
            .having(DSL.count().lt(10))
    ))
    .fetch();

      Complex use case:

ctx.select(b.TITLE, b.PUBLISHED_IN, b.AUTHOR_ID, DSL.rank().over()
        .partitionBy(b.AUTHOR_ID)
        .orderBy(DSL.count().over().partitionBy(b.AUTHOR_ID).desc())
        .as("author_rank"))
    .from(b)
    .join(author).on(b.AUTHOR_ID.eq(author.ID))
    .where(b.PUBLISHED_IN.eq(2024))
    .groupBy(b.AUTHOR_ID, b.TITLE, b.PUBLISHED_IN)
    .having(DSL.count().gt(5))  // Filter authors with more than 5 books published in 2024
    .orderBy(DSL.rank().over()
        .partitionBy(b.AUTHOR_ID)
        .orderBy(DSL.count().over().partitionBy(b.AUTHOR_ID).desc()))
    .fetch();

Second solution: Use native queries for more complex or uncommon queries.

• Simple use case:

String sql = """
        SELECT b.*
        FROM books b
        WHERE b.author_id IN (
                SELECT author_id
                FROM books
                WHERE publication_year = 2020
                GROUP BY author_id
                HAVING COUNT(*) < 10
        )
        """;

Query query = entityManager.createNativeQuery(sql, Book.class);
return query.getResultList();

• Complex use case:

val sql = """
    SELECT b.*
    FROM book b
    JOIN author a ON b.author_id = a.id
    WHERE b.publish_year = 2024
        AND a.id IN (
            SELECT b1.author_id
            FROM book b1
            WHERE b1.publish_year = 2024
            GROUP BY b1.author_id
            HAVING COUNT(b1.id) > :minBooksPublished
        )
    ORDER BY (
        SELECT COUNT(b2.id)
        FROM book b2
        WHERE b2.author_id = a.id AND b2.publish_year = 2024
    ) DESC
"""

// Create the native query
val query: Query = entityManager.createNativeQuery(sql, Book::class.java)

// Set the threshold for the number of books published
query.setParameter("minBooksPublished", minBooksPublished)

// Execute the query and return the result
return query.resultList as List<Book>

Third solution: Writing native queries and generating source code from them at compile-time, such as with tools like DOMA or sqlc.

Let's walk through each of them together :

• Second solution: Native queries are not ideal because they can't be verified until they are executed, which compromises type safety.

• Third option: I must admit that I haven't used these tools before; I just got a recommendation from a colleague who contributed to these libraries. It also seems that they aren't prevalent in the community.

So, we’re left with only the first solution. Let's dive deeper into it.

• JPA and JPQL :
  This choice works well for simple queries, but as queries become more complex, it becomes difficult to build a type-safe query and often requires us to resort to native SQL. We couldn’t handle a complex use case using JPQL alone in the previous example.

  The reason is that this library is designed as an interface focused on providing common functionality across databases. As a result, it lacks the flexibility needed for complex queries without using native SQL.

• Criteria Query Builder
  This is always a safe choice, as it’s officially supported by Java EE and can handle almost any query you can imagine.

  However, one major drawback is its complexity and the fact that it’s not written in an SQL-like syntax. This makes it difficult to determine the generated SQL just by reading the code. Since a large part of an engineer’s work involves tracing bugs and reading code, this approach can be very time-consuming—and, by extension, costly.

  Overall, I would classify this as not user-friendly.

• QueryDSL
  It addresses most of the issues mentioned above, providing the ability to build complex queries while remaining easy to read, thanks to its SQL-like syntax and fluent API for query construction.

  However, one major drawback is that it’s open source but no longer appears to be actively developed.

• jOOQ
  The final candidate, jOOQ, offers all the advantages of QueryDSL while still being actively maintained and developed.

  This is why I appreciate it, especially for its query-building functionality.

Dialect support

One of jOOQ's powerful features is its support for multiple dialects, enabling it to generate custom queries tailored to each dialect.

Use case: When querying orders with processing times shorter than a specified number of days. The processing time is calculated as

processing days = order.end_date - order.start_date

To do this, we need to calculate each order’s processing days before comparing it to the threshold.

To highlight the advantages of jOOQ, I'll implement this query in some competing libraries for comparison.

jOOQ

We can write the query like this on the application side.

// Calculating the difference in days between two dates
Field<Integer> dateDiff = DSL.timestampDiff(DatePart.DAY, ORDERS.END_DATE, ORDERS.START_DATE);

Result<Record1<Integer>> result = ctx.select(dateDiff)
                                     .from(ORDERS)
                                     .fetch();

Based on the dialect, jOOQ will generate a different query for each database.

• MySQL

SELECT TIMESTAMPDIFF(DAY, ORDERS.START_DATE, ORDERS.END_DATE)
FROM ORDERS;

• PostgreSQL

SELECT EXTRACT(DAY FROM ORDERS.END_DATE - ORDERS.START_DATE)
FROM ORDERS;

JPA

In JPA, we don't have an effective solution to handle this. One possible approach is using a native query, but this creates a tight coupling between the database and the application.

• MySQL

Query query = entityManager.createNativeQuery(
    "SELECT TIMESTAMPDIFF(DAY, o.start_date, o.end_date) FROM orders o WHERE ...");
List<Object[]> result = query.getResultList();

• PostgreSQL

Query query = entityManager.createNativeQuery(
    "SELECT EXTRACT(DAY FROM o.end_date - o.start_date) FROM orders o WHERE ...");
List<Object[]> result = query.getResultList();

You could argue that calculating the difference in the application is more efficient. However, imagine a use case where you need to retrieve records based on this condition.

endDate - startDate < threshold

Unless you're okay with querying all records and filtering them on the application side, I believe filtering in the database is more efficient.

QueryDSL and Criteria Query Builder

Although more type-safe, both QueryDSL and the Criteria Query Builder face the same issue as JPA in this case. You can refer to the implementations for QueryDSL and the Criteria Query Builder below :

QueryDSL

• MySQL

QOrder order = QOrder.order;

// Calculate the difference in days between startDate and endDate
NumberExpression<Integer> dateDiff = Expressions.numberTemplate(Integer.class,
    "DATEDIFF({0}, {1})", order.endDate, order.startDate);

List<Tuple> results = queryFactory.select(order.id, dateDiff)
                                   .from(order)
                                   .fetch();

for (Tuple tuple : results) {
    Long orderId = tuple.get(order.id);
    Integer daysDifference = tuple.get(dateDiff);
    // Process the results
}

• PostgreSQL

QOrder order = QOrder.order;

// Calculate the difference in days between startDate and endDate
NumberExpression<Long> dateDiff = Expressions.numberTemplate(Long.class,
    "EXTRACT(DAY FROM {0} - {1})", order.endDate, order.startDate);

List<Tuple> results = queryFactory.select(order.id, dateDiff)
                                   .from(order)
                                   .fetch();

for (Tuple tuple : results) {
    Long orderId = tuple.get(order.id);
    Long daysDifference = tuple.get(dateDiff);
    // Process the results
}

Criteria API

• MySQL

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> query = cb.createTupleQuery();
Root<Order> order = query.from(Order.class);

// Use DATEDIFF for MySQL
query.multiselect(order.get("id"), cb.function("DATEDIFF", Integer.class, order.get("endDate"), order.get("startDate")));

List<Tuple> results = entityManager.createQuery(query).getResultList();

for (Tuple tuple : results) {
    Long orderId = tuple.get(0, Long.class);
    Integer daysDifference = tuple.get(1, Integer.class);
    // Process the results
}

• PostgreSQL

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> query = cb.createTupleQuery();
Root<Order> order = query.from(Order.class);

// Use EXTRACT for PostgreSQL
query.multiselect(order.get("id"), cb.function("EXTRACT", Long.class, cb.diff(order.get("endDate"), order.get("startDate"))));

List<Tuple> results = entityManager.createQuery(query).getResultList();

for (Tuple tuple : results) {
    Long orderId = tuple.get(0, Long.class);
    Long daysDifference = tuple.get(1, Long.class);
    // Process the results
}

With jOOQ, you can write a single code block on the application side, and it will generate SQL based on the dialect. In contrast, other solutions require writing code that depends on the specific dialect being used.

Support for tuple

Use case
Imagine you're running an e-commerce platform and need to retrieve orders for a list of specific customer-product combinations.

For instance, the marketing team wants to analyze the orders where a particular set of customers(identified by their CUSTOMER_ID) bought certain products (identified by their PRODUCT_ID).

Native query
I think we can quickly identify the solution by writing it in your preferred database.

SELECT *
FROM ORDERS
WHERE (CUSTOMER_ID, PRODUCT_ID) IN 
    (ROW(1, 101), ROW(2, 202), ROW(3, 303));

I've encountered this issue before, and my usual solution is to convert the query into this format.

SELECT *
FROM orders
WHERE (customer_id = 1 AND product_id = 101)
   OR (customer_id = 2 AND product_id = 202)
   OR (customer_id = 3 AND product_id = 303);

The reason is that most libraries don't support checking multiple pairs of values in an IN clause. Instead, they offer an alternative solution for combination conditions using `and()` and `or()` to achieve the same effect.

We face two issues with this solution:

• It is difficult to read and construct, making it challenging to maintain.

• Although it's not a major concern and occurs infrequently, this style results in much longer queries, which can lead to limitations on query length.

The solution for jOOQ and other libraries can be seen below.

jOOQ

ctx.selectFrom(ORDERS)
   .where(row(ORDERS.CUSTOMER_ID, ORDERS.PRODUCT_ID)
          .in(row(1, 101), row(2, 202), row(3, 303)))
   .fetch();

QueryDSL

QOrder order = QOrder.order;
BooleanBuilder builder = new BooleanBuilder();

builder.or(
    order.customerId.eq(1).and(order.productId.eq(101)),
    order.customerId.eq(2).and(order.productId.eq(202)),
    order.customerId.eq(3).and(order.productId.eq(303))
);

List<Order> results = queryFactory.selectFrom(order)
                                   .where(builder)
                                   .fetch();

Criteria

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Order> query = cb.createQuery(Order.class);
Root<Order> order = query.from(Order.class);
List<Predicate> predicates = new ArrayList<>();
predicates.add(cb.and(cb.equal(order.get("customerId"), 1), cb.equal(order.get("productId"), 101)));
predicates.add(cb.and(cb.equal(order.get("customerId"), 2), cb.equal(order.get("productId"), 202)));
predicates.add(cb.and(cb.equal(order.get("customerId"), 3), cb.equal(order.get("productId"), 303)));
Predicate finalPredicate = cb.or(predicates.toArray(new Predicate[0]));
query.select(order).where(finalPredicate);
List<Order> results = entityManager.createQuery(query).getResultList();

As you can see, jOOQ is more concise than other solution.

Cons

Despite all its advantages, it does have some drawbacks worth mentioning.

Database-Centric

• jOOQ is designed around a database-first approach, which may not be suitable for projects that prefer a code-first approach (e.g., using Hibernate with entity-relationship mapping).

• Although jOOQ does support a code-first approach by integrating with JPA entities, the setup and usage can be quite complex, as referenced :
  https://www.jooq.org/doc/latest/manual/sql-execution/alternative-execution-models/using-jooq-with-jpa/

No Built-in Entity Mapping

• Since jOOQ doesn't follow the entity mapping approach that ORMs like Hibernate and JPA use. This might be a limitation for projects that need to work with object graphs or have intricate relationships between entities.

• Although jOOQ also supports code-first approach by supporting JPA entity, it's complex to step and also adds overhead to the project dependencies: https://www.jooq.org/doc/latest/manual/sql-execution/alternative-execution-models/using-jooq-with-jpa/

Complexity for Simple Queries

• While jOOQ excels at handling complex queries, it can feel like overkill for basic CRUD operations, which are better suited for simpler ORMs like Spring Data JPA.

Potential Vendor Lock-in

• While jOOQ supports multiple databases, the use of vendor-specific SQL can lead to tighter coupling with a particular database, potentially limiting the portability of your code across different database platforms.

Generated Code Overhead

• jOOQ generates Java code based on the database schema, which can result in large amounts of generated code that must be included in the project. Managing and keeping this code in sync with the database adds complexity.

How Conac is using jOOQ

What is Conac

Conac stands for Consolidation Accounting. It's a project in Money Forward designed to help users simplify the process of consolidation accounting, which involves combining the financial statements of a parent company and its subsidiaries into a single unified set of financial statements. The backend tech stack used in Conac includes :

• Language: Kotlin

• Framework: Spring Boot

• SQL-ORM: jOOQ

• Web service protocol: GraphQL

• Non-blocking model: using r2DBC and Kotlin coroutines.

jOOQ in Conac

As you can see, jOOQ has both advantages and drawbacks. When we choose this technology, we can benefit from its advantages, but we also need a process in place to address its drawbacks.

jOOQ is currently utilized in Conac. In this section, we will provide an overview of how we are implementing jOOQ in our project, offering insights and practical examples for those considering using it in their own projects.

One common challenge teams face when using jOOQ is its database-centric approach, which requires the state of the database to be reflected in the jOOQ-generated code.

To address this, we aim to minimize the differences between environments by following certain principles and implementing the process outlined below :

• Daily Deployments: To ensure our development and production environments stay in sync, we use daily deployments to quickly update the databases.

• jOOQ-Generated Code as Part of the Codebase: Rather than generating jOOQ code at compile time, we store the generated code directly in our codebase. This approach serves two key purposes :

  • Code Sharing Across Repositories: Multiple repositories may share the same database (e.g., one for writing and others for reading).

  • Full Control Over jOOQ-Generated Code: This approach simplifies debugging when issues arise.

• Local Database Synchronization: Developers in Conac primarily work in local environments for feature development, including working with the database. Any new DML operations must be tested locally before being deployed to testing environments. Developers are required to commit both Flyway migration scripts and jOOQ-generated code in their pull requests to ensure everything works correctly.

Conclusion
 

This blog has walked you through jOOQ’s features, compared it with other popular libraries, and showcased real-world applications.

I hope you’ve gained valuable insights from this blog and that you’ll consider adopting jOOQ as part of your tech stack for future projects.