Helion


Szczegóły ebooka

jOOQ Masterclass

jOOQ Masterclass


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.

  • 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 whats 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