Available in versions: Dev (3.22) | Latest (3.21) | 3.20 | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13
What's new
Supported by ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The below list of sections documents all the sections about new features in jOOQ version 3.17.0:
- Copyright, License, and Trademarks
- Getting started with jOOQ
-
SQL building
- The query DSL type
-
The DSLContext API
- SQL Dialect
- SQL Dialect Family
- Connection vs. DataSource
- Custom data
- Custom ExecuteListeners
- Custom Unwrappers
-
Custom Settings
- Auto-attach Records
- Auto-inline bind values
- Backslash Escaping
- Batch size
- Dialect compatibility
- Execute Logging
- Fetch Warnings
- GROUP_CONCAT Configuration
- Identifier style
- Implicit join type
- Inline Threshold
- IN-list Padding
- Interpreter Configuration
- JDBC Flags
- Keyword style
- Listener Invocation Order
- Locales
- Map JPA Annotations
- Meta system objects
- Object qualification
- Optimistic Locking
- Parameter name prefix
- Parameter types
- Parser Configuration
- Readonly column behaviour
- Reflection caching
- Rendering Configuration
- Return all columns on store
- Return Identity Value On Store
- Runtime catalog, schema and table mapping
- Scalar subqueries for stored functions
- Statement Type
- Updatable Primary Keys
- Thread safety
- The DSL API
- The model API
-
SQL Statements (DML)
- The WITH clause
- The WITH RECURSIVE clause
- The SELECT statement
-
The INSERT statement
- INSERT .. VALUES
- INSERT .. DEFAULT VALUES
- INSERT .. SET
- INSERT .. SELECT
- INSERT .. ON DUPLICATE KEY UPDATE
- INSERT .. ON DUPLICATE KEY UPDATE .. EXCLUDED
- INSERT .. ON DUPLICATE KEY UPDATE .. SET ALL TO EXCLUDED
- INSERT .. ON DUPLICATE KEY IGNORE
- INSERT .. ON CONFLICT
- INSERT .. ON CONFLICT .. EXCLUDED
- INSERT .. ON CONFLICT .. SET ALL TO EXCLUDED
- INSERT .. RETURNING
- The UPDATE statement
- The DELETE statement
- The MERGE statement
-
SQL Statements (DDL)
-
The ALTER statement
- ALTER DATABASE
-
ALTER DOMAIN
- ALTER DOMAIN .. RENAME
- ALTER DOMAIN .. SET DEFAULT
- ALTER DOMAIN .. DROP DEFAULT
- ALTER DOMAIN .. SET NOT NULL
- ALTER DOMAIN .. DROP NOT NULL
- ALTER DOMAIN .. ADD CONSTRAINT
- ALTER DOMAIN .. RENAME CONSTRAINT
- ALTER DOMAIN .. RENAME CONSTRAINT IF EXISTS
- ALTER DOMAIN .. DROP CONSTRAINT
- ALTER DOMAIN .. DROP CONSTRAINT IF EXISTS
- ALTER DOMAIN IF EXISTS
- ALTER INDEX
- ALTER SCHEMA
- ALTER SEQUENCE
-
ALTER TABLE
- ALTER TABLE .. ADD COLUMN
- ALTER TABLE .. ADD COLUMN .. FIRST, BEFORE, AFTER
- ALTER TABLE .. ADD COLUMNS
- ALTER TABLE .. ADD COLUMN IF NOT EXISTS
- ALTER TABLE .. ADD PRIMARY KEY
- ALTER TABLE .. ADD UNIQUE
- ALTER TABLE .. ADD FOREIGN KEY
- ALTER TABLE .. ADD CHECK
- ALTER TABLE .. RENAME
- ALTER TABLE .. COMMENT
- ALTER TABLE .. ALTER COLUMN .. SET DEFAULT
- ALTER TABLE .. ALTER COLUMN .. DROP DEFAULT
- ALTER TABLE .. ALTER COLUMN .. SET NOT NULL
- ALTER TABLE .. ALTER COLUMN .. DROP NOT NULL
- ALTER TABLE .. ALTER COLUMN .. SET TYPE
- ALTER TABLE .. ALTER CONSTRAINT .. ENFORCED
- ALTER TABLE .. ALTER CONSTRAINT .. NOT ENFORCED
- ALTER TABLE .. RENAME COLUMN
- ALTER TABLE .. RENAME CONSTRAINT
- ALTER TABLE .. RENAME INDEX
- ALTER TABLE .. DROP COLUMN
- ALTER TABLE .. DROP COLUMN RESTRICT
- ALTER TABLE .. DROP COLUMN CASCADE
- ALTER TABLE .. DROP COLUMNS
- ALTER TABLE .. DROP COLUMN IF EXISTS
- ALTER TABLE .. DROP CONSTRAINT
- ALTER TABLE .. DROP PRIMARY KEY
- ALTER TABLE .. DROP UNIQUE
- ALTER TABLE .. DROP FOREIGN KEY
- ALTER TABLE .. DROP CONSTRAINT IF EXISTS
- ALTER TABLE IF EXISTS
- ALTER TYPE
- ALTER VIEW
- The COMMENT statement
- The CREATE statement
- The DROP statement
- The GRANT statement
- The REVOKE statement
- The SET statement
- The TRUNCATE statement
- Generating DDL from objects
-
The ALTER statement
- Procedural statements
- Catalog and schema expressions
-
Table expressions
- Generated Tables
- Aliased Tables
- Joined tables
- The VALUES() table constructor
- Derived tables
- Inline derived tables
- Common table expressions (CTE)
- The Oracle PIVOT clause
- Relational division
- Array and cursor unnesting
- Table-valued functions
- GENERATE_SERIES
- JSON_TABLE
- XMLTABLE
- The DUAL table
- Temporal tables
- Data change delta tables
-
Column expressions
- Table columns
- Aliased columns
- Cast expressions
- Datatype coercions
- Readonly columns
- Computed columns
- Collations
- Arithmetic expressions
- String concatenation
- Case sensitivity with strings
- General functions
- Numeric functions
- Bitwise functions
- String functions
-
Datetime functions
- CENTURY
- CURRENT_DATE
- CURRENT_LOCALDATE
- CURRENT_LOCALDATETIME
- CURRENT_LOCALTIME
- CURRENT_OFFSETDATETIME
- CURRENT_OFFSETTIME
- CURRENT_TIME
- CURRENT_TIMESTAMP
- DATE
- DATEADD
- DATEDIFF
- DATESUB
- DAY
- DAY_OF_YEAR
- DECADE
- EPOCH
- EXTRACT
- HOUR
- ISO_DAY_OF_WEEK
- LOCALDATE
- LOCALDATEADD
- LOCALDATESUB
- LOCALDATETIME
- LOCALDATETIMEADD
- LOCALDATETIMESUB
- LOCALTIME
- MILLENNIUM
- MINUTE
- MONTH
- QUARTER
- SECOND
- TIME
- TIMESTAMP
- TIMESTAMPADD
- TIMESTAMPSUB
- TO_DATE
- TO_LOCALDATE
- TO_LOCALDATETIME
- TO_TIMESTAMP
- TRUNC
- YEAR
- ARRAY functions
- JSON functions
- XML functions
- CONNECT BY functions
- System functions
- Spatial functions
-
Aggregate functions
- Grouping
- Distinctness
- Filtering
- Ordering
- Ordering WITHIN GROUP
- Keeping
- ANY_VALUE
- ARRAY_AGG
- AVG
- BIT_AND_AGG
- BIT_NAND_AGG
- BIT_NOR_AGG
- BIT_OR_AGG
- BIT_XOR_AGG
- BIT_XNOR_AGG
- BOOL_AND
- BOOL_OR
- COLLECT
- COUNT
- CUME_DIST
- DENSE_RANK
- EVERY
- GROUP_CONCAT
- JSON_ARRAYAGG
- JSON_OBJECTAGG
- LISTAGG
- MAX
- MEDIAN
- MIN
- MODE (ordered)
- MODE (unordered)
- MULTISET_AGG
- PERCENT_RANK
- PERCENTILE_CONT
- PERCENTILE_DISC
- PRODUCT
- RANK
- SUM
- XMLAGG
- Window functions
- User-defined functions
- User-defined aggregate functions
- The CASE expression
- Sequences and serials
- Scalar subqueries
- ARRAY value constructor
- MULTISET value constructor
- Tuples or row value expressions
- Nested records
-
Conditional expressions
- Condition building
- TRUE and FALSE condition
- BOOLEAN columns
- AND, OR, NOT boolean operators
- Boolean operator precedence
- Comparison predicate
- Comparison predicate (degree > 1)
- Quantified comparison predicate
- BETWEEN predicate
- BETWEEN predicate (degree > 1)
- DISTINCT predicate
- DISTINCT predicate (degree > 1)
- DOCUMENT predicate
- EXISTS predicate
- IN predicate
- IN predicate (degree > 1)
- JSON predicate
- JSON_EXISTS predicate
- LIKE predicate
- LIKE REGEX predicate
- Quantified LIKE predicate
- STARTS_WITH predicate
- ENDS_WITH predicate
- CONTAINS predicate
- NULL predicate
- NULL predicate (degree > 1)
- OVERLAPS predicate
- SIMILAR TO predicate
- Spatial predicates
- UNIQUE predicate
- XMLEXISTS predicate
- Query By Example (QBE)
- Operator precedence
-
Data types
- Flags modifying data types
-
Built-in data types
- BIGINT (Long)
- BIGINT UNSIGNED (ULong)
- BINARY (byte[])
- BIT (Boolean)
- BLOB (byte[])
- BOOLEAN (Boolean)
- CHAR (String)
- CLOB (String)
- DATE (Date)
- DECIMAL (BigDecimal)
- DECIMAL INTEGER (BigInteger)
- DOUBLE (Double)
- FLOAT (Double)
- GEOGRAPHY (Geography)
- GEOMETRY (Geometry)
- INSTANT (Instant)
- INTEGER (Integer)
- INTEGER UNSIGNED (UInteger)
- INTERVAL (YearToSecond)
- INTERVAL DAY TO SECOND (DayToSecond)
- INTERVAL YEAR TO MONTH (YearToMonth)
- JSON (JSON)
- JSONB (JSONB)
- LOCALDATE (LocalDate)
- LOCALDATETIME (LocalDateTime)
- LOCALTIME (LocalTime)
- LONGNVARCHAR (String)
- LONGVARBINARY (byte[])
- LONGVARCHAR (String)
- NCHAR (String)
- NCLOB (String)
- NUMERIC (BigDecimal)
- NVARCHAR (String)
- OFFSETDATETIME (OffsetDateTime)
- OFFSETTIME (OffsetTime)
- OTHER (Object)
- REAL (Float)
- RECORD (Record)
- RESULT (Result)
- ROWID (RowId)
- SMALLINT (Short)
- SMALLINT UNSIGNED (UShort)
- TIME (Time)
- TIMESTAMP (Timestamp)
- TIMESTAMP WITH TIME ZONE (OffsetDateTime)
- TIME WITH TIME ZONE (OffsetTime)
- TINYINT (Byte)
- TINYINT UNSIGNED (UByte)
- UUID (UUID)
- VARBINARY (byte[])
- VARCHAR (String)
- XML (XML)
- Extended data types
- Enum data types
- Domain data types
- User-defined data types (UDTs)
- Converted data types
- Synthetic SQL clauses
- Dynamic SQL
- Plain SQL
- Hints
- SQL Parser
- SQL interpreter
- Schema diff
- Schema diff CLI
- Names and identifiers
- Bind values and parameters
-
QueryParts
- SQL rendering
- Declaration vs reference
- Pretty printing SQL
- Variable binding
- Custom syntax elements
- Plain SQL QueryParts
- Serializability
-
SQL transformation
- ANSI JOIN to table lists
- Table lists to ANSI JOIN
- ROWNUM to LIMIT
- QUALIFY to derived table
- IN condition subquery with LIMIT to derived table
- Unnecessary arithmetic expressions
-
Pattern based transformation
- AND to NOT IN
- Arithmetic expressions
- COUNT(*) scalar subquery comparison
- COUNT(expr) scalar subquery comparison
- Empty scalar subquery
- Hyperbolic functions
- Idempotent function repetition
- Inverse hyperbolic functions
- Logarithmic functions
- Merge AND predicates
- Merge BIT_NOT with BIT_NAND
- Merge BIT_NOT with BIT_NOR
- Merge BIT_NOT with BIT_XNOR
- Merge IN predicates
- Merge NOT with comparison predicates
- Merge NOT with DISTINCT predicate
- Merge OR predicates
- Merge range predicates
- Normalise associative operations
- Normalise fields compared to values
- Normalise IN list with single element to comparison
- OR to IN
- Repeated arithmetic negation
- Repeated bitwise negation
- Repeated NOT
- Trigonometric functions
- Trim
- Trivial case abbreviations
- Trivial predicates
- Custom SQL transformation with VisitListener
- Zero-based vs one-based APIs
- SQL building in Kotlin
- SQL building in Scala
- Compile time validation
-
SQL execution
- Comparison between jOOQ and JDBC
- Query vs. ResultQuery
-
Fetching
- Record vs. TableRecord
- Record1 to Record22
- Arrays, Maps and Lists
- ResultQuery as Iterable
- RecordMapper
- POJOs
- RecordMapperProvider
- Ad-hoc Converter
- ConverterProvider
- Lazy fetching
- Lazy fetching with Streams
- Many fetching
- Later fetching
- Reactive Fetching
- ResultSet fetching
- Auto data type conversion
- Custom data type conversion
- Data type lookups
- Static statements vs. Prepared Statements
- Reusing a Query's PreparedStatement
- JDBC flags
- Using JDBC batch operations
- Sequence execution
- Stored procedures and functions
- Exporting to XML, CSV, JSON, HTML, Text, Charts
- Importing data
- CRUD with UpdatableRecords
- DAOs
- Transaction management
- Exception handling
- ExecuteListeners
- Database meta data
- JDBC Connection
- Batched Connection
- Mocking Connection
- Mock File Database
- Parsing Connection
- Diagnostics
- Logging with LoggerListener
- Logging Connection
- Performance considerations
- Alternative execution models
-
Code generation
- Configuration and setup of the generator
-
Advanced generator configuration
- Logging
- Error handling
- Jdbc
- Generator
-
Database
- Database name and properties
- RegexFlags
- Includes and Excludes
- Include object types
- Record Version and Timestamp Fields
- Comments
- Synthetic objects
- Date as timestamp
- Ignore procedure return values (deprecated)
- Readonly columns
- Unsigned types
- Catalog and schema mapping
- Catalog and schema version providers
- Custom ordering of generated code
- Forced types
- Table valued functions
- Generate
- Output target configuration
- Generated object types
- Class names, method names, identifiers
- Custom code sections
- Code generation extensions
- Embeddable types
- Input catalogs and schemas
- Alternative meta data sources
- Alternative output languages
- Code generation execution
- System properties governing code generation
- Code generation dependencies
- Code generation for large schemas
- Code generation and version control
- Features requiring generated code
- Coming from JPA
- What's new
-
Reference
- Supported RDBMS
- Commercial only features
- Experimental features
- SQL to DSL mapping rules
- Quality Assurance
- Security
- Migrating to jOOQ 3.0
-
Don't do this
- jOOQ: Implementing the DSL types
- jOOQ: Referencing the Step types
- Schema: NULL columns
- Schema: Unnamed constraints
- Schema: Unnecessary surrogate keys
- Schema: Wrong data types
- SQL: COUNT(*) instead of EXISTS()
- SQL: N+1
- SQL: NATURAL JOIN or JOIN USING
- SQL: NOT IN predicate
- SQL: ORDER BY [column index]
- SQL: Rely on implicit ordering
- SQL: SELECT *
- SQL: SELECT DISTINCT
- SQL: Unnecessary UNION instead of UNION ALL
- The most important jOOQ types
- Credits
Feedback
Do you have any feedback about this page? We'd love to hear it!