jOOQ Masterclass: A practical guide for Java developers to write SQL queries for complex database interactions

BIRMINGHAM—MUMBAI

jOOQ Masterclass

Copyright © 2022 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

Assistant Group Product Manager: Alok Dhuri

Senior Editor: Kinnari Chohan

Technical Editor: Maran Fernandes

Copy Editor: Safis Editing

Project Coordinator: Manisha Singh

Proofreader: Safis Editing

Indexer: Tejal Daruwale Soni

Production Designer: Aparna Bhagat

Marketing Coordinator: Sonakshi Bubbar

First published: July 2022

Production reference: 2110822

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham

B3 2PB, UK.

ISBN 978-1-80056-689-7

www.packt.com

Foreword

It has been a great pleasure reviewing Anghel's book over the past year.

Anghel is very knowledgeable about databases in general, as well as SQL specifically and the various popular persistence technologies in the Java ecosystem. This shows in his examples, which are suitable both for beginners and for SQL gurus, who will use at least two window functions in every query.

jOOQ Masterclass is very well structured, both for jOOQ rookies who wish to learn about how to put jOOQ to best use and advanced jOOQ users who wish to have a reference for almost any use case that jOOQ supports. Anghel has a vast number of examples, which are both intuitive and powerful.

With this book at your disposal, your next jOOQ application will be a breeze.

– Lukas Eder, Founder and CEO of Data Geekery, the company behind jOOQ

Contributors

About the author

Anghel Leonard is a chief technology strategist and independent consultant with 20+ years of experience in the Java ecosystem. In his daily work, he is focused on architecting and developing Java-distributed applications that empower robust architectures, clean code, and high performance. He is also passionate about coaching, mentoring, and technical leadership. He is the author of several books, videos, and dozens of articles related to Java technologies.

I want to thank Lukas Eder who has guided me while writing this book and who has always been quick to answer my questions, suggestions, and so on.

About the reviewers

Matthew Cachia has a bachelor's degree in computer science and has been working in the payments space for more than 14 years. He is currently the technical architect of Weavr.io.

He is into static-type languages – most notably, Java, Scala, and Kotlin. He has become increasingly fascinated by compilers, transpilers, and languages overall – a field he regrets not picking up when reading his degree.

In his free time, he likes playing role-playing games and spending time with his family – Georgiana, Thomas, and Alex.

Lukas Eder is the founder and CEO of Data Geekery, the company behind jOOQ.

Table of Contents

Preface

Part 1: jOOQ as a Query Builder, SQL Executor, and Code Generator

Chapter 1: Starting jOOQ and Spring Boot

Technical requirements

Starting jOOQ and Spring Boot instantly

Adding the jOOQ open source edition

Adding a jOOQ free trial (commercial edition)

Injecting DSLContext into Spring Boot repositories

Using the jOOQ query DSL API to generate valid SQL

Executing the generated SQL and mapping the result set

Summary

Chapter 2: Customizing the jOOQ Level of Involvement

Technical requirements

Understanding what type-safe queries are

Generating a jOOQ Java-based schema

Code generation from a database directly

Code generation from SQL files (DDL)

Code generation from entities (JPA)

Writing queries using a Java-based schema

jOOQ versus JPA Criteria versus QueryDSL

Configuring jOOQ to generate POJOs

Configuring jOOQ to generate DAOs

Configuring jOOQ to generate interfaces

Tackling programmatic configuration

Introducing jOOQ settings

Summary

Part 2: jOOQ and Queries

Chapter 3: jOOQ Core Concepts

Technical requirements

Hooking jOOQ results (Result) and records (Record)

Fetching Result via plain SQL

Fetching Result via select()

Fetching Result via select() and join()

Fetching Result via selectFrom()

Fetching Result via ad hoc selects

Fetching Result via UDTs

Exploring jOOQ query types

Understanding the jOOQ fluent API

Writing fluent queries

Creating DSLContext

Using Lambdas and streams

Fluent programmatic configuration

Highlighting that jOOQ emphasizes SQL syntax correctness

Casting, coercing, and collating

Casting

Coercing

Collation

Binding values (parameters)

Indexed parameters

Named parameters

Inline parameters

Rendering a query with different types of parameter placeholders

Extracting jOOQ parameters from the query

Extracting binding values

Setting new bind values

Summary

Chapter 4: Building a DAO Layer (Evolving the Generated DAO Layer)

Technical requirements

Hooking the DAO layer

Shaping the DAO design pattern and using jOOQ

Shaping the generic DAO design pattern and using jOOQ

Extending the jOOQ built-in DAO

Summary

Chapter 5: Tackling Different Kinds of SELECT, INSERT, UPDATE, DELETE, and MERGE

Technical requirements

Expressing SELECT statements

Expressing commonly used projections

Expressing SELECT to fetch only the needed data

Expressing SELECT subqueries (subselects)

Expressing scalar subqueries

Expressing correlated subqueries

Expressing row expressions

Expressing the UNION and UNION ALL operators

Expressing the INTERSECT (ALL) and EXCEPT (ALL) operators

Expressing distinctness

Expressing INSERT statements

Expressing UPDATE statements

Expressing DELETE statements

Expressing MERGE statements

Summary

Chapter 6: Tackling Different Kinds of JOINs

Technical requirements

Practicing the most popular types of JOINs

CROSS JOIN

INNER JOIN

OUTER JOIN

PARTITIONED OUTER JOIN

The SQL USING and jOOQ onKey() shortcuts

SQL JOIN … USING

jOOQ onKey()

Practicing more types of JOINs

Implicit and Self Join

NATURAL JOIN

STRAIGHT JOIN

Semi and Anti Joins

LATERAL/APPLY Join

Summary

Chapter 7: Types, Converters, and Bindings

Technical requirements

Default data type conversion

Custom data types and type conversion

Writing an org.jooq.Converter interface

Hooking forced types for converters

JSON converters

UDT converters

Custom data types and type binding

Understanding what's happening without Binding

Manipulating enums

Writing enum converters

Data type rewrites

Handling embeddable types

Replacing fields

Converting embeddable types

Embedded domains

Summary

Chapter 8: Fetching and Mapping

Technical requirements

Simple fetching/mapping

Collector methods

Mapping methods

Simple fetching/mapping continues

Fetching one record, a single record, or any record

Using fetchOne()

Using fetchSingle()

Using fetchAny()

Fetching arrays, lists, sets, and maps

Fetching arrays

Fetching lists and sets

Fetching maps

Fetching groups

Fetching via JDBC ResultSet

Fetching multiple result sets

Fetching relationships

Hooking POJOs

Types of POJOs

jOOQ record mappers

The mighty SQL/JSON and SQL/XML support

Handling SQL/JSON support

Handling SQL/XML support

Nested collections via the astonishing MULTISET

Mapping MULTISET to DTO

The MULTISET_AGG() function

Comparing MULTISETs

Lazy fetching

Lazy featching via fetchStream()/fetchStreamInto()

Asynchronous fetching

Reactive fetching

Summary

Part 3: jOOQ and More Queries

Chapter 9: CRUD, Transactions, and Locking

Technical requirements

CRUD

Attaching/detaching updatable records

What's an original (updatable) record?

Marking (updatable) records as changed/unchanged

Resetting an (updatable) record

Refreshing an updatable record

Inserting updatable records

Updating updatable records (this sounds funny)

Deleting updatable records

Merging updatable records

Storing updatable records

Using updatable records in HTTP conversations

Navigating (updatable) records

Transactions

SpringTransactionProvider

ThreadLocalTransactionProvider

jOOQ asynchronous transactions

@Transactional versus the jOOQ transaction API

Hooking reactive transactions

Locking

Optimistic locking overview

jOOQ optimistic locking

Pessimistic locking overview

jOOQ pessimistic locking

Deadlocks

Summary

Chapter 10: Exporting, Batching, Bulking, and Loading

Technical requirements

Exporting data

Exporting as text

Exporting JSON

Export XML

Exporting HTML

Exporting CSV

Exporting a chart

Exporting INSERT statements

Batching

Batching via DSLContext.batch()

Batching records

Batched connection

Bulking

Loading (the Loader API)

The Loader API syntax

Examples of using the Loader API

Summary

Chapter 11: jOOQ Keys

Technical requirements

Fetching the database-generated primary key

Suppressing a primary key return on updatable records

Updating a primary key of an updatable record

Using database sequences

Inserting a SQL Server IDENTITY

Fetching the Oracle ROWID pseudo-column

Comparing composite primary keys

Working with embedded keys

Working with jOOQ synthetic objects

Synthetic primary/foreign keys

Synthetic unique keys

Synthetic identities

Hooking computed columns

Overriding primary keys

Summary

Chapter 12: Pagination and Dynamic Queries

Technical requirements

Offset and keyset pagination

Index scanning in offset and keyset

jOOQ offset pagination

jOOQ keyset pagination

The jOOQ SEEK clause

Implementing infinite scroll

Paginating JOINs via DENSE_RANK()

Paginating database views via ROW_NUMBER()

Writing dynamic queries

Using the ternary operator

Using jOOQ comparators

Using SelectQuery, InsertQuery, UpdateQuery, and DeleteQuery

Writing generic dynamic queries

Writing functional dynamic queries

Infinite scrolling and dynamic filters

Summary

Part 4: jOOQ and Advanced SQL

Chapter 13: Exploiting SQL Functions

Technical requirements

Regular functions

SQL functions for dealing with NULLs

Numeric functions

String functions

Aggregate functions

Window functions

ROWS

GROUPS

RANGE

BETWEEN start_of_frame AND end_of_frame

frame_exclusion

The QUALIFY clause

Working with ROW_NUMBER()

Working with RANK()

Working with DENSE_RANK()

Working with PERCENT_RANK()

Working with CUME_DIST()

Working with LEAD()/LAG()

Working with NTILE()

Working with FIRST_VALUE() and LAST_VALUE()

Working with RATIO_TO_REPORT()

Aggregates as window functions

Aggregate functions and ORDER BY

FOO_AGG()

COLLECT()

GROUP_CONCAT()

Oracle's KEEP() clause

Ordered set aggregate functions (WITHIN GROUP)

Hypothetical set functions

Inverse distribution functions

LISTAGG()

Grouping, filtering, distinctness, and functions

Grouping

Filtering

Distinctness

Grouping sets

Summary

Chapter 14: Derived Tables, CTEs, and Views

Technical requirements

Derived tables

Extracting/declaring a derived table in a local variable

Exploring Common Table Expressions (CTEs) in jOOQ

Regular CTEs

Recursive CTEs

CTEs and window functions

Using CTEs to generate data

Dynamic CTEs

Expressing a query via a derived table, a temporary table, and a CTE

Handling views in jOOQ

Updatable and read-only views

Types of views (unofficial categorization)

Some examples of views

Summary

Chapter 15: Calling and Creating Stored Functions and Procedures

Technical requirements

Calling stored functions/procedures from jOOQ

Stored functions

Stored procedures

Stored procedures and output parameters

Stored procedures fetching a single result set

Stored procedures with a single cursor

Stored procedures fetching multiple result sets

Stored procedures with multiple cursors

Calling stored procedures via the CALL statement

jOOQ and creating stored functions/procedures

Creating stored functions

Creating stored procedures

Summary

Chapter 16: Tackling Aliases and SQL Templating

Technical requirements

Expressing SQL aliases in jOOQ

Expressing simple aliased tables and columns

Aliases and JOINs

Aliases and GROUP BY/ORDER BY

Aliases and bad assumptions

Aliases and typos

Aliases and derived tables

Derived column list

Aliases and the CASE expression

Aliases and IS NOT NULL

Aliases and CTEs

SQL templating

Summary

Chapter 17: Multitenancy in jOOQ

Technical requirements

Connecting to a separate database per role/login via the RenderMapping API

Connecting to a separate database per role/login via a connection switch

Generating code for two schemas of the same vendor

Generating code for two schemas of different vendors

Summary

Part 5: Fine-tuning jOOQ, Logging, and Testing

Chapter 18: jOOQ SPI (Providers and Listeners)

Technical requirements

jOOQ settings

jOOQ Configuration

jOOQ providers

TransactionProvider

ConverterProvider

RecordMapperProvider

jOOQ listeners

ExecuteListener

jOOQ SQL parser and ParseListener

RecordListener

DiagnosticsListener

TransactionListener

VisitListener

Altering the jOOQ code generation process

Implementing a custom generator

Writing a custom generator strategy

Summary

Chapter 19: Logging and Testing

Technical requirements

jOOQ logging

jOOQ logging in Spring Boot – default zero-configuration logging

jOOQ logging with Logback/log4j2

Turn off jOOQ logging

Customizing result set logging

Customizing binding parameters logging

Customizing logging invocation order

Wrapping jOOQ logging into custom text

Filtering jOOQ logging

jOOQ testing

Mocking the jOOQ API

Writing integration tests

Testing R2DBC

Summary

Other Books You May Enjoy

Preface

The last decade has constantly changed how we think and write applications including the persistence layer, which must face new challenges such as working in microservice architectures and cloud environments. Flexibility, versatility, dialect agnostic, rock-solid SQL support, small learning curve, and high performance are just a few of the attributes that makes jOOQ the most appealing persistence technology for modern applications.

Being part of the modern technology stack, jOOQ is the new persistence trend that respects all standards of a mature, robust, and well-documented technology. This book covers jOOQ in detail, so it prepares you to become a jOOQ power-user and an upgraded version of yourself ready to handle the future of the persistence layer. Don't think of jOOQ as just another piece of technology; think of it as part of your mindset, your straightforward road to exploiting SQL instead of abstracting it, and your approach to doing things right in your organization.

Who this book is for

This book is for Java developers who write applications that interact with databases via SQL. No prior experience with jOOQ is assumed.

What this book covers

Chapter 1, Starting jOOQ and Spring Boot, shows how to create a kickoff application that involves jOOQ and Spring Boot in Java/Kotlin under Maven/Gradle.

Chapter 2, Customizing the jOOQ Level of Involvement, covers the configurations (declarative and programmatic) needed for employing jOOQ as a type-safe query builder and executor. Moreover, we set up jOOQ to generate POJOs and DAOs on our behalf. We use Java/Kotlin under Maven/Gradle.

Chapter 3, jOOQ Core Concepts, discusses jOOQ core concepts such as fluent API, SQL syntax correctness, emulating missing syntax/logic, jOOQ result set, jOOQ records, type safety, CRUD binding, and inlining parameters.

Chapter 4, Building a DAO Layer (Evolving the Generated DAO Layer), shows how implementing a DAO layer can be done in several ways/templates. We tackle an approach for evolving the DAO layer generated by jOOQ.

Chapter 5, Tackling Different Kinds of SELECT, INSERT, UPDATE, DELETE, and MERGE Statements, covers different kinds of SELECT, INSERT, UPDATE, DELETE, and MERGE queries. For example, we cover nested SELECT, INSERT...DEFAULT VALUES, INSERT...SET queries, and so on.

Chapter 6, Tackling Different Kinds of JOIN Statements, tackles different kinds of JOIN. jOOQ excels on standard and non-standard JOIN. We cover INNER, LEFT, RIGHT, …, CROSS, NATURAL, and LATERAL JOIN.

Chapter 7, Types, Converters, and Bindings, covers the custom data types, conversion, and binding.

Chapter 8, Fetching and Mapping, being one of the most comprehensive chapters, covers a wide range of jOOQ fetching and mapping techniques, including JSON/SQL, XML/SQL, and MULTISET features.

Chapter 9, CRUD, Transactions, and Locking, covers jOOQ CRUD support next to Spring/jOOQ transactions and optimistic/pessimistic locking.

Chapter 10, Exporting, Batching, Bulking, and Loading, covers batching, bulking, and loading files into the database via jOOQ. We will do single-thread and multi-thread batching.

Chapter 11, jOOQ Keys, tackles the different kinds of identifiers (auto-generated, natural IDs, and composite IDs) from the jOOQ perspective.

Chapter 12, Pagination and Dynamic Queries, covers pagination and building dynamic queries. Mainly, all jOOQ queries are dynamic, but in this chapter, we will highlight this, and we will write several filters by gluing and reusing different jOOQ artifacts.

Chapter 13, Exploiting SQL Functions, covers window functions (probably the most powerful SQL feature) in the jOOQ context.

Chapter 14, Derived Tables, CTEs, and Views, covers derived tables and recursive Common Table Expressions (CTEs) in the jOOQ context.

Chapter 15, Calling and Creating Stored Functions and Procedures, covers stored procedures and functions in the jOOQ context. This is one of the most powerful and popular jOOQ features.

Chapter 16, Tackling Aliases and SQL Templating, covers aliases and SQL templating. As you'll see, this chapter contains a must-have set of knowledge that will help you to avoid common related pitfalls.

Chapter 17, Multitenancy in jOOQ, covers different aspects of multi-tenancy/partitioning.

Chapter 18, jOOQ SPI (Providers and Listeners), covers jOOQ providers and listeners. Using these kinds of artifacts, we can interfere with the default behavior of jOOQ.

Chapter 19, Logging and Testing, covers jOOQ logging and testing.

To get the most out of this book

To get the most out of this book, you'll need to know the Java language and be familiar with one of the following database technologies:

Refer to this link for additional installation instructions and information you need to set things up: https://github.com/PacktPublishing/jOOQ-Masterclass/tree/master/db.

If you are using the digital version of this book, we advise you to type the code yourself or access the code from the book's GitHub repository (a link is available in the next section). Doing so will help you avoid any potential errors related to the copying and pasting of code.

Download the example code files

You can download the example code files for this book from GitHub at https://github.com/PacktPublishing/jOOQ-Masterclass. If there's an update to the code, it will be updated in the GitHub repository.

We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!

Download the color images

We also provide a PDF file that has color images of the screenshots and diagrams used in this book. You can download it here: https://packt.link/a1q9L.

Conventions used

There are a number of text conventions used throughout this book.

Code in text: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. Here is an example: For instance, the next snippet of code relies on the fetchInto() flavor.

A block of code is set as follows: