# The jOOQ User Manual, version 3.22
> [!NOTE]
> This is the complete jOOQ 3.22 manual in a single Markdown document, generated from the manual sources. Sections are numbered as in the online manual. Each section heading is preceded by an anchor named after the section ID. Links to other sections point to these anchors (e.g. `#select-statement`). The online version of this manual is available at https://www.jooq.org/doc/3.22/manual/
>
> This documentation is for the unreleased development version of jOOQ.
## Overview
This manual is divided into these main sections:
- [Getting started with jOOQ](#getting-started)
This section will get you started with jOOQ quickly. It contains simple explanations about what jOOQ is, what jOOQ isn't and how to set it up for the first time
- [SQL building](#sql-building)
This section explains all about the jOOQ syntax used for building queries through the query DSL and the query model API. It explains the central factories, the supported SQL statements and various other syntax elements
- [Code generation](#code-generation)
This section explains how to configure and use the built-in source code generator
- [SQL execution](#sql-execution)
This section will get you through the specifics of what can be done with jOOQ at runtime, in order to execute queries, perform CRUD operations, import and export data, and hook into the jOOQ execution lifecycle for debugging
- [What's new in version 3.22.0](#whats-new)
This section shows what's new in version 3.22.0 of jOOQ.
- [Reference](#reference)
This section is a reference for elements in this manual
](#transform-group-by-column-index)
- [3.25.8.7. Inline CTE](#transform-inline-cte)
- [3.25.8.8. Unnecessary arithmetic expressions](#transform-unnecessary-arithmetic)
- [3.25.8.9. Pattern based transformation](#transform-patterns)
- [3.25.8.9.1. AND to NOT IN](#transform-patterns-and-ne-to-not-in)
- [3.25.8.9.2. Arithmetic comparisons](#transform-patterns-arithmetic-comparisons)
- [3.25.8.9.3. Arithmetic expressions](#transform-patterns-arithmetic-expressions)
- [3.25.8.9.4. BIT_GET function](#transform-patterns-bit-get)
- [3.25.8.9.5. BIT_SET function](#transform-patterns-bit-set)
- [3.25.8.9.6. CASE searched to CASE simple](#transform-patterns-case-searched-to-simple)
- [3.25.8.9.7. CASE to CASE abbreviation](#transform-patterns-case-to-case-abbreviation)
- [3.25.8.9.8. CASE with DISTINCT FROM to DECODE](#transform-patterns-case-distinct-to-decode)
- [3.25.8.9.9. CASE with ELSE NULL](#transform-patterns-case-else-null)
- [3.25.8.9.10. COUNT(*) scalar subquery comparison](#transform-patterns-scalar-subquery-count-asterisk-gt-zero)
- [3.25.8.9.11. COUNT(const)](#transform-patterns-count-constant)
- [3.25.8.9.12. COUNT(DISTINCT ..) with redundant args](#transform-patterns-count-distinct-multiple-redundant)
- [3.25.8.9.13. COUNT(expr) scalar subquery comparison](#transform-patterns-scalar-subquery-count-expression-gt-zero)
- [3.25.8.9.14. DISTINCT FROM NULL](#transform-patterns-distinct-from-null)
- [3.25.8.9.15. Empty scalar subquery](#transform-patterns-empty-scalar-subquery)
- [3.25.8.9.16. Flatten CASE](#transform-patterns-flatten-case)
- [3.25.8.9.17. Flatten CASE abbreviations](#transform-patterns-flatten-case-abbreviation)
- [3.25.8.9.18. Flatten DECODE](#transform-patterns-flatten-decode)
- [3.25.8.9.19. Hyperbolic functions](#transform-patterns-hyperbolic-functions)
- [3.25.8.9.20. Idempotent function repetition](#transform-patterns-idempotent-function-repetition)
- [3.25.8.9.21. Inverse hyperbolic functions](#transform-patterns-inverse-hyperbolic-functions)
- [3.25.8.9.22. Logarithmic functions](#transform-patterns-logarithmic-functions)
- [3.25.8.9.23. Merge AND predicates](#transform-patterns-merge-and-comparison)
- [3.25.8.9.24. Merge BIT_NOT with BIT_NAND](#transform-patterns-merge-bit-not-bit-nand)
- [3.25.8.9.25. Merge BIT_NOT with BIT_NOR](#transform-patterns-merge-bit-not-bit-nor)
- [3.25.8.9.26. Merge BIT_NOT with BIT_XNOR](#transform-patterns-merge-bit-not-bit-xnor)
- [3.25.8.9.27. Merge CASE .. WHEN and ELSE clauses](#transform-patterns-case-merge-when-else)
- [3.25.8.9.28. Merge CASE .. WHEN clauses](#transform-patterns-case-merge-when-when)
- [3.25.8.9.29. Merge IN predicates](#transform-patterns-merge-in-predicates)
- [3.25.8.9.30. Merge NOT with comparison predicates](#transform-patterns-merge-not-comparison)
- [3.25.8.9.31. Merge NOT with DISTINCT predicate](#transform-patterns-merge-not-not-distinct)
- [3.25.8.9.32. Merge OR predicates](#transform-patterns-merge-or-comparison)
- [3.25.8.9.33. Merge range predicates](#transform-patterns-merge-range-predicates)
- [3.25.8.9.34. Normalise associative operations](#transform-patterns-normalise-associative-ops)
- [3.25.8.9.35. Normalise fields compared to values](#transform-patterns-normalise-field-compare-value)
- [3.25.8.9.36. Normalise IN list with single element to comparison](#transform-patterns-in-list-single-element-to-comparison)
- [3.25.8.9.37. NOT AND](#transform-patterns-not-and)
- [3.25.8.9.38. NOT OR](#transform-patterns-not-or)
- [3.25.8.9.39. NULL ON NULL INPUT](#transform-patterns-null-on-null-input)
- [3.25.8.9.40. OR to IN](#transform-patterns-or-eq-to-in)
- [3.25.8.9.41. Repeated arithmetic negation](#transform-patterns-neg-neg)
- [3.25.8.9.42. Repeated bitwise negation](#transform-patterns-bit-not-bit-not)
- [3.25.8.9.43. Repeated NOT](#transform-patterns-not-not)
- [3.25.8.9.44. Simplify CASE abbreviations](#transform-patterns-simplify-case-abbreviation)
- [3.25.8.9.45. Trigonometric functions](#transform-patterns-trigonometric-functions)
- [3.25.8.9.46. Trim](#transform-patterns-trim)
- [3.25.8.9.47. Trivial bitwise operations](#transform-patterns-trivial-bitwise-operations)
- [3.25.8.9.48. Trivial CASE abbreviations](#transform-patterns-trivial-case-abbreviations)
- [3.25.8.9.49. Trivial predicates](#transform-patterns-trivial-predicates)
- [3.25.8.9.50. Unnecessary DISTINCT](#transform-patterns-unnecessary-distinct)
- [3.25.8.9.51. Unnecessary EXISTS subquery clauses](#transform-patterns-unnecessary-exists-subquery-clauses)
- [3.25.8.9.52. Unnecessary GROUP BY expressions](#transform-patterns-unnecessary-group-by-expressions)
- [3.25.8.9.53. Unnecessary INNER JOIN](#transform-patterns-unnecessary-inner-join)
- [3.25.8.9.54. Unnecessary ORDER BY expressions](#transform-patterns-unnecessary-order-by-expressions)
- [3.25.8.9.55. Unnecessary scalar subquery](#transform-patterns-unnecessary-scalar-subquery)
- [3.25.8.9.56. Unreachable CASE clauses](#transform-patterns-unreachable-case-clauses)
- [3.25.8.9.57. Unreachable DECODE clauses](#transform-patterns-unreachable-decode-clauses)
- [3.25.9. Custom SQL transformation with VisitListener](#custom-sql-transformation)
- [3.25.9.1. Example: Logging abbreviated bind values](#transformation-bind-value-abbreviation)
- [3.25.10. Policies](#policies)
- [3.25.10.1. Configuration](#policies-configuration)
- [3.25.10.2. Implementation](#policies-implementation)
- [3.25.10.3. Inheritance](#policies-inheritance)
- [3.25.10.4. Security considerations](#policies-security)
- [3.26. Zero-based vs one-based APIs](#zero-based-vs-one-based)
- [3.27. SQL building in Kotlin](#kotlin-sql-building)
- [3.27.1. Kotlin MULTISET Collectors](#kotlin-multiset-collectors)
- [3.27.2. Kotlin ResultQuery Collectors](#kotlin-resultquery-collectors)
- [3.27.3. Kotlin BOOLEAN value expressions](#kotlin-boolean-fields)
- [3.27.4. Kotlin ARRAY access](#kotlin-array-access)
- [3.27.5. Kotlin JSON access](#kotlin-json-access)
- [3.27.6. Kotlin coroutine support](#kotlin-coroutines)
- [3.28. SQL building in Scala](#scala-sql-building)
- [3.29. Compile time validation](#checker-framework)
- [4. SQL execution](#sql-execution)
- [4.1. Comparison between jOOQ and JDBC](#comparison-with-jdbc)
- [4.2. Query vs. ResultQuery](#query-vs-resultquery)
- [4.3. Fetching](#fetching)
- [4.3.1. Record vs. TableRecord](#record-vs-tablerecord)
- [4.3.2. Record1 to Record22](#record-n)
- [4.3.3. Arrays, Maps and Lists](#arrays-maps-and-lists)
- [4.3.4. ResultQuery as Iterable](#iterable-fetching)
- [4.3.5. RecordMapper](#recordmapper)
- [4.3.6. POJOs](#pojos)
- [4.3.7. RecordMapperProvider](#recordmapperprovider)
- [4.3.8. Ad-hoc Converter](#ad-hoc-converter)
- [4.3.9. ConverterProvider](#converter-provider)
- [4.3.10. Lazy fetching](#lazy-fetching)
- [4.3.11. Lazy fetching with Streams](#lazy-fetching-with-streams)
- [4.3.12. Many fetching](#many-fetching)
- [4.3.13. Later fetching](#later-fetching)
- [4.3.14. Reactive Fetching](#reactive-fetching)
- [4.3.15. ResultSet fetching](#resultset-fetching)
- [4.3.16. Auto data type conversion](#auto-type-conversion)
- [4.3.17. Custom data type conversion](#data-type-conversion)
- [4.3.18. Data type lookups](#data-type-lookups)
- [4.3.19. Context Converter](#context-converter)
- [4.4. Static statements vs. Prepared Statements](#statement-type)
- [4.5. Reusing a Query's PreparedStatement](#reusing-statements)
- [4.6. JDBC flags](#jdbc-flags)
- [4.7. Using JDBC batch operations](#batch-execution)
- [4.8. Sequence execution](#sequence-execution)
- [4.9. Stored procedures and functions](#stored-procedures)
- [4.9.1. Oracle Packages](#oracle-packages)
- [4.9.2. Oracle member procedures](#oracle-member-procedures)
- [4.10. Exporting to XML, CSV, JSON, HTML, Text, Charts](#exporting)
- [4.10.1. Exporting XML](#exporting-xml)
- [4.10.2. Exporting CSV](#exporting-csv)
- [4.10.3. Exporting JSON](#exporting-json)
- [4.10.4. Exporting HTML](#exporting-html)
- [4.10.5. Exporting Text](#exporting-text)
- [4.10.6. Exporting Charts](#exporting-charts)
- [4.10.7. FormattingProvider](#formatting-provider)
- [4.11. Importing data](#importing)
- [4.11.1. The Loader API](#importing-api)
- [4.11.2. Import options](#importing-options)
- [4.11.2.1. Throttling](#importing-option-throttling)
- [4.11.2.2. Duplicate handling](#import-option-duplicates)
- [4.11.2.3. Error handling](#import-option-errors)
- [4.11.3. Import data sources](#importing-sources)
- [4.11.3.1. Importing CSV](#importing-source-csv)
- [4.11.3.2. Importing JSON](#importing-source-json)
- [4.11.3.3. Importing records](#importing-source-records)
- [4.11.3.4. Importing arrays](#importing-source-arrays)
- [4.11.3.5. Importing XML](#importing-source-xml)
- [4.11.4. Import listeners](#importing-listeners)
- [4.11.5. Import result and error handling](#importing-results)
- [4.12. Creating results and records in memory](#in-memory-results)
- [4.12.1. Creating new results and records](#in-memory-new-result)
- [4.12.2. Reading CSV](#in-memory-csv)
- [4.12.3. Reading JSON](#in-memory-json)
- [4.12.4. Reading XML](#in-memory-xml)
- [4.12.5. Reading HTML](#in-memory-html)
- [4.12.6. Reading text](#in-memory-text)
- [4.12.7. Reading string arrays](#in-memory-string-data)
- [4.12.8. Working with in-memory results](#in-memory-usage)
- [4.13. UpdatableRecords](#crud-with-updatablerecords)
- [4.13.1. Simple CRUD](#simple-crud)
- [4.13.1.1. CRUD: Store](#crud-store)
- [4.13.1.2. CRUD: Insert](#crud-insert)
- [4.13.1.3. CRUD: Update](#crud-update)
- [4.13.1.4. CRUD: Delete](#crud-delete)
- [4.13.1.5. CRUD: Merge](#crud-merge)
- [4.13.1.6. CRUD: Refresh](#crud-refresh)
- [4.13.2. Record internal flags and dirty tracking](#internal-flags)
- [4.13.3. IDENTITY values](#identity-values)
- [4.13.4. Navigation methods](#navigation-methods)
- [4.13.5. Non-updatable records](#non-updatable-records)
- [4.13.6. Optimistic locking](#optimistic-locking)
- [4.13.7. Batch execution](#batch-execution-for-crud)
- [4.13.8. RecordListener SPI](#crud-record-listener)
- [4.14. DAOs](#daos)
- [4.15. Transaction management](#transaction-management)
- [4.16. Exception handling](#exception-handling)
- [4.17. ExecuteListeners](#execute-listeners)
- [4.18. Database meta data](#meta-data)
- [4.18.1. JDBC meta data](#meta-jdbc)
- [4.18.2. Interpreted meta data](#meta-interpreter)
- [4.18.3. XML meta data](#meta-xml)
- [4.18.4. Generated meta data](#meta-generated)
- [4.19. JDBC Connection](#jdbc-connection)
- [4.20. Batched Connection](#batched-connection)
- [4.21. Mocking Connection](#mocking-connection)
- [4.22. Mock File Database](#mock-file-database)
- [4.23. Parsing Connection](#parsing-connection)
- [4.24. Diagnostics](#diagnostics)
- [4.24.1. Too Many Rows](#diagnostics-too-many-rows)
- [4.24.2. Too Many Columns](#diagnostics-too-many-columns)
- [4.24.3. Duplicate Statements](#diagnostics-duplicate-statements)
- [4.24.4. Repeated statements](#diagnostics-repeated-statements)
- [4.24.5. Consecutive aggregation](#diagnostics-consecutive-aggregation)
- [4.24.6. WasNull calls](#diagnostics-was-null-calls)
- [4.24.7. Concatenation in predicates](#diagnostics-concatenation-in-predicates)
- [4.24.8. Possibly wrong expressions](#diagnostics-possibly-wrong-expressions)
- [4.24.9. Incomplete equi join](#diagnostics-incomplete-equi-join)
- [4.24.10. Trivial condition](#diagnostics-trivial-condition)
- [4.24.11. Transform patterns](#diagnostics-transform-patterns)
- [4.25. Logging with LoggerListener](#logging)
- [4.26. Logging with SQLExceptionLoggerListener](#logging-sql-exceptions)
- [4.27. Logging Connection](#logging-connection)
- [4.28. Logging system properties](#logging-system-properties)
- [4.29. Performance considerations](#performance-considerations)
- [4.30. Alternative execution models](#alternative-execution-models)
- [4.30.1. Using jOOQ with Spring's JdbcTemplate](#using-jooq-with-jdbctemplate)
- [4.30.2. Using jOOQ with JPA](#using-jooq-with-jpa)
- [4.30.2.1. Using jOOQ with JPA Native Query](#using-jooq-with-jpa-native)
- [4.30.2.2. Using jOOQ with JPA entities](#using-jooq-with-jpa-entities)
- [4.30.2.3. Using jOOQ with JPA EntityResult](#using-jooq-with-jpa-entityresult)
- [5. Code generation](#code-generation)
- [5.1. Configuration and setup of the generator](#codegen-configuration)
- [5.2. Advanced generator configuration](#codegen-advanced)
- [5.2.1. Logging](#codegen-config-logging)
- [5.2.2. Error handling](#codegen-config-onerror)
- [5.2.3. Jdbc](#codegen-config-jdbc)
- [5.2.4. Generator](#codegen-config-generator)
- [5.2.5. Database](#codegen-config-database)
- [5.2.5.1. Database name and properties](#codegen-database-name)
- [5.2.5.2. Inline database implementation](#codegen-database-inline-implementation)
- [5.2.5.3. RegexFlags](#codegen-database-regex-flags)
- [5.2.5.4. Includes and Excludes](#codegen-database-includes-excludes)
- [5.2.5.5. Include object types](#codegen-database-include-object-types)
- [5.2.5.6. Record Version and Timestamp Fields](#codegen-database-record-version-timestamp-fields)
- [5.2.5.7. Comments](#codegen-database-comments)
- [5.2.5.8. Synthetic objects](#codegen-database-synthetic-objects)
- [5.2.5.8.1. Synthetic columns](#codegen-database-synthetic-columns)
- [5.2.5.8.2. Synthetic readonly columns](#codegen-database-synthetic-readonly-columns)
- [5.2.5.8.3. Synthetic readonly ROWIDs](#codegen-database-synthetic-readonly-rowids)
- [5.2.5.8.4. Synthetic identities](#codegen-database-synthetic-ids)
- [5.2.5.8.5. Synthetic defaults](#codegen-database-synthetic-defaults)
- [5.2.5.8.6. Synthetic enums](#codegen-database-synthetic-enums)
- [5.2.5.8.7. Synthetic primary keys](#codegen-database-synthetic-pks)
- [5.2.5.8.8. Synthetic unique keys](#codegen-database-synthetic-uks)
- [5.2.5.8.9. Synthetic foreign keys](#codegen-database-synthetic-fks)
- [5.2.5.8.10. Synthetic synonyms](#codegen-database-synthetic-synonyms)
- [5.2.5.9. Date as timestamp](#codegen-database-date-as-timestamp)
- [5.2.5.10. Ignore procedure return values (deprecated)](#codegen-database-ignore-procedure-return-values)
- [5.2.5.11. Hidden columns](#codegen-database-hidden-columns)
- [5.2.5.12. Redacted columns](#codegen-database-redacted-columns)
- [5.2.5.13. Readonly columns](#codegen-database-readonly-columns)
- [5.2.5.14. Unsigned types](#codegen-database-unsigned-types)
- [5.2.5.15. Catalog and schema mapping](#codegen-database-catalog-and-schema-mapping)
- [5.2.5.16. Catalog and schema version providers](#codegen-database-version-providers)
- [5.2.5.17. Custom ordering of generated code](#codegen-database-order-provider)
- [5.2.5.18. Forced types](#codegen-database-forced-types)
- [5.2.5.18.1. Matching of forced types](#codegen-database-forced-types-matching)
- [5.2.5.18.2. Data type rewriting](#codegen-database-forced-types-rewrite)
- [5.2.5.18.3. Qualified converters](#codegen-database-forced-types-converter)
- [5.2.5.18.4. Inline converters](#codegen-database-forced-types-inline-converter)
- [5.2.5.18.5. Lambda converters](#codegen-database-forced-types-lambda-converter)
- [5.2.5.18.6. Auto converters](#codegen-database-forced-types-auto-converter)
- [5.2.5.18.7. Enum converters](#codegen-database-forced-types-enum-converter)
- [5.2.5.18.8. Jackson converters](#codegen-database-forced-types-jackson-converter)
- [5.2.5.18.9. JAXB converters](#codegen-database-forced-types-jaxb-converter)
- [5.2.5.18.10. Data type bindings](#codegen-database-forced-types-binding)
- [5.2.5.18.11. Client side computed columns](#codegen-database-forced-types-computed)
- [5.2.5.18.12. Audit columns](#codegen-database-forced-types-audit)
- [5.2.5.18.13. Hidden columns](#codegen-database-forced-types-hidden)
- [5.2.5.18.14. Redacted columns](#codegen-database-forced-types-redacted)
- [5.2.5.18.15. Visibility Modifier (per forced type)](#codegen-database-forced-types-visibility)
- [5.2.5.19. Table valued functions](#codegen-database-table-valued-functions)
- [5.2.6. Generate](#codegen-config-generate)
- [5.2.6.1. Annotations](#codegen-generate-annotations)
- [5.2.6.2. Covariant overrides](#codegen-covariant-overrides)
- [5.2.6.2.1. Overriding as()](#codegen-covariant-overrides-as)
- [5.2.6.2.2. Overriding rename()](#codegen-covariant-overrides-rename)
- [5.2.6.2.3. Overriding where()](#codegen-covariant-overrides-where)
- [5.2.6.3. Default catalog and schema](#codegen-generate-default-catalog-and-schema)
- [5.2.6.4. Explicit nullability](#codegen-generate-explicit-nullability)
- [5.2.6.5. Extended types](#codegen-generate-extended-types)
- [5.2.6.6. Fluent setters](#codegen-generate-fluent-setters)
- [5.2.6.7. Fully Qualified Types](#codegen-generate-fully-qualified-types)
- [5.2.6.8. Global Artefacts](#codegen-generate-globals)
- [5.2.6.9. Global object names](#codegen-generate-global-object-names)
- [5.2.6.10. Implicit JOIN paths](#codegen-implicit-join-paths)
- [5.2.6.11. Java Time Types](#codegen-generate-java-time-types)
- [5.2.6.12. Serial Version UID](#codegen-generate-serial-version-uid)
- [5.2.6.13. Sources](#codegen-generate-sources)
- [5.2.6.14. Text blocks](#codegen-textblocks)
- [5.2.6.15. Visibility Modifier (global)](#codegen-generate-visibility-modifier)
- [5.2.6.16. Whitespace (newlines and indentation)](#codegen-whitespace)
- [5.2.6.17. Zero Scale Decimal Types](#codegen-generate-zero-scale-decimals)
- [5.2.7. Output target configuration](#codegen-config-target)
- [5.3. Generated object types](#codegen-object-types)
- [5.3.1. Generated tables](#codegen-tables)
- [5.3.2. Generated records](#codegen-records)
- [5.3.3. Generated POJOs](#codegen-pojos)
- [5.3.4. Generated Interfaces](#codegen-interfaces)
- [5.3.5. Generated DAOs](#codegen-daos)
- [5.3.6. Generated sequences](#codegen-sequences)
- [5.3.7. Generated synonyms](#codegen-synonyms)
- [5.3.8. Generated procedures](#codegen-procedures)
- [5.3.9. Generated domains](#codegen-domains)
- [5.3.10. Generated UDTs](#codegen-udts)
- [5.3.11. Generated triggers](#codegen-triggers)
- [5.3.12. Generated global artefacts](#codegen-globals)
- [5.4. Class names, method names, identifiers](#codegen-object-naming)
- [5.4.1. Custom generator strategies](#codegen-generatorstrategy)
- [5.4.2. Matcher strategies](#codegen-matcherstrategy)
- [5.4.2.1. MatcherRule](#codegen-matcher-rule)
- [5.4.2.2. Matching catalogs](#codegen-matcher-catalogs)
- [5.4.2.3. Matching schemas](#codegen-matcher-schemas)
- [5.4.2.4. Matching tables](#codegen-matcher-tables)
- [5.4.2.5. Matching fields](#codegen-matcher-fields)
- [5.4.2.6. Matching indexes](#codegen-matcher-indexes)
- [5.4.2.7. Matching primary keys](#codegen-matcher-primary-keys)
- [5.4.2.8. Matching unique keys](#codegen-matcher-unique-keys)
- [5.4.2.9. Matching foreign keys](#codegen-matcher-foreign-keys)
- [5.4.2.10. Matching routines](#codegen-matcher-routines)
- [5.4.2.11. Matching sequences](#codegen-matcher-sequences)
- [5.4.2.12. Matching enums](#codegen-matcher-enums)
- [5.4.2.13. Matching embeddables](#codegen-matcher-embeddables)
- [5.4.2.14. Matching UDTs](#codegen-matcher-udts)
- [5.4.2.15. Matching attributes](#codegen-matcher-attributes)
- [5.4.2.16. Matcher examples](#codegen-matcher-examples)
- [5.5. Custom code sections](#codegen-custom-code)
- [5.6. Code generation extensions](#codegen-extensions)
- [5.6.1. PostgreSQL extensions](#codegen-extension-postgres)
- [5.7. Embeddable types](#codegen-embeddable-types)
- [5.7.1. Configuration](#codegen-embeddable-types-configuration)
- [5.7.2. Overlapping embeddable types](#codegen-overlapping-embeddable-types)
- [5.7.3. Field replacement](#codegen-embeddable-types-replacing-fields)
- [5.7.4. Embedded keys](#codegen-embedded-keys)
- [5.7.5. Embedded domains](#codegen-embedded-domains)
- [5.7.6. Embedded columns (auto configuration)](#codegen-embedded-columns)
- [5.8. Input catalogs and schemas](#input-schema)
- [5.9. Alternative meta data sources](#codegen-meta-sources)
- [5.9.1. JPADatabase: Code generation from entities](#codegen-jpa)
- [5.9.2. XMLDatabase: Code generation from XML files](#codegen-xml)
- [5.9.3. DDLDatabase: Code generation from SQL files](#codegen-ddl)
- [5.9.4. LiquibaseDatabase: Code generation from Liquibase XML, YAML, JSON files](#codegen-liquibase)
- [5.10. Alternative output languages](#codegen-output-languages)
- [5.10.1. XMLGenerator: Generating XML](#xmlgenerator)
- [5.10.2. KotlinGenerator](#kotlingenerator)
- [5.10.3. ScalaGenerator and Scala3Generator](#scalagenerator)
- [5.11. Code generation execution](#codegen-execution)
- [5.11.1. Running the code generator with Maven](#codegen-maven)
- [5.11.2. Running the code generator with Ant](#codegen-ant)
- [5.11.3. Running the code generator with Gradle](#codegen-gradle)
- [5.11.3.1. Multiple executions](#codegen-gradle-executions)
- [5.11.3.2. Compiler dependency](#codegen-gradle-compiler-dependency)
- [5.11.3.3. Data change management](#codegen-gradle-data-change-management)
- [5.11.4. Programmatic configuration and execution](#codegen-programmatic)
- [5.12. System properties governing code generation](#codegen-system-properties)
- [5.13. Code generation dependencies](#codegen-dependencies)
- [5.14. In-memory compilation of programmatic configuration](#codegen-in-memory-compilation)
- [5.15. Code generation for large schemas](#codegen-large-schemas)
- [5.16. Reducing the size of generated code](#codegen-reducing-generated-code-size)
- [5.17. Code generation and version control](#codegen-version-control)
- [5.18. Features requiring generated code](#codegen-feature-dependencies)
- [6. Coming from JPA](#coming-from-jpa)
- [6.1. Set based thinking](#from-jpa-set-based-thinking)
- [6.2. Database first](#from-jpa-database-first)
- [6.3. Eager or lazy loading](#from-jpa-eager-lazy)
- [6.4. First level cache and second level cache](#from-jpa-caches)
- [6.5. StatelessSession](#from-jpa-statelesssession)
- [6.6. Embeddable](#from-jpa-embeddable)
- [6.7. AttributeConverter](#from-jpa-converter)
- [6.8. User types](#from-jpa-user-type)
- [6.9. Implicit JOIN](#from-jpa-implicit-join)
- [6.10. @OneToOne or @ManyToOne](#from-jpa-manytoone)
- [6.11. @OneToMany or @ManyToMany](#from-jpa-onetomany)
- [7. What's new in version 3.22.0](#whats-new)
- [8. Reference](#reference)
- [8.1. Modules](#modules)
- [8.1.1. jOOQ module](#module-jooq)
- [8.1.2. jOOQ-beans-extensions module](#module-jooq-beans-extensions)
- [8.1.3. jOOQ-bom module](#module-jooq-bom)
- [8.1.4. jOOQ-checker module](#module-jooq-checker)
- [8.1.5. jOOQ-codegen module](#module-jooq-codegen)
- [8.1.6. jOOQ-codegen-gradle module](#module-jooq-codegen-gradle)
- [8.1.7. jOOQ-codegen-maven module](#module-jooq-codegen-maven)
- [8.1.8. jOOQ-jackson-extensions module](#module-jooq-jackson-extensions)
- [8.1.9. jOOQ-jpa-extensions module](#module-jooq-jpa-extensions)
- [8.1.10. jOOQ-kotlin module](#module-jooq-kotlin)
- [8.1.11. jOOQ-kotlin-coroutines module](#module-jooq-kotlin-coroutines)
- [8.1.12. jOOQ-meta module](#module-jooq-meta)
- [8.1.13. jOOQ-meta-extensions module](#module-jooq-meta-extensions)
- [8.1.14. jOOQ-meta-extensions-hibernate module](#module-jooq-meta-extensions-hibernate)
- [8.1.15. jOOQ-meta-extensions-liquibase module](#module-jooq-meta-extensions-liquibase)
- [8.1.16. jOOQ-meta-kotlin module](#module-jooq-meta-kotlin)
- [8.1.17. jOOQ-postgres-extensions module](#module-jooq-postgres-extensions)
- [8.1.18. jOOQ-reactor-extensions module](#module-jooq-reactor-extensions)
- [8.1.19. jOOQ-scala module](#module-jooq-scala)
- [8.1.20. jOOQ-xtend module](#module-jooq-xtend)
- [8.2. Supported RDBMS](#supported-rdbms)
- [8.3. Commercial only features](#commercial-only-features)
- [8.4. Experimental features](#experimental-features)
- [8.5. SQL to DSL mapping rules](#dsl-mapping-rules)
- [8.6. Quality Assurance](#quality-assurance)
- [8.7. Security](#security)
- [8.7.1. SQL Injection](#security-sql-injection)
- [8.7.2. Debug logging](#security-debug-logging)
- [8.7.3. Redacted columns](#security-redacted-columns)
- [8.7.4. Exception message](#security-exception-message)
- [8.7.5. Contact](#security-contact)
- [8.8. Migrating to jOOQ 3.0](#migrating-to-3.0)
- [8.9. Don't do this](#dont-do-this)
- [8.9.1. jOOQ: Implementing the DSL types](#dont-do-this-jooq-implement-dsl)
- [8.9.2. jOOQ: Referencing the Step types](#dont-do-this-jooq-step-types)
- [8.9.3. Schema: NULL columns](#dont-do-this-null-columns)
- [8.9.4. Schema: Unnamed constraints](#dont-do-this-schema-unnamed-constraints)
- [8.9.5. Schema: Unnecessary surrogate keys](#dont-do-this-schema-surrogate-keys)
- [8.9.6. Schema: Wrong data types](#dont-do-this-wrong-datatypes)
- [8.9.7. SQL: COUNT(*) instead of EXISTS()](#dont-do-this-count-instead-of-exists)
- [8.9.8. SQL: N+1](#dont-do-this-n-plus-one)
- [8.9.9. SQL: NATURAL JOIN or JOIN USING](#dont-do-this-natural-join-or-using)
- [8.9.10. SQL: NOT IN predicate](#dont-do-this-sql-not-in)
- [8.9.11. SQL: ORDER BY \[column index\]](#dont-do-this-order-by-column-index)
- [8.9.12. SQL: Rely on implicit ordering](#dont-do-this-rely-on-implicit-ordering)
- [8.9.13. SQL: SELECT *](#dont-do-this-sql-select-all)
- [8.9.14. SQL: SELECT DISTINCT](#dont-do-this-sql-select-distinct)
- [8.9.15. SQL: Unnecessary UNION instead of UNION ALL](#dont-do-this-unnecessary-union)
- [8.10. The most important jOOQ types](#reference-types)
- [8.11. Credits](#reference-credits)
bindValues = query.getBindValues();
```
The SQL string built with the jOOQ query DSL can then be executed using JDBC directly, using Spring's JdbcTemplate, using Apache DbUtils and many other tools (note that since jOOQ uses [`java.sql.PreparedStatement`](https://docs.oracle.com/en/java/javase/25/docs/api/java.sql/java/sql/PreparedStatement.html) by default, this will generate a bind variable for "1948". [Read more about bind variables here](#bind-values)).
You can also avoid getting the SQL string and bind values separately:
```java
String sql = query.getSQL(ParamType.INLINED);
```
If you wish to use jOOQ only as a SQL builder, the following sections of the manual will be of interest to you:
- [SQL building](#sql-building): This section contains a lot of information about creating SQL statements using the jOOQ API
- [Plain SQL](#plain-sql): This section contains information useful in particular to those that want to supply [table expressions](#table-expressions), [column expressions](#column-expressions), etc. as plain SQL to jOOQ, rather than through generated artefacts
- [Bind values](#bind-values): This section explains how bind values are managed and/or inlined in jOOQ.
bindValues = query.getBindValues();
```
The SQL string built with the jOOQ query DSL can then be executed using JDBC directly, using Spring's JdbcTemplate, using Apache DbUtils and many other tools (note that since jOOQ uses [`java.sql.PreparedStatement`](https://docs.oracle.com/en/java/javase/25/docs/api/java.sql/java/sql/PreparedStatement.html) by default, this will generate a bind variable for "1948". [Read more about bind variables here](#bind-values)).
You can also avoid getting the SQL string and bind values separately:
```java
String sql = query.getSQL(ParamType.INLINED);
```
If you wish to use jOOQ only as a SQL builder with code generation, the following sections of the manual will be of interest to you:
- [SQL building](#sql-building): This section contains a lot of information about creating SQL statements using the jOOQ API
- [Code generation](#code-generation): This section contains the necessary information to run jOOQ's code generator against your developer database
- [Bind values](#bind-values): This section explains how bind values are managed and/or inlined in jOOQ.
> result =
create.select(BOOK.TITLE, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.from(BOOK)
.join(AUTHOR)
.on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
.where(BOOK.PUBLISHED_IN.eq(1948))
.fetch();
```
By having jOOQ execute your SQL, the jOOQ query DSL becomes truly embedded SQL.
jOOQ doesn't stop here, though! You can execute any SQL with jOOQ. In other words, you can use any other SQL building tool and run the SQL statements with jOOQ. An example is given here:
```java
// Use your favourite tool to construct SQL strings:
String sql = "SELECT title, first_name, last_name FROM book JOIN author ON book.author_id = author.id " +
"WHERE book.published_in = 1984";
// Fetch results using jOOQ
Result result = create.fetch(sql);
// Or execute that SQL with JDBC, fetching the ResultSet with jOOQ:
ResultSet rs = connection.createStatement().executeQuery(sql);
Result result = create.fetch(rs);
```
If you wish to use jOOQ as a SQL executor with (or without) code generation, the following sections of the manual will be of interest to you:
- [SQL building](#sql-building): This section contains a lot of information about creating SQL statements using the jOOQ API
- [Code generation](#code-generation): This section contains the necessary information to run jOOQ's code generator against your developer database
- [SQL execution](#sql-execution): This section contains a lot of information about executing SQL statements using the jOOQ API
- [Fetching](#fetching): This section contains some useful information about the various ways of fetching data with jOOQ
jooq-pro
[your licensee email]
[your license key]
```
#### pom.xml
```xml
central
https://repo1.maven.org/maven2/
jooq-pro
https://repo.jooq.org/repo
central
https://repo1.maven.org/maven2/
jooq-pro
https://repo.jooq.org/repo
```
**Gradle (Kotlin):**
#### settings.gradle.kts
```kotlin
pluginManagement {
repositories {
mavenCentral()
// Other repositories...
maven {
url = uri("https://repo.jooq.org/repo")
credentials {
username = ""
password = ""
}
content {
includeGroupByRegex("org\\.jooq\\..*")
}
}
}
}
```
#### build.gradle.kts
```kotlin
repositories {
mavenCentral()
// Other repositories...
maven {
url = uri("https://repo.jooq.org/repo")
credentials {
username = ""
password = ""
}
content {
includeGroupByRegex("org\\.jooq\\..*")
}
}
}
```
**Gradle (Groovy):**
#### settings.gradle
```groovy
pluginManagement {
repositories {
mavenCentral()
// Other repositories...
maven {
url "https://repo.jooq.org/repo"
credentials {
username ""
password ""
}
}
content {
includeGroupByRegex "org\\.jooq\\..*"
}
}
}
```
#### build.gradle
```groovy
repositories {
mavenCentral()
// Other repositories...
maven {
url "https://repo.jooq.org/repo"
credentials {
username ""
password ""
}
content {
includeGroupByRegex "org\\.jooq\\..*"
}
}
}
```
#### Dependencies
Depending on the edition you're using, please declare the following dependencies in Maven or Gradle:
**Maven:**
```xml
org.jooq
jooq
3.22.0-SNAPSHOT
```
**Gradle (Kotlin):**
```kotlin
dependencies {
// Use org.jooq for the Open Source Edition
// org.jooq.pro for commercial editions with Java 25 support,
// org.jooq.pro-java-21 for commercial editions with Java 21 support,
// org.jooq.pro-java-17 for commercial editions with Java 17 support,
// org.jooq.pro-java-11 for commercial editions with Java 11 support,
// org.jooq.pro-java-8 for commercial editions with Java 8 support,
// org.jooq.trial for the free trial edition with Java 25 support,
// org.jooq.trial-java-21 for the free trial edition with Java 21 support,
// org.jooq.trial-java-17 for the free trial edition with Java 17 support,
// org.jooq.trial-java-11 for the free trial edition with Java 11 support,
// org.jooq.trial-java-8 for the free trial edition with Java 8 support
//
// Note: Only the Open Source Edition is hosted on Maven Central.
// Install the others locally using the provided scripts, or access them from here: https://repo.jooq.org
// See the JDK version support matrix here: https://www.jooq.org/download/support-matrix-jdk
implementation("org.jooq:jooq:3.22.0-SNAPSHOT")
}
```
**Gradle (Groovy):**
```groovy
dependencies {
// Use org.jooq for the Open Source Edition
// org.jooq.pro for commercial editions with Java 25 support,
// org.jooq.pro-java-21 for commercial editions with Java 21 support,
// org.jooq.pro-java-17 for commercial editions with Java 17 support,
// org.jooq.pro-java-11 for commercial editions with Java 11 support,
// org.jooq.pro-java-8 for commercial editions with Java 8 support,
// org.jooq.trial for the free trial edition with Java 25 support,
// org.jooq.trial-java-21 for the free trial edition with Java 21 support,
// org.jooq.trial-java-17 for the free trial edition with Java 17 support,
// org.jooq.trial-java-11 for the free trial edition with Java 11 support,
// org.jooq.trial-java-8 for the free trial edition with Java 8 support
//
// Note: Only the Open Source Edition is hosted on Maven Central.
// Install the others locally using the provided scripts, or access them from here: https://repo.jooq.org
// See the JDK version support matrix here: https://www.jooq.org/download/support-matrix-jdk
implementation "org.jooq:jooq:3.22.0-SNAPSHOT"
}
```
org.jooq
jooq
3.22.0-SNAPSHOT
org.jooq
jooq-meta
3.22.0-SNAPSHOT
org.jooq
jooq-codegen
3.22.0-SNAPSHOT
```
###### Commercial Editions (Java 17+)
```xml
org.jooq.pro
jooq
3.22.0-SNAPSHOT
org.jooq.pro
jooq-meta
3.22.0-SNAPSHOT
org.jooq.pro
jooq-codegen
3.22.0-SNAPSHOT
```
###### Commercial Editions (Java 11+)
```xml
org.jooq.pro-java-11
jooq
3.22.0-SNAPSHOT
org.jooq.pro-java-11
jooq-meta
3.22.0-SNAPSHOT
org.jooq.pro-java-11
jooq-codegen
3.22.0-SNAPSHOT
```
###### Commercial Editions (Java 8+)
```xml
org.jooq.pro-java-8
jooq
3.22.0-SNAPSHOT
org.jooq.pro-java-8
jooq-meta
3.22.0-SNAPSHOT
org.jooq.pro-java-8
jooq-codegen
3.22.0-SNAPSHOT
```
###### Commercial Editions (Free Trial, Java 17+)
```xml
org.jooq.trial
jooq
3.22.0-SNAPSHOT
org.jooq.trial
jooq-meta
3.22.0-SNAPSHOT
org.jooq.trial
jooq-codegen
3.22.0-SNAPSHOT
```
###### Commercial Editions (Free Trial, Java 11+)
```xml
org.jooq.trial-java-11
jooq
3.22.0-SNAPSHOT
org.jooq.trial-java-11
jooq-meta
3.22.0-SNAPSHOT
org.jooq.trial-java-11
jooq-codegen
3.22.0-SNAPSHOT
```
###### Commercial Editions (Free Trial, Java 8+)
```xml
org.jooq.trial-java-8
jooq
3.22.0-SNAPSHOT
org.jooq.trial-java-8
jooq-meta
3.22.0-SNAPSHOT
org.jooq.trial-java-8
jooq-codegen
3.22.0-SNAPSHOT
```
###### Commercial Editions (Java 6+)
```xml
org.jooq.pro-java-6
jooq
3.22.0-SNAPSHOT
org.jooq.pro-java-6
jooq-meta
3.22.0-SNAPSHOT
org.jooq.pro-java-6
jooq-codegen
3.22.0-SNAPSHOT
```
Note that only the jOOQ Open Source Edition is available from Maven Central. If you're using the jOOQ Professional Edition or the jOOQ Enterprise Edition, you will have to manually install jOOQ in your local Nexus, or in your local Maven cache. For more information, please refer to the [licensing pages](https://www.jooq.org/licensing).
Please refer to the manual's section about [Code generation configuration](#codegen-configuration) to learn how to use jOOQ's code generator with Maven.
For this example, we'll be using MySQL. If you haven't already downloaded MySQL Connector/J, download it here:
[https://dev.mysql.com/downloads/connector/j/](https://dev.mysql.com/downloads/connector/j/)
If you don't have a MySQL instance up and running yet, get it from [https://www.mysql.com](https://www.mysql.com/) or [https://hub.docker.com/_/mysql](https://hub.docker.com/_/mysql) now!
com.mysql.cj.jdbc.Driver
jdbc:mysql://localhost:3306/library
root
org.jooq.codegen.JavaGenerator
org.jooq.meta.mysql.MySQLDatabase
library
.*
test.generated
C:/workspace/MySQLTest/src/main/java
```
Replace the username (` result = create.select().from(AUTHOR).fetch();
```
First get an instance of `DSLContext` so we can write a simple `SELECT` query. We pass an instance of the MySQL connection to `DSL`. Note that the DSLContext doesn't close the connection. We'll have to do that ourselves.
We then use jOOQ's query DSL to return an instance of Result. We'll be using this result in the next step.
result = create.select().from(AUTHOR).fetch();
for (Record r : result) {
Integer id = r.getValue(AUTHOR.ID);
String firstName = r.getValue(AUTHOR.FIRST_NAME);
String lastName = r.getValue(AUTHOR.LAST_NAME);
System.out.println("ID: " + id + " first name: " + firstName + " last name: " + lastName);
}
}
// For the sake of this tutorial, let's keep exception handling simple
catch (Exception e) {
e.printStackTrace();
}
}
}
```
jdbc:h2:~/flyway-test
sa
```
##### 0. Maven Project Configuration - Dependencies
While jOOQ and Flyway could be used in standalone migration scripts, in this tutorial, we'll be using Maven for the standard project setup.
These are the dependencies that we're using in our Maven configuration:
```xml
org.jooq
jooq
3.22.0-SNAPSHOT
com.h2database
h2
1.4.197
org.apache.logging.log4j
log4j-slf4j-impl
2.11.0
junit
junit
4.11
test
```
##### 0. Maven Project Configuration - Plugins
After the dependencies, let's simply add the Flyway and jOOQ Maven plugins like so. The Flyway plugin:
```xml
org.flywaydb
flyway-maven-plugin
3.0
generate-sources
migrate
${db.url}
${db.username}
filesystem:src/main/resources/db/migration
```
The above Flyway Maven plugin configuration will read and execute all database migration scripts from `src/main/resources/db/migration` prior to compiling Java source code. While the official Flyway documentation may suggest that migrations be done in the `compile` phase, the jOOQ code generator relies on such migrations having been done *prior* to code generation.
After the Flyway plugin, we'll add the jOOQ Maven Plugin. For more details, please refer to the [manual's section about the code generation configuration](#codegen-configuration).
```xml
org.jooq
jooq-codegen-maven
${org.jooq.version}
generate-sources
generate
${db.url}
${db.username}
.*
FLYWAY_TEST
org.jooq.example.flyway.db.h2
target/generated-sources/jooq-h2
```
This configuration will now read the `FLYWAY_TEST` schema and reverse-engineer it into the `target/generated-sources/jooq-h2` directory, and within that, into the `org.jooq.example.flyway.db.h2` package.
##### 1. Database increments
Now, when we start developing our database. For that, we'll create database increment scripts, which we put into the `src/main/resources/db/migration` directory, as previously configured for the Flyway plugin. We'll add these files:
- V1__initialise_database.sql
- V2__create_author_table.sql
- V3__create_book_table_and_records.sql
These three scripts model our schema versions 1-3 (note the capital V!). Here are the scripts' contents
```sql
-- V1__initialise_database.sql
DROP SCHEMA flyway_test IF EXISTS;
CREATE SCHEMA flyway_test;
```
```sql
-- V2__create_author_table.sql
CREATE SEQUENCE flyway_test.s_author_id START WITH 1;
CREATE TABLE flyway_test.author (
id INT NOT NULL,
first_name VARCHAR(50),
last_name VARCHAR(50) NOT NULL,
date_of_birth DATE,
year_of_birth INT,
address VARCHAR(50),
CONSTRAINT pk_author PRIMARY KEY (ID)
);
```
```sql
-- V3__create_book_table_and_records.sql
CREATE TABLE flyway_test.book (
id INT NOT NULL,
author_id INT NOT NULL,
title VARCHAR(400) NOT NULL,
CONSTRAINT pk_book PRIMARY KEY (id),
CONSTRAINT fk_book_author_id FOREIGN KEY (author_id) REFERENCES flyway_test.author(id)
);
INSERT INTO flyway_test.author VALUES (next value for flyway_test.s_author_id, 'George', 'Orwell', '1903-06-25', 1903, null);
INSERT INTO flyway_test.author VALUES (next value for flyway_test.s_author_id, 'Paulo', 'Coelho', '1947-08-24', 1947, null);
INSERT INTO flyway_test.book VALUES (1, 1, '1984');
INSERT INTO flyway_test.book VALUES (2, 1, 'Animal Farm');
INSERT INTO flyway_test.book VALUES (3, 2, 'O Alquimista');
INSERT INTO flyway_test.book VALUES (4, 2, 'Brida');
```
##### 2. Database migration and 3. Code regeneration
The above three scripts are picked up by Flyway and executed in the order of the versions. This can be seen very simply by executing:
```text
mvn clean install
```
And then observing the log output from Flyway...
```text
[INFO] --- flyway-maven-plugin:3.0:migrate (default) @ jooq-flyway-example ---
[INFO] Database: jdbc:h2:~/flyway-test (H2 1.4)
[INFO] Validated 3 migrations (execution time 00:00.004s)
[INFO] Creating Metadata table: "PUBLIC"."schema_version"
[INFO] Current version of schema "PUBLIC": << Empty Schema >>
[INFO] Migrating schema "PUBLIC" to version 1
[INFO] Migrating schema "PUBLIC" to version 2
[INFO] Migrating schema "PUBLIC" to version 3
[INFO] Successfully applied 3 migrations to schema "PUBLIC" (execution time 00:00.073s).
```
... and from jOOQ on the console:
```text
[INFO] --- jooq-codegen-maven:3.22.0-SNAPSHOT:generate (default) @ jooq-flyway-example ---
[INFO] --- jooq-codegen-maven:3.22.0-SNAPSHOT:generate (default) @ jooq-flyway-example ---
[INFO] Using this configuration:
...
[INFO] Generating schemata : Total: 1
[INFO] Generating schema : FlywayTest.java
[INFO] ----------------------------------------------------------
[....]
[INFO] GENERATION FINISHED! : Total: 337.576ms, +4.299ms
```
##### 4. Development
Note that all of the previous steps are executed automatically, every time someone adds new migration scripts to the Maven module. For instance, a team member might have committed a new migration script, you check it out, rebuild and get the latest jOOQ-generated sources for your own development or integration-test database.
Now, that these steps are done, you can proceed writing your database queries. Imagine the following test case
```java
import org.jooq.Result;
import org.jooq.impl.DSL;
import org.junit.Test;
import java.sql.DriverManager;
import static java.util.Arrays.asList;
import static org.jooq.example.flyway.db.h2.Tables.*;
import static org.junit.Assert.assertEquals;
public class AfterMigrationTest {
@Test
public void testQueryingAfterMigration() throws Exception {
try (Connection c = DriverManager.getConnection("jdbc:h2:~/flyway-test", "sa", "")) {
Result result =
DSL.using(c)
.select(
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
BOOK.ID,
BOOK.TITLE
)
.from(AUTHOR)
.join(BOOK)
.on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
.orderBy(BOOK.ID.asc())
.fetch();
assertEquals(4, result.size());
assertEquals(asList(1, 2, 3, 4), result.getValues(BOOK.ID));
}
}
}
```
##### Reiterate
The power of this approach becomes clear once you start performing database modifications this way. Let's assume that the French guy on our team prefers to have things his way:
```sql
-- V4__le_french.sql
ALTER TABLE flyway_test.book ALTER COLUMN title RENAME TO le_titre;
```
They check it in, you check out the new database migration script, run
```text
mvn clean install
```
And then observing the log output:
```text
[INFO] --- flyway-maven-plugin:3.0:migrate (default) @ jooq-flyway-example ---
[INFO] --- flyway-maven-plugin:3.0:migrate (default) @ jooq-flyway-example ---
[INFO] Database: jdbc:h2:~/flyway-test (H2 1.4)
[INFO] Validated 4 migrations (execution time 00:00.005s)
[INFO] Current version of schema "PUBLIC": 3
[INFO] Migrating schema "PUBLIC" to version 4
[INFO] Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.016s).
```
So far so good, but later on:
```text
[ERROR] COMPILATION ERROR :
[INFO] -------------------------------------------------------------
[ERROR] C:\...\jOOQ-flyway-example\src\test\java\AfterMigrationTest.java:[24,19] error: cannot find symbol
[INFO] 1 error
```
When we go back to our Java integration test, we can immediately see that the TITLE column is still being referenced, but it no longer exists:
```java
public class AfterMigrationTest {
@Test
public void testQueryingAfterMigration() throws Exception {
try (Connection c = DriverManager.getConnection("jdbc:h2:~/flyway-test", "sa", "")) {
Result result =
DSL.using(c)
.select(
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
BOOK.ID,
BOOK.TITLE
// ^^^^^ This column no longer exists. We'll have to rename it to LE_TITRE
)
.from(AUTHOR)
.join(BOOK)
.on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
.orderBy(BOOK.ID.asc())
.fetch();
assertEquals(4, result.size());
assertEquals(asList(1, 2, 3, 4), result.getValues(BOOK.ID));
}
}
}
```
##### Automation
The above steps can be automated in your build using another third party called [testcontainers](https://www.testcontainers.org). Please look at this article here for examples on how to do that: [https://blog.jooq.org/using-testcontainers-to-generate-jooq-code/](https://blog.jooq.org/using-testcontainers-to-generate-jooq-code/)
##### Conclusion
This tutorial shows very easily how you can build a rock-solid development process using Flyway and jOOQ to prevent SQL-related errors very early in your development lifecycle - immediately at compile time, rather than in production!
Please, visit the [Flyway website](https://flywaydb.org/) for more information about Flyway.
codegen@jooq db.xml
```
) {
val properties = Properties();
properties.load(Properties::class.java.getResourceAsStream("/config.properties"));
DSL.using(
properties.getProperty("db.url"),
properties.getProperty("db.username"),
properties.getProperty("db.password")
).use { ctx ->
val a = AUTHOR
val b = BOOK
ctx.select(a.FIRST_NAME, a.LAST_NAME, b.TITLE)
.from(a)
.join(b).on(a.ID.eq(b.AUTHOR_ID))
.orderBy(1, 2, 3)
.forEach {
println("${it[b.TITLE]} by ${it[a.FIRST_NAME]} ${it[a.LAST_NAME]}")
}
}
}
```
Note that Kotlin supports [some means of operator overloading](https://kotlinlang.org/docs/reference/operator-overloading.html). For instance, `a + b` in Kotlin maps to a formal `a.plus(b)` method invocation, and jOOQ provides the required synonyms in its API to help you write such expressions.
One particularly nice language feature is the fact that [square brackets] allow for accessing any object's contents via `get()` and `set()` methods. Instead of using the above `value1()`, `value2()`, and `value3()` methods, we could also iterate as such:
```groovy
ctx.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, BOOK.TITLE)
.from(AUTHOR)
.join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
.orderBy(1, 2, 3)
.forEach {
println("${it[BOOK.TITLE]} by ${it[AUTHOR.FIRST_NAME]} ${it[AUTHOR.LAST_NAME]}")
// Notice: ^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^
}
```
A caveat of Kotlin operator overloading is the fact that operators such as `==` or `>=` map to `a.equals(b)`, `a.compareTo(b) == 0`, `a.compareTo(b) >= 0` respectively. This behaviour does not make sense in a fluent API such as jOOQ.
CONNECT BY clause to the query
*/
@Support({ SQLDialect.INFORMIX, SQLDialect.ORACLE })
SelectConnectByConditionStep connectBy(Condition condition);
```
jOOQ API methods which are not annotated with the [`org.jooq.Support`](https://www.jooq.org/javadoc/3.22.x/org/jooq/Support.html) annotation, or which are annotated with the Support annotation, but without any SQL dialects can be safely used in all SQL dialects. An example for this is the [SELECT statement](#select-statement) factory method:
```java
/**
* Create a new DSL select statement.
*/
@Support
SelectSelectStep select(Field... fields);
```
##### jOOQ's SQL clause emulation capabilities
The aforementioned Support annotation does not only designate, which databases natively support a feature. It also indicates that a feature is emulated by jOOQ for some databases lacking this feature. An example of this is the [DISTINCT predicate](#distinct-predicate), a predicate syntax defined by SQL:1999 and implemented only by H2, HSQLDB, and Postgres:
```sql
A IS DISTINCT FROM B
```
Nevertheless, the `IS DISTINCT FROM` predicate is supported by jOOQ in all dialects, as its semantics can be expressed with an equivalent [CASE expression](#case-expressions). For more details, see the manual's section about the [DISTINCT predicate](#distinct-predicate).
##### jOOQ and the Oracle SQL dialect
Oracle SQL is much more expressive than many other SQL dialects. It features many unique keywords, clauses and functions that are out of scope for the SQL standard. Some examples for this are
- The [CONNECT BY clause](#connect-by-clause), for hierarchical queries
- The [PIVOT](#pivot-tables) keyword for creating PIVOT tables
- [Packages](#oracle-packages), [object-oriented user-defined types, member procedures](#oracle-member-procedures) as described in the section about [stored procedures and functions](#stored-procedures)
- Advanced analytical functions as described in the section about [window functions](#window-functions)
jOOQ has a historic affinity to Oracle's SQL extensions. If something is supported in Oracle SQL, it has a high probability of making it into the jOOQ API
T unwrap(Wrapper wrapper, Class iface) {
try {
if (wrapper instanceof Connection)
// ...
else if (wrapper instanceof Statement)
// ...
else
wrapper.unwrap(iface);
}
catch (SQLException e) {
// ...
}
}
});
// Work with the derived configuration, where needed
DSL.using(c2).fetch("...");
```
books = create
.select(BOOK.ID, BOOK.TITLE)
.from(BOOK)
.fetchInto(BookInfo.class);
```
In Kotlin, parameters with default values may be omitted from the projection:
```kotlin
data class BookInfo(val id: Int, val title: String, val language: String = "en")
// There is no LANGUAGE column in the projection, so the default value "en" is applied
val books = ctx
.select(BOOK.ID, BOOK.TITLE)
.from(BOOK)
.fetchInto(BookInfo::class.java)
```
**Example configuration**
```java
Settings settings = new Settings()
.withMapConstructorParameterNames(true) // Defaults to false
.withMapRecordComponentParameterNames(true); // Defaults to false
```
x = val("x");
Param i = param("x", 42);
DSL.using(configuration)
.select(FIRST_NAME.concat(x))
.from(AUTHOR)
.where(ID.eq(i))
.fetch();
```
**Example configuration**
```java
Settings settings = new Settings()
.withParamType(ParamType.NAMED); // Defaults to INDEXED
```
The following setting [statementType](#settings-statement-type) may override this setting.
") // Defaults to "[jooq ignore start]"
.withParseIgnoreCommentStop(" ") // Defaults to "[jooq ignore stop]"
```
In addition to the above settings, there is also a powerful [parser listener SPI](#sql-parser-listener) called the [`org.jooq.ParseListener`](https://www.jooq.org/javadoc/3.22.x/org/jooq/ParseListener.html).
result =
create.select()
.from(AUTHOR.as("a"))
.join(BOOK.as("b")).on(a.ID.eq(b.AUTHOR_ID))
.where(a.YEAR_OF_BIRTH.gt(1920)
.and(a.FIRST_NAME.eq("Paulo")))
.orderBy(b.TITLE)
.fetch();
```
We'll see how the aliasing works later in the section about [aliased tables](#aliased-tables)
Many other frameworks have similar APIs with similar feature sets. Yet, what makes jOOQ special is its informal [BNF notation](https://blog.jooq.org/the-java-fluent-api-designer-crash-course/) modelling a unified SQL dialect suitable for many vendor-specific dialects, and implementing that BNF notation as a hierarchy of interfaces in Java. This concept is extremely powerful, when [using jOOQ with IDE syntax auto completion](https://blog.jooq.org/why-you-should-use-jooq-with-code-generation/). Not only can you code much faster, your SQL code will be compile-checked to a certain extent. An example of a DSL query equivalent to the previous one is given here:
```java
DSLContext create = DSL.using(connection, dialect);
Result result = create.select()
.from(AUTHOR)
.join(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
.fetch();
```
Unlike other, simpler frameworks that use ["fluent APIs"](https://en.wikipedia.org/wiki/Fluent_interface) or ["method chaining"](https://en.wikipedia.org/wiki/Method_chaining), jOOQ's BNF-based interface hierarchy will not allow bad query syntax. The following will not compile, for instance:
```java
DSLContext create = DSL.using(connection, dialect);
Result result = create.select()
.join(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
// ^^^^ "join" is not possible here
.from(AUTHOR)
.fetch();
Result result = create.select()
.from(AUTHOR)
.join(BOOK)
.fetch();
// ^^^^^ "on" is missing here
Result result = create.select(rowNumber())
// ^^^^^^^^^ "over()" is missing here
.from(AUTHOR)
.fetch();
Result result = create.select()
.from(AUTHOR)
.where(AUTHOR.ID.in(select(BOOK.TITLE).from(BOOK)))
// ^^^^^^^^^^^^^^^^^^
// AUTHOR.ID is of type Field but subselect returns Record1
.fetch();
Result result = create.select()
.from(AUTHOR)
.where(AUTHOR.ID.in(select(BOOK.AUTHOR_ID, BOOK.ID).from(BOOK)))
// ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
// AUTHOR.ID is of degree 1 but subselect returns Record2
.fetch();
```
field = substring(BOOK.TITLE, 2, 4);
// Access the expression's internals using the model API
if (field instanceof QOM.Substring substring) {
Field string = substring.$string();
Field startingPosition = substring.$startingPosition();
Field length = substring.$length();
}
```
Every argument passed to the DSL API has a `$` prefixed accessor method on the model API, exposing the wrapped argument. Using these accessor methods, users can traverse the expression tree manually or via the [model API traversal API](#model-api-traversal). More recent Java language features like pattern matching can be very helpful for such operations, especially as we're planning to seal the entire query object model API.
All of the model API is immutable, but new expressions can still be created using equivalent `$` prefixed setter methods, which don't mutate the original expression but return a copy:
```java
// Produces a substring(BOOK.TITLE, 2, 4) column expression
QOM.Substring substring = (QOM.Substring) substring(BOOK.TITLE, 2, 4);
// Produces a substring(BOOK.TITLE, 3, 5) column expression
substring.$startingPosition(val(3)).$length(val(5));
```
These basic operations allow for powerful [SQL transformations](#sql-transformation) on expression trees created with the [DSL API](#dsl-api) but also with the [SQL parser](#sql-parser).
{
/**
* A supplier for a temporary data structure to accumulate {@link QueryPart}
* objects into during traversal.
*/
Supplier supplier();
/**
* An optional traversal abort condition to short circuit traversal e.g.
* when the searched object has been found.
*/
Predicate abort();
/**
* An optional recursion condition to prevent entering a specific
* {@link QueryPart}, e.g. when it is undesired to enter any subqueries.
*/
Predicate recurse();
/**
* An optional recursion condition to prevent entering a specific
* {@link QueryPart}'s children, e.g. when it is desired to traverse only
* into certain operators.
*/
Predicate recurseChildren();
/**
* A callback that is invoked before recursing into a subtree.
*/
BiFunction before();
/**
* A callback that is invoked after recursing into a subtree.
*/
BiFunction after();
/**
* An optional transformation function to turn the temporary data structure
* supplied by {@link #supplier()} into the final data structure.
*/
Function finisher();
}
```
Some elements are similar to that of a [`java.util.stream.Collector`](https://docs.oracle.com/en/java/javase/25/docs/api/java.base/java/util/stream/Collector.html), others are specific to tree traversal.
A simple illustration shows what can be done:
```java
// Any ordinary QueryPart:
Condition condition = BOOK.ID.eq(1);
int count = condition.$traverse(
// Supplier of the initial data structure: an int
() -> 0,
// Print all traversed QueryParts and increment the counter
(r, q) -> {
System.out.println("Part " + r + ": " + q);
return r + 1;
}
);
System.out.println("Count : " + count);
```
The above will print:
```text
Part 0: "BOOK"."ID" = 1
Part 1: "BOOK"."ID"
Part 2: 1
Count : 3
```
Using the same traverser on a slightly more complex `QueryPart`
```java
Condition condition = BOOK.ID.eq(1).or(BOOK.ID.eq(2));
// ...
```
```text
Part 0: ("BOOK"."ID" = 1 or "BOOK"."ID" = 2)
Part 1: "BOOK"."ID" = 1
Part 2: "BOOK"."ID"
Part 3: 1
Part 4: "BOOK"."ID" = 2
Part 5: "BOOK"."ID"
Part 6: 2
Count : 7
```
##### Re-using your JDK collectors
Any `Collector` can be turned into a `Traverser` using [`Traversers.collecting(Collector)`](https://www.jooq.org/javadoc/3.22.x/org/jooq/Traversers.html#collecting(java.util.stream.Collector)). For example, if you want to count all `QueryPart` items in an expression, instead of the above hand-written traverser, just use the JDK [`Collectors.counting()`](https://docs.oracle.com/en/java/javase/25/docs/api/java.base/java/util/stream/Collectors.html#counting()):
```java
// Contains 3 query parts
long count1 = BOOK.ID.eq(1)
.$traverse(Traversers.collecting(Collectors.counting());
// Contains 7 query parts
long count2 = BOOK.ID.eq(1).or(BOOK.ID.eq(2))
.$traverse(Traversers.collecting(Collectors.counting());
```
##### Limitations
Just like [model API replacement](#model-api-replacement), traversers cannot traverse into "opaque" [`org.jooq.QueryPart`](https://www.jooq.org/javadoc/3.22.x/org/jooq/QueryPart.html) instances, including [custom QueryParts](#custom-queryparts) or [plain SQL templates](#plain-sql-templating-language). See also [features requiring code generation](#codegen-feature-dependencies) for more details.
query = create.selectQuery();
query.addFrom(AUTHOR);
// Join books only under certain circumstances
if (join) {
query.addJoin(BOOK, BOOK.AUTHOR_ID.eq(AUTHOR.ID));
}
Result result = query.fetch();
```
This query is equivalent to the one shown before using the DSL syntax. In fact, internally, the DSL API constructs precisely this SelectQuery object. Note, that you can always access the SelectQuery object to switch between DSL and model APIs:
```java
DSLContext create = DSL.using(connection, dialect);
SelectFinalStep select = create.select().from(AUTHOR);
// Add the JOIN clause on the internal QueryObject representation
SelectQuery query = select.getQuery();
query.addJoin(BOOK, BOOK.AUTHOR_ID.eq(AUTHOR.ID));
```
This API is completely mutable, and for historic reasons, [early DSL API elements have inherited this mutability](#mutability-historic).
> t1 =
name("t1").fields("f1", "f2").as(select(val(1), val("a")));
CommonTableExpression> t2 =
name("t2").fields("f3", "f4").as(select(val(2), val("b")));
Result result2 =
create.with(t1)
.with(t2)
.select(
t1.field("f1").add(t2.field("f3")).as("add"),
t1.field("f2").concat(t2.field("f4")).as("concat"))
.from(t1, t2)
.fetch();
```
See also the section about [common table expressions](#common-table-expressions).
SelectWhereStep selectFrom(Table table);
```
As you can see, there is no way to further restrict/project the selected fields. This just selects all known TableFields in the supplied Table, and it also binds to your Table's associated Record. An example of such a Query would then be:
```java
BookRecord book = create.selectFrom(BOOK)
.where(BOOK.LANGUAGE.eq("DE"))
.orderBy(BOOK.TITLE)
.fetchAny();
```
The "reduced" SELECT API is limited in the way that it skips DSL access to any of these clauses:
- [SELECT clause](#select-clause)
- [JOIN operator](#join-clause)
In most parts of this manual, it is assumed that you do not use the "reduced" SELECT API. For more information about the simple SELECT API, see the manual's section about [fetching strongly or weakly typed records](#record-vs-tablerecord).
select(Collection> fields);
public static SelectSelectStep select(SelectField... fields);
// Typesafe select methods:
public static SelectSelectStep> select(SelectField field1);
public static SelectSelectStep> select(SelectField field1, SelectField field2);
// [...]
```
The type that is being projected is the [`org.jooq.SelectField`](https://www.jooq.org/javadoc/3.22.x/org/jooq/SelectField.html), see also the [next section about SelectField](#select-clause-selectfield). Since the generic R type is bound to some [Record[N]](#record-n), the associated T type information can be used in various other contexts, e.g. the [IN predicate](#in-predicate). Such a `SELECT` statement can be assigned typesafely:
```java
Select> s1 = create.select(BOOK.ID, BOOK.TITLE);
Select> s2 = create.select(BOOK.ID, trim(BOOK.TITLE));
// Alternatively, just use var to infer the type:
var s3 = create.select(BOOK.ID, trim(BOOK.TITLE));
```
For more information about typesafe record types with degree up to 22, see the manual's section about [Record1 to Record22](#record-n).
> result =
create.select(AUTHOR, BOOK)
.from(AUTHOR)
.join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
.fetch();
```
This plays very well together with [implicit joins](#implicit-join):
```java
Result> result =
create.select(BOOK.author(), BOOK)
.from(BOOK)
.fetch();
```
Behind the scenes, the implementation may either be native in dialects that support this kind of projection (e.g. PostgreSQL), or emulated using the usual [nested records](#nested-records) emulations.
> [!TIP]
> Nesting tables using this syntax is different from using the [TABLE.asterisk()](#select-clause-asterisk) syntax, which doesn't nest the table but flattens its fields into the surrounding projection.
###### Dialect support
This example using jOOQ:
```java
select(BOOK).from(BOOK)
```
Translates to the following dialect specific expressions:
###### ASE, Access, SQLDataWarehouse, SQLServer, Sybase
```sql
SELECT BOOK.ID [BOOK__ID],
BOOK.AUTHOR_ID [BOOK__AUTHOR_ID],
BOOK.TITLE [BOOK__TITLE],
BOOK.PUBLISHED_IN [BOOK__PUBLISHED_IN],
BOOK.LANGUAGE_ID [BOOK__LANGUAGE_ID]
FROM BOOK
```
###### Aurora MySQL, BigQuery, MariaDB, MemSQL, MySQL, SQLite, Spanner
```sql
SELECT BOOK.ID `BOOK__ID`,
BOOK.AUTHOR_ID `BOOK__AUTHOR_ID`,
BOOK.TITLE `BOOK__TITLE`,
BOOK.PUBLISHED_IN `BOOK__PUBLISHED_IN`,
BOOK.LANGUAGE_ID `BOOK__LANGUAGE_ID`
FROM BOOK
```
###### Aurora Postgres, CockroachDB, DuckDB, H2, Informix, Postgres, YugabyteDB
```sql
SELECT ROW (
BOOK.ID,
BOOK.AUTHOR_ID,
BOOK.TITLE,
BOOK.PUBLISHED_IN,
BOOK.LANGUAGE_ID
) BOOK
FROM BOOK
```
###### ClickHouse
```sql
SELECT TUPLE (
BOOK.ID,
BOOK.AUTHOR_ID,
BOOK.TITLE,
BOOK.PUBLISHED_IN,
BOOK.LANGUAGE_ID
) BOOK
FROM BOOK
```
###### Databricks
```sql
SELECT STRUCT (
coalesce(BOOK.ID),
coalesce(BOOK.AUTHOR_ID),
coalesce(BOOK.TITLE),
coalesce(BOOK.PUBLISHED_IN),
coalesce(BOOK.LANGUAGE_ID)
) BOOK
FROM BOOK
```
###### DB2, Exasol, Firebird, HSQLDB, Hana, Oracle, Redshift, Snowflake, Teradata, Trino, Vertica
```sql
SELECT BOOK.ID "BOOK__ID",
BOOK.AUTHOR_ID "BOOK__AUTHOR_ID",
BOOK.TITLE "BOOK__TITLE",
BOOK.PUBLISHED_IN "BOOK__PUBLISHED_IN",
BOOK.LANGUAGE_ID "BOOK__LANGUAGE_ID"
FROM BOOK
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
1 1984 2 Animal Farm 3 O Alquimista 4 Brida
```
1 1984 |
2 Animal Farm |
3 O Alquimista |
4 Brida |
```
Alternatively, provide an explicit element name for rows (the default being `row`, as above):
```sql
SELECT id, title
FROM book
ORDER BY id
FOR XML PATH ('book')
```
```java
create.select(BOOK.ID, BOOK.TITLE)
.from(BOOK)
.orderBy(BOOK.ID)
.forXML().path("book")
.fetch();
```
This will produce
```xml
1 1984 2 Animal Farm 3 O Alquimista 4 Brida |
|
|
|
```
###### Dialect support
This example using jOOQ:
```java
select(BOOK.ID).from(BOOK).orderBy(BOOK.ID).forXML().raw()
```
Translates to the following dialect specific expressions:
###### DB2, Oracle, Postgres
```sql
SELECT xmlagg(xmlelement(
NAME row,
xmlattributes(t.ID AS ID)
))
FROM (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
) t
```
###### SQLServer
```sql
SELECT (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
FOR XML RAW
)
```
###### Sybase
```sql
SELECT (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
FOR XML RAW
)
FROM SYS.DUMMY
```
###### Teradata
```sql
SELECT xmlagg(xmlelement(
NAME row,
xmlattributes(t.ID AS ID)
))
FROM (
SELECT *
FROM (
SELECT TOP 999999999999999999 BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
) x
) t
```
###### ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, Databricks, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, Informix, MariaDB, MemSQL, MySQL, Redshift, SQLDataWarehouse, SQLite, Snowflake, Spanner, Trino, Vertica, YugabyteDB
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
|
|
|
|
```
###### Dialect support
This example using jOOQ:
```java
select(BOOK.ID).from(BOOK).orderBy(BOOK.ID).forXML().raw().root("result")
```
Translates to the following dialect specific expressions:
###### DB2, Oracle, Postgres
```sql
SELECT xmlelement(
NAME result,
xmlagg(xmlelement(
NAME row,
xmlattributes(t.ID AS ID)
))
)
FROM (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
) t
```
###### SQLServer
```sql
SELECT (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
FOR XML RAW, ROOT ('result')
)
```
###### Teradata
```sql
SELECT xmlelement(
NAME result,
xmlagg(xmlelement(
NAME row,
xmlattributes(t.ID AS ID)
))
)
FROM (
SELECT *
FROM (
SELECT TOP 999999999999999999 BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
) x
) t
```
###### ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, Databricks, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, Informix, MariaDB, MemSQL, MySQL, Redshift, SQLDataWarehouse, SQLite, Snowflake, Spanner, Sybase, Trino, Vertica, YugabyteDB
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
1 1984 |
2 Animal Farm |
3 O Alquimista |
4 Brida |
```
###### Dialect support
This example using jOOQ:
```java
select(BOOK.ID).from(BOOK).orderBy(BOOK.ID).forXML().raw().elements()
```
Translates to the following dialect specific expressions:
###### DB2, Oracle, Postgres
```sql
SELECT xmlagg(xmlelement(
NAME row,
xmlelement(NAME ID, ID)
))
FROM (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
) t
```
###### SQLServer
```sql
SELECT (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
FOR XML RAW, ELEMENTS
)
```
###### Sybase
```sql
SELECT (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
FOR XML RAW, ELEMENTS
)
FROM SYS.DUMMY
```
###### Teradata
```sql
SELECT xmlagg(xmlelement(
NAME row,
xmlelement(NAME ID, ID)
))
FROM (
SELECT *
FROM (
SELECT TOP 999999999999999999 BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
) x
) t
```
###### ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, Databricks, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, Informix, MariaDB, MemSQL, MySQL, Redshift, SQLDataWarehouse, SQLite, Snowflake, Spanner, Trino, Vertica, YugabyteDB
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
> s1 = select(BOOK.ID, BOOK.TITLE).from(BOOK);
Select> s2 = selectOne();
Select> s3 = select(one(), zero());
Select> s4 = select(one(), inline("abc"));
// Let's try to combine them:
s1.union(s2); // Doesn't compile because of a degree mismatch. Expected: Record2<...>, got: Record1<...>
s1.union(s3); // Doesn't compile because of a type mismatch. Expected: , got:
s1.union(s4); // OK. The two Record[N] types match
```
> result =
create.select(BOOK.ID)
.from(BOOK)
.union(
// This has no effect
select(AUTHOR.ID.convertFrom(i -> -i))
.from(AUTHOR))
.fetch();
```
While this can lead to subtle bugs, it makes perfect sense, knowing that a `Converter` is always applied at the client side of the execution.
step =
create.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME);
step.values("A", "B", "C");
// ^^^ Doesn't compile, the expected type is Integer
```
###### INSERT .. VALUES with multiple rows
The SQL standard specifies that multiple rows can be supplied to the VALUES() constructor in an INSERT statement. Here's an example of a multi-record INSERT
```sql
INSERT INTO AUTHOR
(ID, FIRST_NAME, LAST_NAME)
VALUES (100, 'Hermann', 'Hesse'),
(101, 'Alfred', 'Döblin');
```
```java
create.insertInto(AUTHOR,
AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.values(100, "Hermann", "Hesse")
.values(101, "Alfred", "Döblin")
.execute()
```
jOOQ tries to stay close to actual SQL. In detail, however, Java's expressiveness is limited. That's why the values() clause is repeated for every record in multi-record inserts.
Some RDBMS do not support inserting several records in a single statement. In those cases, jOOQ emulates multi-record INSERTs using the following SQL:
```sql
INSERT INTO AUTHOR
(ID, FIRST_NAME, LAST_NAME)
SELECT 100, 'Hermann', 'Hesse' FROM DUAL UNION ALL
SELECT 101, 'Alfred', 'Döblin' FROM DUAL;
```
```java
create.insertInto(AUTHOR,
AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.values(100, "Hermann", "Hesse")
.values(101, "Alfred", "Döblin")
.execute();
```
If your inserted rows or records are dynamic, you can use `valuesOfRows()` or `valuesOfRecords()` instead:
```sql
INSERT INTO AUTHOR
(ID, FIRST_NAME, LAST_NAME)
VALUES (100, 'Hermann', 'Hesse'),
(101, 'Alfred', 'Döblin');
```
```java
List> records = ...
create.insertInto(AUTHOR,
AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.valuesOfRecords(records)
.execute();
```
###### Dialect support
This example using jOOQ:
```java
insertInto(AUTHOR).columns(AUTHOR.ID, AUTHOR.LAST_NAME).values(100, "Hesse")
```
Translates to the following dialect specific expressions:
###### All dialects
```sql
INSERT INTO AUTHOR (ID, LAST_NAME)
VALUES (
100,
'Hesse'
)
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
, Select>)` method in the `UPDATE` DSL API, for convenience (see the section about [scalar subqueries](#scalar-subqueries) for more details):
```sql
UPDATE AUTHOR
SET FIRST_NAME = (
SELECT FIRST_NAME
FROM PERSON
WHERE PERSON.ID = AUTHOR.ID
),
WHERE ID = 3;
```
```java
create.update(AUTHOR)
.set(AUTHOR.FIRST_NAME,
select(PERSON.FIRST_NAME)
.from(PERSON)
.where(PERSON.ID.eq(AUTHOR.ID))
)
.where(AUTHOR.ID.eq(3))
.execute();
```
###### Dialect support
This example using jOOQ:
```java
update(BOOK_TO_BOOK_STORE).set(BOOK_TO_BOOK_STORE.STOCK, 0).from(BOOK).where(BOOK_TO_BOOK_STORE.BOOK_ID.eq(BOOK.ID)).and(BOOK.AUTHOR_ID.eq(1))
```
Translates to the following dialect specific expressions:
###### ASE, BigQuery, Oracle, SQLServer, Sybase
```sql
UPDATE BOOK_TO_BOOK_STORE
SET
BOOK_TO_BOOK_STORE.STOCK = 0
FROM BOOK
WHERE (
BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
AND BOOK.AUTHOR_ID = 1
)
```
###### Aurora MySQL, MariaDB, MemSQL, MySQL, Trino
```sql
UPDATE BOOK_TO_BOOK_STORE
CROSS JOIN BOOK
SET
BOOK_TO_BOOK_STORE.STOCK = 0
WHERE (
BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
AND BOOK.AUTHOR_ID = 1
)
```
###### Aurora Postgres, CockroachDB, DuckDB, Postgres, Redshift, SQLite, Snowflake, YugabyteDB
```sql
UPDATE BOOK_TO_BOOK_STORE
SET
STOCK = 0
FROM BOOK
WHERE (
BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
AND BOOK.AUTHOR_ID = 1
)
```
###### ClickHouse
```sql
UPDATE BOOK_TO_BOOK_STORE
SET
STOCK = 0
WHERE (NAME, BOOK_ID) IN (
SELECT BOOK_TO_BOOK_STORE.NAME, BOOK_TO_BOOK_STORE.BOOK_ID
FROM BOOK_TO_BOOK_STORE, BOOK
WHERE (
BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
AND BOOK.AUTHOR_ID = 1
)
)
```
###### Databricks
```sql
UPDATE BOOK_TO_BOOK_STORE
SET
STOCK = 0
WHERE EXISTS (
SELECT alias_1.v0, alias_1.v1
FROM (
SELECT
BOOK_TO_BOOK_STORE.NAME v0,
BOOK_TO_BOOK_STORE.BOOK_ID v1
FROM BOOK_TO_BOOK_STORE, BOOK
WHERE (
BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
AND BOOK.AUTHOR_ID = 1
)
) alias_1
WHERE STRUCT (
coalesce(BOOK_TO_BOOK_STORE.NAME),
coalesce(BOOK_TO_BOOK_STORE.BOOK_ID)
) = STRUCT (
coalesce(alias_1.v0),
coalesce(alias_1.v1)
)
)
```
###### DB2, Exasol, Firebird, H2, HSQLDB
```sql
MERGE INTO BOOK_TO_BOOK_STORE
USING BOOK
ON (
BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
AND BOOK.AUTHOR_ID = 1
)
WHEN MATCHED THEN UPDATE SET
BOOK_TO_BOOK_STORE.STOCK = 0
```
###### Hana
```sql
UPDATE BOOK_TO_BOOK_STORE
FROM BOOK_TO_BOOK_STORE, BOOK
SET
BOOK_TO_BOOK_STORE.STOCK = 0
WHERE (
BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
AND BOOK.AUTHOR_ID = 1
)
```
###### Spanner
```sql
UPDATE BOOK_TO_BOOK_STORE
SET
BOOK_TO_BOOK_STORE.STOCK = 0
WHERE EXISTS (
SELECT alias_1.v0, alias_1.v1
FROM (
SELECT
BOOK_TO_BOOK_STORE.NAME v0,
BOOK_TO_BOOK_STORE.BOOK_ID v1
FROM BOOK_TO_BOOK_STORE, BOOK
WHERE (
BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
AND BOOK.AUTHOR_ID = 1
)
) alias_1
WHERE (BOOK_TO_BOOK_STORE.NAME, BOOK_TO_BOOK_STORE.BOOK_ID) = (alias_1.v0, alias_1.v1)
)
```
###### SQLDataWarehouse
```sql
UPDATE BOOK_TO_BOOK_STORE
SET
BOOK_TO_BOOK_STORE.STOCK = 0
WHERE EXISTS (
SELECT alias_1.v0, alias_1.v1
FROM (
SELECT
BOOK_TO_BOOK_STORE.NAME v0,
BOOK_TO_BOOK_STORE.BOOK_ID v1
FROM BOOK_TO_BOOK_STORE, BOOK
WHERE (
BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
AND BOOK.AUTHOR_ID = 1
)
) alias_1
WHERE (
BOOK_TO_BOOK_STORE.NAME = alias_1.v0
AND BOOK_TO_BOOK_STORE.BOOK_ID = alias_1.v1
)
)
```
###### Teradata, Vertica
```sql
MERGE INTO BOOK_TO_BOOK_STORE
USING BOOK
ON (
BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
AND BOOK.AUTHOR_ID = 1
)
WHEN MATCHED THEN UPDATE SET
STOCK = 0
```
###### Access, Informix
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
i1 = in("i1", INTEGER);
Parameter i2 = in("i2", INTEGER);
create.createFunction("my_sum")
.parameters(i1, i2)
.returns(INTEGER)
.as(return_(i1.plus(i2)))
.execute();
```
Once you've created the above functions, you can either run code generation to get a type safe stub for calling them, or use [plain SQL](#plain-sql) (specifically, [`DSL.function()`](https://www.jooq.org/javadoc/3.22.x/org/jooq/impl/DSL.html#function(org.jooq.Name,org.jooq.DataType,org.jooq.Field...))) from within a [SELECT statement](#select-statement):
```java
// Call the previously created functions with generated code:
create.select(one(), mySum(1, 2)).fetchOne();
// ...or with plain SQL
create.select(
function(name("one"), INTEGER),
function(name("my_sum"), INTEGER, val(1), val(2))
).fetchOne();
```
Both yielding:
```text
+-----+--------+
| ONE | MY_SUM |
+-----+--------+
| 1 | 3 |
+-----+--------+
```
i1 = in("i1", INTEGER);
Parameter i2 = in("i2", INTEGER);
// The function always returns NULL if any argument value is NULL
create.createFunction("my_sum")
.parameters(i1, i2)
.returns(INTEGER)
.returnsNullOnNullInput()
.as(return_(i1.plus(i2)))
.execute();
// The function may not return NULL if any argument value is NULL
create.createFunction("my_null_safe_sum")
.parameters(i1, i2)
.returns(INTEGER)
.calledOnNullInput()
.as(return_(coalesce(i1, 0).plus(coalesce(i2, 0))))
.execute();
```
###### 3.6.3.3.6. COMMENT characteristic
Supported editions: Express, Professional, Enterprise (not available in the Open Source Edition)
Some RDBMS support a SQL `COMMENT` as a characteristic on functions, which can be inlined directly with the function definition.
```java
create.createFunction("f1").returns(INTEGER).comment("my function").as(return_(1)).execute();
```
o = out("o", INTEGER);
create.createProcedure("p1")
.parameters(o)
.noSQL()
.as(o.set(1))
.execute();
create.createProcedure("p2")
.parameters(o)
.containsSQL()
.as(o.set(select(val(1))))
.execute();
create.createProcedure("p3")
.parameters(o)
.readsSQLData()
.as(o.set(selectCount().from(BOOK)))
.execute();
create.createProcedure("p4")
.parameters(o)
.modifiesSQLData()
.as(
insertInto(LOGS).columns(LOGS.TEXT).values("Function F4 was called"),
o.set(1)
)
.execute();
```
This works just like [SQL data access characteristics for functions](#create-function-characteristics-sql-data-access)
###### 3.6.3.5.3. COMMENT characteristic
Supported editions: Express, Professional, Enterprise (not available in the Open Source Edition)
Some RDBMS support a SQL `COMMENT` as a characteristic on procedures, which can be inlined directly with the procedure definition.
```java
create.createProcedure("f1").comment("my function").as(return_()).execute();
```
i = var(name("i"), INTEGER);
create.begin(
for_(i).in(1, 10).loop(
insertInto(T).columns(T.COL).values(i)
)
).execute();
```
The entire loop is executed on the server, which may greatly help reduce client server round trips.
Apart from the [block statement](#procedural-block), this feature set is available only in our commercial distributions.
message = in("message", VARCHAR);
create.createProcedure("log")
.parameters(message)
.as(insertInto(LOG).columns(LOG.TEXT).values(message))
.execute();
create.createProcedure("some_other_procedure")
.as(
// ...
call("log").args(val("My first message")),
// ...
call("log").args(val("My second message"))
// ...
)
.execute();
```
##### Dialect support
This example using jOOQ:
```java
call("log").args(val("message"))
```
Translates to the following dialect specific expressions:
###### BigQuery, DB2, HSQLDB, Hana, MariaDB, MySQL, Postgres, Snowflake, YugabyteDB
```sql
CALL log('message')
```
###### Firebird, Informix
```sql
EXECUTE PROCEDURE log('message')
```
###### Oracle
```sql
BEGIN
log('message');
END;
```
###### SQLDataWarehouse, SQLServer
```sql
EXEC log 'message'
```
###### ASE, Access, Aurora MySQL, Aurora Postgres, ClickHouse, CockroachDB, Databricks, DuckDB, Exasol, H2, MemSQL, Redshift, SQLite, Spanner, Sybase, Teradata, Trino, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
>
LOOP
i := i + 1;
CONTINUE label WHEN i <= 10;
END LOOP;
```
```java
// All dialects
Label label = label("label");
label.label(loop(
i.set(i.plus(1)),
continue(label).when(i.le(10))
))
```
Notice that `continue` is a reserved keyword in the Java language, so the jOOQ API cannot use it as a method name. We've suffixed such conflicts with an underscore: `continue_()`.
##### Dialect support
This example using jOOQ:
```java
loop(i.set(i.plus(1)), continueWhen(i.gt(10)))
```
Translates to the following dialect specific expressions:
###### Aurora Postgres, CockroachDB, Oracle, Postgres, YugabyteDB
```sql
LOOP
i := (i + 1);
CONTINUE WHEN i > 10;
END LOOP
```
###### BigQuery
```sql
LOOP
SET i = (i + 1);
IF i > 10 THEN
CONTINUE;
END IF;
END LOOP
```
###### DB2, HSQLDB, MariaDB, MySQL, Trino
```sql
alias_1:
LOOP
SET i = (i + 1);
IF i > 10 THEN
ITERATE alias_1;
END IF;
END LOOP
```
###### H2
```sql
for (;;) {
i = (i + 1);
if (i > 10) {
continue;
}
}
```
###### Hana
```sql
WHILE 1 = 1 DO
i = (i + 1);
IF i > 10 THEN
CONTINUE;
END IF;
END WHILE
```
###### Informix
```sql
LOOP
LET i = (i + 1);
IF i > 10 THEN
CONTINUE;
END IF;
END LOOP
```
###### Snowflake
```sql
LOOP
i := (:i + 1);
IF (:i > 10) THEN
CONTINUE;
END IF;
END LOOP
```
###### SQLDataWarehouse, SQLServer
```sql
WHILE 1 = 1 BEGIN
SET @i = (@i + 1);
IF @i > 10
CONTINUE;
END
```
###### ASE, Access, Aurora MySQL, ClickHouse, Databricks, DuckDB, Exasol, Firebird, MemSQL, Redshift, SQLite, Spanner, Sybase, Teradata, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
>
LOOP
i := i + 1;
EXIT label WHEN i > 10;
END LOOP;
```
```java
// All dialects
Label label = label("label");
label.label(loop(
i.set(i.plus(1)),
exit(label).when(i.gt(10))
))
```
##### Dialect support
This example using jOOQ:
```java
loop(i.set(i.plus(1)), exitWhen(i.gt(10)))
```
Translates to the following dialect specific expressions:
###### Aurora Postgres, CockroachDB, Oracle, Postgres, YugabyteDB
```sql
LOOP
i := (i + 1);
EXIT WHEN i > 10;
END LOOP
```
###### BigQuery
```sql
LOOP
SET i = (i + 1);
IF i > 10 THEN
BREAK;
END IF;
END LOOP
```
###### DB2, HSQLDB, MariaDB, MySQL, Trino
```sql
alias_1:
LOOP
SET i = (i + 1);
IF i > 10 THEN
LEAVE alias_1;
END IF;
END LOOP
```
###### Firebird
```sql
WHILE (1 = 1) DO BEGIN
:i = (:i + 1);
IF (:i > 10) THEN
LEAVE;
END
```
###### H2
```sql
for (;;) {
i = (i + 1);
if (i > 10) {
break;
}
}
```
###### Hana
```sql
WHILE 1 = 1 DO
i = (i + 1);
IF i > 10 THEN
BREAK;
END IF;
END WHILE
```
###### Informix
```sql
LOOP
LET i = (i + 1);
EXIT WHEN i > 10;
END LOOP
```
###### Snowflake
```sql
LOOP
i := (:i + 1);
IF (:i > 10) THEN
BREAK;
END IF;
END LOOP
```
###### SQLDataWarehouse, SQLServer
```sql
WHILE 1 = 1 BEGIN
SET @i = (@i + 1);
IF @i > 10
BREAK;
END
```
###### ASE, Access, Aurora MySQL, ClickHouse, Databricks, DuckDB, Exasol, MemSQL, Redshift, SQLite, Spanner, Sybase, Teradata, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
i = var("i", INTEGER);
for_(i).in(1, 10).loop(
insertInto(T).columns(T.COL).values(i)
)
```
In addition to simplifying the most common case, there are also options of traversing the arguments in a reversed way, and using an additional optional step variable, for example:
```sql
-- pgplsql
FOR i IN REVERSE 10 .. 1 BY 2 LOOP
INSERT INTO t (col) VALUES (i);
END LOOP;
```
```java
// All dialects
Variable i = var("i", INTEGER);
for_(i).inReverse(10, 1).by(2).loop(
insertInto(T).columns(T.COL).values(i)
)
```
Not all dialects support the entirety of this syntax, but luckily it is easy for jOOQ to emulate in all dialects using [WHILE](#procedural-while):
```sql
-- PL/SQL
WHILE i >= 1 LOOP
INSERT INTO t (col) VALUES (i);
i := i - 2;
END LOOP;
```
Notice that `for` is a reserved keyword in the Java language, so the jOOQ API cannot use it as a method name. We've suffixed such conflicts with an underscore: `for_()`.
##### Dialect support
This example using jOOQ:
```java
for_(i).in(1, 10).loop(insertInto(BOOK).columns(BOOK.ID).values(i))
```
Translates to the following dialect specific expressions:
###### Aurora Postgres, CockroachDB, Exasol, Oracle, Postgres, YugabyteDB
```sql
FOR i IN 1 .. 10 LOOP
INSERT INTO BOOK (ID)
VALUES (i);
END LOOP
```
###### BigQuery
```sql
BEGIN
DECLARE i int64 DEFAULT 1;
WHILE i <= 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
SET i = (i + 1);
END WHILE;
END;
```
###### DB2
```sql
BEGIN
DECLARE i integer;
SET i = 1;
WHILE i <= 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
SET i = (i + 1);
END WHILE;
END;
```
###### Firebird
```sql
DECLARE i integer DEFAULT 1;
WHILE (:i <= 10) DO BEGIN
INSERT INTO BOOK (ID)
VALUES (:i);
:i = (:i + 1);
END
```
###### H2
```sql
for (Integer i = 1; i <= 10; i++) {
try (PreparedStatement s = c.prepareStatement(
"INSERT INTO BOOK (ID)\n" +
"VALUES (?)"
)) {
s.setObject(1, i);
s.execute();
}
}
```
###### Hana
```sql
BEGIN
DECLARE i integer;
FOR i IN 1 .. 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
END FOR;
END;
```
###### HSQLDB
```sql
BEGIN ATOMIC
DECLARE i int;
SET i = 1;
WHILE i <= 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
SET i = (i + 1);
END WHILE;
END;
```
###### Informix
```sql
BEGIN
DEFINE i integer;
LET i = 1;
FOR i IN (1 TO 10) LOOP
INSERT INTO BOOK (ID)
VALUES (i);
END LOOP;
END;
```
###### MariaDB
```sql
FOR i IN 1 .. 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
END FOR
```
###### MySQL
```sql
BEGIN
DECLARE i int;
SET i = 1;
WHILE i <= 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
SET i = (i + 1);
END WHILE;
END;
```
###### Snowflake
```sql
FOR i IN 1 TO 10 DO
INSERT INTO BOOK (ID)
SELECT :i;
END FOR
```
###### SQLDataWarehouse
```sql
BEGIN
DECLARE @i int DEFAULT 1;
WHILE @i <= 10 BEGIN
INSERT INTO BOOK (ID)
SELECT @i;
SET @i = (@i + 1);
END;
END;
```
###### SQLServer
```sql
BEGIN
DECLARE @i int = 1;
WHILE @i <= 10 BEGIN
INSERT INTO BOOK (ID)
VALUES (@i);
SET @i = (@i + 1);
END;
END;
```
###### Trino
```sql
BEGIN
DECLARE i int;
SET i = 1;
WHILE i <= 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
SET i = (i + 1);
END WHILE;
END
```
###### ASE, Access, Aurora MySQL, ClickHouse, Databricks, DuckDB, MemSQL, Redshift, SQLite, Spanner, Sybase, Teradata, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
>
INSERT INTO t (col) VALUES (3);
END;
```
```java
// All dialects
Label l1 = label("l1");
begin(
insertInto(T).columns(T.COL).values(1),
goto_(l1),
insertInto(T).columns(T.COL).values(2),
l1.label(insertInto(T).columns(T.COL).values(3))
)
```
Some dialects (e.g. T-SQL) may implement "full GOTO" in the ways that are generally not really helping readability or maintainability of code, namely the idea that `GOTO` can be used to jump into any arbitrary scope that should not be reachable through ordinary control flow. This is not possible in other languages, like PL/SQL, and currently cannot be emulated by jOOQ.
Notice that `goto` is a reserved keyword in the Java language, so the jOOQ API cannot use it as a method name. We've suffixed such conflicts with an underscore: `goto_()`.
##### Dialect support
This example using jOOQ:
```java
begin(l.label(insertInto(BOOK).columns(BOOK.ID).values(1)), goto_(l))
```
Translates to the following dialect specific expressions:
###### DB2
```sql
BEGIN
l:
BEGIN
INSERT INTO BOOK (ID)
VALUES (1);
END;
GOTO l;
END
```
###### Informix
```sql
BEGIN
<>
INSERT INTO BOOK (ID)
VALUES (1);
GOTO l;
END;
```
###### Oracle
```sql
BEGIN
<>
WHILE 1 = 1 LOOP
INSERT INTO BOOK (ID)
VALUES (1);
EXIT l;
END LOOP;
GOTO l;
END;
```
###### SQLDataWarehouse, SQLServer
```sql
BEGIN
l:
INSERT INTO BOOK (ID)
VALUES (1);
GOTO l;
END;
```
###### ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, Databricks, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, MariaDB, MemSQL, MySQL, Postgres, Redshift, SQLite, Snowflake, Spanner, Sybase, Teradata, Trino, Vertica, YugabyteDB
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
>
BEGIN NULL; END;
```
```java
// All dialects
Label label = label("label");
label.label(begin())
```
That's a lot of labels.
The main usages of these labels will be discussed in the following sections about, [EXIT](#procedural-exit), and [CONTINUE](#procedural-continue)
##### Dialect support
This example using jOOQ:
```java
l.label(loop())
```
Translates to the following dialect specific expressions:
###### Aurora Postgres, CockroachDB, Informix, Postgres, YugabyteDB
```sql
<>
LOOP
END LOOP
```
###### DB2, HSQLDB, MariaDB, MySQL, Trino
```sql
l:
LOOP
END LOOP
```
###### Firebird
```sql
l:
WHILE (1 = 1) DO BEGIN
END
```
###### H2
```sql
l:
for (;;) {
}
```
###### Oracle
```sql
<>
LOOP
NULL;
END LOOP
```
###### Snowflake
```sql
LOOP
NULL;
END LOOP l
```
###### SQLDataWarehouse, SQLServer
```sql
l:
WHILE 1 = 1 BEGIN
IF 1 = 0 SELECT 1;
END
```
###### ASE, Access, Aurora MySQL, BigQuery, ClickHouse, Databricks, DuckDB, Exasol, Hana, MemSQL, Redshift, SQLite, Spanner, Sybase, Teradata, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
i = var("i", INTEGER);
repeat(
insertInto(T).columns(T.COL).values(i),
i.set(i.plus(1))
).until(i.gt(10))
```
##### Dialect support
This example using jOOQ:
```java
repeat(deleteFrom(BOOK).where(BOOK.ID.eq(i)), i.set(i.plus(1))).until(i.gt(10))
```
Translates to the following dialect specific expressions:
###### Aurora Postgres, CockroachDB, Oracle, Postgres, YugabyteDB
```sql
<>
LOOP
DELETE FROM BOOK
WHERE BOOK.ID = i;
i := (i + 1);
EXIT alias_2 WHEN i > 10;
END LOOP
```
###### BigQuery, DB2, HSQLDB, MariaDB, MySQL, Trino
```sql
REPEAT
DELETE FROM BOOK
WHERE BOOK.ID = i;
SET i = (i + 1);
UNTIL i > 10
END REPEAT
```
###### Exasol
```sql
REPEAT
DELETE FROM BOOK
WHERE BOOK.ID = i;
i := (i + 1);
UNTIL i > 10
END REPEAT
```
###### Firebird
```sql
alias_2:
WHILE (1 = 1) DO BEGIN
DELETE FROM BOOK
WHERE BOOK.ID = :i;
:i = (:i + 1);
IF (:i > 10) THEN
LEAVE alias_2;
END
```
###### H2
```sql
do {
try (PreparedStatement s = c.prepareStatement(
"DELETE FROM BOOK\n" +
"WHERE BOOK.ID = ?"
)) {
s.setObject(1, i);
s.execute();
}
i = (i + 1);
}
while (!(i > 10))
```
###### Hana
```sql
WHILE 1 = 1 DO
DELETE FROM BOOK
WHERE BOOK.ID = i;
i = (i + 1);
IF i > 10 THEN
BREAK;
END IF;
END WHILE
```
###### Informix
```sql
<>
LOOP
DELETE FROM BOOK
WHERE BOOK.ID = i;
LET i = (i + 1);
EXIT alias_2 WHEN i > 10;
END LOOP
```
###### Snowflake
```sql
REPEAT
DELETE FROM BOOK
WHERE BOOK.ID = :i;
i := (:i + 1);
UNTIL (:i > 10)
END REPEAT
```
###### ASE, Access, Aurora MySQL, ClickHouse, Databricks, DuckDB, MemSQL, Redshift, SQLDataWarehouse, SQLServer, SQLite, Spanner, Sybase, Teradata, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
i = var("i", INTEGER);
```
This variable doesn't do anything on its own yet. But like many things in jOOQ, it has to be declared first, outside of an actual jOOQ expression, in order to be usable in jOOQ expressions.
We can now reference the variable in a declaration statement as follows:
```sql
-- MySQL syntax
DECLARE i INTEGER;
```
```java
// All dialects
declare(i)
```
Notice that there are many different ways to declare a local variable in different dialects. There is
**The Oracle PL/SQL, PostgreSQL pgplsql style**
In these languages, the `DECLARE` statement is actually not an independent statement that can be used anywhere. It is part of a [procedural block](#procedural-block), prepended to `BEGIN .. END`:
```sql
-- PL/SQL syntax
DECLARE
i INT;
BEGIN
NULL;
END;
```
When using jOOQ, you can safely ignore this fact, and prepend that there is a `DECLARE` statement also in these dialects. jOOQ will add additional `BEGIN .. END` blocks to your surrounding block, to make sure the whole block becomes syntactically and semantically correct.
**The T-SQL, MySQL style**
In these languages, the `DECLARE` statement is really an independent statement that can be used anywhere. Just like in the Java language, variables can be declared at any position and used only "further down", lexically. Ignoring T-SQL's JavaScript-esque understanding of scope for a moment.
```sql
-- T-SQL syntax
DECLARE @i INTEGER;
```
Notice that you can safely ignore the `@` sign that is required in some dialects, such as T-SQL. jOOQ will generate it for you.
##### Assignment
A local variable needs a way to have a value assigned to it. Assignments are possible both on [`org.jooq.Variable`](https://www.jooq.org/javadoc/3.22.x/org/jooq/Variable.html), or on [`org.jooq.Declaration`](https://www.jooq.org/javadoc/3.22.x/org/jooq/Declaration.html), directly. For example
```sql
-- T-SQL syntax
DECLARE @i INTEGER = 1;
```
```java
// All dialects
declare(i).set(1)
```
Alternatively, you can split declaration and assignment, or re-assign new values to variables:
```sql
-- T-SQL syntax
DECLARE @i INTEGER;
SET @i = 1;
SET @i = 2;
```
```java
// All dialects
declare(i),
i.set(1),
i.set(2)
```
Some dialects also support using subqueries in assignment expressions, and other expresions in their procedural languages. For example:
```sql
-- T-SQL syntax
SET @i = (SELECT MAX(col) FROM t);
```
```java
// All dialects
i.set(select(max(T.COL)).from(T))
```
The above is equivalent to this:
```sql
-- PL/SQL syntax
SELECT MAX(col) INTO i FROM t;
```
```java
// All dialects
select(max(T.COL)).into(i).from(T)
```
##### Row assignment
Some dialects support row assignment of variables, which other languages call "destructuring". This is particularly useful when assigning multiple values from a query:
```sql
-- HSQLDB syntax
SET (i, j) = (SELECT MIN(col), MAX(col) FROM t);
```
```java
// All dialects
row(i, j).set(select(min(T.COL), max(T.COL)).from(T))
```
The above is equivalent to this:
```sql
-- PL/SQL syntax
SELECT MIN(col), MAX(col) INTO i, j FROM t;
```
```java
// All dialects
select(min(T.COL), max(T.COL)).into(i, j).from(T)
```
##### Referencing
Obviously, once we've assigned a value to a local variable, we want to reference it as well in arbitrary expressions, and queries.
For this purpose, [`org.jooq.Variable`](https://www.jooq.org/javadoc/3.22.x/org/jooq/Variable.html) extends [`org.jooq.Field`](https://www.jooq.org/javadoc/3.22.x/org/jooq/Field.html), and as such, can be used in arbitrary places where any other [column expression](#column-expressions) can be used. Within the procedural language, a simple example would be to increment a local variable:
```sql
-- PL/SQL syntax
i := i + 1;
```
```java
// All dialects
i.set(i.plus(1))
```
Or in a more complete example, use it in a SQL statement:
```sql
-- PL/SQL syntax
DECLARE
i INT;
BEGIN
i := 1;
INSERT INTO t (col) VALUES (i);
END;
```
```java
// All dialects
Variable i = var("i", INTEGER);
create.begin(
declare(i),
i.set(1),
insertInto(T).columns(T.COL).values(i)
).execute();
```
##### Dialect support
This example using jOOQ:
```java
begin(declare(i), i.set(1))
```
Translates to the following dialect specific expressions:
###### Aurora Postgres, Postgres, YugabyteDB
```sql
DO $$
DECLARE
i int;
BEGIN
i := 1;
END;
$$
```
###### BigQuery
```sql
BEGIN
DECLARE i int64;
SET i = 1;
END;
```
###### CockroachDB
```sql
DO $$
DECLARE
i int4;
BEGIN
i := 1;
END;
$$
```
###### DB2
```sql
BEGIN
DECLARE i integer;
SET i = 1;
END
```
###### Exasol
```sql
BEGIN
i int;
i := 1;
END;
```
###### Firebird
```sql
EXECUTE BLOCK AS
DECLARE i integer;
BEGIN
:i = 1;
END
```
###### H2
```sql
CREATE ALIAS block_1794083324747_9642749 AS $$
void x(Connection c) throws SQLException {
Integer i = null;
i = 1;
}
$$;
CALL block_1794083324747_9642749();
DROP ALIAS block_1794083324747_9642749;
```
###### Hana
```sql
DO BEGIN
DECLARE i integer;
i = 1;
END;
```
###### HSQLDB
```sql
BEGIN ATOMIC
DECLARE i int;
SET i = 1;
END;
```
###### Informix
```sql
BEGIN
DEFINE i integer;
LET i = 1;
END;
```
###### MariaDB
```sql
BEGIN NOT ATOMIC
DECLARE i int;
SET i = 1;
END;
```
###### MySQL
```sql
CREATE PROCEDURE block_1794083325381_7381783()
MODIFIES SQL DATA
BEGIN
DECLARE i int;
SET i = 1;
END;
CALL block_1794083325381_7381783();
DROP PROCEDURE block_1794083325381_7381783;
```
###### Oracle
```sql
DECLARE
i number(10);
BEGIN
i := 1;
END;
```
###### Snowflake
```sql
BEGIN
LET i number(10);
i := 1;
END;
```
###### SQLDataWarehouse, SQLServer
```sql
BEGIN
DECLARE @i int;
SET @i = 1;
END;
```
###### Trino
```sql
BEGIN
DECLARE i int;
SET i = 1;
END
```
###### ASE, Access, Aurora MySQL, ClickHouse, Databricks, DuckDB, MemSQL, Redshift, SQLite, Spanner, Sybase, Teradata, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
i = var("i", INTEGER);
while_(i.le(10)).loop(
insertInto(T).columns(T.COL).values(i)
)
```
Notice that `while` is a reserved keyword in the Java language, so the jOOQ API cannot use it as a method name. We've suffixed such conflicts with an underscore: `while_()`.
##### Dialect support
This example using jOOQ:
```java
while_(i.le(10)).loop(deleteFrom(BOOK).where(BOOK.ID.eq(i)))
```
Translates to the following dialect specific expressions:
###### Aurora Postgres, CockroachDB, Exasol, Informix, Oracle, Postgres, YugabyteDB
```sql
WHILE i <= 10 LOOP
DELETE FROM BOOK
WHERE BOOK.ID = i;
END LOOP
```
###### BigQuery, DB2, HSQLDB, Hana, MariaDB, MySQL, Trino
```sql
WHILE i <= 10 DO
DELETE FROM BOOK
WHERE BOOK.ID = i;
END WHILE
```
###### Firebird
```sql
WHILE (:i <= 10) DO BEGIN
DELETE FROM BOOK
WHERE BOOK.ID = :i;
END
```
###### H2
```sql
while (i <= 10) {
try (PreparedStatement s = c.prepareStatement(
"DELETE FROM BOOK\n" +
"WHERE BOOK.ID = ?"
)) {
s.setObject(1, i);
s.execute();
}
}
```
###### Snowflake
```sql
WHILE (:i <= 10) DO
DELETE FROM BOOK
WHERE BOOK.ID = :i;
END WHILE
```
###### SQLDataWarehouse, SQLServer
```sql
WHILE @i <= 10 BEGIN
DELETE FROM BOOK
WHERE BOOK.ID = @i;
END
```
###### ASE, Access, Aurora MySQL, ClickHouse, Databricks, DuckDB, MemSQL, Redshift, SQLite, Spanner, Sybase, Teradata, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
.ID reference
```
Note that if you *know* that the `ID` column is of the same type as the `AUTHOR.ID` column, you can use that again to dereference the column as is explained again in the section [dereferencing table columns](#dereferenced-table-columns).
```java
// Now with inferred Integer type
Field id = named.field(AUTHOR.ID); // Produces a t.ID reference
Field id = unnamed.field(AUTHOR.ID); // Produces a .ID reference
```
> t1 =
name("t1").fields("f1", "f2").as(select(val(1), val("a")));
CommonTableExpression> t2 =
name("t2").fields("f3", "f4").as(select(val(2), val("b")));
Result result2 =
create.with(t1)
.with(t2)
.select(
t1.field("f1").add(t2.field("f3")).as("add"),
t1.field("f2").concat(t2.field("f4")).as("concat"))
.from(t1, t2)
.fetch();
```
Note that the [`org.jooq.CommonTableExpression`](https://www.jooq.org/javadoc/3.22.x/org/jooq/CommonTableExpression.html) type extends the commonly used [`org.jooq.Table`](https://www.jooq.org/javadoc/3.22.x/org/jooq/Table.html) type, and can thus be used wherever a table can be used.
##### Inlined common table expressions
If you're just operating on [plain SQL](#plain-sql), you may not need to keep intermediate references to such common table expressions. An example of such usage would be this:
```sql
WITH "a" AS (SELECT
1 AS "x",
'a' AS "y"
)
SELECT
FROM "a"
;
```
```java
create.with("a").as(select(
val(1).as("x"),
val("a").as("y")
))
.select()
.from(table(name("a")))
.fetch();
```
r1 = create.selectFrom(fBooks(null)).fetch();
// Lateral joining the table-valued function to another table using CROSS APPLY:
create.select(BOOK.ID, F_BOOKS.TITLE)
.from(BOOK.crossApply(fBooks(BOOK.ID)))
.fetch();
```
> r = create.selectFrom(generateSeries(1, 10)).fetch();
```
##### Dialect support
This example using jOOQ:
```java
selectFrom(generateSeries(1, 10))
```
Translates to the following dialect specific expressions:
###### Aurora Postgres, DuckDB, Postgres, YugabyteDB
```sql
SELECT generate_series.generate_series
FROM generate_series(1, 10)
```
###### BigQuery, Spanner
```sql
SELECT generate_series.generate_series
FROM (
SELECT null generate_series
FROM UNNEST([STRUCT(1 AS dual)]) AS dual
WHERE FALSE
UNION ALL
SELECT *
FROM unnest(generate_array(1, 10)) generate_series
) generate_series
```
###### ClickHouse
```sql
SELECT generate_series.generate_series
FROM (
SELECT null generate_series
WHERE FALSE
UNION ALL
SELECT *
FROM (
SELECT cast(number AS Nullable(integer)) generate_series
FROM numbers(1, (10 + 1))
) generate_series
) generate_series
```
###### CockroachDB
```sql
SELECT generate_series.generate_series
FROM generate_series(1, 10) generate_series (generate_series)
```
###### Databricks
```sql
SELECT generate_series.generate_series
FROM explode(sequence(1, 10)) generate_series (generate_series)
```
###### DB2
```sql
SELECT generate_series.generate_series
FROM (
WITH
generate_series(generate_series) AS (
SELECT 1
FROM SYSIBM.DUAL
UNION ALL
SELECT (generate_series + 1)
FROM generate_series
WHERE generate_series < 10
)
SELECT generate_series
FROM generate_series
) generate_series
```
###### Exasol, Oracle
```sql
SELECT generate_series.generate_series
FROM (
SELECT (level + (1 - 1)) generate_series
FROM DUAL
CONNECT BY level <= ((10 + 1) - 1)
) generate_series
```
###### Firebird
```sql
SELECT generate_series.generate_series
FROM (
WITH RECURSIVE
generate_series(generate_series) AS (
SELECT 1
FROM RDB$DATABASE
UNION ALL
SELECT (generate_series + 1)
FROM generate_series
WHERE generate_series < 10
)
SELECT generate_series
FROM generate_series
) generate_series
```
###### H2
```sql
SELECT generate_series.generate_series
FROM system_range(1, 10) generate_series (generate_series)
```
###### Hana
```sql
SELECT generate_series.generate_series
FROM (
SELECT cast(
null
AS integer
) generate_series
FROM SYS.DUMMY
WHERE 1 = 0
UNION ALL
SELECT *
FROM (
SELECT generated_period_end generate_series
FROM series_generate_integer(1, (1 - 1), 10)
) generate_series
) generate_series
```
###### HSQLDB
```sql
SELECT generate_series.generate_series
FROM (
WITH RECURSIVE
generate_series(generate_series) AS (
SELECT 1
FROM (VALUES (1)) AS dual (dual)
UNION ALL
SELECT (generate_series + 1)
FROM generate_series
WHERE generate_series < 10
)
SELECT generate_series
FROM generate_series
) generate_series
```
###### Informix
```sql
SELECT generate_series.generate_series
FROM (
SELECT (level + (1 - 1)) generate_series
FROM (
SELECT 1 AS dual
FROM systables
WHERE (tabid = 1)
) AS dual
CONNECT BY level <= ((10 + 1) - 1)
) generate_series
```
###### MariaDB, MySQL, SQLite, Trino
```sql
SELECT generate_series.generate_series
FROM (
WITH RECURSIVE
generate_series(generate_series) AS (
SELECT 1
UNION ALL
SELECT (generate_series + 1)
FROM generate_series
WHERE generate_series < 10
)
SELECT generate_series
FROM generate_series
) generate_series
```
###### Snowflake
```sql
SELECT generate_series.generate_series
FROM (
SELECT ((seq4() + 1) + (1 - 1)) generate_series
FROM TABLE(generator(rowcount => (10 - (1 - 1))))
) generate_series (generate_series)
```
###### SQLDataWarehouse
```sql
WITH
generate_series(generate_series) AS (
SELECT 1
UNION ALL
SELECT (generate_series + 1)
FROM generate_series
WHERE generate_series < 10
)
SELECT generate_series.generate_series
FROM (
SELECT generate_series
FROM generate_series
) generate_series
```
###### SQLServer
```sql
SELECT generate_series.generate_series
FROM (
SELECT *
FROM generate_series(1, 10)
) generate_series (generate_series)
```
###### Sybase
```sql
SELECT generate_series.generate_series
FROM (
WITH RECURSIVE
generate_series(generate_series) AS (
SELECT 1
FROM SYS.DUMMY
UNION ALL
SELECT (generate_series + 1)
FROM generate_series
WHERE generate_series < 10
)
SELECT generate_series
FROM generate_series
) generate_series
```
###### Teradata
```sql
WITH RECURSIVE
generate_series(generate_series) AS (
SELECT 1
FROM (
SELECT 1 AS "dual"
) AS "dual"
UNION ALL
SELECT (generate_series + 1)
FROM generate_series
WHERE generate_series < 10
)
SELECT generate_series.generate_series
FROM (
SELECT generate_series
FROM generate_series
) generate_series
```
###### ASE, Access, Aurora MySQL, MemSQL, Redshift, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
5 10 |
7 20 |
'
COLUMNS
id FOR ORDINALITY,
a INT,
x INT PATH 'b/x',
y INT PATH 'b/y'
)
```
```java
create.select()
.from(xmltable("//row")
.passing(xml(
""
+ "5 10 |
"
+ "7 20 |
"
+ " "
))
.column("id").forOrdinality()
.column("a", INTEGER)
.column("x", INTEGER).path("b/x")
.column("y", INTEGER).path("b/y"))
.fetch();
```
The result would look like this:
```text
+----+---+----+----+
| ID | A | X | Y |
+----+---+----+----+
| 1 | 5 | 10 | |
| 2 | 7 | | 20 |
+----+---+----+----+
```
##### Dialect support
This example using jOOQ:
```java
selectFrom(xmltable("//row").passing(xml("5 10 |
5 10 |
5 10 |
5 10 |
5 10 |
5 10 |
5 10 |
field1 = BOOK.TITLE;
// A function created from the DSL
Field field2 = trim(BOOK.TITLE);
// More complex function with advanced DSL syntax
Field field4 = listAgg(BOOK.TITLE)
.withinGroupOrderBy(BOOK.ID.asc())
.over().partitionBy(AUTHOR.ID);
```
` record type, and provides access to the container [`org.jooq.Table`](https://www.jooq.org/javadoc/3.22.x/org/jooq/Table.html) instance.
See the manual's section about [generated tables](#codegen-tables) for more information about what is really generated by the [code generator](#code-generation)
firstName = field(name("AUTHOR", "FIRST_NAME"), INTEGER);
```
Note that by default, these names are quoted (among other reasons to prevent [SQL injection](#sql-injection)), and thus case sensitive. For more details, please refer to [the section about names and identifiers](#names).
{
// Cast this field to the type of another field
Field cast(Field field);
// Cast this field to a given DataType
Field cast(DataType type);
// Cast this field to the default DataType for a given Class
Field cast(Class type);
}
// And additional convenience methods in the DSL:
public class DSL {
Field cast(Object object, Field field);
Field cast(Object object, DataType type);
Field cast(Object object, Class type);
Field castNull(Field field);
Field castNull(DataType type);
Field castNull(Class type);
}
```
> [!TIP]
> The `CAST` expression converts between data types directly in SQL. If you wish to change a data type without any effect on rendered SQL, you may prefer to [coerce](#datatype-coercions) your expressions.
##### Dialect support
This example using jOOQ:
```java
cast("1", VARCHAR(10))
```
Translates to the following dialect specific expressions:
###### Access
```sql
cstr('1')
```
###### ASE, Aurora Postgres, DB2, Databricks, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica, YugabyteDB
```sql
cast('1' AS varchar(10))
```
###### Aurora MySQL, MariaDB, MemSQL, MySQL
```sql
cast('1' AS char(10))
```
###### BigQuery, Spanner
```sql
cast('1' AS string)
```
###### ClickHouse
```sql
cast('1' AS String(10))
```
###### CockroachDB
```sql
cast('1' AS string(10))
```
###### Informix
```sql
cast('1' AS lvarchar(10))
```
###### Oracle
```sql
cast('1' AS varchar2(10))
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
field1 = val(1).coerce(String.class);
Field field2 = val("1").coerce(Integer.class);
```
In the above example, `field1` will be treated by jOOQ as a `Field`, binding the numeric literal `1` as a `VARCHAR` value. The same applies to `field2`, whose string literal `"1"` will be bound as an `INTEGER` value.
This technique is better than performing unsafe or rawtype casting in Java, if you cannot access the "right" field type from any given expression.
)
)
FROM UNNEST(ARRAY[1, 2, 2, 3]) AS e WITH OFFSET AS o
WHERE mod(e, 2) = 0
)
```
###### ClickHouse
```sql
arrayFilter(
e -> mod(e, 2) = 0,
ARRAY(1, 2, 2, 3)
)
```
###### CockroachDB
```sql
(
SELECT coalesce(
array_agg(e),
cast(ARRAY[] AS int4[])
)
FROM UNNEST(ARRAY[1, 2, 2, 3]) t (e)
WHERE mod(e, 2) = 0
)
```
###### Databricks
```sql
filter(
ARRAY(1, 2, 2, 3),
e -> (e % 2) = 0
)
```
###### DuckDB
```sql
array_filter(
ARRAY[1, 2, 2, 3],
LAMBDA e: (e % 2) = 0
)
```
###### H2, HSQLDB
```sql
(
SELECT coalesce(
array_agg(e),
cast(ARRAY[] AS int array)
)
FROM UNNEST(ARRAY[1, 2, 2, 3]) t (e)
WHERE mod(e, 2) = 0
)
```
###### Trino
```sql
filter(
ARRAY[1, 2, 2, 3],
e -> mod(e, 2) = 0
)
```
###### ASE, Access, Aurora MySQL, DB2, Exasol, Firebird, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Spanner, Sybase, Teradata, Vertica, YugabyteDB
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
)
)
FROM UNNEST(ARRAY[1, 2, 2, 3]) AS e WITH OFFSET AS o
)
```
###### ClickHouse
```sql
arrayMap(
e -> (e * e),
ARRAY(1, 2, 2, 3)
)
```
###### CockroachDB
```sql
(
SELECT coalesce(
array_agg((e * e)),
cast(ARRAY[] AS int4[])
)
FROM UNNEST(ARRAY[1, 2, 2, 3]) t (e)
)
```
###### Databricks
```sql
transform(
ARRAY(1, 2, 2, 3),
e -> (e * e)
)
```
###### DuckDB
```sql
array_transform(
ARRAY[1, 2, 2, 3],
LAMBDA e: (e * e)
)
```
###### H2, HSQLDB
```sql
(
SELECT coalesce(
array_agg((e * e)),
cast(ARRAY[] AS int array)
)
FROM UNNEST(ARRAY[1, 2, 2, 3]) t (e)
)
```
###### Trino
```sql
transform(
ARRAY[1, 2, 2, 3],
e -> (e * e)
)
```
###### ASE, Access, Aurora MySQL, DB2, Exasol, Firebird, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Spanner, Sybase, Teradata, Vertica, YugabyteDB
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
)
)
FROM UNNEST(ARRAY[1, 2, 2, 3]) AS e WITH OFFSET AS o
WHERE e <> 2
)
```
###### Databricks
```sql
array_remove(
ARRAY(1, 2, 2, 3),
2
)
```
###### DuckDB
```sql
array_filter(
ARRAY[1, 2, 2, 3],
LAMBDA e: e <> 2
)
```
###### H2, HSQLDB
```sql
(
SELECT coalesce(
array_agg(e),
cast(ARRAY[] AS int array)
)
FROM UNNEST(ARRAY[1, 2, 2, 3]) t (e)
WHERE e <> 2
)
```
###### ASE, Access, Aurora MySQL, ClickHouse, DB2, Exasol, Firebird, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Spanner, Sybase, Teradata, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
)
)
FROM UNNEST(ARRAY[1, 2, 2, 3]) AS e WITH OFFSET AS o
)
```
###### Databricks
```sql
array_replace(
ARRAY(1, 2, 2, 3),
2,
-1
)
```
###### DuckDB
```sql
array_transform(
ARRAY[1, 2, 2, 3],
LAMBDA e: CASE
WHEN e IS NOT DISTINCT FROM 2 THEN -1
ELSE e
END
)
```
###### H2, HSQLDB
```sql
(
SELECT coalesce(
array_agg(
CASE
WHEN e IS NOT DISTINCT FROM 2 THEN -1
ELSE e
END
),
cast(ARRAY[] AS int array)
)
FROM UNNEST(ARRAY[1, 2, 2, 3]) t (e)
)
```
###### Trino
```sql
transform(
ARRAY[1, 2, 2, 3],
e -> CASE
WHEN e IS NOT DISTINCT FROM 2 THEN -1
ELSE e
END
)
```
###### ASE, Access, Aurora MySQL, ClickHouse, DB2, Exasol, Firebird, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Spanner, Sybase, Teradata, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
)
)
)
FROM (
SELECT AUTHOR.ID a
FROM AUTHOR
) t
)
```
###### SQLite
```sql
(
SELECT json_group_array(a)
FROM (
SELECT AUTHOR.ID a
FROM AUTHOR
) t
)
```
###### SQLServer
```sql
coalesce(
(
SELECT json_arrayagg(a)
FROM (
SELECT AUTHOR.ID
FROM AUTHOR
) t (a)
),
'[]'
)
```
###### Trino
```sql
coalesce(
(
SELECT cast(array_agg(a) AS json)
FROM (
SELECT AUTHOR.ID
FROM AUTHOR
) t (a)
),
json_parse('[]')
)
```
###### ASE, Access, Aurora MySQL, Exasol, Firebird, HSQLDB, Hana, Informix, MemSQL, Redshift, SQLDataWarehouse, Sybase, Teradata, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
)
)
)
FROM (
SELECT LANGUAGE.DESCRIPTION a
FROM LANGUAGE
) t
)
```
###### SQLite
```sql
(
SELECT json_group_array(a)
FROM (
SELECT LANGUAGE.DESCRIPTION a
FROM LANGUAGE
) t
)
```
###### Trino
```sql
coalesce(
(
SELECT cast(array_agg(a) AS json)
FROM (
SELECT LANGUAGE.DESCRIPTION
FROM LANGUAGE
) t (a)
),
json_parse('[]')
)
```
###### ASE, Access, Aurora MySQL, ClickHouse, Databricks, Exasol, Firebird, HSQLDB, Hana, Informix, MemSQL, MySQL, Redshift, SQLDataWarehouse, SQLServer, Sybase, Teradata, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
)
)
)
FROM (
SELECT LANGUAGE.DESCRIPTION a
FROM LANGUAGE
) t
)
```
###### SQLite
```sql
(
SELECT json_group_array(a)
FROM (
SELECT LANGUAGE.DESCRIPTION a
FROM LANGUAGE
) t
)
```
###### SQLServer
```sql
coalesce(
(
SELECT json_arrayagg(a)
FROM (
SELECT LANGUAGE.DESCRIPTION
FROM LANGUAGE
) t (a)
),
'[]'
)
```
###### Trino
```sql
coalesce(
(
SELECT cast(array_agg(a) AS json)
FROM (
SELECT LANGUAGE.DESCRIPTION
FROM LANGUAGE
) t (a)
),
json_parse('[]')
)
```
###### ASE, Access, Aurora MySQL, Exasol, Firebird, HSQLDB, Hana, Informix, MemSQL, Redshift, SQLDataWarehouse, Sybase, Teradata, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
text-content | content content content content content content content content child = field("child", INTEGER);
Field parent = field("parent", INTEGER);
create.select(
child,
parent,
sysConnectByPath(child, "/"),
connectByIsCycle())
.from(values(
row(val(1), val(null, INTEGER)),
row(2, 1),
row(3, 2),
row(2, 3)).as(table("t"), child, parent))
.startWith(parent.isNull())
.connectByNoCycle(prior(child).eq(parent))
.fetch();
```
The result being, for example
```text
+-------+--------+--------+--------------------+
| child | parent | path | connect_by_iscycle |
+-------+--------+--------+--------------------+
| 1 | | /1 | 0 |
| 2 | 1 | /1/2 | 0 |
| 3 | 2 | /1/2/3 | 1 |
+-------+--------+--------+--------------------+
```
###### Dialect support
This example using jOOQ:
```java
connectByIsCycle()
```
Translates to the following dialect specific expressions:
###### Exasol, Informix, Oracle
```sql
connect_by_iscycle
```
###### ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DB2, Databricks, DuckDB, Firebird, H2, HSQLDB, Hana, MariaDB, MemSQL, MySQL, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Spanner, Sybase, Teradata, Trino, Vertica, YugabyteDB
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
child = field("child", INTEGER);
Field parent = field("parent", INTEGER);
ctx.select(
child,
parent,
sysConnectByPath(child, "/"),
connectByIsLeaf())
.from(values(
row(val(1), val(null, INTEGER)),
row(2, 1),
row(3, 2),
row(4, 1)).as(table("t"), child, parent))
.startWith(parent.isNull())
.connectByNoCycle(prior(child).eq(parent))
.fetch();
```
The result being, for example
```text
+-------+--------+---------------------+-------------------+
| child | parent | sys_connect_by_path | connect_by_isleaf |
+-------+--------+---------------------+-------------------+
| 1 | {null} | /1 | false |
| 2 | 1 | /1/2 | false |
| 3 | 2 | /1/2/3 | true |
| 4 | 1 | /1/4 | true |
+-------+--------+---------------------+-------------------+
```
###### Dialect support
This example using jOOQ:
```java
connectByIsLeaf()
```
Translates to the following dialect specific expressions:
###### Exasol, Informix, Oracle
```sql
connect_by_isleaf
```
###### ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DB2, Databricks, DuckDB, Firebird, H2, HSQLDB, Hana, MariaDB, MemSQL, MySQL, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Spanner, Sybase, Teradata, Trino, Vertica, YugabyteDB
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
child = field("child", INTEGER);
Field parent = field("parent", INTEGER);
create.select(
child,
parent,
sysConnectByPath(child, "/"),
connectByRoot(child))
.from(values(
row(val(1), val(null, INTEGER)),
row(2, 1),
row(val(3), val(null, INTEGER)),
row(4, 3)).as(table("t"), child, parent))
.startWith(parent.isNull())
.connectByNoCycle(prior(child).eq(parent))
.fetch();
```
The result being, for example
```text
+-------+--------+---------------------+-----------------+
| child | parent | sys_connect_by_path | connect_by_root |
+-------+--------+---------------------+-----------------+
| 1 | {null} | /1 | 1 |
| 2 | 1 | /1/2 | 1 |
| 3 | {null} | /3 | 3 |
| 4 | 3 | /3/4 | 3 |
+-------+--------+---------------------+-----------------+
```
###### Dialect support
This example using jOOQ:
```java
connectByRoot(AUTHOR.ID)
```
Translates to the following dialect specific expressions:
###### Exasol, Informix, Oracle, Snowflake
```sql
connect_by_root AUTHOR.ID
```
###### ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DB2, Databricks, DuckDB, Firebird, H2, HSQLDB, Hana, MariaDB, MemSQL, MySQL, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Spanner, Sybase, Teradata, Trino, Vertica, YugabyteDB
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
child = field("child", INTEGER);
Field parent = field("parent", INTEGER);
ctx.select(
child,
parent,
sysConnectByPath(child, "/"),
level())
.from(values(
row(val(1), val(null, INTEGER)),
row(2, 1),
row(3, 2),
row(4, 1)).as(table("t"), child, parent))
.startWith(parent.isNull())
.connectByNoCycle(prior(child).eq(parent))
.fetch();
```
The result being, for example
```text
+-------+--------+---------------------+-------+
| child | parent | sys_connect_by_path | level |
+-------+--------+---------------------+-------+
| 1 | {null} | /1 | 1 |
| 2 | 1 | /1/2 | 2 |
| 3 | 2 | /1/2/3 | 3 |
| 4 | 1 | /1/4 | 2 |
+-------+--------+---------------------+-------+
```
###### Dialect support
This example using jOOQ:
```java
level()
```
Translates to the following dialect specific expressions:
###### Aurora Postgres, CockroachDB, DB2, Exasol, Firebird, H2, HSQLDB, Informix, MariaDB, MySQL, Oracle, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Trino, YugabyteDB
```sql
level
```
###### ASE, Access, Aurora MySQL, BigQuery, ClickHouse, Databricks, DuckDB, Hana, MemSQL, Spanner, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
child = field("child", INTEGER);
Field parent = field("parent", INTEGER);
ctx.select(
child,
parent,
sysConnectByPath(child, "/"))
.from(values(
row(val(1), val(null, INTEGER)),
row(2, 1),
row(3, 2)).as(table("t"), child, parent))
.startWith(parent.isNull())
.connectByNoCycle(prior(child).eq(parent))
.fetch();
```
The result being, for example
```text
+-------+--------+---------------------+
| child | parent | sys_connect_by_path |
+-------+--------+---------------------+
| 1 | {null} | /1 |
| 2 | 1 | /1/2 |
| 3 | 2 | /1/2/3 |
+-------+--------+---------------------+
```
###### Dialect support
This example using jOOQ:
```java
prior(AUTHOR.ID)
```
Translates to the following dialect specific expressions:
###### Exasol, Informix, Oracle, Redshift, Snowflake
```sql
PRIOR AUTHOR.ID
```
###### ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DB2, Databricks, DuckDB, Firebird, H2, HSQLDB, Hana, MariaDB, MemSQL, MySQL, Postgres, SQLDataWarehouse, SQLServer, SQLite, Spanner, Sybase, Teradata, Trino, Vertica, YugabyteDB
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
child = field("child", INTEGER);
Field parent = field("parent", INTEGER);
ctx.select(
child,
parent,
sysConnectByPath(child, "/"))
.from(values(
row(val(1), val(null, INTEGER)),
row(2, 1),
row(3, 2)).as(table("t"), child, parent))
.startWith(parent.isNull())
.connectByNoCycle(prior(child).eq(parent))
.fetch();
```
The result being, for example
```text
+-------+--------+---------------------+
| child | parent | sys_connect_by_path |
+-------+--------+---------------------+
| 1 | {null} | /1 |
| 2 | 1 | /1/2 |
| 3 | 2 | /1/2/3 |
+-------+--------+---------------------+
```
###### Dialect support
This example using jOOQ:
```java
sysConnectByPath(AUTHOR.ID, "/")
```
Translates to the following dialect specific expressions:
###### Exasol, Informix, Oracle, Snowflake
```sql
sys_connect_by_path(AUTHOR.ID, '/')
```
###### ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DB2, Databricks, DuckDB, Firebird, H2, HSQLDB, Hana, MariaDB, MemSQL, MySQL, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Spanner, Sybase, Teradata, Trino, Vertica, YugabyteDB
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
)
)
)
```
###### ClickHouse
```sql
toJSONString(groupArray(AUTHOR.ID))
```
###### Databricks, DuckDB
```sql
to_json(array_agg(AUTHOR.ID))
```
###### DB2
```sql
cast(
('[' || listagg(
AUTHOR.ID,
','
) || ']')
AS varchar(32672)
)
```
###### MariaDB, MySQL
```sql
json_merge_preserve(
'[]',
concat(
'[',
group_concat(AUTHOR.ID SEPARATOR ','),
']'
)
)
```
###### Snowflake
```sql
array_agg(coalesce(
to_variant(AUTHOR.ID),
parse_json('null')
))
```
###### SQLite
```sql
json_group_array(AUTHOR.ID)
```
###### Trino
```sql
cast(array_agg(AUTHOR.ID) AS json)
```
###### ASE, Access, Exasol, Firebird, HSQLDB, Hana, Informix, MemSQL, Redshift, Sybase, Teradata, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
)
)
))
```
###### DuckDB
```sql
to_json(array_agg(LANGUAGE.DESCRIPTION) FILTER (WHERE LANGUAGE.DESCRIPTION IS NOT NULL))
```
###### MariaDB, MySQL
```sql
json_merge_preserve(
'[]',
concat(
'[',
group_concat(json_quote(LANGUAGE.DESCRIPTION) SEPARATOR ','),
']'
)
)
```
###### Snowflake
```sql
array_agg(LANGUAGE.DESCRIPTION)
```
###### SQLite
```sql
json_group_array(LANGUAGE.DESCRIPTION) FILTER (WHERE LANGUAGE.DESCRIPTION IS NOT NULL)
```
###### Trino
```sql
cast(array_agg(LANGUAGE.DESCRIPTION) FILTER (WHERE LANGUAGE.DESCRIPTION IS NOT NULL) AS json)
```
###### ASE, Access, ClickHouse, DB2, Databricks, Exasol, Firebird, HSQLDB, Hana, Informix, MemSQL, Redshift, Sybase, Teradata, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
)
)
)
```
###### SQLite
```sql
json_group_array(LANGUAGE.DESCRIPTION)
```
###### Trino
```sql
cast(array_agg(LANGUAGE.DESCRIPTION) AS json)
```
###### ASE, Access, BigQuery, ClickHouse, DB2, Databricks, Exasol, Firebird, HSQLDB, Hana, Informix, MemSQL, Redshift, Sybase, Teradata, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
)
)
)
```
###### Databricks
```sql
array_agg(STRUCT (
coalesce(BOOK.ID),
coalesce(BOOK.TITLE)
))
```
###### DB2
```sql
xmlelement(
NAME result,
xmlagg(xmlelement(
NAME record,
xmlelement(NAME v0, BOOK.ID),
xmlelement(
NAME v1,
xmlattributes(
CASE
WHEN BOOK.TITLE IS NULL THEN 'true'
END AS "xsi:nil"
),
BOOK.TITLE
)
))
)
```
###### DuckDB
```sql
array_agg(ROW (BOOK.ID, BOOK.TITLE))
```
###### H2
```sql
json_arrayagg(json_array(
BOOK.ID, BOOK.TITLE
NULL ON NULL
))
```
###### MariaDB, MySQL
```sql
json_merge_preserve(
'[]',
concat(
'[',
group_concat(json_array(BOOK.ID, BOOK.TITLE) SEPARATOR ','),
']'
)
)
```
###### Oracle
```sql
json_arrayagg(json_array(
BOOK.ID, BOOK.TITLE
NULL ON NULL
RETURNING clob
) FORMAT JSON RETURNING clob)
```
###### Snowflake
```sql
array_agg(array_construct(coalesce(
to_variant(BOOK.ID),
parse_json('null')
), coalesce(
to_variant(BOOK.TITLE),
parse_json('null')
)))
```
###### SQLite
```sql
json_group_array(json_array(BOOK.ID, BOOK.TITLE))
```
###### Teradata
```sql
xmlelement(
NAME "result",
xmlagg(xmlelement(
NAME record,
xmlelement(NAME v0, BOOK.ID),
xmlelement(
NAME v1,
xmlattributes(
CASE
WHEN BOOK.TITLE IS NULL THEN 'true'
END AS nil
),
BOOK.TITLE
)
))
)
```
###### Trino
```sql
cast(array_agg(cast(
ARRAY[
cast(BOOK.ID AS json),
cast(BOOK.TITLE AS json)
]
AS json
)) AS json)
```
###### ASE, Access, Aurora MySQL, ClickHouse, Exasol, Firebird, HSQLDB, Hana, Informix, MemSQL, Redshift, SQLDataWarehouse, SQLServer, Sybase, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
1 2 2 1 result =
create.select(
NAME(CUSTOMER.FIRST_NAME, CUSTOMER.LAST_NAME),
ADDRESS(
CUSTOMER.STREET,
CUSTOMER.NUMBER,
CUSTOMER.ZIP,
CUSTOMER.CITY,
COUNTRY(
CUSTOMER.COUNTRY_ISO_CODE,
CUSTOMER.COUNTRY_DESCRIPTION
)
))
.from(CUSTOMER)
.where(CUSTOMER.ID.eq(1))
.fetchOne();
System.out.println(result.value1().getFirstName());
System.out.println(result.value1().getLastName());
System.out.println(result.value2().getCountry().getIsoCode());
```
result =
create.select(CUSTOMER.NAME, CUSTOMER.ADDRESS)
.from(CUSTOMER)
.where(CUSTOMER.ID.eq(1))
.fetchOne();
System.out.println(result.value1().getFirstName());
System.out.println(result.value1().getLastName());
System.out.println(result.value2().getCountry().getIsoCode());
```
If you don't need to fetch the entire UDT, you can also dereference individual attributes directly in SQL, in a type safe way!
```java
Record3 result =
create.select(
CUSTOMER.NAME.FIRST_NAME,
CUSTOMER.NAME.LAST_NAME,
CUSTOMER.ADDRESS.COUNTRY.ISO_CODE)
.from(CUSTOMER)
.where(CUSTOMER.ID.eq(1))
.fetchOne();
System.out.println(result.value1());
System.out.println(result.value2());
System.out.println(result.value3());
```
currval();
// Get a field for the NEXTVAL sequence property
Field nextval();
```
So if you have a sequence like this in Oracle:
```sql
CREATE SEQUENCE s_author_id
```
You can then use your [generated sequence](#codegen-sequences) object directly in a SQL statement as such:
```java
// Reference the sequence in a SELECT statement:
Field s = S_AUTHOR_ID.nextval();
BigInteger nextID = create.select(s).fetchOne(s);
// Reference the sequence in an INSERT statement:
create.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.values(S_AUTHOR_ID.nextval(), val("William"), val("Shakespeare"))
.execute();
```
- For more information about generated sequences, refer to the manual's section about [generated sequences](#codegen-sequences)
- For more information about executing standalone calls to sequences, refer to the manual's section about [sequence execution](#sequence-execution)
>` type qualifies as a scalar subquery, irrespective of content and whether it is "correlated".
There are mostly 3 ways of creating scalar subqueries in jOOQ
- Type safe wrapping using [`DSL.field(Select)`](https://www.jooq.org/javadoc/3.22.x/org/jooq/impl/DSL.html#field(org.jooq.Select))
- Type unsafe wrapping using [`Select.asField()`](https://www.jooq.org/javadoc/3.22.x/org/jooq/Select.html#asField())
- Through convenience methods, such as [`Field.eq(Select)`](https://www.jooq.org/javadoc/3.22.x/org/jooq/Field.html#eq(org.jooq.Select))
For example:
```sql
SELECT
AUTHOR.ID, (
SELECT count(*) FROM AUTHOR
) AS authors
FROM AUTHOR
```
```java
create.select(
AUTHOR.ID,
field(selectCount().from(AUTHOR)).as("authors"))
.from(AUTHOR)
.fetch();
```
##### Correlated subqueries
A "correlated" subquery is a subquery (scalar or not) whose execution depends on the query that it is embedded in. It acts as a function taking the current row as an input argument.
```sql
SELECT
AUTHOR.ID, (
SELECT count(*)
FROM BOOK
WHERE BOOK.AUTHOR_ID = AUTHOR.ID
) AS books
FROM AUTHOR
```
```java
create.select(
AUTHOR.ID,
field(selectCount()
.from(BOOK)
.where(BOOK.AUTHOR_ID.eq(AUTHOR.ID)))
.from(AUTHOR)
.fetch();
```
In the above example, the subquery counts the number of books for each author from the outer query.
> result =
```
The result of the above query may look like this:
```text
+----------+---------+--------+--------------------------------------------------+
|first_name|last_name|books |book_stores |
+----------+---------+--------+--------------------------------------------------+
|George |Orwell |[en] |[Ex Libris, Orell Füssli] |
|Paulo |Coelho |[de, pt]|[Buchhandlung im Volkshaus, Ex Libris, Orell Fü...|
+----------+---------+--------+--------------------------------------------------+
```
>, // books
Result> // book_stores
>> result = ...
```
Notice also that in a lot of cases, using [RecordMappers](#recordmapper) can be very helpful when nesting collections to DTO trees, especially when combined with [ad hoc converters](#ad-hoc-converter).
The result of the above query may look like this:
```text
+----------+---------+-----------------------------+--------------------------------------------------+
|first_name|last_name|books |book_stores |
+----------+---------+-----------------------------+--------------------------------------------------+
|George |Orwell |[(en, English)] |[(Ex Libris), (Orell Füssli)] |
|Paulo |Coelho |[(de, Deutsch), (pt, {null})]|[(Buchhandlung im Volkshaus), (Ex Libris), (Ore...|
+----------+---------+-----------------------------+--------------------------------------------------+
```
Or, when [exported as JSON](#exporting-json) (alternatively, use [JSON_ARRAYAGG](#json-arrayagg-function) directly):
```text
[
{
"first_name": "George",
"last_name": "Orwell",
"books": [
{
"cd": "en",
"description": "English"
}
],
"book_stores": [
{ "book_store_name": "Ex Libris" },
{ "book_store_name": "Orell Füssli" }
]
},
{
"first_name": "Paulo",
"last_name": "Coelho",
"books": [
{
"cd": "de",
"description": "Deutsch"
},
{
"cd": "pt",
"description": null
}
],
"book_stores": [
{ "book_store_name": "Buchhandlung im Volkshaus" },
{ "book_store_name": "Ex Libris" },
{ "book_store_name": "Orell Füssli" }
]
}
]
```
Or, when [exported as XML](#exporting-xml) (alternatively, use [XMLAGG](#xml-agg-function) directly):
```text
George
Orwell
en
English
Ex Libris
Orell Füssli
Paulo
Coelho
de
Deutsch
pt
Buchhandlung im Volkshaus
Ex Libris
Orell Füssli
```
##### Implementation
The bad news is, hardly any dialect supports the `MULTISET` constructor natively (e.g. Informix or Oracle do). In all other dialects, it has to be emulated using SQL/JSON or SQL/XML. The above query may look like this, in PostgreSQL:
```sql
SELECT
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
(
SELECT COALESCE(
JSONB_AGG(JSONB_BUILD_ARRAY(V0, V1)),
JSONB_BUILD_ARRAY()
)
FROM (
SELECT DISTINCT
ALIAS_86077489.CD AS V0,
ALIAS_86077489.DESCRIPTION AS V1
FROM BOOK
JOIN LANGUAGE AS ALIAS_86077489
ON BOOK.LANGUAGE_ID = ALIAS_86077489.ID
WHERE BOOK.AUTHOR_ID = AUTHOR.ID
) AS T
) AS BOOKS,
(
SELECT COALESCE(
JSONB_AGG(JSONB_BUILD_ARRAY(V0)),
JSONB_BUILD_ARRAY()
)
FROM (
SELECT DISTINCT BOOK_TO_BOOK_STORE.BOOK_STORE_NAME AS V0
FROM BOOK_TO_BOOK_STORE
JOIN BOOK AS ALIAS_129518614
ON BOOK_TO_BOOK_STORE.BOOK_ID = ALIAS_129518614.ID
WHERE ALIAS_129518614.AUTHOR_ID = AUTHOR.ID
) AS T
) AS BOOK_STORES
FROM AUTHOR
ORDER BY AUTHOR.ID
```
As you might notice, this produces a slightly different JSON structure than what one might have created manually. It generates arrays of arrays, which look something like this in a formatted result:
```text
+----------+---------+---------------------------------+----------------------------------------------------------------+
|first_name|last_name|books |book_stores |
+----------+---------+---------------------------------+----------------------------------------------------------------+
|George |Orwell |[["en", "English"]] |[["Ex Libris"], ["Orell Füssli"]] |
|Paulo |Coelho |[["de", "Deutsch"], ["pt", null]]|[["Buchhandlung im Volkshaus"], ["Ex Libris"], ["Orell Füssli"]]|
+----------+---------+---------------------------------+----------------------------------------------------------------+
```
The benefits are:
1. Arrays take less space than objects in JSON, so the serialisation format is more optimal
2. Arrays don't care about duplicate column names, which can cause issues with various JSON parsers (even if JSON supports it)
3. Array elements have a well defined order, object keys do not, and index lookups are faster than name lookups
The resulting JSON or XML document will be parsed and mapped to a jOOQ [`org.jooq.Result`](https://www.jooq.org/javadoc/3.22.x/org/jooq/Result.html) and [`org.jooq.Record`](https://www.jooq.org/javadoc/3.22.x/org/jooq/Record.html) hierarchy.
By default, the "best" serialisation format is used (JSON, XML, or ARRAY in the future), but you can override it using [`Settings.emulateMultiset`](https://www.jooq.org/javadoc/3.22.x/org/jooq/conf/Settings.html#emulateMultiset), which offers the following values:
- [`DEFAULT`](https://www.jooq.org/javadoc/3.22.x/org/jooq/conf/NestedCollectionEmulation.html#DEFAULT): Let jOOQ decide how to serialise nested collections
- [`XML`](https://www.jooq.org/javadoc/3.22.x/org/jooq/conf/NestedCollectionEmulation.html#XML): Use XML to serialise nested collections
- [`JSON`](https://www.jooq.org/javadoc/3.22.x/org/jooq/conf/NestedCollectionEmulation.html#JSON): Use JSON to serialise nested collections
- [`JSONB`](https://www.jooq.org/javadoc/3.22.x/org/jooq/conf/NestedCollectionEmulation.html#JSONB): Use JSONB to serialise nested collections
- [`NATIVE`](https://www.jooq.org/javadoc/3.22.x/org/jooq/conf/NestedCollectionEmulation.html#NATIVE): Generate a native syntax
##### Dialect support
This example using jOOQ:
```java
multiset(select(BOOK.ID, BOOK.TITLE).from(BOOK))
```
Translates to the following dialect specific expressions:
###### Aurora Postgres, CockroachDB, Postgres, YugabyteDB
```sql
(
SELECT coalesce(
jsonb_agg(jsonb_build_array(t.v0, t.v1)),
jsonb_build_array()
)
FROM (
SELECT
BOOK.ID v0,
BOOK.TITLE v1
FROM BOOK
) t
)
```
###### BigQuery
```sql
(
SELECT coalesce(
to_json(
coalesce(
array_agg(json_array(t.v0, t.v1)), cast(ARRAY[] AS array)
)
),
json_array()
)
FROM (
SELECT
BOOK.ID v0,
BOOK.TITLE v1
FROM BOOK
) t
)
```
###### Databricks
```sql
(
SELECT array_agg(t.c)
FROM (
SELECT STRUCT (
coalesce(col1),
coalesce(col2)
)
FROM (
SELECT BOOK.ID, BOOK.TITLE
FROM BOOK
) t (col1, col2)
) t (c)
)
```
###### DB2
```sql
(
SELECT xmlelement(
NAME result,
xmlagg(xmlelement(
NAME record,
xmlelement(NAME v0, BOOK.ID),
xmlelement(
NAME v1,
xmlattributes(
CASE
WHEN BOOK.TITLE IS NULL THEN 'true'
END AS "xsi:nil"
),
BOOK.TITLE
)
))
)
FROM BOOK
)
```
###### DuckDB
```sql
ARRAY(
SELECT t
FROM (
SELECT BOOK.ID, BOOK.TITLE
FROM BOOK
) t
)
```
###### H2
```sql
(
SELECT coalesce(
json_arrayagg(json_array(
BOOK.ID, BOOK.TITLE
NULL ON NULL
)),
json_array(NULL ON NULL)
)
FROM BOOK
)
```
###### Informix
```sql
MULTISET(
SELECT BOOK.ID, BOOK.TITLE
FROM BOOK
)
```
###### MariaDB
```sql
(
SELECT coalesce(
json_merge_preserve(
'[]',
concat(
'[',
group_concat(json_array(BOOK.ID, BOOK.TITLE) SEPARATOR ','),
']'
)
),
json_array()
)
FROM BOOK
)
```
###### MySQL
```sql
(
SELECT coalesce(
json_merge_preserve(
'[]',
concat(
'[',
group_concat(json_array(t.v0, t.v1) SEPARATOR ','),
']'
)
),
json_array()
)
FROM (
SELECT
BOOK.ID v0,
BOOK.TITLE v1
FROM BOOK
) t
)
```
###### Oracle
```sql
(
SELECT coalesce(
json_arrayagg(json_array(
t.v0, t.v1
NULL ON NULL
RETURNING clob
) FORMAT JSON RETURNING clob),
json_array(RETURNING clob)
)
FROM (
SELECT
BOOK.ID v0,
BOOK.TITLE v1
FROM BOOK
) t
)
```
###### Snowflake
```sql
(
SELECT coalesce(
array_agg(array_construct(coalesce(
to_variant(BOOK.ID),
parse_json('null')
), coalesce(
to_variant(BOOK.TITLE),
parse_json('null')
))) WITHIN GROUP (ORDER BY NULL),
array_construct()
)
FROM BOOK
)
```
###### Spanner
```sql
(
SELECT coalesce(
to_json(
coalesce(
array_agg(json_array(BOOK.ID, BOOK.TITLE)), cast(ARRAY[] AS array)
)
),
json_array()
)
FROM BOOK
)
```
###### SQLite
```sql
(
SELECT coalesce(
json_group_array(json_array(t.v0, t.v1)),
json_array()
)
FROM (
SELECT
BOOK.ID v0,
BOOK.TITLE v1
FROM BOOK
) t
)
```
###### SQLServer
```sql
coalesce(
(
SELECT
v0 v0,
v1 v1
FROM (
SELECT BOOK.ID, BOOK.TITLE
FROM BOOK
) t (v0, v1)
FOR XML RAW ('record'), ELEMENTS XSINIL, BINARY BASE64, TYPE, ROOT ('result')
),
' Row1 row(T1 t1) { ... }
public static Row2 row(T1 t1, T2 t2) { ... }
public static Row3 row(T1 t1, T2 t2, T3 t3) { ... }
public static Row4 row(T1 t1, T2 t2, T3 t3, T4 t4) { ... }
// [ ... idem for Row5, Row6, Row7, ..., Row22 ]
// Degrees of more than 22 are supported without type-safety
public static RowN row(Object... values) { ... }
```
##### Using row value expressions in predicates
Row value expressions are incompatible with most other [QueryParts](#queryparts), but they can be used as a basis for constructing various [conditional expressions](#conditional-expressions), such as:
- [comparison predicates](#comparison-predicate-degree-n)
- [NULL predicates](#null-predicate-degree-n)
- [BETWEEN predicates](#between-predicate-degree-n)
- [IN predicates](#in-predicate-degree-n)
- [OVERLAPS predicate](#overlaps-predicate) (for degree 2 row value expressions only)
See the relevant sections for more details about how to use row value expressions in predicates.
##### Projecting row value expressions
Row value expressions can be used to [project nested records](#nested-records), which allows for powerful mapping of structure data directly in SQL.
##### Using row value expressions in UPDATE statements
The [UPDATE statement](#update-statement) also supports a variant where row value expressions are updated, rather than single columns. See the relevant section for more details
##### Higher-degree row value expressions
jOOQ chose to explicitly support degrees up to 22 to match Scala 2's typesafe tuple, function and product support. Just like in Scala, 22 is an arbitrary choice of number as good as any other (though, in the case of jOOQ, reasonably high for most SQL queries). Unlike Scala 2, however, jOOQ also supports higher degrees without the additional typesafety.
##### Dialect support
This example using jOOQ:
```java
row(BOOK.ID, BOOK.TITLE)
```
Translates to the following dialect specific expressions:
###### ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DB2, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, MariaDB, MemSQL, MySQL, Oracle, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Spanner, Sybase, Teradata, Trino, Vertica, YugabyteDB
```sql
(BOOK.ID, BOOK.TITLE)
```
###### Databricks
```sql
STRUCT (
coalesce(BOOK.ID),
coalesce(BOOK.TITLE)
)
```
###### Informix
```sql
ROW (BOOK.ID, BOOK.TITLE)
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
` is bound to the appropriate [Record1 to Record22](#record-n) type, which allows for easily projecting nested records:
```sql
SELECT
ID,
ROW(
FIRST_NAME,
LAST_NAME
)
FROM AUTHOR
```
```java
// Type inference via lambdas or var really shines here!
Result>> result =
create.select(
AUTHOR.ID,
row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME))
.from(AUTHOR)
.fetch();
```
##### Combining nested records with arrays
If your RDBMS supports `ARRAY` types and `ARRAY` constructors, and if nested records are natively supported, chances are that you can combine the two features. For example, to find all books for an author, as a nested collection rather than a flat [join](#join-clause):
```sql
SELECT
ID,
ROW(
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME
),
ARRAY(
SELECT BOOK.ID, BOOK.TITLE
FROM BOOK
WHERE BOOK.AUTHOR_ID = AUTHOR.ID
)
FROM AUTHOR
```
```java
// Type inference via lambdas or var really shines here!
Result,
Record2[]
>> result =
create.select(
AUTHOR.ID,
row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
array(
select(row(BOOK.ID, BOOK.TITLE))
.from(BOOK)
.where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
)
)
.from(AUTHOR)
.fetch();
```
##### Attaching RecordMappers to nested records
Nested records help structure your result sets using structural typing, but they really shine when you attach a `RecordMapper` to them. A [RecordMapper](#recordmapper) is a [`java.lang.FunctionalInterface`](https://docs.oracle.com/en/java/javase/25/docs/api/java.base/java/lang/FunctionalInterface.html) that can convert a `Record` subtype to any user type `E`. By calling e.g. [`Row2.mapping()`](https://www.jooq.org/javadoc/3.22.x/org/jooq/Row2.html#mapping(java.lang.Class,org.jooq.Function2)), you can attach an [ad-hoc converter](#ad-hoc-converter) to the nested record type to turn the nested object into something much more meaningful:
```java
// Especially useful using Java 16 record types!
record Name(String firstName, String lastName) {}
record Author(int id, Name name) {}
// The "scary" structural type has gone!
List authors =
create.select(
AUTHOR.ID,
row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).mapping(Name::new)
)
.from(AUTHOR)
.fetch(Records.mapping(Author::new));
```
All of the above is type safe and uses no reflection! Try it out yourself - add or remove a column to the query or to the records, and observe the compilation errors that appear.
Now for the `ARRAY` example:
```java
record Name(String firstName, String lastName) {}
record Book(int id, String title) {}
record Author(int id, Name name, Book[] books) {}
// Again, no structural typing here has gone!
List authors =
create.select(
AUTHOR.ID,
row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).mapping(Name::new),
array(
select(row(BOOK.ID, BOOK.TITLE).mapping(Book.class, Book::new)
.from(BOOK)
.where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
)
)
.from(AUTHOR)
.fetch(Records.mapping(Author::new));
```
Again, everything is type safe. Unfortunately, reflection is needed in this case to construct a `Book[]` array. You must pass the `Book.class` reference to help jOOQ with that. If you prefer lists, no problem. You can wrap the array again using the same technique, using an explicit [ad-hoc converter](#ad-hoc-converter):
```java
record Name(String firstName, String lastName) {}
record Book(int id, String title) {}
record Author(int id, Name name, List books) {} // Is now using a List instead of Book[]
// Again, no structural typing here has gone!
List authors =
create.select(
AUTHOR.ID,
row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).mapping(Name::new),
array(
select(row(BOOK.ID, BOOK.TITLE).mapping(Book.class, Book::new)
.from(BOOK)
.where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
).convertFrom(Arrays::asList) // Additional converter here
)
.from(AUTHOR)
.fetch(Records.mapping(Author::new));
```
##### Dialect support
This example using jOOQ:
```java
select(row(BOOK.ID, BOOK.TITLE))
```
Translates to the following dialect specific expressions:
###### Access
```sql
SELECT BOOK.ID [nested__ID],
BOOK.TITLE [nested__TITLE]
FROM (
SELECT count(*) dual
FROM MSysResources
) AS dual
```
###### ASE, SQLDataWarehouse, SQLServer
```sql
SELECT BOOK.ID [nested__ID],
BOOK.TITLE [nested__TITLE]
```
###### Aurora MySQL, MemSQL
```sql
SELECT BOOK.ID `nested__ID`,
BOOK.TITLE `nested__TITLE`
FROM DUAL
```
###### Aurora Postgres, CockroachDB, DuckDB, H2, Postgres, YugabyteDB
```sql
SELECT ROW (BOOK.ID, BOOK.TITLE) nested
```
###### BigQuery, MariaDB, MySQL, SQLite, Spanner
```sql
SELECT BOOK.ID `nested__ID`,
BOOK.TITLE `nested__TITLE`
```
###### ClickHouse
```sql
SELECT TUPLE (BOOK.ID, BOOK.TITLE) nested
```
###### Databricks
```sql
SELECT STRUCT (
coalesce(BOOK.ID),
coalesce(BOOK.TITLE)
) nested
```
###### DB2
```sql
SELECT BOOK.ID "nested__ID",
BOOK.TITLE "nested__TITLE"
FROM SYSIBM.DUAL
```
###### Exasol, Oracle, Redshift, Snowflake, Trino, Vertica
```sql
SELECT BOOK.ID "nested__ID",
BOOK.TITLE "nested__TITLE"
```
###### Firebird
```sql
SELECT BOOK.ID "nested__ID",
BOOK.TITLE "nested__TITLE"
FROM RDB$DATABASE
```
###### Hana
```sql
SELECT BOOK.ID "nested__ID",
BOOK.TITLE "nested__TITLE"
FROM SYS.DUMMY
```
###### HSQLDB
```sql
SELECT BOOK.ID "nested__ID",
BOOK.TITLE "nested__TITLE"
FROM (VALUES (1)) AS dual (dual)
```
###### Informix
```sql
SELECT ROW (BOOK.ID, BOOK.TITLE) nested
FROM (
SELECT 1 AS dual
FROM systables
WHERE (tabid = 1)
) AS dual
```
###### Sybase
```sql
SELECT BOOK.ID [nested__ID],
BOOK.TITLE [nested__TITLE]
FROM SYS.DUMMY
```
###### Teradata
```sql
SELECT BOOK.ID "nested__ID",
BOOK.TITLE "nested__TITLE"
FROM (
SELECT 1 AS "dual"
) AS "dual"
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
list = List.of(1, 7);
Condition condition = list
.stream()
.map(BOOK.ID::eq)
.reduce(falseCondition(), Condition::or)
```
If a dialect does not support [boolean column types](#boolean-column), jOOQ will simply generate `1 = 0`.
##### NO condition
If you think that the "left-over" identity that is generated from the above reductions is ugly, you can just use the auxiliary [`DSL.noCondition()`](https://www.jooq.org/javadoc/3.22.x/org/jooq/impl/DSL.html#noCondition()), which acts as a pseudo-identity for both `AND` and `OR`, not generating any SQL, except if the reduction produces nothing (from an empty set), in case of which it will behave like `TRUE`, which is what you usually want when placing a dynamic condition in a `WHERE` clause.
If used with actual data:
```sql
ID = 1
OR
ID = 7
```
```java
List list = List.of(1, 7);
Condition condition = list.stream().map(BOOK.ID::eq)
.reduce(noCondition(), Condition::or)
```
If used with empty sets:
```sql
TRUE
```
```java
List list = List.of(1, 7);
Condition condition = list.stream().map(BOOK.ID::eq)
.reduce(noCondition(), Condition::or)
```
Some additional information about the `noCondition()` and related topics can be found in the section about [dynamic SQL](#dynamic-sql).
` column types in jOOQ's [code generator](#code-generation). But even if your dialect doesn't support the `BOOLEAN` type out of the box, you may have applied a [data type rewrite](#codegen-database-forced-types-rewrite) to force a `TINYINT(1)` or `CHAR(1)` or `NUMBER(1)` column to act as a `BOOLEAN`.
When you have such a column, you will want to use it as a condition, and vice-versa. A [`org.jooq.Field`](https://www.jooq.org/javadoc/3.22.x/org/jooq/Field.html) can be turned into a [`org.jooq.Condition`](https://www.jooq.org/javadoc/3.22.x/org/jooq/Condition.html) using [`DSL.condition(Field)`](https://www.jooq.org/javadoc/3.22.x/org/jooq/impl/DSL.html#condition(Field)). The inverse operation can be achieved using [`DSL.field(Condition)`](https://www.jooq.org/javadoc/3.22.x/org/jooq/impl/DSL.html#field(Condition)):
```java
Condition condition = BOOK.TITLE.eq("Animal Farm");
Field field = field(condition);
// Fetch boolean values from a table
create.select(field).from(BOOK).fetch();
// Use a boolean field as a condition
create.selectFrom(BOOK).where(field).fetch();
```
); // = (some column expression)
eq or equal(Select>); // = (some scalar SELECT statement)
ne or notEqual(T); // <> (some bind value)
ne or notEqual(Field); // <> (some column expression)
ne or notEqual(Select>); // <> (some scalar SELECT statement)
lt or lessThan(T); // < (some bind value)
lt or lessThan(Field); // < (some column expression)
lt or lessThan(Select>); // < (some scalar SELECT statement)
le or lessOrEqual(T); // <= (some bind value)
le or lessOrEqual(Field); // <= (some column expression)
le or lessOrEqual(Select>); // <= (some scalar SELECT statement)
gt or greaterThan(T); // > (some bind value)
gt or greaterThan(Field); // > (some column expression)
gt or greaterThan(Select>); // > (some scalar SELECT statement)
ge or greaterOrEqual(T); // >= (some bind value)
ge or greaterOrEqual(Field); // >= (some column expression)
ge or greaterOrEqual(Select>); // >= (some scalar SELECT statement)
```
Note that every operator is represented by two methods. A verbose one (such as `equal()`) and a two-character one (such as `eq()`). Both methods are the same. You may choose either one, depending on your taste. The manual will always use the more verbose one.
##### jOOQ's convenience methods using comparison operators
In addition to the above, jOOQ provides a few convenience methods for common operations performed on strings using comparison predicates:
```sql
LOWER(TITLE) = LOWER('animal farm')
```
```java
BOOK.TITLE.equalIgnoreCase("animal farm")
```
t1, Field t2);
Condition overlaps(Row2 row);
```
This allows for expressing the above predicates as such:
```java
// The date range tuples version
row(Date.valueOf('2010-01-01'), Date.valueOf('2010-01-03')).overlaps(Date.valueOf('2010-01-02'), Date.valueOf('2010-01-04'))
// The INTERVAL tuples version
row(Date.valueOf('2010-01-01'), new DayToSecond(2)).overlaps(Date.valueOf('2010-01-02'), new DayToSecond(2))
```
##### jOOQ's extensions to the standard
Unlike the standard (or any database implementing the standard), jOOQ also supports the `OVERLAPS` predicate for comparing arbitrary [row vlaue expressions of degree 2](#row-value-expressions). For instance, `(1, 3) OVERLAPS (2, 4)` will yield true in jOOQ. This is emulated as such
```sql
-- This predicate
(A, B) OVERLAPS (C, D)
-- can be emulated as such
(C <= B) AND (A <= D)
```
##### Dialect support
This example using jOOQ:
```java
row(1, 3).overlaps(row(2, 4))
```
Translates to the following dialect specific expressions:
###### All dialects
```sql
(
2 <= 3
AND 1 <= 4
)
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
books1 =
DSL.using(configuration)
.selectFrom(BOOK)
.where(condition(book))
.fetch();
// Using the convenience API on DSLContext
Result books2 = DSL.using(configuration).fetchByExample(book);
```
The latter API call makes use of the convenience API [`DSLContext.fetchByExample(TableRecord)`](https://www.jooq.org/javadoc/3.22.x/org/jooq/DSLContext.html#fetchByExample(org.jooq.TableRecord)).
`](https://www.jooq.org/javadoc/3.22.x/org/jooq/Field.html) type, where the `T` type variable is derived from its [`Field.getDataType()`](https://www.jooq.org/javadoc/3.22.x/org/jooq/Typed.html#getDataType()) property. The [`org.jooq.DataType`](https://www.jooq.org/javadoc/3.22.x/org/jooq/DataType.html) API models the rich world of SQL data types. A `DataType` models:
- A database type reference, such as [VARCHAR](#data-type-varchar) or [INTEGER](#data-type-integer)
- A variety of flags modifying the type, such as [precision](#data-type-length), [precision](#data-type-precision), [scale](#data-type-scale), and many more.
- A Java representation of the database type reference, usually denoted as the `T` type variable (for "type"), e.g. [`java.lang.String`](https://docs.oracle.com/en/java/javase/25/docs/api/java.base/java/lang/String.html) or [`java.lang.Integer`](https://docs.oracle.com/en/java/javase/25/docs/api/java.base/java/lang/Integer.html)
- A Java representation of the user type reference, usually denoted as the `U` type variable (for "user type"), e.g. `Email` if it's a [custom data type](#converted-data-types). By default, this is the same as `T`.
Data types include:
- [Built-in data types](#built-in-data-types): These are types that are supported by the underlying RDBMS directly, such as [VARCHAR](#data-type-varchar) or [INTEGER](#data-type-integer). jOOQ's core library offers support for these out of the box, and can emulate some of them where not supported, e.g. [UUID](#data-type-uuid) is just a `VARCHAR(36)`
- [Extended data types](#extended-data-types): These are also built-in data types, but very dialect specific, such that jOOQ's core library doesn't offer them out of the box.
- [Enum data types](#enum-data-types): Some dialects support a special kind of constraint on [VARCHAR](#data-type-varchar) style types, which allows only for certain enumerated values in the type.
- [Domain data types](#domain-data-types): The SQL standard specifies `DOMAIN` types to be a set of re-usable constraints on an existing data type, which is supported in numerous dialects.
- [User-defined data types](#user-defined-data-types): The SQL standard specifies user-defined types to be a number of refinements on existing types, or the creation of a object type. The latter is what a few ORDBMS, JBDC, and jOOQ support.
- [Converted data types](#converted-data-types): Any data type `T` of the above list can be converted to a user type `U` using a [Converter](#data-type-converter) and/or [Binding](#data-type-binding), in order to change the user representation in Java code.
`
```java
Hstore store = create.fetchValue(T.HSTORE, T.ID.eq(id));
Map value = store.data();
```
> [!TIP]
> Support for this data type is included in the [jooq-postgres-extensions](#codegen-extension-postgres) module.
dayOfWeekType = VARCHAR.asEnumDataType(DayOfWeek.class);
```
And then, the type can be attached to any expression, e.g. if generated code is not available:
```java
// Plain SQL template based
Field f1 = DSL.field("my_table.day_of_week", emailType);
// Name based
Field f2 = DSL.field(name("my_table", "day_of_week"), emailType);
```
` for this domain type, the generated code can be used as follows:
```java
DataType emailType = Domains.EMAIL.getDataType();
```
Additional type safety can be achieved using [embedded domains](#codegen-embedded-domains), in case of which:
```java
DataType emailType = Domains.EMAIL.getDataType();
```
` is available for use in expressions of type `PointRecord`, wherever this UDT is referenced.
` converts from some [Built-in data types](#built-in-data-types) to a custom data type. The simplest way to get a `DataType {
// Your conversion logic goes into these two methods, that can convert
// between the database type T and the user type U in both directions:
U from(T databaseObject);
T to(U userObject);
// You need to provide Class instances for each type, too:
Class fromType();
Class toType();
}
```
Or, using a specific example:
```java
record Email(String email) {}
// Using a simpler, functional API:
Converter emailConverter = Converter.of(
String.class,
Email.class,
// Converting String -> Email
Email::new,
// Converting Email -> String
Email::email
);
DataType emailType = VARCHAR.asConvertedDataType(emailConverter);
```
In most practical cases, the `DataType f1 = DSL.field("my_table.my_email_column", emailType);
// Name based
Field f2 = DSL.field(name("my_table", "my_email_column"), emailType);
```
> [!TIP]
> In many cases, converters are single use and single direction only (e.g. fetching only). In those cases, there exists convenience API that is much less verbose to use, see [ad-hoc converters](#ad-hoc-converter) for details.
extends Serializable {
// A converter that does the conversion between the database type T
// and the user type U (see previous examples)
Converter converter();
// A callback that generates the SQL string for bind values of this
// binding type. Typically, just ?, but also ?::json, etc.
void sql(BindingSQLContext ctx) throws SQLException;
// Callbacks that implement all interaction with JDBC types, such as
// PreparedStatement, CallableStatement, SQLOutput, SQLinput, ResultSet
void register(BindingRegisterContext ctx) throws SQLException;
void set(BindingSetStatementContext ctx) throws SQLException;
void set(BindingSetSQLOutputContext ctx) throws SQLException;
void get(BindingGetResultSetContext ctx) throws SQLException;
void get(BindingGetStatementContext ctx) throws SQLException;
void get(BindingGetSQLInputContext ctx) throws SQLException;
}
```
Below is full fledged example implementation that uses Google Gson to model JSON documents in Java
```java
// We're binding = JSON (or JSONB), and = JsonElement (user type)
// Alternatively, extend org.jooq.impl.AbstractBinding to implement fewer methods.
public class PostgresJSONGsonBinding implements Binding {
private final Gson gson = new Gson();
// The converter does all the work
@Override
public Converter converter() {
return new Converter() {
@Override
public JsonElement from(JSON t) {
return t == null ? JsonNull.INSTANCE : gson.fromJson(t.data(), JsonElement.class);
}
@Override
public JSON to(JsonElement u) {
return u == null || u == JsonNull.INSTANCE ? null : JSON.json(gson.toJson(u));
}
@Override
public Class fromType() {
return JSON.class;
}
@Override
public Class toType() {
return JsonElement.class;
}
};
}
// Rending a bind variable for the binding context's value and casting it to the json type
@Override
public void sql(BindingSQLContext ctx) throws SQLException {
// Depending on how you generate your SQL, you may need to explicitly distinguish
// between jOOQ generating bind variables or inlined literals.
if (ctx.render().paramType() == ParamType.INLINED)
ctx.render().visit(DSL.inline(ctx.convert(converter()).value())).sql("::json");
else
ctx.render().sql(ctx.variable()).sql("::json");
}
// Registering VARCHAR types for JDBC CallableStatement OUT parameters
@Override
public void register(BindingRegisterContext ctx) throws SQLException {
ctx.statement().registerOutParameter(ctx.index(), Types.VARCHAR);
}
// Converting the JsonElement to a String value and setting that on a JDBC PreparedStatement
@Override
public void set(BindingSetStatementContext ctx) throws SQLException {
JSON json = ctx.convert(converter()).value();
ctx.statement().setString(ctx.index(), json == null ? null : json.data());
}
// Getting a String value from a JDBC ResultSet and converting that to a JsonElement
@Override
public void get(BindingGetResultSetContext ctx) throws SQLException {
ctx.convert(converter()).value(JSON.json(ctx.resultSet().getString(ctx.index())));
}
// Getting a String value from a JDBC CallableStatement and converting that to a JsonElement
@Override
public void get(BindingGetStatementContext ctx) throws SQLException {
ctx.convert(converter()).value(JSON.json(ctx.statement().getString(ctx.index())));
}
// Setting a value on a JDBC SQLOutput (useful for Oracle OBJECT types)
@Override
public void set(BindingSetSQLOutputContext ctx) throws SQLException {
throw new SQLFeatureNotSupportedException();
}
// Getting a value from a JDBC SQLInput (useful for Oracle OBJECT types)
@Override
public void get(BindingGetSQLInputContext ctx) throws SQLException {
throw new SQLFeatureNotSupportedException();
}
}
```
Just like with simple [Converters](#data-type-converter), a [`org.jooq.Binding`](https://www.jooq.org/javadoc/3.22.x/org/jooq/Binding.html) can be attached to a [column expression](#column-expressions) either directly in the code generator using [forced types](#codegen-database-forced-types) configuration, or explictly, e.g. when constructing [plain SQL](#plain-sql) expressions:
```java
// Construct the DataType:
DataType jsonElementType = JSON.asConvertedDataType(new PostgresJSONGsonBinding());
// Plain SQL template based
Field f1 = DSL.field("my_table.my_json_column", jsonElementType);
// Name based
Field f2 = DSL.field(name("my_table", "my_json_column"), jsonElementType);
```
0101000000000000000000F03F000000000000F03F |
+------------------------------------------------+---------------------------------------------------+
```
This produces GeoJSON and WKB representations, respectively, because, why not. What if we wanted a WKT/WKB representation in both cases, though? A [`org.jooq.Formatter`](https://www.jooq.org/javadoc/3.22.x/org/jooq/Formatter.html) implementation can help:
```java
public class SpatialFormatter implements Formatter {
@Override
public final void formatJSON(FormatterContext ctx) {
ctx.field(ctx.field().cast(CLOB));
}
@Override
public final void formatJSONB(FormatterContext ctx) {
formatJSON(ctx);
}
@Override
public final void formatXML(FormatterContext ctx) {
}
}
```
This simple `Formatter`, if attached to a [`org.jooq.Binding`](https://www.jooq.org/javadoc/3.22.x/org/jooq/Binding.html) for [GEOMETRY types](#data-type-geometry), ensures that any reference to a [`org.jooq.Geometry`](https://www.jooq.org/javadoc/3.22.x/org/jooq/Geometry.html) typed `Field` will be wrapped in a `CAST(.. AS TEXT)` expression. So, the SQL generated instead would be:
```sql
SELECT
JSON_ARRAY(CAST(CAST('point (1 1)' AS geometry) AS text)),
XMLELEMENT(NAME e, CAST('point (1 1)' AS geometry))
```
Resulting in:
```text
+----------------------------------------------------+---------------------------------------------------+
| json_array | xmlelement |
+----------------------------------------------------+---------------------------------------------------+
| "[\"0101000000000000000000F03F000000000000F03F\"]" | 0101000000000000000000F03F000000000000F03F |
+----------------------------------------------------+---------------------------------------------------+
```
For this to work, just return the formatter from your `Binding`:
```java
public class MyGeometryBinding implements Binding {
// ...
static final SpatialFormatter FORMATTER = new SpatialFormatter();
public Formatter formatter() {
return FORMATTER;
}
}
```
> [!TIP]
> jOOQ offers such `Formatter` implementations out of the box for all [Built-in data types](#built-in-data-types) (including [GEOMETRY](#data-type-geometry)) and [Extended data types](#extended-data-types).
field(String sql);
Field field(String sql, Object... bindings);
Field field(String sql, QueryPart... parts);
// A field with a known data type
Field field(String sql, Class type);
Field