Chapter 3: jOOQ Core Concepts

Before exploring more awesome features of jOOQ, we have to cover the core (fundamental) concepts that jOOQ relies on. Having a decent insight into jOOQ core concepts helps us to make the right decisions and to understand how jOOQ works under the hood. Don't worry, our aim is not to enter the jOOQ bowels! We aim to bring you close to the jOOQ paradigm and start thinking about your persistent layer in the jOOQ context.

The goal of this chapter is to briefly introduce the following topics:

• Hooking jOOQ results (Result) and records (Record)
• Exploring jOOQ query types
• Understanding the jOOQ fluent API
• Highlighting how jOOQ emphasizes SQL syntax correctness
• Casting, coercing, and collating
• Binding values (parameters)

By the end of this chapter, you'll be familiar with the jOOQ core concepts that will help you to easily follow the upcoming chapters.

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/Chapter03.

Hooking jOOQ results (Result) and records (Record)

In the previous chapters, we've mapped the JDBC result set of our queries to POJOs via the jOOQ fetchInto() method. But, in jOOQ, between the JDBC result set and a well-known List<POJO> (or other data structure such as an array, map, and set), there is another fundamental layer referenced as Result<Record> represented from the following two interfaces:

• org.jooq.Record: When we trigger a SELECT query, we get back a result set that contains a list of columns and the corresponding list of values. Typically, we refer to the content of the result set as records. jOOQ maps each such record to its Record interface. Think of Record as the jOOQ internal representation of records.
• org.jooq.Result: The jOOQ Result interface is a java.util.List of org.jooq.Record. In other words, jOOQ maps each record of the result set to a Record and collects this record in Result. Once Result<Record> is complete (the whole...

Exploring jOOQ query types

jOOQ distinguishes between two main types of queries:

• DML (INSERT, UPDATE, DELETE, and MERGE, among others) and DDL (CREATE, ALTER, DROP, RENAME, and similar) queries that produce a modification in the database
• DQL (SELECT) queries that produce results

DML and DDL queries are represented in jOOQ by the org.jooq.Query interface, while DQL queries are represented by the org.jooq.ResultQuery interface. The ResultQuery interface extends (among others) the Query interface.

For instance, the following snippet of code contains two jOOQ queries:

Query query = ctx.query("DELETE FROM payment 
  WHERE customer_number = 103");
Query query = ctx.deleteFrom(PAYMENT)
  .where(PAYMENT.CUSTOMER_NUMBER.eq(103L));  

These queries can be executed via jOOQ and they return the number of affected rows:

int affectedRows = query.execute();

And, here are two result queries: first, a plain SQL query –...

Understanding the jOOQ fluent API

Most of the time spent with jOOQ is about writing fluent code via the jOOQ fluent API. This approach is quite convenient for building fluent SQL expressions that avoid disrupting or chunking the code. Moreover, fluent APIs are easy to enrich with more operations.

Relying on a brilliant implementation of the interface-driven design concept, jOOQ hides most implementations from client code and acts as a good friend that is ready to listen regarding the SQL that you need to run. Let's see several usages of the jOOQ fluent API.

Writing fluent queries

So far, we have written several SQL in the jOOQ DSL API fluent style. Let's have another one as follows:

DSL.select(
      ORDERDETAIL.ORDER_LINE_NUMBER, 
      sum(ORDERDETAIL.QUANTITY_ORDERED).as("itemsCount"),
      sum(ORDERDETAIL.PRICE_EACH
       ...

Highlighting that jOOQ emphasizes SQL syntax correctness

One of the coolest features of jOOQ consists of the fact that jOOQ doesn't allow us to write bad SQL syntax. If you aren't an SQL expert or simply have issues with SQL-specific syntax, then all you have to do is to let jOOQ guide you step by step.

Having a fluent API for chaining methods to obtain a SQL is cool, but having a fluent API that emphasizes SQL syntax correctness is the coolest. jOOQ knows exactly how the query parts fit the puzzle and will help you via your IDE.

For instance, let's assume that we accidentally wrote the following bad SQLs. Let's start with an SQL that misses the ON clause:

ctx.select(EMPLOYEE.JOB_TITLE, 
           EMPLOYEE.OFFICE_CODE, SALE.SALE_)
   .from(EMPLOYEE)
   .join(SALE)
   // "on" clause is missing here
   .fetch();

The IDE signals...

Casting, coercing, and collating

jOOQ was designed to handle most of the casting issues under the hood, including for ultra-strong-typed databases such as DB2. Nevertheless, explicit casting and/or coercing still serve some isolated cases. Most probably, we'll need them when we are not satisfied with the jOOQ automatic mapping (for instance, we consider that jOOQ didn't find the most accurate mapping), or we just need a certain type to respond to a special case. Even if they add a little bit of verbosity, casting and coercing can be used fluently; therefore, the DSL expressions are not disrupted.

Casting

Most of the time, jOOQ finds the most accurate data type mapping between the database and Java. If we look into a jOOQ generated class that mirrors a database table, then we see that, for each column that has a database-specific type (for example, VARCHAR), jOOQ has found a Java type correspondent (for example, String). If we compare the schema of the PAYMENT table...

Binding values (parameters)

Binding values is another fundamental topic of jOOQ.

The well-known prepared statements and bind values combination is the preferable approach to express SQL statements in JDBC. Among benefits, this combination provides protection against SQL injections, sustains caching (for instance, most connection pools cache prepared statements across connections or rely on JDBC driver caching capabilities as HikariCP does), and reusability capabilities (re-using execution plans for identical SQL statements, regardless of actual bind values).

Having security and performance packed into this combination makes it preferable against static statements (java.sql.Statement) and inlined values, so jOOQ also embraces it as default.

Important Note

By default, jOOQ aligns its support for bind values to JDBC style. In other words, jOOQ relies on java.sql.PreparedStatement and indexed bind values or indexed parameters. Moreover, exactly like JDBC, jOOQ uses a ? (question...

Summary

This was a comprehensive chapter, which covered several fundamental aspects of jOOQ. So far, you have learned how to create DSLContext, how the jOOQ fluent API works, how to deal with jOOQ Result and Record, how to tackle edge cases of casting and coercing, and how to use bind values. As a rule of thumb, having these fundamentals under your tool belt is a major advantage that helps you to make the correct and optimal decisions and will be a great support in the next chapters.

In the next chapter, we will discuss alternatives for building a DAO layer and/or evolving the jOOQ-generated DAO layer.