You're reading from jOOQ Masterclass A practical guide for Java developers to write SQL queries for complex database interactions

Product type Paperback

Published in Aug 2022

Publisher Packt

ISBN-13 9781800566897

Length 764 pages

Edition 1st Edition

Languages

Java

Tools

Spring

Concepts

Database Programming

Author (1):

Anghel Leonard

View More author details

Table of Contents (26) Chapters

Preface

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

2. Chapter 1: Starting jOOQ and Spring Boot FREE CHAPTER

3. Chapter 2: Customizing the jOOQ Level of Involvement

4. Part 2: jOOQ and Queries

5. Chapter 3: jOOQ Core Concepts

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

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

8. Chapter 6: Tackling Different Kinds of JOINs

9. Chapter 7: Types, Converters, and Bindings

10. Chapter 8: Fetching and Mapping

11. Part 3: jOOQ and More Queries

12. Chapter 9: CRUD, Transactions, and Locking

13. Chapter 10: Exporting, Batching, Bulking, and Loading

14. Chapter 11: jOOQ Keys

15. Chapter 12: Pagination and Dynamic Queries

16. Part 4: jOOQ and Advanced SQL

17. Chapter 13: Exploiting SQL Functions

18. Chapter 14: Derived Tables, CTEs, and Views

19. Chapter 15: Calling and Creating Stored Functions and Procedures

20. Chapter 16: Tackling Aliases and SQL Templating

21. Chapter 17: Multitenancy in jOOQ

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

23. Chapter 18: jOOQ SPI (Providers and Listeners)

24. Chapter 19: Logging and Testing

25. Other Books You May Enjoy

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