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

Cover
Title Page
Copyright and Credits
Foreword
Contributors
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
Index
Other Books You May Enjoy