jOOQ Masterclass E-Book

Anghel Leonard

• Herausgeber: Packt Publishing
• Kategorie: Fachliteratur
• Sprache: Englisch

Seitenzahl: 721

Veröffentlichungsjahr: 2022

Beschreibung

jOOQ is an excellent query builder framework that allows you to emulate database-specific SQL statements using a fluent, intuitive, and flexible DSL API. jOOQ is fully capable of handling the most complex SQL in more than 30 different database dialects.
jOOQ Masterclass covers jOOQ from beginner to expert level using examples (for MySQL, PostgreSQL, SQL Server, and Oracle) that show you how jOOQ is a mature and complete solution for implementing the persistence layer. You’ll learn how to use jOOQ in Spring Boot apps as a replacement for SpringTemplate and Spring Data JPA. Next, you’ll unleash jOOQ type-safe queries and CRUD operations via jOOQ’s records, converters, bindings, types, mappers, multi-tenancy, logging, and testing. Later, the book shows you how to use jOOQ to exploit powerful SQL features such as UDTs, embeddable types, embedded keys, and more. As you progress, you’ll cover trending topics such as identifiers, batching, lazy loading, pagination, and HTTP long conversations. For implementation purposes, the jOOQ examples explained in this book are written in the Spring Boot context for Maven/Gradle against MySQL, Postgres, SQL Server, and Oracle.
By the end of this book, you’ll be a jOOQ power user capable of integrating jOOQ in the most modern and sophisticated apps including enterprise apps, microservices, and so on.

Leseprobe

jOOQ Masterclass

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

Anghel Leonard

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<Record> via plain SQL

Fetching Result<Record> via select()

Fetching Result<Record> via select() and join()

Fetching Result<Record> via selectFrom()

Fetching Result<Record> via ad hoc selects

Fetching Result<Record> 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

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

By the end of this part, you will know how to take advantage of the aforementioned three terms in the title in different kickoff applications. You will see how jOOQ can be used as a companion or as a total replacement for your current persistence technology (most probably, an ORM).

This part contains the following chapters:

Chapter 1, Starting jOOQ and Spring BootChapter 2, Customizing the jOOQ Level of Involvement