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
- Lazy fetching
- Asynchronous fetching
- Reactive fetching
Let's get started!
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 collectors. For instance, here is an example of collecting into List<String>:
List<String> result = ctx.select(CUSTOMER.CUSTOMER_NAME)
.from(CUSTOMER)
.collect(intoList()); // or, Java's Collectors.toList()
And, here is an example of collecting into Map<Long, String>:
Map<Long, String> result = ctx.select(
CUSTOMER.CUSTOMER_NUMBER, CUSTOMER.PHONE)
.from(CUSTOMER)
.collect(intoMap());
Note that, while the ubiquitous into() methods use reflection, these utilities are a pure declarative mapping of jOOQ results/records without using reflection.
Mapping methods
The mapping methods are actually multiple flavors of the mapping (Function[N]) method. A mapping method creates a RecordMapper parameter that can map from Record[N] to another type (for instance, POJO and Java 16 records) in a type-safe way. For instance, you can map to a Java record as follows:
public record PhoneCreditLimit(
String phone, BigDecimal creditLimit) {}List<PhoneCreditLimit> result = ctx.select(
CUSTOMER.PHONE, CUSTOMER.CREDIT_LIMIT)
.from(CUSTOMER)
.fetch(mapping(PhoneCreditLimit::new));
When mapping nested rows (for instance, LEFT JOIN) you can achieve null safety by combining mapping() with Functions.nullOnAllNull(Function1) or Functions.nullOnAnyNull(Function1). Here is an example:
List<SalarySale> result = ctx.select(
EMPLOYEE.SALARY, SALE.SALE_)
.from(EMPLOYEE)
.leftJoin(SALE)
.on(EMPLOYEE.EMPLOYEE_NUMBER.eq(SALE.EMPLOYEE_NUMBER))
.fetch(mapping(nullOnAnyNull(SalarySale::new)));
So, how does this work? For instance, when an employee has no sale (or you have an orphan sale), you'll obtain a null value instead of an instance of SalarySale having the sale as null, SalarySale[salary=120000, sale=null].
Many more examples are available for MySQL/PostgreSQL in the bundle code, Records.
Simple fetching/mapping continues
Next, let's see other techniques of fetching/mapping data that can be used quite intuitively and effortlessly. Since the jOOQ manual is filled to the brim with examples, let's try to niche several things in this section. For instance, a simple fetch can be done via DSLContext.resultQuery() and plain SQL, as follows:
Result<Record> result = ctx.resultQuery(
"SELECT customer_name FROM customer").fetch();
List<String> result = ctx.resultQuery(
"SELECT customer_name FROM customer")
.fetchInto(String.class);
List<String> result = ctx.resultQuery(
"SELECT customer_name FROM customer")
.collect(intoList(r -> r.get(0, String.class)));
Another approach might rely on DSLContext.fetch() and plain SQL, as follows:
Result<Record> result = ctx.fetch(
"SELECT customer_name FROM customer");
List<String> result = ctx.fetch(
"SELECT customer_name FROM customer").into(String.class);
List<String> result = ctx.fetch(
"SELECT customer_name FROM customer")
.collect(intoList(r -> r.get(0, String.class)));
So, the idea is quite simple. Whenever you have to execute a plain SQL that you can't (or don't want to) express via a jOOQ-generated Java-based schema, then simply rely on ResultQuery.collect(collector) or the resultQuery() … fetch()/fetchInto() combination. Alternatively, simply pass it to the fetch() method and call the proper into() method or the intoFoo() method to map the result set to the necessary data structure. There are plenty of such methods that can map a result set to scalars, arrays, lists, sets, maps, POJO, XML, and more.
On the other hand, using the Java-based schema (which is, of course, the recommended way to go) leads to the following less popular but handy query:
List<String> result = ctx.fetchValues(CUSTOMER.CUSTOMER_NAME);
This is a shortcut for fetching a single field and obtaining the mapped result (values) without explicitly calling an into() method or an intoFoo() method. Essentially, jOOQ automatically maps the fetched field to the Java type associated with it when the Java-based schema was generated by the jOOQ generator.
Whenever you need to fetch a single value, you can rely on fetchValue():
Timestamp ts = ctx.fetchValue(currentTimestamp());
The <T> T fetchValue(Field<T> field) and <T> List<T> fetchValues(TableField<?,T> tf) methods are just two of the many flavors of methods that are available. Check out the jOOQ documentation to see the rest of them.
However, since you have made it this far in this book, I'm sure that you think of this query as a shortcut for the following four, more popular, approaches:
List<String> result = ctx.select(CUSTOMER.CUSTOMER_NAME)
.from(CUSTOMER).fetch(CUSTOMER.CUSTOMER_NAME);
List<String> result = ctx.select(CUSTOMER.CUSTOMER_NAME)
.from(CUSTOMER).fetchInto(String.class)
List<String> result = ctx.select(CUSTOMER.CUSTOMER_NAME)
.from(CUSTOMER).collect(intoList());
// or, mapping to Result<Record1<String>>
var result = ctx.select(CUSTOMER.CUSTOMER_NAME)
.from(CUSTOMER).fetch();
And you are right, as long as you don't also think of the following, too:
List<String> result = ctx.select().from(CUSTOMER)
.fetch(CUSTOMER.CUSTOMER_NAME);
List<String> result = ctx.selectFrom(CUSTOMER)
.fetch(CUSTOMER.CUSTOMER_NAME);
All six of these queries project the same result, but they are not the same. As a jOOQ novice, it is understandable that you might a bad choice and go for the last two queries. Therefore, let's clarify this concern by looking at the generated SQLs. The first four queries produce the following SQL:
SELECT `classicmodels`.`customer`.`customer_name`
FROM `classicmodels`.`customer`
In contrast, the last two queries produce the following SQL:
SELECT `classicmodels`.`customer`.`customer_number`,
`classicmodels`.`customer`.`customer_name`,
...
`classicmodels`.`customer`.`first_buy_date`
FROM `classicmodels`.`customer`
Now, it is obvious that the last two queries perform unnecessary work. We only need the CUSTOMER_NAME field, but these queries will fetch all fields, and this is pointless work that negatively impacts performance. In such cases, don't blame jOOQ or the database because both of them did exactly what you asked!
Important Note
As a rule of thumb, when you don't need to fetch all fields, rely on the first four approaches from earlier and enlist the necessary fields in the SELECT statement. In this context, allow me to reiterate the SelectOnlyNeededData application from Chapter 5, Tackling Different Kinds of SELECT, INSERT, UPDATE, DELETE, and MERGE Statements.
When you fetch more than one field, but not all fields, you should write something like this:
// Result<Record2<String, BigDecimal>>
var result = ctx.select(
CUSTOMER.CUSTOMER_NAME, CUSTOMER.CREDIT_LIMIT)
.from(CUSTOMER).fetch();
ExpectedType result = ctx.select(
CUSTOMER.CUSTOMER_NAME, CUSTOMER.CREDIT_LIMIT)
.from(CUSTOMER)
.fetchInto(…) // or, collect(), fetch(mapping(…)), ...
Now, let's consider another simple fetching method based on the following two POJOs:
class NamePhone {String customerName; String phone;}class PhoneCreditLimit {String phone; BigDecimal creditLimit;}Populating these POJOs can be done via two SELECT statements, as follows:
List<NamePhone> result1 = ctx.select(
CUSTOMER.CUSTOMER_NAME, CUSTOMER.PHONE)
.from(CUSTOMER).fetchInto(NamePhone.class);
List<PhoneCreditLimit> result2 = ctx.select(
CUSTOMER.PHONE, CUSTOMER.CREDIT_LIMIT)
.from(CUSTOMER).fetchInto(PhoneCreditLimit.class);
However, here, jOOQ allows us to map Result<Record> into multiple results. In other words, we can obtain the same result and trigger a single SELECT statement, as follows:
// Result<Record3<String, String, BigDecimal>>
var result = ctx.select(CUSTOMER.CUSTOMER_NAME,
CUSTOMER.PHONE, CUSTOMER.CREDIT_LIMIT)
.from(CUSTOMER).fetch();
List<NamePhone> r1=result.into(NamePhone.class);
List<PhoneCreditLimit> r2=result.into(PhoneCreditLimit.class);
Nice! Of course, this doesn't only apply when mapping result sets to POJOs. In the code bundle of this book, SimpleFetch (which is available for MySQL), you can see a result set produced by a single SELECT statement formatted entirely as JSON, while a part of it is mapped to a Java Set. Next, let's dive into the fetchOne(), fetchSingle(), and fetchAny() methods.
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, you can fetch directly into the Employee POJO via fetchOneInto():
Employee result = ctx.selectFrom(EMPLOYEE)
.where(EMPLOYEE.EMPLOYEE_NUMBER.eq(1370L))
.fetchOneInto(Employee.class);
However, pay attention. Remember that fetchOneInto(Employee.class) is not the same thing as fetchOne().into(Employee.class) since the latter is prone to throw NullPointerException exceptions. So, it is better to avoid writing something like this:
Employee result = ctx.selectFrom(EMPLOYEE)
.where(EMPLOYEE.EMPLOYEE_NUMBER.eq(1370L))
.fetchOne().into(Employee.class);
If there is no EMPLOYEE POJO with a primary key of 1370, then this code throws an NPE exception.
Also, avoid chaining the component[N]() and value[N]() methods, as follows (this code is also prone to throw NullPointerException):
String result = ctx.select(EMPLOYEE.EMAIL).from(EMPLOYEE)
.where(EMPLOYEE.EMPLOYEE_NUMBER.eq(1370L))
.fetchOne().value1();
Also, prefer fetching into a proper type (here, it is String):
String result = ctx.select(EMPLOYEE.EMAIL).from(EMPLOYEE)
.where(EMPLOYEE.EMPLOYEE_NUMBER.eq(1370L))
.fetchOneInto(String.class);
Of course, an NPE check is still needed before using result, but you can wrap this check via Objects.requireNonNullElseGet(), as follows:
String result = Objects.requireNonNullElseGet(
ctx.select(EMPLOYEE.EMAIL).from(EMPLOYEE)
.where(EMPLOYEE.EMPLOYEE_NUMBER.eq(1370L))
.fetchOneInto(String.class), () -> "");
Alternatively, simply wrap it into an Optional type via the jOOQ's fetchOptional() method:
Optional<EmployeeRecord> result = ctx.selectFrom(EMPLOYEE)
.where(EMPLOYEE.EMPLOYEE_NUMBER.eq(1370L))
.fetchOptional();
Alternatively, you may prefer fetchOptionalInto():
Optional<Employee> result = ctx.selectFrom(EMPLOYEE)
.where(EMPLOYEE.EMPLOYEE_NUMBER.eq(1370L))
.fetchOptionalInto(Employee.class);
As usual, fetchOne() comes in many flavors, all of which are available in the documentation. For instance, you can use DSLContext.fetchOne()as follows:
EmployeeRecord result = ctx.fetchOne(EMPLOYEE,
EMPLOYEE.EMPLOYEE_NUMBER.eq(1370L));
Or you can fetch a record and convert it based on a user-defined converter (this converter was introduced in Chapter 7, Types, Converters, and Bindings):
YearMonth result = ctx.select(CUSTOMER.FIRST_BUY_DATE)
.from(CUSTOMER)
.where(CUSTOMER.CUSTOMER_NUMBER.eq(112L))
.fetchOne(CUSTOMER.FIRST_BUY_DATE,
INTEGER_YEARMONTH_CONVERTER);
Many other examples are available in the bundled code for MySQL, FetchOneAnySingle.
Using fetchSingle()
The fetchSingle() method returns exactly one resulting record. In other words, if the fetched result set contains more than one record, then fetchSingle() throws the jOOQ-specific TooManyRowsException error. And if it doesn't contain any records, then it throws the jOOQ-specific NoDataFoundException error.
Essentially, fetchSingle() is similar to fetchOne(), except that it throws an exception instead of returning null when the fetched result set doesn't contain any records. This means that fetchSingle() is useful for fetching a record by a primary key, other unique keys, or a predicate that guarantees uniqueness when you are not expecting null results. For example, see the following code block:
Employee result = ctx.selectFrom(EMPLOYEE)
.where(EMPLOYEE.EMPLOYEE_NUMBER.eq(1370L))
.fetchSingleInto(Employee.class);
Or you might only fetch the email of this employee, as follows:
String result = ctx.select(EMPLOYEE.EMAIL).from(EMPLOYEE)
.where(EMPLOYEE.EMPLOYEE_NUMBER.eq(1370L))
.fetchSingle().value1(); // fetchSingleInto(String.class)
Many other examples are available in the bundled code.
Using fetchAny()
The fetchAny() method returns the first resulting record. In other words, if the fetched result set contains more than one record, then fetchAny() returns the first one. And, if it doesn't contain any records, then it returns null. This is similar to …limit(1).fetchOne();. So, pay attention to avoid any usages that are prone to throw a NullPointerException exception. Here's an example:
SaleRecord result = ctx.selectFrom(SALE)
.where(SALE.EMPLOYEE_NUMBER.eq(1370L))
.fetchAny();
Let's see another example:
String result = ctx.select(SALE.TREND).from(SALE)
.where(SALE.EMPLOYEE_NUMBER.eq(1370L))
.fetchAnyInto(String.class);
Many other examples are available for MySQL in FetchOneAnySingle.
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(Field<T> field, Converter<? super T,? extends U> cnvrtr), as follows (note that the INTEGER_YEARMONTH_CONVERTER converter was introduced in Chapter 7, Types, Converters, and Bindings):
YearMonth[] result = ctx.select(CUSTOMER.FIRST_BUY_DATE)
.from(CUSTOMER)
.fetchArray(CUSTOMER.FIRST_BUY_DATE,
INTEGER_YEARMONTH_CONVERTER);
What do you think about fetching a database array into a Java array, such as the DEPARTMENT.TOPIC field that was defined in our PostgreSQL schema? Well, the result, in this case, is String[][]:
String[][] result = ctx.select(DEPARTMENT.TOPIC)
.from(DEPARTMENT).fetchArray(DEPARTMENT.TOPIC);
If we return this String[][] from a Spring Boot REST controller, the result will be a JSON array:
[
["publicity", "promotion"],
["commerce","trade","sellout","transaction"],
...
]
What about fetching a UDT type into a Java array? In our PostgreSQL schema, we have the MANAGER.MANAGER_EVALUATION UDT type, so let's give it a try and fetch it as an array next to the MANAGER_NAME type:
// Record2<String, EvaluationCriteriaRecord>[]
var result = ctx.select(MANAGER.MANAGER_NAME,
MANAGER.MANAGER_EVALUATION)
.from(MANAGER).fetchArray();
Let's print out the first manager name and their evaluation:
System.out.println(result[0].value1()+" "
+ result[0].value2().format());
Here is the output (the format() method formats EvaluationCriteriaRecord as a tabular text):
Figure 8.1– Printing the first manager and their evaluation
Finally, let's try fetching an embeddable type as an array, too:
OfficeFullAddressRecord[] result = ctx.select(
OFFICE.OFFICE_FULL_ADDRESS).from(OFFICE)
.fetchArray(OFFICE.OFFICE_FULL_ADDRESS);
OfficeFullAddressRecord[] result = ctx.select(
OFFICE.OFFICE_FULL_ADDRESS).from(OFFICE)
.collect(intoArray(new OfficeFullAddressRecord[0]));
The last example from this section relies on fetchArrays():
Object[][] result = ctx.select(DEPARTMENT.DEPARTMENT_ID,
DEPARTMENT.OFFICE_CODE, DEPARTMENT.NAME)
.from(DEPARTMENT).fetchArrays();
If we return this Object[][] from a Spring Boot REST controller, then the result will be a JSON array of arrays:
[
[1, "1", "Advertising"],
[2, "1", "Sales"],
[3, "2", "Accounting"],
[4, "3", "Finance"]
]
In the bundled code, you can find over 15 examples of fetching jOOQ results as arrays.
Fetching lists and sets
So far, most examples fetch the result set in java.util.List or org.jooq.Result (that is, the jOOQ wrappers of List), so there is no mystery regarding how the following examples work:
List<String> result = ctx.select(DEPARTMENT.NAME)
.from(DEPARTMENT).fetch(DEPARTMENT.NAME);
List<String> result = ctx.select(DEPARTMENT.NAME)
.from(DEPARTMENT).collect(intoList());
List<Department> result = ctx.select(DEPARTMENT.DEPARTMENT_ID,
DEPARTMENT.OFFICE_CODE, DEPARTMENT.NAME)
.from(DEPARTMENT).fetchInto(Department.class);
So, let's focus on more interesting cases, such as how to fetch the DEPARTMENT.TOPIC array field defined in our PostgreSQL schema:
List<String[]> result = ctx.select(DEPARTMENT.TOPIC)
.from(DEPARTMENT)
.fetch(DEPARTMENT.TOPIC, String[].class);
Instead of calling fetch(), which will return Result<Record1<String[]>>, we prefer to call fetch(Field<?> field, Class<? extends U> type). This allow us to return a List<String[]>.
Trying to fetch DEPARTMENT.TOPIC as a Set<String[]> can be done via the jOOQ fetchSet() method (check out the documentation to see all the flavors of this method):
Set<String[]> result = ctx.select(DEPARTMENT.TOPIC)
.from(DEPARTMENT).fetchSet(DEPARTMENT.TOPIC);
Consider the bundled code, which contains more examples of fetching lists and sets, including fetching UDT and embeddable types.
Fetching maps
jOOQ comes with a set of fetchMap()/intoMap() methods that allow us to split a result set into key-value pairs of a java.util.Map wrapper. There are more than 20 such methods, but we can primarily distinguish between the fetchMap(key)/intoMap(Function keyMapper) methods. These methods allow us to specify the field(s) representing the key, while the value is inferred from the SELECT result, and the fetchMap(key, value)/intoMap(Function keyMapper, Function valueMapper) methods in which we specify the field(s) that represents the key and the value, respectively. The Records.intoMap() method without any arguments is only useful if you have a two-column ResultQuery and you want to map the first column as a key and the second column as a value.
For instance, let's fetch a Map that has DEPARTMENT_ID as the key (so, the DEPARTMENT primary key) and DepartmentRecord as the value:
Map<Integer, DepartmentRecord>
result = ctx.selectFrom(DEPARTMENT)
.fetchMap(DEPARTMENT.DEPARTMENT_ID);
Map<Integer, DepartmentRecord>
result = ctx.selectFrom(DEPARTMENT)
.collect(intoMap(r -> r.get(DEPARTMENT.DEPARTMENT_ID)));
Alternatively, let's instruct jOOQ that the map value should be a Department POJO (generated by jOOQ) instead of DepartmentRecord:
Map<Integer, Department> result = ctx.selectFrom(DEPARTMENT)
.fetchMap(DEPARTMENT.DEPARTMENT_ID, Department.class);
Do you think this is impressive? How about mapping a one-to-one relationship between the CUSTOMER and CUSTOMERDETAIL tables? Here is the magical code:
Map<Customer, Customerdetail> result = ctx.select()
.from(CUSTOMER)
.join(CUSTOMERDETAIL)
.on(CUSTOMER.CUSTOMER_NUMBER
.eq(CUSTOMERDETAIL.CUSTOMER_NUMBER))
.fetchMap(Customer.class, Customerdetail.class);
In order to obtain a correct mapping, you have to provide explicit equals() and hashCode() methods for the involved POJOs.
Simply returning this Map from a REST controller will result in the following JSON code:
{"Customer (99, Australian Home, Paoule, Sart,
40.11.2555, 1370, 21000.00, 20210)":
{"customerNumber": 99, "addressLineFirst": "43 Rue 2",
"addressLineSecond": null, "city": "Paris", "state":
null, "postalCode": "25017", "country": "France"
},
...
Alterantively, you might want to fetch this one-to-one relationship by only using a subset of fields:
Map<Record, Record> result = ctx.select(
CUSTOMER.CONTACT_FIRST_NAME, CUSTOMER.CONTACT_LAST_NAME,
CUSTOMERDETAIL.CITY, CUSTOMERDETAIL.COUNTRY)
.from(CUSTOMER)
.join(CUSTOMERDETAIL)
.on(CUSTOMER.CUSTOMER_NUMBER
.eq(CUSTOMERDETAIL.CUSTOMER_NUMBER))
.fetchMap(new Field[]{CUSTOMER.CONTACT_FIRST_NAME, CUSTOMER.CONTACT_LAST_NAME},
new Field[]{CUSTOMERDETAIL.CITY, CUSTOMERDETAIL.COUNTRY});
In the bundled code, ArrListMap (which is available for PostgreSQL), you can see more examples, including mapping a flattened one-to-many relationship, mapping arrays, UDTs and embeddable types, and using fetchMaps(), fetchSingleMap(), fetchOneMap(), and fetchAnyMap(). Next, let's talk about fetching groups.
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 column as a key and the second column as a value. Additionally, the intoResultGroup() method returns a collector that collects a jOOQ Record, which results from a ResultQuery in a Map using the result of the RecordMapper parameter as a key to collect the records themselves into a jOOQ Result.
For instance, you can fetch all the OrderRecord values and group them by customer (CUSTOMER_NUMBER) as follows:
Map<Long, Result<OrderRecord>> result = ctx.selectFrom(ORDER)
.fetchGroups(ORDER.CUSTOMER_NUMBER);
Map<Long, List<OrderRecord>> result = ctx.selectFrom(ORDER)
.collect(intoGroups(r -> r.get(ORDER.CUSTOMER_NUMBER)));
Or you can group all bank transfers (BANK_TRANSACTION.TRANSFER_AMOUNT) by customer (BANK_TRANSACTION.CUSTOMER_NUMBER) into Map<Long, List<BigDecimal>>:
Map<Long, List<BigDecimal>> result = ctx.select(
BANK_TRANSACTION.CUSTOMER_NUMBER,
BANK_TRANSACTION.TRANSFER_AMOUNT)
.from(BANK_TRANSACTION)
.fetchGroups(BANK_TRANSACTION.CUSTOMER_NUMBER,
BANK_TRANSACTION.TRANSFER_AMOUNT);
Map<Long, List<BigDecimal>> result = ctx.select(
BANK_TRANSACTION.CUSTOMER_NUMBER,
BANK_TRANSACTION.TRANSFER_AMOUNT)
.from(BANK_TRANSACTION)
.collect(intoGroups());
// or, …
.collect(intoGroups(
r -> r.get(BANK_TRANSACTION.CUSTOMER_NUMBER),
r -> r.get(BANK_TRANSACTION.TRANSFER_AMOUNT)));
You can group them into Map<Long, List<Record2<Long, BigDecimal>>> or Map<Long, Result<Record2<Long, BigDecimal>>>, respectively:
Map<Long, List<Record2<Long, BigDecimal>>> result
= ctx.select(BANK_TRANSACTION.CUSTOMER_NUMBER,
BANK_TRANSACTION.TRANSFER_AMOUNT)
.from(BANK_TRANSACTION)
.collect(intoGroups(r ->
r.get(BANK_TRANSACTION.CUSTOMER_NUMBER)));
Map<Long, Result<Record2<Long, BigDecimal>>> result
= ctx.select(BANK_TRANSACTION.CUSTOMER_NUMBER,
BANK_TRANSACTION.TRANSFER_AMOUNT)
.from(BANK_TRANSACTION)
.collect(intoResultGroups(r ->
r.get(BANK_TRANSACTION.CUSTOMER_NUMBER)));
As you've probably intuited already, fetchGroups() is very handy for fetching and mapping one-to-many relationships. For instance, each product line (PRODUCTLINE) has multiple products (PRODUCT), and we can fetch this data as follows:
Map<Productline, List<Product>> result = ctx.select()
.from(PRODUCTLINE)
.innerJoin(PRODUCT)
.on(PRODUCTLINE.PRODUCT_LINE.eq(PRODUCT.PRODUCT_LINE))
.fetchGroups(Productline.class, Product.class);
Returning this map from a REST controller results in the following JSON:
{"Productline (Motorcycles, 599302, Our motorcycles ...)": [
{"productId": 1,
"productName": "1969 Harley Davidson Ultimate Chopper",
...
},
{"productId": 3,
"productName": "1996 Moto Guzzi 1100i",
...
},
...
],
"Productline (Classic Cars, 599302 ... )": [
...
]
}
Of course, relying on user-defined POJOs/Java records is also possible. For instance, let's say you just need the code and name of each product line, along with the product ID and buy price of each product. Having the proper POJOs named SimpleProductline and SimpleProduct, we can map the following one-to-many relationship:
Map<SimpleProductline, List<SimpleProduct>> result =
ctx.select(PRODUCTLINE.PRODUCT_LINE, PRODUCTLINE.CODE,
PRODUCT.PRODUCT_ID, PRODUCT.BUY_PRICE)
.from(PRODUCTLINE)
.innerJoin(PRODUCT)
.on(PRODUCTLINE.PRODUCT_LINE.eq(PRODUCT.PRODUCT_LINE))
.fetchGroups(SimpleProductline.class, SimpleProduct.class);
In order to obtain a correct mapping, you have to provide explicit equals() and hashCode() methods for the involved POJOs. For the jOOQ-generated POJO, this is a configuration step that can be accomplished via <pojosEqualsAndHashCode/>, as follows:
<generate>
<pojosEqualsAndHashCode>true</pojosEqualsAndHashCode>
</generate>
Notice that using fetchGroups() works as expected for INNER JOIN, but not for LEFT JOIN. If the fetched parent doesn't have children, then instead of an empty list, you'll get a list containing a single NULL item. So, if you want to use LEFT JOIN (at least until https://github.com/jOOQ/jOOQ/issues/11888 is resolved), you can rely on the mighty ResultQuery.collect()collector, as follows:
Map<Productline, List<Product>> result = ctx.select()
.from(PRODUCTLINE)
.leftOuterJoin(PRODUCT)
.on(PRODUCTLINE.PRODUCT_LINE.eq(PRODUCT.PRODUCT_LINE))
.collect(groupingBy(
r -> r.into(Productline.class),
filtering(
r -> r.get(PRODUCT.PRODUCT_ID) != null,
mapping(
r -> r.into(Product.class),
toList()
)
)
));
This time, a parent with no children produces an empty list.
Fetching and mapping a many-to-many relationship is also possible. We can do it elegantly via CROSS APPLY (for additional details, check out Chapter 6, Tackling Different Kinds of JOIN Statements). For instance, we have a many-to-many relationship between OFFICE and MANAGER via the OFFICE_HAS_MANAGER junction table, and we can map it via fetchGroups(), as follows:
Map<Manager, List<Office>> result = ctx.select().from(MANAGER)
.crossApply(select().from(OFFICE).join(OFFICE_HAS_MANAGER)
.on(OFFICE.OFFICE_CODE
.eq(OFFICE_HAS_MANAGER.OFFICES_OFFICE_CODE))
.where(MANAGER.MANAGER_ID
.eq(OFFICE_HAS_MANAGER.MANAGERS_MANAGER_ID)))
.fetchGroups(Manager.class, Office.class);
Passing this map through a REST controller produces the necessary JSON. Of course, mapping a one-to-many relationship with a junction table is quite obvious based on the previous examples.
However, please consider Lukas Eder's note:
"When talking about fetchGroups(), I think it's always worth pointing out that RDBMS can often do this natively as well, using ARRAY_AGG(), JSON_ARRAYAGG(), or XMLAGG(). Chances are (to be verified), that this may be faster, as less data has to be transferred over the wire."
In the bundled code, you can practice many more examples of how to use fetchGroups(). The application is named FetchGroups (and is available for PostgreSQL).
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 underlying ResultSet. So, can we reverse this operation and obtain the ResultSet from the jOOQ Result? Yes, we can! We can do this via intoResultSet(), as follows:
ResultSet rsInMem = result.intoResultSet();
The important thing to note is that this magic happens without an active connection to the database. By default, jOOQ closes the database connection after the jOOQ Result is fetched. This means that, when we call intoResultSet() to obtain this in-memory ResultSet, there is no active connection to the database. jOOQ mirrors the Result object back into a ResultSet without interacting with the database. Next, processing this ResultSet is straightforward:
while (rsInMem.next()) {...
}
This matters because, typically, operating on a JDBC ResultSet can be done as long as you hold an open connection to your database. Check out the complete code next to the other two bullets in the bundled application named ResultSetFetch (which is available for MySQL).
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 the Results type also allows for accessing interleaved update counts and exceptions, which is something that is done frequently in T-SQL databases, like SQL Server or Sybase."
Done! In the FetchMany application (which is available for MySQL and SQL Server), you can check out this example next to another one that returns two result sets from a query that combines DELETE and SELECT.
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, the mighty SQL JSON/XML mapping, and the astonishing MULTISET value constructor operator, jOOQ provides so many fetching modes that it is almost impossible to not find a solution.
Anyway, let's not deviate too much from the subject. Let's consider the following query:
// Map<Record, Result<Record>>
var map = ctx.select(PRODUCTLINE.PRODUCT_LINE,
PRODUCTLINE.TEXT_DESCRIPTION,PRODUCT.PRODUCT_NAME,
PRODUCT.PRODUCT_VENDOR, PRODUCT.QUANTITY_IN_STOCK)
.from(PRODUCTLINE)
.join(PRODUCT)
.on(PRODUCTLINE.PRODUCT_LINE
.eq(PRODUCT.PRODUCT_LINE))
.orderBy(PRODUCTLINE.PRODUCT_LINE).limit(3)
.fetchGroups(new Field[]{PRODUCTLINE.PRODUCT_LINE,PRODUCTLINE.TEXT_DESCRIPTION},
new Field[]{PRODUCT.PRODUCT_NAME, PRODUCT.PRODUCT_VENDOR, PRODUCT.QUANTITY_IN_STOCK});
With Map<Record, Result<Record>> (which, most of the time, is all you need), we can populate our bidirectional domain model, as follows:
List<SimpleProductLine> result = map.entrySet()
.stream()
.map((e) -> {SimpleProductLine productLine
= e.getKey().into(SimpleProductLine.class);
List<SimpleProduct> products
= e.getValue().into(SimpleProduct.class);
productLine.setProducts(products);
products.forEach(p ->
((SimpleProduct) p).setProductLine(productLine));
return productLine;
}).collect(Collectors.toList());
If you want to avoid passing through fetchGroups(), then you can rely on ResultQuery.collect() and Collectors.groupingBy(). This is especially useful if you want to run a LEFT JOIN statement since fetchGroups() has the following issue: https://github.com/jOOQ/jOOQ/issues/11888. Another approach is to map from ResultSet. You can see these approaches along with other approaches for unidirectional/bidirectional one-to-one and many-to-many relationships in the bundled code in the OneToOne, OneToMany, and ManyToMany applications (which are available for MySQL).
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")).from(CUSTOMER).fetchInto(SimplestCustomer.class);
Pay attention to the as("customerPhone") alias. This is needed to map CUSTOMER.PHONE to POJO's customerPhone field; otherwise, this POJO field will be left null since jOOQ cannot find the proper match. Another approach is to add a constructor with arguments, as shown in the following POJO:
public class SimpleDepartment {private String depName;
private Short depCode;
private String[] depTopic;
public SimpleDepartment(String depName,
Short depCode, String[] depTopic) {this.depName = depName;
this.depCode = depCode;
this.depTopic = depTopic;
}
...
}
Even if none of the POJO's field names match the names of the fetched fields, the POJO is correctly populated by jOOQ based on this constructor with arguments:
List<SimpleDepartment> result = ctx.select(
DEPARTMENT.NAME, DEPARTMENT.CODE, DEPARTMENT.TOPIC)
.from(DEPARTMENT).fetchInto(SimpleDepartment.class);
List<SimpleDepartment> result = ctx.select(
DEPARTMENT.NAME, DEPARTMENT.CODE, DEPARTMENT.TOPIC)
.from(DEPARTMENT)
.fetch(mapping(SimpleDepartment::new));
User-defined POJOs are useful for mapping jOOQ results that contain fields from multiple tables. For example, a POJO can be used to flatten a one-to-many relationship, as shown here:
public class FlatProductline {private String productLine;
private Long code;
private String productName;
private String productVendor;
private Integer quantityInStock;
// constructors, getters, setters, toString()
}
And, here's the jOOQ query:
List<FlatProductline> result = ctx.select(
PRODUCTLINE.PRODUCT_LINE, PRODUCTLINE.CODE,
PRODUCT.PRODUCT_NAME, PRODUCT.PRODUCT_VENDOR,
PRODUCT.QUANTITY_IN_STOCK)
.from(PRODUCTLINE)
.join(PRODUCT)
.on(PRODUCTLINE.PRODUCT_LINE.eq(PRODUCT.PRODUCT_LINE))
.fetchInto(FlatProductline.class);
// .fetch(mapping(FlatProductline::new));
Alternatively, you can map UDTs and/or embeddable types. For instance, here is a user-defined POJO that fetches a String and an embeddable type containing a UDT. For the embeddable type, we relied on the jOOQ-generated POJO:
import jooq.generated.embeddables.pojos.ManagerStatus;
public class SimpleManagerStatus { private Long managerId;
private ManagerStatus ms;
// constructors, getters, setters, toString()
}
And, the jOOQ query is as follows:
List<SimpleManagerStatus> result =
ctx.select(MANAGER.MANAGER_ID, MANAGER.MANAGER_STATUS)
.from(MANAGER).fetchInto(SimpleManagerStatus.class);
More examples are available in the bundled code (that is, in the PojoTypes application, which is available for PostgreSQL). Next, let's talk about the different types of POJOs supported by jOOQ.
Types of POJOs
Besides the typical POJOs from the previous section, jOOQ also supports several other types of POJOs. For instance, it supports immutable POJOs.
Immutable POJOs
A user-defined immutable POJO can be written as follows:
public final class ImmutableCustomer {private final String customerName;
private final YearMonth ym;
// constructor and only getters
}
And a jOOQ query that maps to this POJO is shown next:
List<ImmutableCustomer> result = ctx.select(
CUSTOMER.CUSTOMER_NAME,
CUSTOMER.FIRST_BUY_DATE.coerce(YEARMONTH))
.from(CUSTOMER)
.fetchInto(ImmutableCustomer.class);
// .fetch(mapping(ImmutableCustomer::new));
To work as expected, immutable POJOs require an exact match between the fetched fields and the POJO's fields (the constructor arguments). However, you can explicitly relax this match via @ConstructorProperties (java.beans.ConstructorProperties). Please check the bundled code (Example 2.2) for a meaningful example.
jOOQ can generate immutable POJOs on our behalf via the following configuration in the <generate/> tag:
<immutablePojos>true</immutablePojos>
Also, it can generate @ConstructorProperties via the following:
<constructorPropertiesAnnotationOnPojos>
true
</constructorPropertiesAnnotationOnPojos>
In the bundled code, next to the other examples, you can also practice mapping UDTs and embeddable types via user-defined immutable POJOs.
POJOs decorated with @Column (jakarta.persistence.Column)
jOOQ can map a Result object to a POJO as follows:
public class JpaCustomer {@Column(name = "customer_name")
public String cn;
@Column(name = "first_buy_date")
public YearMonth ym;
}
As you can see, jOOQ recognizes the @Column annotation and uses it as the primary source for mapping metainformation:
List<JpaCustomer> result = ctx.select(CUSTOMER.CUSTOMER_NAME,
CUSTOMER.FIRST_BUY_DATE.coerce(YEARMONTH))
.from(CUSTOMER).fetchInto(JpaCustomer.class);
jOOQ can generate such POJOs via the following configuration in <generate/>:
<jpaAnnotations>true</jpaAnnotations>
Check out more examples in the bundled code.
JDK 16 records
Consider the following JDK 16 record:
public record RecordDepartment(
String name, Integer code, String[] topic) {}And the jOOQ query is as follows:
List<RecordDepartment> result = ctx.select(
DEPARTMENT.NAME, DEPARTMENT.CODE, DEPARTMENT.TOPIC)
.from(DEPARTMENT)
.fetchInto(RecordDepartment.class);
// .fetch(mapping(RecordDepartment::new));
Alternatively, here is a user-defined JDK 16 record along with a UDT type:
public record RecordEvaluationCriteria(
Integer communicationAbility, Integer ethics,
Integer performance, Integer employeeInput) {}public record RecordManager(
String managerName, RecordEvaluationCriteria rec) {}And the jOOQ query is as follows:
List<RecordManager> result = ctx.select(
MANAGER.MANAGER_NAME, MANAGER.MANAGER_EVALUATION)
.from(MANAGER).fetchInto(RecordManager.class);
Or you can use a user-defined JDK 16 record with an embeddable type (here, we are using the POJO generated by jOOQ for the embeddable type):
import jooq.generated.embeddables.pojos.OfficeFullAddress;
public record RecordOffice(
String officecode, OfficeFullAddress ofa) {}List<RecordOffice> result = ctx.select(
OFFICE.OFFICE_CODE, OFFICE.OFFICE_FULL_ADDRESS)
.from(OFFICE).fetchInto(RecordOffice.class);
jOOQ can generate JDK 16 records on our behalf via the following configuration in <generate/>:
<pojosAsJavaRecordClasses>true</pojosAsJavaRecordClasses>
In the bundled code, you can practice JDK 16 records for UDT, embeddable types, and more.
Interfaces and abstract classes
Finally, jOOQ can map a result into interfaces (abstract classes) known as "proxyable" types. You can find examples in the bundled code and in the jOOQ manual at https://www.jooq.org/doc/latest/manual/sql-execution/fetching/pojos/.
Moreover, jOOQ can generate interfaces on our behalf via this configuration in the <generate/> tag:
<interfaces>true</interfaces>
If POJOs are also generated, then they will implement these interfaces.
Useful configurations for POJOs
Among POJO's configurations, we can ask jOOQ to not generate the toString() method for the POJO via the <pojosToString/> flag, to not generate serializable POJOs (to not implement Serializable) via the <serializablePojos/> flag, and to generate fluent setters via the <fluentSetters/> flag. Moreover, besides POJOs for Java, we can ask jOOQ to generate POJOs for Kotlin via the <pojosAsKotlinDataClasses/> flag or for Scala via the <pojosAsScalaCaseClasses/> flag.
In addition, under the <database/> tag, we can force LocalDateTime into POJOs via <dateAsTimestamp/> and use unsigned types via <unsignedTypes/>.
The complete code is named PojoTypes (which is available for PostgreSQL (Maven/Gradle)). In the next section, let's talk about record mappers.
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("
To: " + record.getValue(EMPLOYEE.EMAIL));
System.out.println("From: " + "[email protected]");
System.out.println("Body:
Dear, "+ record.getValue(EMPLOYEE.FIRST_NAME)
+ " " + record.getValue(EMPLOYEE.LAST_NAME) + " ...");
});
You can check out this example for MySQL in ForEachConsumer.
However, especially for such cases, jOOQ provides a functional interface that allows us to express the custom mappings of a jOOQ result. In this context, we have org.jooq.RecordMapper, which returns the result produced after applying a custom mapping to the jOOQ result. For instance, let's consider a legacy POJO that was implemented via the Builder pattern and is named LegacyCustomer:
public final class LegacyCustomer {private final String customerName;
private final String customerPhone;
private final BigDecimal creditLimit;
…
public static CustomerBuilder getBuilder(
String customerName) {return new LegacyCustomer.CustomerBuilder(customerName);
}
public static final class CustomerBuilder {…
public LegacyCustomer build() {return new LegacyCustomer(this);
}
}
…
}
Mapping a jOOQ result into LegacyCustomer can be done via a RecordMapper parameter, as follows:
List<LegacyCustomer> result
= ctx.select(CUSTOMER.CUSTOMER_NAME, CUSTOMER.PHONE,
CUSTOMER.CREDIT_LIMIT)
.from(CUSTOMER)
.fetch((Record3<String, String, BigDecimal> record) -> {LegacyCustomer customer = LegacyCustomer.getBuilder(
record.getValue(CUSTOMER.CUSTOMER_NAME))
.customerPhone(record.getValue(CUSTOMER.PHONE))
.creditLimit(record.getValue(CUSTOMER.CREDIT_LIMIT))
.build();
return customer;
});
This example is available in the bundled code, RecordMapper (which is available for PostgreSQL), next to other examples such as using a RecordMapper parameter to map a jOOQ result into a max-heap. Moreover, in Chapter 18, jOOQ SPI (Providers and Listeners), you'll see how to configure record mappers via RecordMapperProvider so that jOOQ will automatically pick them up.
However, if you need more generic mapping algorithms, then we have to check out some third-party libraries that work with jOOQ. In the top three such libraries, we have ModelMapper, SimpleFlatMapper, and Orika Mapper.
It is beyond the scope of this book to deep dive into all these libraries. Therefore, I decided to go with the SimpleFlatMapper library (https://simpleflatmapper.org/). Let's assume the following one-to-many mapping:
public class SimpleProductLine {private String productLine;
private String textDescription;
private List<SimpleProduct> products;
…
}
public class SimpleProduct {private String productName;
private String productVendor;
private Short quantityInStock;
…
}
Essentially, SimpleFlatMapper can map a jOOQ result via SelectQueryMapper, as shown in the following self-explanatory example:
private final SelectQueryMapper<SimpleProductLine> sqMapper;
private final DSLContext ctx;
public ClassicModelsRepository(DSLContext ctx) {this.ctx = ctx;
this.sqMapper = SelectQueryMapperFactory
.newInstance().newMapper(SimpleProductLine.class);
}
public List<SimpleProductLine> findProductLineWithProducts() {List<SimpleProductLine> result = sqMapper.asList(
ctx.select(PRODUCTLINE.PRODUCT_LINE,
PRODUCTLINE.TEXT_DESCRIPTION,
PRODUCT.PRODUCT_NAME, PRODUCT.PRODUCT_VENDOR,
PRODUCT.QUANTITY_IN_STOCK)
.from(PRODUCTLINE)
.innerJoin(PRODUCT)
.on(PRODUCTLINE.PRODUCT_LINE
.eq(PRODUCT.PRODUCT_LINE))
.orderBy(PRODUCTLINE.PRODUCT_LINE));
return result;
}
In this code, SimpleFlatMapper maps the jOOQ result, so it acts directly on the jOOQ records. This code is available in the SFMOneToManySQM application (available for MySQL). However, as you can see in the SFMOneToManyJM application, this library can also take advantage of the fact that jOOQ allows us to manipulate the ResultSet object itself, so it can act directly on the ResultSet object via an API named JdbcMapper. This way, SimpleFlatMapper bypasses the jOOQ mapping to Record.
Moreover, the bundled code includes applications for mapping one-to-one and many-to-many relationships next to SFMOneToManyTupleJM, which is an application that combines SimpleFlatMapper and the jOOL Tuple2 API to map a one-to-many relationship without using POJOs. For brevity, we cannot list this code in the book, so you need to reserve some time to explore it by yourself.
From another perspective, via the same SelectQueryMapper and JdbcMapper APIs, the SimpleFlatMapper library can co-work with jOOQ to map chained and/or nested JOIN statements. For instance, consider this model:
public class SimpleEmployee {private Long employeeNumber;
private String firstName;
private String lastName;
private Set<SimpleCustomer> customers;
private Set<SimpleSale> sales;
...
}
public class SimpleCustomer { private String customerName; … }public class SimpleSale { private Float sale; … }Using the SimpleFlatMapper and jOOQ combination, we can populate this model as follows:
this.sqMapper = …;
List<SimpleEmployee> result = sqMapper.asList(
ctx.select(EMPLOYEE.EMPLOYEE_NUMBER, EMPLOYEE.FIRST_NAME,
EMPLOYEE.LAST_NAME, CUSTOMER.CUSTOMER_NAME, SALE.SALE_)
.from(EMPLOYEE)
.leftOuterJoin(CUSTOMER)
.on(CUSTOMER.SALES_REP_EMPLOYEE_NUMBER
.eq(EMPLOYEE.EMPLOYEE_NUMBER))
.leftOuterJoin(SALE)
.on(EMPLOYEE.EMPLOYEE_NUMBER
.eq(SALE.EMPLOYEE_NUMBER))
.where(EMPLOYEE.OFFICE_CODE.eq(officeCode))
.orderBy(EMPLOYEE.EMPLOYEE_NUMBER));
The complete code is named SFMMultipleJoinsSQM. The version of this code that uses JdbcMapper is named SFMMultipleJoinsJM. Moreover, in the bundled code, you can find an example of mapping a deep hierarchical JOIN of type (EMPLOYEE has CUSTOMER has ORDER has ORDERDETAIL has PRODUCT). This JOIN is also mapped in SFMMultipleJoinsInnerLevelsTupleJM using jOOL Tuple2 and no POJOs. Anyway, even if such things work, I don't recommend you to do it in real applications. You better rely on the SQL/JSON/XML operators or MULTISET, as you'll do later.
Again, for brevity, we cannot list this code in the book, so you need to reserve some time to explore it by yourself. At this point, we have reached the climax of this chapter. It's time to beat the drums because the next section covers the outstanding mapping support of jOOQ SQL/JSON and SQL/XML.
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 the JSON path
- JSON_TABLE (DSL.jsonTable()) to transform JSON values into SQL tables
- SQL Server's FOR JSON syntax (including ROOT, PATH, AUTO, and WITHOUT_ARRAY_WRAPPER); the jOOQ commercial edition emulates the FOR JSON syntax for the databases that don't support it (in this book, you can see this for SQL Server and Oracle)
Let's see some introductory examples of these operators via the jOOQ DSL API.
Constructing and aggregating JSON data from the values
Constructing JSON data from values can be done via the JSON_OBJECT operator. This is implemented in jOOQ via different flavors of the DSL.jsonObject() method. For instance, you can map the CUSTOMER.CUSTOMER_NAME and CUSTOMER.CREDIT_LIMIT fields to an org.jooq.JSON object as follows:
Result<Record1<JSON>> result = ctx.select(jsonObject(
key("customerName").value(CUSTOMER.CUSTOMER_NAME), key("creditLimit").value(CUSTOMER.CREDIT_LIMIT)) .as("json_result")).from(CUSTOMER).fetch();
In contrast to the key().value() construction, we can use jsonObject(JSON Entry<?>... entries), as follows:
Result<Record1<JSON>> result = ctx.select(jsonObject(
jsonEntry("customerName", CUSTOMER.CUSTOMER_NAME), jsonEntry("creditLimit", CUSTOMER.CREDIT_LIMIT)) .as("json_result")).from(CUSTOMER).fetch();
The returned Result object (remember that this is a wrapper of java.util.List) has a size equal to the number of fetched customers. Each Record1 object wraps an org.jooq.JSON instance representing a customer name and credit limit. If we just want to format this Result object as a JSON, we can call the formatJSON() method (this will be presented in the next chapter). This will return a simple formatted representation such as the one here:
System.out.println(result.formatJSON());
{ "fields": [{"name": "json_result", "type": "JSON"}],"records":
[
[{"creditLimit": 21000, "customerName": "Australian Home"}], [{"creditLimit": 21000, "customerName": "Joliyon"}], [{"creditLimit": 21000, "customerName": "Marquez Xioa"}]…
]
}
However, this response is too verbose to send to the client. For instance, you might only need the "records" key. In such cases, we can rely on the formatJSON(JSONFormat) flavor as follows:
System.out.println(
result.formatJSON(JSONFormat.DEFAULT_FOR_RECORDS));
[
[{"creditLimit": 50000.00, "customerName":"GOLD"}], [{"creditLimit": null, "customerName": "Australian Home"}], [{"creditLimit": null, "customerName": "Joliyon"}],...
]
Supposing that you just want to send the first JSON array, you can extract it from the Result object as result.get(0).value1().data():
result.get(0) // 0-first JSON, 1-second JSON, 2-third JSON …
.value1() // this is the value from Record1, a JSON
.data() // this is the data of the first JSON as String
{"creditLimit": 21000.00, "customerName": "Australian Home"}However, perhaps you are planning to send all these JSONs as a List<String> to the client. Then, rely on fetchInto(String.class), which will return all of the JSONs as a List<String>. Note that each String is a JSON:
List<String> result = ctx.select(jsonObject(
jsonEntry("customerName", CUSTOMER.CUSTOMER_NAME), jsonEntry("creditLimit", CUSTOMER.CREDIT_LIMIT)) .as("json_result")).from(CUSTOMER).fetchInto(String.class);
Also, you can send the response as a list of JSON arrays. Just wrap each JSON object into an array via jsonArray(), as follows:
List<String> result = ctx.select(jsonArray(jsonObject(
jsonEntry("customerName", CUSTOMER.CUSTOMER_NAME), jsonEntry("creditLimit", CUSTOMER.CREDIT_LIMIT))) .as("json_result")).from(CUSTOMER).fetchInto(String.class);
This time, the first JSON array (at index 0 in the list) is [{"creditLimit": 21000.00, "customerName": "Australian Home"}], the second one (at index 1 in the list) is [{"creditLimit": 21000, "customerName": "Joliyon"}], and so on.
However, it is more practical to aggregate all of these JSONs into a single array. This is possible via jsonArrayAgg(), which will return a single JSON array containing all of the fetched data:
String result = ctx.select(jsonArrayAgg(jsonObject(
jsonEntry("customerName", CUSTOMER.CUSTOMER_NAME), jsonEntry("creditLimit", CUSTOMER.CREDIT_LIMIT))) .as("json_result")).from(CUSTOMER).fetchSingleInto(String.class);
The aggregated JSON array is given here:
[
{"creditLimit": 21000,"customerName": "Australian Home"}, {"creditLimit": 21000,"customerName": "Joliyon"},...
]
However, we can also aggregate the fetched data as a single JSON object that has CUSTOMER_NAME as the key and CREDIT_LIMIT as the value. This can be done via the jsonObjectAgg() method, as follows:
String result = ctx.select(jsonObjectAgg(
CUSTOMER.CUSTOMER_NAME, CUSTOMER.CREDIT_LIMIT)
.as("json_result")).from(CUSTOMER).fetchSingleInto(String.class);
This time, the resulting JSON is as follows:
{"Joliyon": 21000,
"Falafel 3": 21000,
"Petit Auto": 79900,
…
}
If you are a SQL Server fan, then you know that fetching data as JSON can be done via the non-standard FOR JSON syntax. jOOQ supports this syntax via the forJson() API. It also supports clauses such as ROOT via root(), PATH via path(), AUTO via auto(), and WITHOUT_ARRAY_WRAPPER via withoutArrayWrapper(). Here is an example that produces nested results by using dot-separated column names via PATH:
Result<Record1<JSON>> result = ctx.select(
CUSTOMER.CONTACT_FIRST_NAME, CUSTOMER.CREDIT_LIMIT,
PAYMENT.INVOICE_AMOUNT.as("Payment.Amount"), PAYMENT.CACHING_DATE.as("Payment.CachingDate")).from(CUSTOMER)
.join(PAYMENT)
.on(CUSTOMER.CUSTOMER_NUMBER.eq(PAYMENT.CUSTOMER_NUMBER))
.orderBy(CUSTOMER.CREDIT_LIMIT).limit(5)
.forJSON().path().root("customers").fetch();
And here is an example of using AUTO, which automatically produces the output based on the structure of the SELECT statement:
Result<Record1<JSON>> result = ctx.select(
CUSTOMER.CONTACT_FIRST_NAME, CUSTOMER.CREDIT_LIMIT,
PAYMENT.INVOICE_AMOUNT, PAYMENT.CACHING_DATE)
.from(CUSTOMER)
.join(PAYMENT)
.on(CUSTOMER.CUSTOMER_NUMBER.eq(PAYMENT.CUSTOMER_NUMBER))
.orderBy(CUSTOMER.CREDIT_LIMIT).limit(5)
.forJSON().auto().withoutArrayWrapper().fetch();
You can check out these examples in the bundled code for SimpleJson and get familiar with the produced JSONs. For now, let's talk about ordering and limiting the content of the resulting JSON when using SQL-standard JSON operators (for SQL Server's FOR JSON syntax, consider the previous two examples).
Using ORDER BY and LIMIT
When we don't use aggregation operators, ordering and limiting are quite similar to regular queries. For instance, you can order by CUSTOMER_NAME and limit the result to three JSONs as follows:
List<String> result = ctx.select(jsonObject(
key("customerName").value(CUSTOMER.CUSTOMER_NAME), key("creditLimit").value(CUSTOMER.CREDIT_LIMIT)) .as("json_result")).from(CUSTOMER)
.orderBy(CUSTOMER.CUSTOMER_NAME).limit(3)
.fetchInto(String.class);
On the other hand, when the aggregation operators (jsonArrayAgg() and jsonObjectAgg()) are involved, limiting should be done before the aggregation (for instance, in a subquery, JOIN, and more). Otherwise, this operation will be applied to the resulted aggregation itself, not to the aggregated data. During aggregation, ordering can be done before limiting, respectively. For instance, in the following example, the subquery orders the customers by CUSTOMER_NAME and limits the returned result to 3, while the aggregation orders this result by CREDIT_LIMIT:
String result = ctx.select(jsonArrayAgg(jsonObject(
jsonEntry("customerName", field("customer_name")), jsonEntry("creditLimit", field("credit_limit")))) .orderBy(field("credit_limit")).as("json_result")).from(select(CUSTOMER.CUSTOMER_NAME, CUSTOMER.CREDIT_LIMIT)
.from(CUSTOMER).orderBy(CUSTOMER.CUSTOMER_NAME).limit(3))
.fetchSingleInto(String.class);
The resulting aggregation is ordered by CREDIT_LIMIT:
[
{"creditLimit": 0,"customerName": "American Souvenirs Inc"}, {"creditLimit": 61100,"customerName": "Alpha Cognac"}, {"creditLimit": 113000,"customerName": "Amica Models & Co."}]
More examples are available in the bundled code for SimpleJson. Note that, in the applications that uses PostgreSQL and Oracle, you can see the SQL standard's NULL ON NULL (nonOnNull()) and ABSENT ON NULL (absentOnNull()) syntax at work. For now, let's query documents with the JSON path.
Querying JSON documents with the JSON path
Via JSON_EXISTS and JSON_VALUE, we can query and construct JSON documents that rely on the JSON path. In order to practice jOOQ's jsonExists() and jsonValue() queries, let's consider the MANAGER.MANAGER_DETAIL field, which stores data in JSON format. Please take a quick look at this JSON so that you can become familiar with its structure and content.
Now, selecting the MANAGER.MANAGER_ID and MANAGER.MANAGER_NAME fields of the managers that are also shareholders(with the "shareholder" key in JSON) can be done via jsonExists() and the JSON path, as follows:
Result<Record2<Long, String>> result = ctx.select(
MANAGER.MANAGER_ID, MANAGER.MANAGER_NAME)
.from(MANAGER)
.where(jsonExists(MANAGER.MANAGER_DETAIL, "$.shareholder"))
.fetch();
If the fetched JSON is constructed from JSON values, then we should rely on jsonValue() and the JSON path. For instance, fetching the cities of all managers can be done like this:
Result<Record1<JSON>> result = ctx.select(
jsonValue(MANAGER.MANAGER_DETAIL, "$.address.city")
.as("city")).from(MANAGER).fetch();
Combining jsonExists() and jsonValue() allows us to query and construct JSON results from JSON documents. For instance, in PostgreSQL and Oracle, we can select the emails of the managers that had the role of Principal Manager by exploiting the JSON path:
Result<Record1<JSON>> result = ctx.select(
jsonValue(MANAGER.MANAGER_DETAIL, "$.email").as("email")).from(MANAGER)
.where(jsonExists(MANAGER.MANAGER_DETAIL,
"$[*] ? (@.projects[*].role == "Principal Manager")"))
.fetch();
More examples are available in the bundled code, SimpleJson. Next, let's tackle JSON_TABLE.
Transforming JSON values into SQL tables
Transforming JSON values into SQL tables can be done via the JSON_TABLE operator, which, in jOOQ, is equivalent to the jsonTable() method. For instance, let's build a SQL table containing all projects of the development type via the jsonTable(Field<JSON> json, Field<String> path) flavor:
Result<Record> result = ctx.select(table("t").asterisk()).from(MANAGER, jsonTable(MANAGER.MANAGER_DETAIL,
val("$.projects[*]")) .column("id").forOrdinality() .column("name", VARCHAR).column("start", DATE) .column("end", DATE).column("type", VARCHAR) .column("role", VARCHAR).column("details", VARCHAR).as("t")) .where(field("type").eq("development")).fetch();This query will produce a table, as follows:
Figure 8.2 – The result of the previous query
Once you fetch a SQL table, you can think and act on it in the same way as any other database table. For brevity, I simply used VARCHAR, but it is better to specify a size in order to avoid defaulting to VARCHAR(max).
More examples, including how to use JSON_TABLE with aggregates, ORDER BY, LIMIT, and how to transform back into JSON from a SQL table, are available in the bundled code, SimpleJson.
Handling relationships via SQL/JSON
The well-known one-to-one, one-to-many, and many-to-many relationships can be easily shaped via SQL/JSON support.
Mapping relationships to JSON
So, if by any chance you had a feeling that there is a shortcoming in jOOQ regarding mapping relationships, then you'll be very happy to see that a one-to-many relationship can be easily fetched directly into JSON as follows (in this case, we're looking at the relationship between PRODUCTLINE and PRODUCT):
Result<Record1<JSON>> result = ctx.select(jsonObject(
key("productLine").value(PRODUCTLINE.PRODUCT_LINE), key("textDescription").value(PRODUCTLINE.TEXT_DESCRIPTION), key("products").value(select(jsonArrayAgg( jsonObject(key("productName").value(PRODUCT.PRODUCT_NAME), key("productVendor").value(PRODUCT.PRODUCT_VENDOR), key("quantityInStock").value(PRODUCT.QUANTITY_IN_STOCK))) .orderBy(PRODUCT.QUANTITY_IN_STOCK))
.from(PRODUCT)
.where(PRODUCTLINE.PRODUCT_LINE
.eq(PRODUCT.PRODUCT_LINE)))))
.from(PRODUCTLINE).orderBy(PRODUCTLINE.PRODUCT_LINE)
.fetch();
As you can infer from the preceding code, expressing the one-to-one and many-to-many relationships is just a matter of juggling with the SQL/JSON operators. You can find these examples, including how to use JOIN instead of a SELECT subquery, in the bundled code for JsonRelationships.
If you think that Result<Record1<JSON>> is not ready to be sent to the client (for instance, via a REST controller), then decorate it a little bit more by aggregating all the product lines under a JSON array and relying on fetchSingleInto():
String result = ctx.select(
jsonArrayAgg(jsonObject(…))
…
.orderBy(PRODUCTLINE.PRODUCT_LINE))
.from(PRODUCTLINE).fetchSingleInto(String.class);
In SQL Server, we can obtain a similar result via forJson():
Result<Record1<JSON>> result = ctx.select(
PRODUCTLINE.PRODUCT_LINE.as("productLine"), PRODUCTLINE.TEXT_DESCRIPTION.as("textDescription"), select(PRODUCT.PRODUCT_NAME.as("productName"), PRODUCT.PRODUCT_VENDOR.as("productVendor"), PRODUCT.QUANTITY_IN_STOCK.as("quantityInStock")).from(PRODUCT)
.where(PRODUCT.PRODUCT_LINE.eq(PRODUCTLINE.PRODUCT_LINE))
.orderBy(PRODUCT.QUANTITY_IN_STOCK)
.forJSON().path().asField("products")).from(PRODUCTLINE)
.orderBy(PRODUCTLINE.PRODUCT_LINE).forJSON().path().fetch();
Or we can obtain a String via formatJSON(JSONformat):
String result = ctx.select(
PRODUCTLINE.PRODUCT_LINE.as("productLine"),...
.forJSON().path()
.fetch()
.formatJSON(JSONFormat.DEFAULT_FOR_RECORDS);
Both examples will produce a JSON, as follows (as you can see, altering the default JSON keys inferred from the field names can be done with aliases via as("alias")):
[
{"productLine": "Classic Cars",
"textDescription": "Attention car enthusiasts...",
"products": [
{"productName": "1968 Ford Mustang",
"productVendor": "Autoart Studio Design",
"quantityInStock": 68
},
{"productName": "1970 Chevy Chevelle SS 454",
"productVendor": "Unimax Art Galleries",
"quantityInStock": 1005
}
...
]
},
{"productLine": "Motorcycles", ...
}
]
In the bundled code for JsonRelationships, you can find a lot of examples to do with one-to-one, one-to-many, and many-to-many relationships. Moreover, you can check out several examples of how to map arrays and UDTs into JSON.
Mapping JSON relationships to POJOs
As you just saw, jOOQ can fetch and map a relationship directly into JSON. However, that's not all! jOOQ can go even further and map the resulted JSON to the domain model (POJOs). Yes, you read that right; as long as we have Gson, Jackson (Spring Boot has this by default), or JAXB in the classpath, jOOQ can automatically map the query results to our nested data structures. This is quite useful when you don't actually need the JSON itself – you can just rely on JSON to facilitate the nesting data structures and map them back to Java. For instance, let's assume the following domain model:
public class SimpleProductLine {private String productLine;
private String textDescription;
private List<SimpleProduct> products;
}
public class SimpleProduct {private String productName;
private String productVendor;
private Short quantityInStock;
}
Can we populate this model from jOOQ Result by just using jOOQ? Yes, we can do it via SQL/JSON support, as follows:
List<SimpleProductLine> result = ctx.select(jsonObject(
key("productLine").value(PRODUCTLINE.PRODUCT_LINE), key("textDescription").value(PRODUCTLINE.TEXT_DESCRIPTION), key("products").value(select(jsonArrayAgg(jsonObject( key("productName").value(PRODUCT.PRODUCT_NAME), key("productVendor").value(PRODUCT.PRODUCT_VENDOR), key("quantityInStock").value(PRODUCT.QUANTITY_IN_STOCK))).orderBy(PRODUCT.QUANTITY_IN_STOCK)).from(PRODUCT)
.where(PRODUCTLINE.PRODUCT_LINE
.eq(PRODUCT.PRODUCT_LINE)))))
.from(PRODUCTLINE)
.orderBy(PRODUCTLINE.PRODUCT_LINE)
.fetchInto(SimpleProductLine.class);
That's so cool, right?! The same thing can be accomplished for one-to-one and many-to-many relationships, as you can see in the bundled code. All examples are available in JsonRelationshipsInto.
Mapping arbitrary models
If you think that what you've just seen is impressive, then get ready for more because jOOQ can fetch and map almost any kind of arbitrary model, not just the well-known 1:1,1:n, and n:n relationships. Let's consider the following three arbitrary models:
Figure 8.3 – Arbitrary domain models
Which one do you choose to be exemplified in the book? The second one (Model 2), of course! So, our goal is to write a query that returns a JSON that mirrors this model. For this, we rely on jsonObject() and jsonArrayAgg(), as follows:
Result<Record1<JSON>> result = ctx.select(jsonObject(
jsonEntry("customerName", CUSTOMER.CUSTOMER_NAME), jsonEntry("creditLimit", CUSTOMER.CREDIT_LIMIT), jsonEntry("payments", select(jsonArrayAgg(jsonObject( jsonEntry("customerNumber", PAYMENT.CUSTOMER_NUMBER), jsonEntry("invoiceAmount", PAYMENT.INVOICE_AMOUNT), jsonEntry("cachingDate", PAYMENT.CACHING_DATE), jsonEntry("transactions", select(jsonArrayAgg(jsonObject( jsonEntry("bankName", BANK_TRANSACTION.BANK_NAME), jsonEntry("transferAmount", BANK_TRANSACTION.TRANSFER_AMOUNT)))
.orderBy(BANK_TRANSACTION.TRANSFER_AMOUNT))
.from(BANK_TRANSACTION)
.where(BANK_TRANSACTION.CUSTOMER_NUMBER
.eq(PAYMENT.CUSTOMER_NUMBER)
.and(BANK_TRANSACTION.CHECK_NUMBER
.eq(PAYMENT.CHECK_NUMBER))))))
.orderBy(PAYMENT.CACHING_DATE))
.from(PAYMENT)
.where(PAYMENT.CUSTOMER_NUMBER
.eq(CUSTOMER.CUSTOMER_NUMBER))),
jsonEntry("details", select( jsonObject(jsonEntry("city", CUSTOMERDETAIL.CITY), jsonEntry("addressLineFirst",CUSTOMERDETAIL.ADDRESS_LINE_FIRST),
jsonEntry("state", CUSTOMERDETAIL.STATE))).from(CUSTOMERDETAIL)
.where(CUSTOMERDETAIL.CUSTOMER_NUMBER
.eq(CUSTOMER.CUSTOMER_NUMBER)))))
.from(CUSTOMER).orderBy(CUSTOMER.CREDIT_LIMIT).fetch();
Lukas Eder states that:
"What I always like to mention in this regard is that there are no accidental Cartesian Products or costly de-duplication going on (as with JPA), because all the data is already nested correctly in SQL, and transferred optimally. This approach should be the first choice when nesting collections with SQL or producing JSON/XML for some frontend. Never use ordinary joins, which should be used only for flat results or aggregations."
Take your time to dissect this query and check out the bundled code to see the output. Moreover, in the bundled code, you can practice Model 1 and Model 3, too. For each of these models, you have the JSON result and the corresponding mapping to POJOs. The application is named NestedJson.
I'm sure that, as a SQL Server fan, you are impatient to see the version of the previous query expressed via forJson(), so here it is:
Result<Record1<JSON>> result = ctx.select(
CUSTOMER.CUSTOMER_NAME, CUSTOMER.CREDIT_LIMIT,
select(PAYMENT.CUSTOMER_NUMBER, PAYMENT.INVOICE_AMOUNT,
PAYMENT.CACHING_DATE,
select(BANK_TRANSACTION.BANK_NAME,
BANK_TRANSACTION.TRANSFER_AMOUNT)
.from(BANK_TRANSACTION)
.where(BANK_TRANSACTION.CUSTOMER_NUMBER
.eq(PAYMENT.CUSTOMER_NUMBER)
.and(BANK_TRANSACTION.CHECK_NUMBER
.eq(PAYMENT.CHECK_NUMBER)))
.orderBy(BANK_TRANSACTION.TRANSFER_AMOUNT)
.forJSON().path().asField("transactions")).from(PAYMENT).where(PAYMENT.CUSTOMER_NUMBER
.eq(CUSTOMER.CUSTOMER_NUMBER))
.orderBy(PAYMENT.CACHING_DATE)
.forJSON().path().asField("payments"),select(CUSTOMERDETAIL.CITY,
CUSTOMERDETAIL.ADDRESS_LINE_FIRST,CUSTOMERDETAIL.STATE)
.from(CUSTOMERDETAIL)
.where(CUSTOMERDETAIL.CUSTOMER_NUMBER
.eq(CUSTOMER.CUSTOMER_NUMBER))
.forJSON().path().asField("details")).from(CUSTOMER).orderBy(CUSTOMER.CREDIT_LIMIT).forJSON().path().fetch();
Of course, you can check out these examples in the bundled code next to the examples for Model 1 and Model 3.
Important Note
Next to SQL/JSON support, jOOQ also provides SQL/JSONB support. You can explicitly use JSONB via org.jooq.JSONB and the operators such as jsonbObject(), jsonbArrayAgg(), and jsonbTable().
Now, it is time to talk about SQL/XML support.
Handling SQL/XML support
In a nutshell, for SQL/XML, we can talk about the following operators that have a jOOQ implementation in the org.jooq.impl.DSL class:
- XMLELEMENT (DSL.xmlelement()), XMLATTRIBUTES (DSL.xmlattributes()), XMLFOREST (DSL.xmlforest()), XMLCONCAT (DSL.xmlconcat()), and XMLCOMMENT (DSL.xmlcomment()) to construct XML data
- XMLAGG (DSL.xmlagg()) to aggregate data into nested XML documents
- XMLEXISTS (DSL.xmlexists()) and XMLQUERY (DSL.xmlquery()) to query XML documents with XPath
- XMLPARSE (DSL.xmlparseContent() and DSL.xmlparseDocument()) for parsing XML content and documents
- XMLPI (DSL.xmlpi()) for producing XML processing instructions
- XMLTABLE (DSL.xmltable()) to transform XML values into SQL tables
SQL Server's FOR XML syntax (including ROOT, PATH, ELEMENTS, RAW, and AUTO, and EXPLICIT (jOOQ 3.17.x +)) – jOOQ's commercial editions emulate the FOR XML syntax for databases that don't support it (in this book, you can practice this for SQL Server and Oracle).
Let's see some introductory examples of these operators via the jOOQ DSL API.
Constructing and aggregating XML data from values
A good start for constructing XML data from values relies on the XMLELEMENT operator. In jOOQ, XMLELEMENT is rendered via the xmlelement() method. For instance, the following snippet of code fetches the CUSTOMER_NAME field of each customer and uses it as the text of an XML element named <name/>:
Result<Record1<XML>> result = ctx.select(
xmlelement("name", CUSTOMER.CUSTOMER_NAME)).from(CUSTOMER).fetch();
The returned Result has a size that is equal to the number of fetched customers. Each Record1 wraps an org.jooq.XML instance representing a <name/> element. If we just want to format this Result as an XML, we can call the formatXML() method (this will be presented in the next chapter). This will return a simple formatted representation such as the one here:
<result xmlns="http:…">
<fields>
<field name="xmlconcat" type="XML"/>
</fields>
<records>
<record xmlns="http:…">
<value field="xmlconcat">
<name>Australian Home</name>
</value>
</record>
<record xmlns="http:…">
<value field="xmlconcat">
<name>Joliyon</name>
</value>
</record>
…
However, this response is too verbose to send to the client. For instance, you might only need the "records" element. In such cases, we can rely on the formatXML(XMLFormat) flavor, as you'll see in the bundled code. Supposing that you want to just send the first <name/> element, you can extract it from the Result object as result.get(0).value1().data():
result.get(0) // 0-first <name/>, 1-second <name/> …
.value1() // this is the value from Record1, a XML
.data() // this is the data of the first XML as String
<name>Australian Home</name>
However, perhaps you are planning to send all of these <name/> tags as a List<String> to the client. Then, rely on fetchInto(String.class) to return all the <name/> elements as a List<String>. Note that each String is a <name/>:
List<String> result = ctx.select(
xmlelement("name", CUSTOMER.CUSTOMER_NAME)).from(CUSTOMER).fetchInto(String.class);
Alternatively, it would be more practical to aggregate all these <name/> elements as a single String. This is possible via xmlagg(), which returns a single XML containing all of the fetched data (for convenience, let's aggregate everything under the <names/> tag):
String result = ctx.select(xmlelement("names", xmlagg( xmlelement("name", CUSTOMER.CUSTOMER_NAME)))).from(CUSTOMER).fetchSingleInto(String.class);
The aggregated XML is shown here:
<names>
<name>Australian Home</name>
<name>Joliyon</name>
...
</names>
What about adding attributes to the XML elements? This can be done via xmlattributes(), as shown in the following intuitive example:
Result<Record1<XML>> result = ctx.select(xmlelement("contact", xmlattributes(CUSTOMER.CONTACT_FIRST_NAME.as("firstName"), CUSTOMER.CONTACT_LAST_NAME.as("lastName"), CUSTOMER.PHONE))).from(CUSTOMER).fetch();
The expected XML will look like this:
<contact firstName="Sart"
lastName="Paoule" phone="40.11.2555"/>
A relatively useful XML operator is xmlforest(). This operator converts its parameters into XML and returns an XML fragment obtained by the concatenation of these converted arguments. Here is an example:
Result<Record1<XML>> result = ctx.select(
xmlelement("allContacts", xmlagg(xmlelement("contact", xmlforest(CUSTOMER.CONTACT_FIRST_NAME.as("firstName"), CUSTOMER.CONTACT_LAST_NAME.as("lastName"),CUSTOMER.PHONE)))))
.from(CUSTOMER).fetch();
The effect of xmlforest() can be seen in the resulting XML:
<allContacts>
<contact>
<firstName>Sart</firstName>
<lastName>Paoule</lastName>
<phone>40.11.2555</phone>
</contact>
…
</allContacts>
If you are a SQL Server fan, then you know that fetching data as XML can be done via the non-standard FOR XML syntax. jOOQ supports this syntax via the forXml() API. It also supports clauses such as ROOT via root(), PATH via path(), AUTO via auto(), RAW via raw(), and ELEMENTS via elements(), as you can see in the following example:
Result<Record1<XML>> result = ctx.select(
OFFICE.OFFICE_CODE, OFFICE.CITY, OFFICE.COUNTRY)
.from(OFFICE)
.forXML().path("office").elements().root("offices").fetch();
The produced XML looks like this:
<offices>
<office>
<office_code>1</office_code>
<city>San Francisco</city>
<country>USA</country>
</office>
<office>
<office_code>10</office_code>
</office>
<office>
<office_code>11</office_code>
<city>Paris</city>
<country>France</country>
</office>
...
</offices>
Note that missing tags (check the second <office/> instance, which does not have <city/> or <country/>) represent missing data.
As a side note, allow me to mention that jOOQ can also transform XML into an org.w3c.dom.Document by calling a flavor of intoXML() on Record1<XML>. Moreover, you'll love jOOX, or object-oriented XML (https://github.com/jOOQ/jOOX), which can be used to XSL transform or navigate the resulting XML document in a jQuery style.
I totally agree (sharing his enthusiasm) with Lukas Eder, who states:
"I don't know about you, but when I see these examples, I just want to write a huge application using jOOQ :) I mean, how else would anyone ever want to query databases and produce JSON or XML documents??"
You can see these examples (alongside many others) in the bundled code for SimpleXml and get familiar with the produced XMLs. For now, let's talk about how to order and limit the content of the resulting XML.
Using ORDER BY and LIMIT
When we don't use the xmlagg() aggregation operator, ordering and limiting is the same as for regular queries. For instance, you can order by CUSTOMER_NAME and limit the result to three XMLs as follows:
Result<Record1<XML>> result = ctx.select(
xmlelement("name", CUSTOMER.CUSTOMER_NAME)).from(CUSTOMER)
.orderBy(CUSTOMER.CUSTOMER_NAME).limit(3).fetch();
On the other hand, when the xmlagg() aggregation operator is used, then limiting should be done before the aggregation (for instance, in a subquery, JOIN, and more). Otherwise, this operation will be applied to the resulting aggregation itself. During aggregation, ordering can be done before limiting, respectively. For instance, in the following example, the subquery orders the customers by CONTACT_LAST_NAME and limits the returned results to 3, while the aggregation orders this result by CONTACT_FIRST_NAME:
String result = ctx.select(xmlelement("allContacts", xmlagg(xmlelement("contact", xmlforest(field("contact_first_name").as("firstName"), field("contact_last_name").as("lastName"),field("phone")))) .orderBy(field("contact_first_name")))).from(select(CUSTOMER.CONTACT_FIRST_NAME,
CUSTOMER.CONTACT_LAST_NAME, CUSTOMER.PHONE)
.from(CUSTOMER).orderBy(CUSTOMER.CONTACT_LAST_NAME).limit(3))
.fetchSingleInto(String.class);
The resulted aggregation is ordered by CUSTOMER_FIRST_NAME:
<allContacts>
<contact>
<firstName>Mel</firstName>
<lastName>Andersen</lastName>
<phone>030-0074555</phone>
</contact>
<contact>
<firstName>Paolo</firstName>
<lastName>Accorti</lastName>
<phone>011-4988555</phone>
</contact>
<contact>
<firstName>Raanan</firstName>
<lastName>Altagar,G M</lastName>
<phone>+ 972 9 959 8555</phone>
</contact>
</allContacts>
More examples are available in the bundle code for SimpleXml. For now, let's learn how to query XML documents with XPath.
Querying XML documents with XPath
Querying XML documents can be done via the XPath expressions, and we can distinguish between queries that check for the existence of an element/attribute via XMLEXISTS (xmlexists()) and queries that fetches certain data from an XML document via XMLQUERY (xmlquery()). For instance, in PRODUCTLINE, we have a field named HTML_DESCRIPTION that holds the description of a product line in XML format. If a product line has a description, then this description starts with the <productline/> tag. So, fetching all product lines that have a description can be done via xmlexists(), as follows:
Result<Record1<String>> result =
ctx.select(PRODUCTLINE.PRODUCT_LINE)
.from(PRODUCTLINE)
.where(xmlexists("/productline").passing(PRODUCTLINE.HTML_DESCRIPTION)).fetch();
In xmlexists("/productline").passing(…),/productline represents the XPath that should be searched, and the argument of the passing() method represents the XML document (or fragment) in which this XPath is searched.
On the other hand, the following snippet of code relies on xmlquery() to fetch an XML containing certain data from HTML_DESCRIPTION:
String result = ctx.select(xmlagg(
xmlquery("productline/capacity/c[position()=last()]").passing(PRODUCTLINE.HTML_DESCRIPTION)))
.from(PRODUCTLINE).fetchSingleInto(String.class);
Of course, the argument of passing() can be an XML build from certain fields, too:
Result<Record1<XML>> result = ctx.select(
xmlquery("//contact/phone").passing( xmlelement("allContacts", xmlagg(xmlelement("contact", xmlforest(CUSTOMER.CONTACT_FIRST_NAME.as("firstName"), CUSTOMER.CONTACT_LAST_NAME.as("lastName"),CUSTOMER.PHONE))))))
.from(CUSTOMER).fetch();
This query fetches all the <phone/> tags from the given XML (for instance, <phone>(26) 642-7555</phone>). More examples are available in SimpleXml. Next, let's tackle XMLTABLE.
Transforming XML values into SQL tables
Transforming XML values into SQL tables can be done via the XMLTABLE operator, which, in jOOQ, is equivalent to xmltable(). For instance, let's build a SQL table containing the details of each product line extracted from HTML_DESCRIPTION:
Result<Record> result = ctx.select(table("t").asterisk()) .from(PRODUCTLINE, xmltable("//productline/details").passing(PRODUCTLINE.HTML_DESCRIPTION)
.column("id").forOrdinality() .column("power", VARCHAR) .column("type", VARCHAR) .column("nr_of_lines", INTEGER).path("type/@nr_of_lines") .column("command", VARCHAR).path("type/@command") .as("t")).fetch();This query will produce a table as follows:
Figure 8.4 – The result of the previous query
Once you fetch a SQL table, you can think and act on it in the same way as any other database table. For brevity, I simply used VARCHAR, but it is better to specify a size in order to avoid defaulting to VARCHAR(max).
More examples, including how to use XMLTABLE with aggregates, ORDER BY, LIMIT, and how to transform back into XML from a SQL table, are available in SimpleXml.
Handling relationships via SQL/XML
Handling the typical 1:1, 1:n, and n:n relationships can be done via jOOQ SQL/XML support. Let's go through a quick rundown of it.
Mapping relationships to XML
Most of the time, such relationships can be materialized into XML via a thoughtful combination of xmlelement(), xmlagg(), and xmlforest(). Since you are already familiar with the one-to-many relationship between PRODUCTLINE and PRODUCT, let's shape it into XML via SQL/XML support:
Result<Record1<XML>> result = ctx.select(
xmlelement("productLine", xmlelement("productLine", PRODUCTLINE.PRODUCT_LINE), xmlelement("textDescription", PRODUCTLINE.TEXT_DESCRIPTION), xmlelement("products", field(select(xmlagg( xmlelement("product", xmlforest( PRODUCT.PRODUCT_NAME.as("productName"), PRODUCT.PRODUCT_VENDOR.as("productVendor"), PRODUCT.QUANTITY_IN_STOCK.as("quantityInStock"))))).from(PRODUCT)
.where(PRODUCTLINE.PRODUCT_LINE.eq(PRODUCT.PRODUCT_LINE))))))
.from(PRODUCTLINE)
.orderBy(PRODUCTLINE.PRODUCT_LINE).fetch();
As you can infer from the preceding code, expressing the one-to-one and many-to-many relationships is just a matter of juggling with the SQL/XML operators. You can find these examples, including how to use JOIN instead of a SELECT subquery, in the bundled code for XmlRelationships.
If you think that Result<Record1<XML>> is not ready to be sent to the client (for instance, via a REST controller), then decorate it a little bit more by aggregating all the product lines under a XML element (root) and relying on fetchSingleInto(), as follows:
String result = ctx.select(
xmlelement("productlines", xmlagg( xmlelement("productLine",...
.from(PRODUCTLINE).fetchSingleInto(String.class);
In SQL Server, we can obtain a similar result via forXml():
Result<Record1<XML>> result = ctx.select(
PRODUCTLINE.PRODUCT_LINE.as("productLine"), PRODUCTLINE.TEXT_DESCRIPTION.as("textDescription"), select(PRODUCT.PRODUCT_NAME.as("productName"), PRODUCT.PRODUCT_VENDOR.as("productVendor"), PRODUCT.QUANTITY_IN_STOCK.as("quantityInStock")) .from(PRODUCT)
.where(PRODUCT.PRODUCT_LINE.eq(PRODUCTLINE.PRODUCT_LINE))
.forXML().path().asField("products")).from(PRODUCTLINE)
.forXML().path("productline").root("productlines").fetch();
Or we can obtain a String via formatXML(XMLformat):
String result = ctx.select(
PRODUCTLINE.PRODUCT_LINE.as("productLine"), ...
.forXML().path("productline").root("productlines").fetch()
.formatXML(XMLFormat.DEFAULT_FOR_RECORDS);
Both examples will produce almost an identical XML, as follows (as you can see, altering the default XML tags inferred from the field names can be done with aliases via as("alias")):
<productlines>
<productline>
<productLine>Classic Cars</productLine>
<textDescription>Attention ...</textDescription>
<products>
<product>
<productName>1952 Alpine Renault 1300</productName>
<productVendor>Classic Metal Creations</productVendor>
<quantityInStock>7305</quantityInStock>
</product>
<product>
<productName>1972 Alfa Romeo GTA</productName>
<productVendor>Motor City Art Classics</productVendor>
<quantityInStock>3252</quantityInStock>
</product>
...
</products>
</productline>
<productline>
<productLine>Motorcycles</productLine>
...
You can check out these examples in the XmlRelationships application.
Mapping arbitrary nested models
jOOQ allows us to map arbitrarily nested models, not just the well-known 1:1,1:n, and n:n relationships, via SQL/XML support. Remember Model 2 (see Figure 8.3)? Well, you already know how to fetch and map that model via SQL/JSON support, so this time, let's see how it can be done via SQL/XML:
Result<Record1<XML>> result = ctx.select(
xmlelement("customer", xmlelement("customerName", CUSTOMER.CUSTOMER_NAME), xmlelement("creditLimit", CUSTOMER.CREDIT_LIMIT), xmlelement("payments", field(select(xmlagg( xmlelement("payment", // optional xmlforest(PAYMENT.CUSTOMER_NUMBER.as("customerNumber"), PAYMENT.INVOICE_AMOUNT.as("invoiceAmount"), PAYMENT.CACHING_DATE.as("cachingDate"), field(select(xmlagg(xmlelement("transaction", // optional xmlforest(BANK_TRANSACTION.BANK_NAME.as("bankName"), BANK_TRANSACTION.TRANSFER_AMOUNT.as("transferAmount"))))).from(BANK_TRANSACTION)
.where(BANK_TRANSACTION.CUSTOMER_NUMBER
.eq(PAYMENT.CUSTOMER_NUMBER)
.and(BANK_TRANSACTION.CHECK_NUMBER
.eq(PAYMENT.CHECK_NUMBER)))).as("transactions"))))).from(PAYMENT).where(PAYMENT.CUSTOMER_NUMBER
.eq(CUSTOMER.CUSTOMER_NUMBER)))),
xmlelement("details", field(select(xmlagg(xmlforest(CUSTOMERDETAIL.ADDRESS_LINE_FIRST
.as("addressLineFirst"), CUSTOMERDETAIL.STATE.as("state")))).from(CUSTOMERDETAIL)
.where(CUSTOMERDETAIL.CUSTOMER_NUMBER
.eq(CUSTOMER.CUSTOMER_NUMBER))))))
.from(CUSTOMER).orderBy(CUSTOMER.CREDIT_LIMIT).fetch();
This is the power of example; there is not much else to say. Take your time to dissect this query, and check out the bundled code to see the output. Moreover, in the bundled code, you can see Model 1 and Model 3, too. The application is named NestedXml.
As a SQL Server fan, you might be more interested in the previous query expressed via forXML(), so here it is:
Result<Record1<XML>> result = ctx.select(
CUSTOMER.CUSTOMER_NAME, CUSTOMER.CREDIT_LIMIT,
select(PAYMENT.CUSTOMER_NUMBER, PAYMENT.INVOICE_AMOUNT,
PAYMENT.CACHING_DATE, select(BANK_TRANSACTION.BANK_NAME,
BANK_TRANSACTION.TRANSFER_AMOUNT).from(BANK_TRANSACTION)
.where(BANK_TRANSACTION.CUSTOMER_NUMBER
.eq(PAYMENT.CUSTOMER_NUMBER)
.and(BANK_TRANSACTION.CHECK_NUMBER
.eq(PAYMENT.CHECK_NUMBER)))
.orderBy(BANK_TRANSACTION.TRANSFER_AMOUNT)
.forXML().path().asField("transactions")).from(PAYMENT).where(PAYMENT.CUSTOMER_NUMBER
.eq(CUSTOMER.CUSTOMER_NUMBER))
.orderBy(PAYMENT.CACHING_DATE)
.forXML().path().asField("payments"),select(CUSTOMERDETAIL.CITY,
CUSTOMERDETAIL.ADDRESS_LINE_FIRST, CUSTOMERDETAIL.STATE)
.from(CUSTOMERDETAIL)
.where(CUSTOMERDETAIL.CUSTOMER_NUMBER
.eq(CUSTOMER.CUSTOMER_NUMBER))
.forXML().path().asField("details")).from(CUSTOMER).orderBy(CUSTOMER.CREDIT_LIMIT)
.forXML().path().fetch();
In the bundled code, NestedXml, you can practice many more examples that, for brevity reasons, couldn't be listed here. Remember that, especially for this chapter, I beat the drums. Now, it is time to bring in an entire orchestra and pay tribute to the coolest feature of jOOQ mapping. Ladies and gentlemen, allow me to introduce the MULTISET!
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<R> table) starting with jOOQ 3.17.x as follows (later, we will refer to this example as Exhibit A):
var result = ctx.select(
PRODUCTLINE.PRODUCT_LINE, PRODUCTLINE.TEXT_DESCRIPTION,
multiset(
select(PRODUCT.PRODUCT_NAME, PRODUCT.PRODUCT_VENDOR,
PRODUCT.QUANTITY_IN_STOCK)
.from(PRODUCT)
.where(PRODUCTLINE.PRODUCT_LINE
.eq(PRODUCT.PRODUCT_LINE))
).as("products")) // MULTISET ends here.from(PRODUCTLINE)
.orderBy(PRODUCTLINE.PRODUCT_LINE)
.fetch();
So, the usage is quite simple! The jOOQ multiset() constructor gets a SELECT statement as an argument(or, a table-like object, starting with jOOQ 3.17.x). Formally speaking, the result set of this SELECT statement represents a collection that will be nested in the outer collection (the result set produced by the outer SELECT statement). By nesting/mixing multiset() and select() (or selectDistinct()), we can achieve any level or shape/hierarchy of nested collections. Previously, we used the Java 10 var keyword as the type of result, but the real type is Result<Record3<String, String, Result<Record3<String, String, Integer>>>>. Of course, more nesting will produce a really hard-to-digest Result object, so using var is the recommended way to go. As you already intuited, Result<Record3<String, String, Integer>> is produced by the SELECT statement from multiset(), while Result<Record3<String, String, nested_result>> is produced by the outer SELECT statement. The following diagram will help you to better understand this type:
Figure 8.5 – The type returned by the previous query
Since MULTISET has quite poor native support in databases, jOOQ has to emulate it via the SQL/JSON or SQL/XML operators. For instance, the previous query renders the following SQL in MySQL (check out how jOOQ uses json_merge_preserve() and json_array()):
SET @t = @@group_concat_max_len;
SET @@group_concat_max_len = 4294967295;
SELECT `classicmodels`.`productline`.`product_line`,
`classicmodels`.`productline`.`text_description`,
(SELECT coalesce(json_merge_preserve('[]', concat('[', group_concat(json_array(`v0`, `v1`, `v2`) separator
','), ']')), json_array())
FROM
(SELECT `classicmodels`.`product`.`product_name` AS `v0`,
`classicmodels`.`product`.`product_vendor` AS `v1`,
`classicmodels`.`product`.`quantity_in_stock` AS `v2`
FROM `classicmodels`.`product`
WHERE `classicmodels`.`productline`.`product_line` =
`classicmodels`.`product`.`product_line`)
AS `t`) AS `products`
FROM `classicmodels`.`productline`
ORDER BY `classicmodels`.`productline`.`product_line`;
SET @@group_concat_max_len = @t;
At any moment, you can transform this collection of Record into plain JSON or XML via formatJSON()/formatXML(). However, allow me to take this opportunity to highlight that if all you want is to fetch a JSON/XML (since this is what your client needs), then it is better to use the SQL/JSON and SQL/XML operators directly (as you saw in the previous section) instead of passing through MULTISET. You can find examples in the bundled code, MultisetRelationships, alongside examples of how to use MULTISET for one-to-one and many-to-many relationships. In the example for many-to-many relationships, you can see how well the jOOQ type-safe implicit (one-to-one) join feature fits with MULTISET.
Remember Model 2 (see Figure 8.3)? Well, you already know how to fetch and map that model via SQL/JSON and SQL/XML support, so let's see how to do it via MULTISET, too (later on, we will refer to this example as Exhibit B):
var result = ctx.select(
CUSTOMER.CUSTOMER_NAME, CUSTOMER.CREDIT_LIMIT,
multiset(select(PAYMENT.CUSTOMER_NUMBER,
PAYMENT.INVOICE_AMOUNT, PAYMENT.CACHING_DATE,
multiset(select(BANK_TRANSACTION.BANK_NAME,
BANK_TRANSACTION.TRANSFER_AMOUNT)
.from(BANK_TRANSACTION)
.where(BANK_TRANSACTION.CUSTOMER_NUMBER
.eq(PAYMENT.CUSTOMER_NUMBER)
.and(BANK_TRANSACTION.CHECK_NUMBER
.eq(PAYMENT.CHECK_NUMBER)))
.orderBy(BANK_TRANSACTION.TRANSFER_AMOUNT)))
.from(PAYMENT)
.where(PAYMENT.CUSTOMER_NUMBER
.eq(CUSTOMER.CUSTOMER_NUMBER))).as("payments"),multiset(select(CUSTOMERDETAIL.CITY,
CUSTOMERDETAIL.ADDRESS_LINE_FIRST,
CUSTOMERDETAIL.STATE)
.from(CUSTOMERDETAIL)
.where(CUSTOMERDETAIL.CUSTOMER_NUMBER
.eq(CUSTOMER.CUSTOMER_NUMBER)))
.as("customer_details")).from(CUSTOMER)
.orderBy(CUSTOMER.CREDIT_LIMIT.desc())
.fetch();
This time, the returned type is quite verbose: Result<Record4<String, BigDecimal, Result<Record4<Long, BigDecimal, LocalDateTime, Result<Record2<String, BigDecimal>>>>, Result<Record3<String, String, String>>>>. The following diagram explains this:
Figure 8.6 – The type returned by the previous query
You can find this example next to Model 1 and Model 3 in the application named NestedMultiset. Next, let's see how we can map MULTISET to DTO (for instance, POJO and Java 16 records).
Mapping MULTISET to DTO
Having the result of a MULTISET as a generic structural type is cool, but most probably, you'll love to have a List of POJO/Java records instead. For instance, if we think of Exhibit A, then you'll probably write the following Java records as the mapping model:
public record RecordProduct(String productName,
String productVendor, Integer quantityInStock) {}public record RecordProductLine(String productLine,
String textDescription, List<RecordProduct> products) {}So, you're expecting that Result<Record3<String, String, Integer>> will be fetched via MULTISET to be mapped to List<RecordProduct> and the whole query result to List<RecordProductLine>. The first part can be accomplished via the new ad hoc Field.convertFrom() converter, which was introduced in Chapter 7, Types, Converters, and Bindings. With Field.convertFrom(), we convert the given Field<T> (here, Field<Result<Record3<String, String, Integer>>> is returned by multiset()) into a read-only Field<U> (here, Field<List<RecordProduct>>) for ad hoc usage:
Field<List<RecordProduct>> result = multiset(
select(PRODUCT.PRODUCT_NAME, PRODUCT.PRODUCT_VENDOR,
PRODUCT.QUANTITY_IN_STOCK)
.from(PRODUCT)
.where(PRODUCTLINE.PRODUCT_LINE.eq(PRODUCT.PRODUCT_LINE)))
.as("products").convertFrom(r -> r.map(mapping(RecordProduct::new)));
The r parameter from r -> r.map(mapping(RecordProduct::new)) is Result<Record3<String, String, Integer>>, so this lambda can be seen as Result<Record3<String, String, Integer>> -> RecordProduct. The r.map(…) part is the Result.map(RecordMapper<R, E>) method. Finally, the Records.mapping() method (introduced earlier in this chapter) turns the constructor reference of the Function3<String, String, Integer, RecordProduct> type into a RecordMapper parameter, which is further used to turn a Result<Record3<String, String, Integer>> into a List<RecordProduct>. The resulting Field<List<SimpleProduct>> (which is like any other jOOQ Field) is now part of the outer SELECT next to PRODUCTLINE.PRODUCT_LINE (which is a String), and PRODUCTLINE.TEXT_DESCRIPTION (which is also a String).
So, our last mission is to convert the outer-most Result3<String, String, List<RecordProduct>> into List<RecordProductLine>. For this, we rely only on mapping(), as follows:
List<RecordProductLine> resultRecord = ctx.select(
PRODUCTLINE.PRODUCT_LINE, PRODUCTLINE.TEXT_DESCRIPTION,
multiset(
select(PRODUCT.PRODUCT_NAME, PRODUCT.PRODUCT_VENDOR,
PRODUCT.QUANTITY_IN_STOCK)
.from(PRODUCT)
.where(PRODUCTLINE.PRODUCT_LINE.eq(PRODUCT.PRODUCT_LINE)))
.as("products").convertFrom(r -> r.map(Records.mapping(RecordProduct::new))))
.from(PRODUCTLINE)
.orderBy(PRODUCTLINE.PRODUCT_LINE)
.fetch(mapping(RecordProductLine::new));
Done! Now, we can manipulate the List<RecordProductLine>. You can find this example in MultisetRelationshipsInto. By applying what we've learned here to the more complex Exhibit B, we obtain the following model:
public record RecordBank (
String bankName, BigDecimal transferAmount) {}public record RecordCustomerDetail(
String city, String addressLineFirst, String state) {}public record RecordPayment(
Long customerNumber, BigDecimal invoiceAmount,
LocalDateTime cachingDate, List<RecordBank> transactions) {}public record RecordCustomer(String customerName,
BigDecimal creditLimit, List<RecordPayment> payments,
List<RecordCustomerDetail> details) {}And the Exhibit B query is as follows:
List<RecordCustomer> resultRecord = ctx.select(
CUSTOMER.CUSTOMER_NAME, CUSTOMER.CREDIT_LIMIT,
multiset(select(PAYMENT.CUSTOMER_NUMBER,
PAYMENT.INVOICE_AMOUNT, PAYMENT.CACHING_DATE,
multiset(select(BANK_TRANSACTION.BANK_NAME,
BANK_TRANSACTION.TRANSFER_AMOUNT)
.from(BANK_TRANSACTION)
.where(BANK_TRANSACTION.CUSTOMER_NUMBER
.eq(PAYMENT.CUSTOMER_NUMBER)
.and(BANK_TRANSACTION.CHECK_NUMBER
.eq(PAYMENT.CHECK_NUMBER)))
.orderBy(BANK_TRANSACTION.TRANSFER_AMOUNT))
.convertFrom(r -> r.map(mapping(RecordBank::new))))
.from(PAYMENT)
.where(PAYMENT.CUSTOMER_NUMBER
.eq(CUSTOMER.CUSTOMER_NUMBER))).as("payments").convertFrom(r -> r.map(mapping(RecordPayment::new))),
multiset(select(CUSTOMERDETAIL.CITY,
CUSTOMERDETAIL.ADDRESS_LINE_FIRST,
CUSTOMERDETAIL.STATE)
.from(CUSTOMERDETAIL)
.where(CUSTOMERDETAIL.CUSTOMER_NUMBER
.eq(CUSTOMER.CUSTOMER_NUMBER)))
.as("customer_details").convertFrom(r ->
r.map(mapping(RecordCustomerDetail::new))))
.from(CUSTOMER)
.orderBy(CUSTOMER.CREDIT_LIMIT.desc())
.fetch(mapping(RecordCustomer::new));
This example, next to the examples for Model 1 and Model 3 from Figure 8.3, is available in NestedMultiset. Next, let's tackle the MULTISET_AGG() function.
The MULTISET_AGG() function
The jOOQ MULTISET_AGG() function is a synthetic aggregate function that can be used as an alternative to MULTISET. Its goal is to aggregate data into a nested collection represented as a jOOQ Result in a type-safe manner. The MULTISET_AGG() function is a convenient solution when we need to order by some aggregate value or create a WHERE statement based on result of not-deeply nested collection. For instance, the well-known one-to-many PRODUCTLINE:PRODUCT relationship can be aggregated as a nested collection as follows (the result type is Result<Record3<String, String, Result<Record3<String, String, Integer>>>>):
Starting with jOOQ 3.17.x, we can turn an expression of type String, Name, Field, and so in, into a multiset via DSL.asMultiset() methods. Check out the jOOQ documentation for more details.
var result = ctx.select(
PRODUCTLINE.PRODUCT_LINE, PRODUCTLINE.TEXT_DESCRIPTION,
multisetAgg(PRODUCT.PRODUCT_NAME, PRODUCT.PRODUCT_VENDOR,
PRODUCT.QUANTITY_IN_STOCK).as("products")).from(PRODUCTLINE)
.join(PRODUCT)
.on(PRODUCTLINE.PRODUCT_LINE.eq(PRODUCT.PRODUCT_LINE))
.groupBy(PRODUCTLINE.PRODUCT_LINE,
PRODUCTLINE.TEXT_DESCRIPTION)
.orderBy(PRODUCTLINE.PRODUCT_LINE)
.fetch();
This example is available, along with more examples, in MultisetAggRelationships.
Mapping the Result object to a DTO (for instance, POJO and Java 16 records) is accomplished by following the same principles as in the case of MULTISET:
List<RecordProductLine> resultRecord = ctx.select(
PRODUCTLINE.PRODUCT_LINE, PRODUCTLINE.TEXT_DESCRIPTION,
multisetAgg(PRODUCT.PRODUCT_NAME, PRODUCT.PRODUCT_VENDOR,
PRODUCT.QUANTITY_IN_STOCK).as("products").convertFrom(r -> r.map(mapping(RecordProduct::new))))
.from(PRODUCTLINE)
.join(PRODUCT)
.on(PRODUCTLINE.PRODUCT_LINE.eq(PRODUCT.PRODUCT_LINE))
.groupBy(PRODUCTLINE.PRODUCT_LINE,
PRODUCTLINE.TEXT_DESCRIPTION)
.orderBy(PRODUCTLINE.PRODUCT_LINE)
.fetch(mapping(RecordProductLine::new));
This example is available alongside other examples in MultisetAggRelationshipsInto. Next, let's try to compare MULTISETs.
Comparing MULTISETs
MULTISETs can be used in predicates, too. Check out the following example:
ctx.select(count().as("equal_count")).from(EMPLOYEE)
.where(multiset(selectDistinct(SALE.FISCAL_YEAR)
.from(SALE)
.where(EMPLOYEE.EMPLOYEE_NUMBER
.eq(SALE.EMPLOYEE_NUMBER)))
.eq(multiset(select(val(2003).union(select(val(2004))
.union(select(val(2007)))))))
.fetch();
But when we can say that two MULTISETs are equal? Check out the following examples that are meant to clarify this:
// A
ctx.selectCount()
.where(multiset(select(val("a"), val("b"), val("c"))) .eq(multiset(select(val("a"), val("b"), val("c"))))).fetch();
// B
ctx.selectCount()
.where(multiset(select(val("a"), val("b"), val("c"))) .eq(multiset(select(val("a"), val("c"), val("b"))))).fetch();
// C
ctx.selectCount()
.where(multiset(select(val("a")).union(select(val("b")) .union(select(val("c"))))) .eq(multiset(select(val("a")).union(select(val("b")) .union(select(val("c"))))))).fetch();
// D
ctx.selectCount()
.where(multiset(select(val("a")).union(select(val("b")) .union(select(val("c"))))) .eq(multiset(select(val("a")).union(select(val("b")))))).fetch();
So, which of A, B, C, and D will return 1? The correct answer is A and C. This means that two MULTISETs are equal if they have the exactly same number of elements in the same order. The application is named MultisetComparing. Feel free to determine when a MULTISET X is greater/lesser/contained … than a MULTISET Y.
Also, don't forget to read https://blog.jooq.org/jooq-3-15s-new-multiset-operator-will-change-how-you-think-about-sql/ and https://blog.jooq.org/the-performance-of-various-to-many-nesting-algorithms/. It looks as though jOOQ 3.17 will enrich MULTISET support with even more cool features, https://twitter.com/JavaOOQ/status/1493261571103105030, so stay tuned!
Moreover, since MULTISET and MULTISET_AGG() are such hot topics you should constantly update your skills from real scenarios exposed at https://stackoverflow.com/search?q=%5Bjooq%5D+multiset.
Next, let's talk about lazy fetching.
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 set in small chunks (for example, one by one). For such scenarios, jOOQ comes with the org.jooq.Cursor API. Practically, jOOQ holds a reference to an open result set and allows you to iterate (that is, load and process into memory) the result set via a number of methods such as fetchNext(), fetchNextOptional(), fetchNextInto(), and fetchNextOptionalInto(). However, to get a reference to an open result set, we have to call the fetchLazy() method, as shown in the following examples:
try (Cursor<CustomerRecord> cursor
= ctx.selectFrom(CUSTOMER).fetchLazy()) { while (cursor.hasNext()) {CustomerRecord customer = cursor.fetchNext();
System.out.println("Customer:
" + customer);}
}
Notice that we are relying on the try-with-resources wrapping to ensure that the underlying result set is closed at the end of the iterating process. In this snippet of code, jOOQ fetches the records from the underlying result set into memory one by one via fetchNext(), but this doesn't mean that the JDBC driver does the same thing. JDBC drivers act differently across different database vendors and even across different versions of the same database. For instance, MySQL and PostgreSQL pre-fetches all records in a single database round-trip, SQL Server uses adaptive buffering (in the JDBC URL, we have selectMethod = direct; responseBuffering = adaptive;) and a default fetch size of 128 to avoid out-of-memory errors, and Oracle JDBC fetches a result set of 10 rows at a time from the database cursor (on the JDBC URL level, this can be altered via the defaultRowPrefetch property).
Important Note
Bear in mind that the fetch size is just a JDBC hint trying to instruct the driver about the number of rows to fetch in one go from the database. However, the JDBC driver is free to ignore this hint.
In jOOQ, configuring the fetch size can be done via ResultQuery.fetchSize(int size) or Settings.withFetchSize(int size). jOOQ uses this configuration to set the underlying Statement.setFetchSize(int size) JDBC. Most JDBC drivers only apply this setting in certain contexts. For instance, MySQL should only apply this setting if we do the following:
- Set a forward-only result set (this can be set via jOOQ, resultSetType()).
- Set a concurrency read-only result set (via jOOQ, resultSetConcurrency()).
The fetch size is either set to Integer.MIN_VALUE for fetching records one by one or to the desired size while adding useCursorFetch=true to the JDBC URL for relying on cursor-based streaming.
Here is a snippet of code that takes advantage of these settings for MySQL:
try (Cursor<CustomerRecord> cursor = ctx.selectFrom(CUSTOMER)
.resultSetType(ResultSet.TYPE_FORWARD_ONLY)
.resultSetConcurrency(ResultSet.CONCUR_READ_ONLY)
.fetchSize(Integer.MIN_VALUE).fetchLazy()) { while (cursor.hasNext()) {CustomerRecord customer = cursor.fetchNext();
System.out.println("Customer:
" + customer);}
}
The complete example is named LazyFetching.
On the other hand, PostgreSQL uses the fetch size if we do the following:
- Set forward-only result set (can be set via jOOQ, resultSetType())
- Disable the auto-commit mode (in Spring Boot with the default Hikari CP connection pool, this can be done in application.properties via the following flag-property, spring.datasource.hikari.auto-commit=false, or in jOOQ via <autoCommit>false</autoCommit> in the <jdbc/> tag of your configuration)
So, the code for PostgreSQL can be as follows:
try ( Cursor<CustomerRecord> cursor = ctx.selectFrom(CUSTOMER)
.resultSetType(ResultSet.TYPE_FORWARD_ONLY) // default
.fetchSize(1).fetchLazy()) { while (cursor.hasNext()) {CustomerRecord customer = cursor.fetchNext();
System.out.println("Customer:
" + customer);}
}
Moreover, in PostgreSQL, the fetch size can be altered via defaultRowFetchSize and added to the JDBC URL. The complete example is also named LazyFetching.
For SQL Server and Oracle, we can rely on the default fetch size since both of them prevent out-of-memory errors. Nevertheless, enabling the fetch size in SQL Server is quite challenging while using the Microsoft JDBC driver (as in this book). It is much simpler if you rely on the jTDS driver.
Our examples for SQL Server and Oracle (LazyFetching) rely on the default fetching size; therefore, 128 for SQL Server and 10 for Oracle.
Finally, you can combine ResultSet and the Cursor API as follows:
ResultSet rs = ctx.selectFrom(CUSTOMER)
.fetchLazy().resultSet();
Cursor<Record> cursor = ctx.fetchLazy(rs);
Additionally, you can do it like this:
Cursor<Record> result = ctx.fetchLazy(
rs, CUSTOMER.CUSTOMER_NAME, CUSTOMER.CREDIT_LIMIT);
Cursor<Record> result = ctx.fetchLazy(
rs, VARCHAR, DECIMAL);
Cursor<Record> result = ctx.fetchLazy(
rs, String.class, BigDecimal.class);
Next, let's talk about lazy fetching via streaming.
Lazy featching via fetchStream()/fetchStreamInto()
In jOOQ, lazy fetching can also be achieved via fetchStream()/fetchStreamInto(). This method keeps an open JDBC result set internally and allows us to stream its content (that is, lazy fetching the result set into memory). For example, plain SQL can take advantage of DSLContext.fetchStream(), as follows:
try ( Stream<Record> stream
= ctx.fetchStream("SELECT sale FROM sale")) { stream.filter(rs -> rs.getValue("sale", Double.class) > 5000).forEach(System.out::println);
}
Or we can use the generated Java-based schema, as follows:
try ( Stream<SaleRecord> stream
= ctx.selectFrom(SALE).fetchStream()) {stream.filter(rs -> rs.getValue(SALE.SALE_) > 5000)
.forEach(System.out::println);
}
This code works in the same way as the next one, which uses stream(), not fetchStream():
try ( Stream<SaleRecord> stream
= ctx.selectFrom(SALE).stream()) {stream.filter(rs -> rs.getValue(SALE.SALE_) > 5000)
.forEach(System.out::println);
}
However, pay attention as this code is not the same as the next one (the previous example uses org.jooq.ResultQuery.stream(), while the next example uses java.util.Collection.stream()):
ctx.selectFrom(SALE)
.fetch() // jOOQ fetches the whole result set into memory
// and closes the database connection
.stream() // stream over the in-memory result set
// (no database connection is active)
.filter(rs -> rs.getValue(SALE.SALE_) > 5000)
.forEach(System.out::println);
Here, the fetch() method fetches the whole result set into memory and closes the database connection – this time, we don't need the try-with-resources wrapping since we are, essentially, streaming a list of records. Next, the stream() method opens a stream over the in-memory result set and no database connection is kept open. So, pay attention to how you write such snippets of code since you will be prone to accidental mistakes – for instance, you might need lazy fetching but accidentally add fetch(), or you might want eager fetching but accidentally forget to add fetch().
Using org.jooq.ResultQuery.collect()
Sometimes, we need the stream pipeline to apply specific operations (for instance, filter()), and to collect the results, as shown in the following example:
try ( Stream<Record1<Double>> stream = ctx.select(SALE.SALE_)
.from(SALE).fetchStream()) { // jOOQ API ends here SimpleSale result = stream.filter( // Stream API starts here
rs -> rs.getValue(SALE.SALE_) > 5000)
.collect(Collectors.teeing(
summingDouble(rs -> rs.getValue(SALE.SALE_)),
mapping(rs -> rs.getValue(SALE.SALE_), toList()),
SimpleSale::new));
}
However, if we don't actually need the stream pipeline (for instance, we don't need the filter() call or any other operation), and all we want is to lazily collect the result set, then it is pointless calling fetchStream(). But if we remove fetchStream(), how can we still collect in a lazy fashion? The answer is the jOOQ collect() method, which is available in org.jooq.ResultQuery. This method is very handy because it can handle resources internally and bypass the intermediate Result data structure. As you can see, there is no need to use try-with-resources after removing the fetchStream() call:
SimpleSale result = ctx.select(SALE.SALE_).from(SALE)
.collect(Collectors.teeing( // org.jooq.ResultQuery.collect()
summingDouble(rs -> rs.getValue(SALE.SALE_)),
mapping(rs -> rs.getValue(SALE.SALE_), toList()),
SimpleSale::new));
However, please bear in mind the following note.
Important Note
It is always a good practice to ensure that streaming is really needed. If your stream operations have SQL counterparts (for example, filter() can be replaced with a WHERE clause and summingDouble() can be replaced with the SQL's SUM() aggregate function), then go for the SQL. This will be much faster due to the significantly lower data transfer. So, always ask yourself: "Can I translate this streaming operation into my SQL?" If yes, then do it! If not, then go for streaming, as we will do in the following example.
Here is another example that lazy fetches groups. jOOQ doesn't fetch everything in memory thanks to collect(), and since we also set the fetch size, the JDBC driver fetches the result set in small chunks (here, a chunk has five records). The PostgreSQL version is as follows:
Map<Productline, List<Product>> result = ctx.select()
.from(PRODUCTLINE).leftOuterJoin(PRODUCT)
.on(PRODUCTLINE.PRODUCT_LINE.eq(PRODUCT.PRODUCT_LINE))
.fetchSize(5) // Set the fetch size for JDBC driver
.collect(Collectors.groupingBy(
rs -> rs.into(Productline.class),
Collectors.mapping(
rs -> rs.into(Product.class), toList())));
The complete application is named LazyFetchingWithStreams. Next, let's talk about asynchronous fetching.
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", "New order inserted...", 363L, BigDecimal.ZERO)
.returning().fetchOne();
}).thenApply(order -> { order.setStatus("ON HOLD"); order.setComments("Reverted to on hold ...");ctx.executeUpdate(order);
return order.getOrderId();
}).thenAccept(id -> ctx.deleteFrom(ORDER)
.where(ORDER.ORDER_ID.eq(id)).execute());
}
This example is available for MySQL next to another one in the application named SimpleAsync.
You can exploit CompletableFuture and jOOQ, as demonstrated in the previous example. However, you can also rely on two jOOQ shortcuts, fetchAsync() and executeAsync(). For instance, let's suppose that we want to fetch managers (MANAGER), offices (OFFICE), and employees (EMPLOYEE) and serve them to the client in HTML format. Fetching managers, offices, and employees can be done asynchronously since these three queries are not dependent on each other. In this context, the jOOQ fetchAsync() method allows us to write the following three methods:
@Async
public CompletableFuture<String> fetchManagersAsync() { return ctx.select(MANAGER.MANAGER_ID, MANAGER.MANAGER_NAME)
.from(MANAGER).fetchAsync()
.thenApply(rs -> rs.formatHTML()).toCompletableFuture();
}
@Async
public CompletableFuture<String> fetchOfficesAsync() {return ctx.selectFrom(OFFICE).fetchAsync()
.thenApply(rs -> rs.formatHTML()).toCompletableFuture();
}
@Async
public CompletableFuture<String> fetchEmployeesAsync() {return ctx.select(EMPLOYEE.OFFICE_CODE,
EMPLOYEE.JOB_TITLE, EMPLOYEE.SALARY)
.from(EMPLOYEE).fetchAsync()
.thenApply(rs -> rs.formatHTML()).toCompletableFuture();
}
Next, we wait for these three asynchronous methods to complete via the CompletableFuture.allOf() method:
public String fetchCompanyAsync() {CompletableFuture<String>[] fetchedCf
= new CompletableFuture[]{classicModelsRepository.fetchManagersAsync(),
classicModelsRepository.fetchOfficesAsync(),
classicModelsRepository.fetchEmployeesAsync()};
// Wait until they are all done
CompletableFuture<Void> allFetchedCf
= CompletableFuture.allOf(fetchedCf);
allFetchedCf.join();
// collect the final result
return allFetchedCf.thenApply(r -> {StringBuilder result = new StringBuilder();
for (CompletableFuture<String> cf : fetchedCf) {result.append(cf.join());
}
return result.toString();
}).join();
}
The String returned by this method (for instance, from a REST controller) represents a piece of HTML produced by jOOQ via the formatHTML() method. Curious about what this HTML looks like? Then, simply run the FetchAsync application under MySQL and use the provided controller to fetch the data in a browser. You might also like to practice the ExecuteAsync (which is available for MySQL) application that uses the jOOQ executeAsync() method as an example.
Lukas Eder mentions that:
"Perhaps worth mentioning that there's an ExecutorProvider SPI that allows for routing these async executions elsewhere when the default ForkJoinPool is not the correct place? jOOQ's own CompletionStage implementations also make sure that everything is always executed on the Executor provided by ExecutorProvider, unlike the JDK APIs, which always defaults back to the ForkJoinPool again (unless that has changed, recently)."
Next, let's tackle reactive fetching.
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.ofSeconds(2))
.share();
}
So, jOOQ is responsible for fetching some data from EMPLOYEE, and Flux is responsible for publishing the fetched data. You can practice this example in SimpleReactive (which is available for MySQL).
What about a more complex example? One of the important architectures that can be applied to mitigate data loss in a streaming pipeline is Hybrid Message Logging (HML). Imagine a streaming pipeline for meetup RSVPs. In order to ensure that we don't lose any RSVPs, we can rely on Receiver-Based Message Logging (RBML) to write every received RSVP to stable storage (for instance, PostgreSQL) before any action is performed on it. Moreover, we can rely on Sender-Based Message Logging (SBML) to write each RSVP in the stable storage right before we send it further on in the pipeline (for example, to a message queuing). This is the RSVP that was processed by the application business logic, so it might not the same as the received RSVP. The following diagram represents data flowing through an HML implementation:
Figure 8.7 – Data flow through HML
Based on the preceding diagram, we can implement the processing and recovery of data asynchronously. For instance, the RBML part can be expressed in jOOQ as follows:
public void insertRsvps(String message) {Flux<RsvpDocument> fluxInsertRsvp =
Flux.from(ctx.insertInto(RSVP_DOCUMENT)
.columns(RSVP_DOCUMENT.ID, RSVP_DOCUMENT.RSVP,
RSVP_DOCUMENT.STATUS)
.values((long) Instant.now().getNano(), message, "PENDING")
.returningResult(RSVP_DOCUMENT.ID, RSVP_DOCUMENT.RSVP,
RSVP_DOCUMENT.STATUS))
.map(rsvp -> new RsvpDocument(rsvp.value1(), rsvp.value2(),
rsvp.value3()));
processRsvp(fluxInsertRsvp);
}
On the other hand, the SBML part can be expressed as follows:
private void recoverRsvps() {Flux<RsvpDocument> fluxFindAllRsvps = Flux.from(
ctx.select(RSVP_DOCUMENT.ID, RSVP_DOCUMENT.RSVP,
RSVP_DOCUMENT.STATUS)
.from(RSVP_DOCUMENT))
.map(rsvp -> new RsvpDocument(rsvp.value1(),
rsvp.value2(), rsvp.value3()));
processRsvp(fluxFindAllRsvps);
}
What about deleting or updating an RSVP? For the complete code, check out the HML application, which is available for PostgreSQL.
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.