Chapter 8: Fetching and Mapping

Fetching result sets and mapping them in the shape and format expected by the client is one of the most important tasks of querying a database. jOOQ excels in this area and provides a comprehensive API for fetching data and mapping it to scalars, arrays, lists, sets, maps, POJO, Java 16 records, JSON, XML, nested collections, and more. As usual, the jOOQ API hides the friction and challenges raised by different database dialects along with the boilerplate code necessary to map the result set to different data structures. In this context, our agenda covers the following topics:

• Simple fetching/mapping
• Fetching one record, a single record, or any record
• Fetching arrays, lists, sets, and maps
• Fetching groups
• Fetching via JDBC ResultSet
• Fetching multiple result sets
• Fetching relationships
• Hooking POJOs
• jOOQ record mapper
• The mighty SQL/JSON and SQL/XML support
• Nested collections via the astonishing MULTISET...

Technical requirements

The code for this chapter can be found on GitHub at https://github.com/PacktPublishing/jOOQ-Masterclass/tree/master/Chapter08.

Simple fetching/mapping

By simple fetching/mapping, we refer to the jOOQ fetching techniques that you learned earlier in this book (for instance, the ubiquitous into() methods) but also to the new jOOQ utility, org.jooq.Records. This utility is available from jOOQ 3.15 onward, and it contains two types of utility methods, as we will discuss next.

Collector methods

The collector methods are named intoFoo(), and their goal is to create a collector (java.util.stream.Collector) for collecting records (org.jooq.Record[N]) into arrays, lists, maps, groups, and more. These collectors can be used in ResultQuery.collect() as any other collector. ResultQuery<R> implements Iterable<R> and comes with convenience methods such as collect() on top of it. Besides the fact that collect() handles resources internally (there is no need to use try-with-resources), you can use it for any collectors such as standard JDK collectors, jOOλ collectors, Records collectors, or your own...

Fetching one record, a single record, or any record

jOOQ has come with three handy methods named fetchOne(), fetchSingle(), and fetchAny(). All three are capable of returning a resulting record, but each of them will do this under certain coordinates. So, let's go through each method in detail.

Using fetchOne()

For instance, the fetchOne() method returns, at most, one resulting record. In other words, if the fetched result set has more than one record, then fetchOne() throws a jOOQ-specific TooManyRowsException exception. But if the result set has no records, then fetchOne() returns null. In this context, fetchOne() can be useful for fetching a record by a primary key, other unique keys, or a predicate that guarantees uniqueness, while you prepare to handle potentially null results. Here is an example of using fetchOne():

EmployeeRecord result = ctx.selectFrom(EMPLOYEE) 
   .where(EMPLOYEE.EMPLOYEE_NUMBER.eq(1370L))
   .fetchOne();

Alternatively...

Fetching arrays, lists, sets, and maps

jOOQ reduces the code that is needed for fetching Result<Record> as an array, list, set, or map down to a simple call of its amazing API.

Fetching arrays

Fetching arrays can be done via a comprehensive set of jOOQ methods, including fetchArray() (along with its flavors), fetchOneArray(), fetchSingleArray(), fetchAnyArray(), fetchArrays(), and intoArray(). For instance, fetching all the DEPARTMENT fields as an array of Record can be done as follows:

Record[] result = ctx.select().from(DEPARTMENT).fetchArray();

In comparison, you can just fetch DEPARTMENT.NAME as a String[] as follows:

String[] result = ctx.select(DEPARTMENT.NAME).from(DEPARTMENT)
   .fetchArray(DEPARTMENT.NAME);
String[] result = ctx.select(DEPARTMENT.NAME).from(DEPARTMENT)
   .collect(intoArray(new String[0]));

Alternatively, fetching all CUSTOMER.FIRST_BUY_DATE fields as an array of the YearMonth type can be done via fetchArray...

Fetching groups

The jOOQ fetching groups feature is similar to fetching maps, except that it allows us to fetch a list of records as the value of each key-value pair. There are over 40 flavors of the fetchGroups(), intoGroups(), and intoResultGroup() methods; therefore, take your time to practice (or, at the very least, read about) each of them.

We can distinguish between the fetchGroups(key) and intoGroups(Function keyMapper) methods that allow us to specify the field(s) representing the key, while the value is inferred from the SELECT result as the Result<Record>/List<Record> and fetchGroups(key, value)/intoGroups(Function keyMapper, Function valueMapper) methods in which we specify the field(s) that represents the key and the value, respectively, which could be Result<Record>, List<POJO>, List<scalar>, and more. The Records.intoGroups() method without any arguments is only useful if you have a two-column ResultQuery, and you want to map the first...

Fetching via JDBC ResultSet

jOOQ is an extremely versatile and transparent tool. For instance, jOOQ acts as a wrapper for JDBC ResultSet but also allows us to access it directly and even provide support to do this smoothly and painlessly. Practically, we can do the following:

• Execute a ResultQuery with jOOQ, but return a JDBC ResultSet (this relies on the fetchResultSet() method).
• Transform the jOOQ Result object into a JDBC ResultSet (this relies on the intoResultSet() method).
• Fetch data from a legacy ResultSet using jOOQ.

All three of these bullets are exemplified in the bundled code. However, here, let's consider the second bullet that starts with the following jOOQ query:

// Result<Record2<String, BigDecimal>>
var result = ctx.select(CUSTOMER.CUSTOMER_NAME,  
   CUSTOMER.CREDIT_LIMIT).from(CUSTOMER).fetch();

We understand that the returned result is a jOOQ-specific Result that was built automatically from the...

Fetching multiple result sets

Some RDBMSs (for instance, SQL Server and MySQL after appending the allowMultiQueries=true property to the JDBC URL) can return multiple result sets. Here is such a jOOQ query for MySQL:

ctx.resultQuery(
  "SELECT * FROM employee LIMIT 10; 
   SELECT * FROM sale LIMIT 5");

To fetch multiple result sets in jOOQ, call fetchMany(). This method returns an object of the org.jooq.Results type, as shown in the following snippet (notice the pluralization to avoid any confusion with org.jooq.Result):

Results results = ctx.resultQuery(
   "SELECT * FROM employee LIMIT 10; 
    SELECT * FROM sale LIMIT 5")
 .fetchMany();    

Next, you can map each result set to its POJO:

List<Employee> employees =results.get(0).into(Employee.class);
List<Sale> sales = results.get(1).into(Sale.class);

Lukas Eder says:

"Perhaps out of scope, but...

Fetching relationships

I'm pretty sure that you're familiar with the one-to-one, one-to-many, and many-to-many relationships. An emblematic mapping of unidirectional one-to-many roughly looks like this:

public class SimpleProductLine implements Serializable {
   ... 
   private List<SimpleProduct> products = new ArrayList<>();  
}
public class SimpleProduct implements Serializable { ... }

Moreover, when SimpleProduct contains a reference to SimpleProductLine, this is considered a bidirectional one-to-many relationship:

public class SimpleProduct implements Serializable {
   ...
   private SimpleProductLine productLine;
}

If we have this POJO model, can we map the corresponding result set to it via the jOOQ API? The answer is definitely yes, and this can be done in several ways. From the fetchInto(), fetchMap(), and fetchGroups() methods that you already saw to the record mappers...

Hooking POJOs

You already know that jOOQ can generate POJOs on our behalf and it can handle user-defined POJOs, too. Moreover, you saw a significant number of mappings of a jOOQ result into POJOs (typically, via fetchInto()); therefore, this is not a brand new topic for you. However, in this section, let's take a step further and really focus on different types of POJOs that are supported by jOOQ.

If all we configure is <pojos>true</pojos> (here, Maven), then jOOQ generates POJOs with private fields, empty constructors, constructors with arguments, getters and setters, and toString(). However, jOOQ can also handle a very simple user-defined POJO such as this one:

public class SimplestCustomer { 
   public String customerName; 
   public String customerPhone; 
}

Here is a query that populates this POJO:

List<SimplestCustomer> result = ctx.select(
   CUSTOMER.CUSTOMER_NAME, CUSTOMER.PHONE.as("customerPhone...

jOOQ record mappers

Sometimes, we need a custom mapping that cannot be achieved via the fetchInto() method, the fetchMap() method, the fetchGroups() method, or the Records utility. A simple approach relies on Iterable.forEach(Consumer), as shown in the following mapping:

ctx.select(EMPLOYEE.FIRST_NAME, 
           EMPLOYEE.LAST_NAME, EMPLOYEE.EMAIL)
   .from(EMPLOYEE)
   .forEach((Record3<String, String, String> record) -> {
      System.out.println("\n\nTo: " 
       + record.getValue(EMPLOYEE.EMAIL));
      System.out.println("From: " 
       + "[e-mail]");
      System.out.println("Body: \n   Dear, "
       + record...

The mighty SQL/JSON and SQL/XML support

Starting with jOOQ 3.14, we have support for mapping a result set to any kind of hierarchical/nested structure that can be shaped via JSON or XML into, practically, almost anything. For instance, if you develop a REST API, you can return JSON/XML data in the exact desired shape without mapping anything to your domain model.

As you probably know, most RDBMSs support SQL/JSON (standard or vendor-specific), and some of them support SQL/XML, too.

Handling SQL/JSON support

In a nutshell, for SQL/JSON, we can talk about the following operators that have a jOOQ implementation in the org.jooq.impl.DSL class:

• JSON_OBJECT (DSL.jsonObject(), DSL.jsonEntry()), JSON_ARRAY (DSL.jsonArray()), and JSON_VALUE (DSL.jsonValue()) to construct JSON data from values
• JSON_ARRAYAGG (DSL.jsonArrayAgg()) and JSON_OBJECTAGG (DSL.jsonObjectAgg()) to aggregate data into nested JSON documents
• JSON_EXISTS (DSL.jsonExists()) to query documents with...

Nested collections via the astonishing MULTISET

The MULTISET value constructor (or MULTISET for short) is a SQL standard future that shapes nested subqueries (except scalar subqueries) into a single nested collection value. jOOQ 3.15+ provides marvelous and glorious support for MULTISET. It's marvelous because despite its tremendous power, it is quite easy (effortless) and intuitive to use via jOOQ, and it is glorious because it can produce any nested collection value of jOOQ Record or DTO (POJO/Java records) in a fully type-safe manner, with 0 reflections, no N+1 risks, no deduplications. This allows the database to perform nesting and to optimize the query execution plan.

Consider the well-known one-to-many relationship between PRODUCTLINE and PRODUCT. We can fetch and map this relationship via jOOQ's <R extends Record> Field<Result<R>> multiset(Select<R> select), in jOOQ before 3.17.x, and Field<Result<R>> multiset(TableLike<...

Lazy fetching

Hibernate JPA guy: So, how do you handle huge result sets in jOOQ?

jOOQ guy (me): jOOQ supports lazy fetching.

Hibernate JPA guy: And how do you manage LazyInitializationException?

jOOQ guy (me): For Hibernate JPA users that have just got here, I'd like to stress this right from the start – don't assume that jOOQ lazy fetching is related to or similar to Hibernate JPA lazy loading. jOOQ doesn't have and doesn't need a Persistence Context and doesn't rely on a Session object and proxy objects. Your code is not prone to any kind of lazy loading exceptions!

Then, what is jOOQ lazy fetching?

Well, most of the time, fetching the entire result set into memory is the best way to exploit your RDBMS (especially in web applications that face high traffic by optimizing small result sets and short transactions). However, there are cases (for instance, you might have a huge result set) when you'll like to fetch and process the result...

Asynchronous fetching

Whenever you consider that you need asynchronous fetching (for instance, a query takes too long to wait for it or multiple queries can run independently of each other (non-atomically)) you can rely on the jOOQ + CompletableFuture combination. For instance, the following asynchronous operation chains an INSERT statement, an UPDATE statement, and a DELETE statement using the CompletableFuture API and the threads obtained from the default ForkJoinPool API (if you are not familiar with this API, then you can consider purchasing the Java Coding Problems book from Packt, which dives deeper into this topic):

@Async
public CompletableFuture<Void> insertUpdateDeleteOrder() {
 return CompletableFuture.supplyAsync(() -> {
  return ctx.insertInto(ORDER)
   .values(null, LocalDate.of(2003, 2, 12), 
    LocalDate.of(2003, 3, 1), LocalDate.of(2003, 2, 27),    
    "Shipped"...

Reactive fetching

Reactive fetching refers to the use of a reactive API in combination with jOOQ. Since you are using Spring Boot, there is a big chance that you are already familiar with the Project Reactor reactive library (https://projectreactor.io/) or the Mono and Flux APIs. So, without going into further detail, let's take an example of combining Flux and jOOQ in a controller:

@GetMapping(value = "/employees", 
            produces = MediaType.TEXT_EVENT_STREAM_VALUE)
public Flux<String> fetchEmployees() {
 return Flux.from(
   ctx.select(EMPLOYEE.FIRST_NAME, EMPLOYEE.LAST_NAME, 
              EMPLOYEE.JOB_TITLE, EMPLOYEE.SALARY)
      .from(EMPLOYEE))
      .map(e -> e.formatHTML())
        .delayElements(Duration...

Summary

This was a big chapter that covered one of the most powerful capabilities of jOOQ, fetching and mapping data. As you learned, jOOQ supports a wide range of approaches for fetching and mapping data, from simple fetching to record mappers, to the fancy SQL/JSON and SQL/XML, to the marvelous and glorious MULTISET support, and finally, to lazy, asynchronous, and reactive fetching. In the next chapter, we will talk about how to batch and bulk data.