Chapter 11: jOOQ Keys

Choosing the proper type of keys for our tables has a significant benefit on our queries. jOOQ sustains this statement by supporting a wide range of keys, from the well-known unique and primary keys to the fancy embedded and synthetic/surrogate keys. The most commonly used synthetic identifiers (or surrogate identifiers) are numerical or UUIDs. In comparison with natural keys, surrogate identifiers don't have a meaning or a correspondent in the real world. A surrogate identifier can be generated by a Numerical Sequence Generator (for instance, an identity or sequence) or by a Pseudorandom Number Generator (for instance, a GUID or UUID). Moreover, let me use this context to recall that in clustered environments, most relational databases rely on numerical sequences and different offsets per node to avoid the risk of conflicts. Use numerical sequences instead of UUIDs because they require less memory than UUIDs (a UUID requires 16 bytes, while BIGINT requires...

Technical requirements

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

Fetching the database-generated primary key

A typical scenario consists of fetching a database-generated (identity) primary key after an INSERT operation is executed via the insertInto()method or the updatable record's insert()method. If you are using insertInto() (DSL.insertInto() or DSLContext.insertInto()), the database-generated primary key can be obtained via the returningResult()/returning() methods. For instance, the identity primary key of SALE is shaped in MySQL via AUTO_INCREMENT, in SQL Server via IDENTITY, and for historic reasons (because both now support standard SQL IDENTITY columns), in PostgreSQL and Oracle via database sequences. In all these cases, the generated identity primary key of SALE can be fetched as here (SALE.SALE_ID):

long insertedId = ctx.insertInto(SALE, SALE.FISCAL_YEAR, 
    SALE.SALE_, SALE.EMPLOYEE_NUMBER, SALE.FISCAL_MONTH, 
    SALE.REVENUE_GROWTH)
 .values(2004, 2311.42, 1370L, 1, 0.0)
 .returningResult...

Suppressing a primary key return on updatable records

In the previous section, you saw that jOOQ automatically fetches and sets the generated primary key for updatable records. Suppressing this action can be done via the withReturnIdentityOnUpdatableRecord() flag setting. In some dialects, a database round trip (the lastID() style) can be prevented, so this is mostly a performance feature. By default, this flag is true, but if we explicitly set it to false, then jOOQ will no longer attempt to fetch the generated primary key:

DSLContext derivedCtx = ctx.configuration().derive(
 new Settings().withReturnIdentityOnUpdatableRecord(false))
               .dsl();
 SaleRecord sr = derivedCtx.newRecord(SALE);
 sr.setFiscalYear(2021);
 ...
 sr.insert();

This time, calling sr.getSaleId() returns null.

Updating a primary key of an updatable record

As a good practice, a primary key should never be updated anyway. But, who am I to judge?!

By default, calling the store() method after changing (to a non-null value) the primary key of an updatable record previously loaded via jOOQ causes an INSERT statement to be executed. However, we can force jOOQ to generate and execute an UPDATE of the primary key via the withUpdatablePrimaryKeys() flag setting:

DSLContext derivedCtx = ctx.configuration().derive(
  new Settings().withUpdatablePrimaryKeys(true)).dsl();
 SaleRecord sr = derivedCtx.selectFrom(SALE)
    .where(SALE.SALE_ID.eq(2L))
    .fetchSingle();
 sr.setSaleId(new_primary_key);
 sr.store(); // UPDATE primary key

Of course, we can also update the primary key via an explicit UPDATE, and if you really have to do it, then go for this instead of a jOOQ flag:

ctx.update(SALE)
   .set(SALE.SALE_ID, sr.getSaleId...

Using database sequences

To yield sequential numbers, databases such as PostgreSQL, SQL Server, and Oracle rely on sequences. A database sequence lives independently from tables – it can be associated with the primary key and non-primary key columns, it can be auto-generated (as in the case of PostgreSQL (BIG)SERIAL), it can be used across multiple tables, it can have independent permissions, it can have cycles, it can increment values in its own transactions to guarantee uniqueness across transactions using it, we can explicitly alter its values by setting minimum, maximum, increment, and current values, and so on.

For instance, let's consider the following sequence (employee_seq), defined in our PostgreSQL schema for the employee.employee_number primary key:

CREATE SEQUENCE "employee_seq" START 100000 INCREMENT 10 
       MINVALUE 100000 MAXVALUE 10000000 
       OWNED BY "employee...

Inserting a SQL Server IDENTITY

This is not the first time in this book that we have talked about inserting SQL Server IDENTITY values, but let's consider this section a must-have for this chapter. The problem consists of the fact that SQL Server doesn't allow us to specify an explicit value for an IDENTITY field as the PRODUCT primary key:

CREATE TABLE [product] (
  [product_id] BIGINT NOT NULL IDENTITY,
  ...
);

In other words, the following INSERT statement will cause the following error – Cannot insert explicit value for identity column in table 'product' when IDENTITY_INSERT is set to OFF:

ctx.insertInto(PRODUCT, PRODUCT.PRODUCT_ID, 
               PRODUCT.PRODUCT_LINE, PRODUCT.CODE, 
               PRODUCT.PRODUCT_NAME)
   .values(5555L, "Classic Cars"...

Fetching the Oracle ROWID pseudo-column

If you are a fan of the Oracle database, then it is impossible not to have heard about the ROWID pseudo-column. However, as a quick reminder, the ROWID pseudo-column is associated with each row by Oracle, and its main goal is to return the address of the row. The information contained by ROWID can be used to locate a certain row. In jOOQ, we can refer to ROWID via the rowid() method.

For instance, the following statement inserts a new SALE and fetches the generated primary key and the ROWID:

ctx.insertInto(SALE, SALE.FISCAL_YEAR, SALE.SALE_, 
               SALE.EMPLOYEE_NUMBER, SALE.FISCAL_MONTH, 
               SALE.REVENUE_GROWTH)
   .values(2004, 2311.42, 1370L, 1, 0.0)
   .returningResult(SALE.SALE_ID, rowid())
   .fetchOne();  

...

Comparing composite primary keys

By definition, a composite primary key involves two or more columns that should uniquely identify a record. A composite primary key is usually a natural key (even if it is composed of references to surrogate keys) and can often be preferable to surrogate keys in relationship tables: https://blog.jooq.org/2019/03/26/the-cost-of-useless-surrogate-keys-in-relationship-tables/. This means that predicates based on composite keys must contain all the involved columns. For instance, the PRODUCTLINE table has a composite key as (PRODUCT_LINE, CODE), and we can write a predicate for fetching a certain record by chaining the fields of the composite key via and(), as follows:

var result = ctx.selectFrom(PRODUCTLINE)
 .where(PRODUCTLINE.PRODUCT_LINE.eq("Classic Cars")
   .and(PRODUCTLINE.CODE.eq(599302L)))
 .fetchSingle();

Alternatively, we can separate fields from values using row() (the eq() method doesn't require an explicit...

Working with embedded keys

As part of the embeddable types introduced in Chapter 7, Types, Converters, and Bindings, we have jOOQ-embedded keys. An embedded key is materialized by the jOOQ Code Generator into the implementation of the jOOQ org.jooq.EmbeddableRecord interface and a handy POJO class. An embedded key extends the default implementation of the org.jooq.EmbeddableRecord interface, which is org.jooq.impl.EmbeddableRecordImpl.

We can define embedded keys for primary and unique keys. Practically, we indicate to jOOQ the primary/unique keys that should become embedded keys, and jOOQ will generate the corresponding artifacts for each primary/unique key, as well as for each foreign key referencing these primary/unique keys. Roughly, embedded keys mirror the primary/unique keys and the corresponding foreign keys in Java classes.

However, in order to employ embedded keys, we need the following configuration:

// Maven and standalone
<database>
  ...
 ...

Working with jOOQ synthetic objects

jOOQ synthetic objects is a powerful and exciting feature introduced in version 3.14 that reveals its full usability with database (updatable) views, databases that you cannot but want to alter, and legacy databases that have some missing parts. By missing parts, we mean identities, primary keys, unique keys, and foreign keys that simply don't exist, or do exist but are not enabled or reported by the database (and are not present in the database metadata). The jOOQ Code Generator can tackle this aspect by producing synthetic objects that emulate these missing parts. Let's adopt the learning by example technique to see how synthetic objects work.

Synthetic primary/foreign keys

Let's consider that we have the following two database views (in PostgreSQL):

CREATE OR REPLACE VIEW "customer_master" AS
SELECT "customerdetail"."city",
       "customerdetail"...

Overriding primary keys

Let's consider the following schema fragment (from PostgreSQL):

CREATE TABLE "customer" (
  "customer_number" BIGINT NOT NULL 
                         DEFAULT NEXTVAL ('"customer_seq"'),
  "customer_name" VARCHAR(50) NOT NULL, 
  ... 
  CONSTRAINT "customer_pk" PRIMARY KEY ("customer_number"), 
  CONSTRAINT "customer_name_uk" UNIQUE ("customer_name") 
  ... 
); 
CREATE TABLE "department" ( 
  "department_id" SERIAL NOT NULL, 
  "code" INT NOT NULL, 
  ... 
  CONSTRAINT "department_pk" PRIMARY KEY ("department_id"), 
  CONSTRAINT "department_code_uk" UNIQUE ("code"...

Summary

I hope you enjoyed this short but comprehensive chapter about jOOQ keys. The examples from this chapter covered popular aspects of dealing with different kinds of keys, from unique/primary keys to jOOQ-embedded and synthetic keys. I really hope that you don't stop at these examples and get curious to deep dive into these amazing jOOQ features – for instance, an interesting topic that deserves your attention is read-only columns: https://www.jooq.org/doc/dev/manual/sql-building/column-expressions/readonly-columns/.

In the next chapter, we will tackle pagination and dynamic queries.