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

https://www.masiesdelpenedes.com/i0j1bii jOOQ Masterclass Foreword Contributors About the author About the reviewers Preface Who this book is for What this book covers To get the most out of this book Download the example code files Download the color images Conventions used Get in touch Share Your Thoughts 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 Why subscribe? Other Books You May Enjoy Packt is searching for authors like you Share Your Thoughts