Overview This manual is divided into six main sections: Getting started with jOOQ 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 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 This section explains how to configure and use the built-in source code generator

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

Tools This section is dedicated to tools that ship with jOOQ.

Reference This section is a reference for elements in this manual

1. Preface Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition jOOQ's reason for being - compared to JPA Java and SQL have come a long way. SQL is an "old", yet established and well-understood technology. Java is a legacy too, although its platform JVM allows for many new and contemporary languages built on top of it. Yet, after all these years, libraries dealing with the interface between SQL and Java have come and gone, leaving JPA to be a standard that is accepted only with doubts, short of any surviving options. So far, there had been only few database abstraction frameworks or libraries, that truly respected SQL as a first class citizen among languages. Most frameworks, including the industry standards JPA, EJB, Hibernate, JDO, Criteria Query, and many others try to hide SQL itself, minimising its scope to things called JPQL, HQL, JDOQL and various other inferior query languages jOOQ has come to fill this gap. jOOQ's reason for being - compared to LINQ Other platforms incorporate ideas such as LINQ (with LINQ-to-SQL), or Scala's SLICK, or also Java's QueryDSL to better integrate querying as a concept into their respective language. By querying, they understand querying of arbitrary targets, such as SQL, XML, Collections and other heterogeneous data stores. jOOQ claims that this is going the wrong way too. In more advanced querying use-cases (more than simple CRUD and the occasional JOIN), people will want to profit from the expressivity of SQL. Due to the relational nature of SQL, this is quite different from what object-oriented and partially functional languages such as C#, Scala, or Java can offer. It is very hard to formally express and validate joins and the ad-hoc table expression types they create. It gets even harder when you want support for more advanced table expressions, such as pivot tables, unnested cursors, or just arbitrary projections from derived tables. With a very strong object-oriented typing model, these features will probably stay out of scope. In essence, the decision of creating an API that looks like SQL or one that looks like C#, Scala, Java is a definite decision in favour of one or the other platform. While it will be easier to evolve SLICK in similar ways as LINQ (or QueryDSL in the Java world), SQL feature scope that clearly communicates its underlying intent will be very hard to add, later on (e.g. how would you model Oracle's partitioned outer join syntax? How would you model ANSI/ISO SQL:1999 grouping sets? How can you support scalar subquery caching? etc...). jOOQ has come to fill this gap. jOOQ's reason for being - compared to SQL / JDBC So why not just use SQL? SQL can be written as plain text and passed through the JDBC API. Over the years, people have become wary of this approach for many reasons: No typesafety

No syntax safety

No bind value index safety

Verbose SQL String concatenation

Boring bind value indexing techniques

Verbose resource and exception handling in JDBC

A very "stateful", not very object-oriented JDBC API, which is hard to use For these many reasons, other frameworks have tried to abstract JDBC away in the past in one way or another. Unfortunately, many have completely abstracted SQL away as well jOOQ has come to fill this gap. jOOQ is different SQL was never meant to be abstracted. To be confined in the narrow boundaries of heavy mappers, hiding the beauty and simplicity of relational data. SQL was never meant to be object-oriented. SQL was never meant to be anything other than... SQL!

2. Copyright, License, and Trademarks Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition This section lists the various licenses that apply to different versions of jOOQ. Prior to version 3.2, jOOQ was shipped for free under the terms of the Apache Software License 2.0. With jOOQ 3.2, jOOQ became dual-licensed: Apache Software License 2.0 (for use with Open Source databases) and commercial (for use with commercial databases). This manual itself (as well as the www.jooq.org public website) is licensed to you under the terms of the CC BY-SA 4.0 license. Please contact legal@datageekery.com, should you have any questions regarding licensing. License for jOOQ 3.2 and later This work is dual-licensed - under the Apache Software License 2.0 (the "ASL") - under the jOOQ License and Maintenance Agreement (the "jOOQ License") ============================================================================= You may choose which license applies to you: - If you're using this work with Open Source databases, you may choose either ASL or jOOQ License. - If you're using this work with at least one commercial database, you must choose jOOQ License For more information, please visit http://www.jooq.org/licenses Apache Software License 2.0: ----------------------------------------------------------------------------- Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. jOOQ License and Maintenance Agreement: ----------------------------------------------------------------------------- Data Geekery grants the Customer the non-exclusive, timely limited and non-transferable license to install and use the Software under the terms of the jOOQ License and Maintenance Agreement. This library is distributed with a LIMITED WARRANTY. See the jOOQ License and Maintenance Agreement for more details: http://www.jooq.org/licensing Historic license for jOOQ 1.x, 2.x, 3.0, 3.1 Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. Trademarks owned by Data Geekery™ GmbH jOOλ™ is a trademark by Data Geekery™ GmbH

jOOQ™ is a trademark by Data Geekery™ GmbH

jOOR™ is a trademark by Data Geekery™ GmbH

jOOU™ is a trademark by Data Geekery™ GmbH

jOOX™ is a trademark by Data Geekery™ GmbH Trademarks owned by Data Geekery™ GmbH partners GSP and General SQL Parser are trademarks by Gudu Software Limited

SQL 2 jOOQ is a trademark by Data Geekery™ GmbH and Gudu Software Limited

Flyway is a trademark by Snow Mountain Labs UG (haftungsbeschränkt) Trademarks owned by database vendors with no affiliation to Data Geekery™ GmbH Access® is a registered trademark of Microsoft® Inc.

Adaptive Server® Enterprise is a registered trademark of Sybase®, Inc.

DB2® is a registered trademark of IBM® Corp.

Derby is a trademark of the Apache™ Software Foundation

H2 is a trademark of the H2 Group

HANA is a trademark of SAP SE

HSQLDB is a trademark of The hsql Development Group

Ingres is a trademark of Actian™ Corp.

MariaDB is a trademark of Monty Program Ab

MySQL® is a registered trademark of Oracle® Corp.

Firebird® is a registered trademark of Firebird Foundation Inc.

Oracle® database is a registered trademark of Oracle® Corp.

PostgreSQL® is a registered trademark of The PostgreSQL Global Development Group

Postgres Plus® is a registered trademark of EnterpriseDB® software

SQL Anywhere® is a registered trademark of Sybase®, Inc.

SQL Server® is a registered trademark of Microsoft® Inc.

SQLite is a trademark of Hipp, Wyrick & Company, Inc. Other trademarks by vendors with no affiliation to Data Geekery™ GmbH Java® is a registered trademark by Oracle® Corp. and/or its affiliates

Scala is a trademark of EPFL Other trademark remarks Other names may be trademarks of their respective owners. Throughout the manual, the above trademarks are referenced without a formal ® (R) or ™ (TM) symbol. It is believed that referencing third-party trademarks in this manual or on the jOOQ website constitutes "fair use". Please contact us if you think that your trademark(s) are not properly attributed. Contributions The following are authors and contributors of jOOQ or parts of jOOQ in alphabetical order: Aaron Digulla

Andreas Franzén

Anuraag Agrawal

Arnaud Roger

Art O Cathain

Artur Dryomov

Ben Manes

Brent Douglas

Brett Meyer

Christian Stein

Christopher Deckers

Ed Schaller

Eric Peters

Ernest Mishkin

Espen Stromsnes

Eugeny Karpov

Fabrice Le Roy

Gonzalo Ortiz Jaureguizar

Gregory Hlavac

Henrik Sjöstrand

Ivan Dugic

Javier Durante

Johannes Bühler

Joseph B Phillips

Joseph Pachod

Knut Wannheden

Laurent Pireyn

Luc Marchaud

Lukas Eder

Matti Tahvonen

Michael Doberenz

Michael Simons

Michał Kołodziejski

Miguel Gonzalez Sanchez

Mustafa Yücel

Nathaniel Fischer

Oliver Flege

Peter Ertl

Richard Bradley

Robin Stocker

Samy Deghou

Sander Plas

Sean Wellington

Sergey Epik

Sergey Zhuravlev

Stanislas Nanchen

Stephan Schroevers

Sugiharto Lim

Sven Jacobs

Szymon Jachim

Terence Zhang

Timothy Wilson

Timur Shaidullin

Thomas Darimont

Tsukasa Kitachi

Victor Bronstein

Victor Z. Peng

Vladimir Kulev

Vladimir Vinogradov

Vojtech Polivka

Wang Gaoyuan

Zoltan Tamasi See the following website for details about contributing to jOOQ:

http://www.jooq.org/legal/contributions

3. Getting started with jOOQ Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition These chapters contain a quick overview of how to get started with this manual and with jOOQ. While the subsequent chapters contain a lot of reference information, this chapter here just wraps up the essentials.

3.1. How to read this manual Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition This section helps you correctly interpret this manual in the context of jOOQ. Code blocks The following are code blocks: -- A SQL code block SELECT 1 FROM DUAL // A Java code block for (int i = 0; i < 10; i++); <!-- An XML code block --> <hello what="world"></hello> # A config file code block org.jooq.property=value These are useful to provide examples in code. Often, with jOOQ, it is even more useful to compare SQL code with its corresponding Java/jOOQ code. When this is done, the blocks are aligned side-by-side, with SQL usually being on the left, and an equivalent jOOQ DSL query in Java usually being on the right: -- In SQL: SELECT 1 FROM DUAL // Using jOOQ: create.selectOne().fetch() Code block contents The contents of code blocks follow conventions, too. If nothing else is mentioned next to any given code block, then the following can be assumed: -- SQL assumptions ------------------ -- If nothing else is specified, assume that the Oracle syntax is used SELECT 1 FROM DUAL // Java assumptions // ---------------- // Whenever you see "standalone functions", assume they were static imported from org.jooq.impl.DSL // "DSL" is the entry point of the static query DSL exists(); max(); min(); val(); inline(); // correspond to DSL.exists(); DSL.max(); DSL.min(); etc... // Whenever you see BOOK/Book, AUTHOR/Author and similar entities, assume they were (static) imported from the generated schema BOOK.TITLE, AUTHOR.LAST_NAME // correspond to com.example.generated.Tables.BOOK.TITLE, com.example.generated.Tables.BOOK.TITLE FK_BOOK_AUTHOR // corresponds to com.example.generated.Keys.FK_BOOK_AUTHOR // Whenever you see "create" being used in Java code, assume that this is an instance of org.jooq.DSLContext. // The reason why it is called "create" is the fact, that a jOOQ QueryPart is being created from the DSL object. // "create" is thus the entry point of the non-static query DSL DSLContext create = DSL.using(connection, SQLDialect.ORACLE); Your naming may differ, of course. For instance, you could name the "create" instance "db", instead. Execution When you're coding PL/SQL, T-SQL or some other procedural SQL language, SQL statements are always executed immediately at the semi-colon. This is not the case in jOOQ, because as an internal DSL, jOOQ can never be sure that your statement is complete until you call fetch() or execute() . The manual tries to apply fetch() and execute() as thoroughly as possible. If not, it is implied: SELECT 1 FROM DUAL UPDATE t SET v = 1 create.selectOne().fetch(); create.update(T).set(T.V, 1).execute(); Degree (arity) jOOQ records (and many other API elements) have a degree N between 1 and 22. The variable degree of an API element is denoted as [N], e.g. Row[N] or Record[N]. The term "degree" is preferred over arity, as "degree" is the term used in the SQL standard, whereas "arity" is used more often in mathematics and relational theory. Settings jOOQ allows to override runtime behaviour using org.jooq.conf.Settings. If nothing is specified, the default runtime settings are assumed. Sample database jOOQ query examples run against the sample database. See the manual's section about the sample database used in this manual to learn more about the sample database.

3.2. The sample database used in this manual Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition For the examples in this manual, the same database will always be referred to. It essentially consists of these entities created using the Oracle dialect CREATE TABLE language ( id NUMBER(7) NOT NULL PRIMARY KEY, cd CHAR(2) NOT NULL, description VARCHAR2(50) ); CREATE TABLE author ( id NUMBER(7) NOT NULL PRIMARY KEY, first_name VARCHAR2(50), last_name VARCHAR2(50) NOT NULL, date_of_birth DATE, year_of_birth NUMBER(7), distinguished NUMBER(1) ); CREATE TABLE book ( id NUMBER(7) NOT NULL PRIMARY KEY, author_id NUMBER(7) NOT NULL, title VARCHAR2(400) NOT NULL, published_in NUMBER(7) NOT NULL, language_id NUMBER(7) NOT NULL, CONSTRAINT fk_book_author FOREIGN KEY (author_id) REFERENCES author(id), CONSTRAINT fk_book_language FOREIGN KEY (language_id) REFERENCES language(id) ); CREATE TABLE book_store ( name VARCHAR2(400) NOT NULL UNIQUE ); CREATE TABLE book_to_book_store ( name VARCHAR2(400) NOT NULL, book_id INTEGER NOT NULL, stock INTEGER, PRIMARY KEY(name, book_id), CONSTRAINT fk_b2bs_book_store FOREIGN KEY (name) REFERENCES book_store (name) ON DELETE CASCADE, CONSTRAINT fk_b2bs_book FOREIGN KEY (book_id) REFERENCES book (id) ON DELETE CASCADE ); More entities, types (e.g. UDT's, ARRAY types, ENUM types, etc), stored procedures and packages are introduced for specific examples In addition to the above, you may assume the following sample data: INSERT INTO language (id, cd, description) VALUES (1, 'en', 'English'); INSERT INTO language (id, cd, description) VALUES (2, 'de', 'Deutsch'); INSERT INTO language (id, cd, description) VALUES (3, 'fr', 'Français'); INSERT INTO language (id, cd, description) VALUES (4, 'pt', 'Português'); INSERT INTO author (id, first_name, last_name, date_of_birth , year_of_birth) VALUES (1 , 'George' , 'Orwell' , DATE '1903-06-26', 1903 ); INSERT INTO author (id, first_name, last_name, date_of_birth , year_of_birth) VALUES (2 , 'Paulo' , 'Coelho' , DATE '1947-08-24', 1947 ); INSERT INTO book (id, author_id, title , published_in, language_id) VALUES (1 , 1 , '1984' , 1948 , 1 ); INSERT INTO book (id, author_id, title , published_in, language_id) VALUES (2 , 1 , 'Animal Farm' , 1945 , 1 ); INSERT INTO book (id, author_id, title , published_in, language_id) VALUES (3 , 2 , 'O Alquimista', 1988 , 4 ); INSERT INTO book (id, author_id, title , published_in, language_id) VALUES (4 , 2 , 'Brida' , 1990 , 2 ); INSERT INTO book_store VALUES ('Orell Füssli'); INSERT INTO book_store VALUES ('Ex Libris'); INSERT INTO book_store VALUES ('Buchhandlung im Volkshaus'); INSERT INTO book_to_book_store VALUES ('Orell Füssli' , 1, 10); INSERT INTO book_to_book_store VALUES ('Orell Füssli' , 2, 10); INSERT INTO book_to_book_store VALUES ('Orell Füssli' , 3, 10); INSERT INTO book_to_book_store VALUES ('Ex Libris' , 1, 1 ); INSERT INTO book_to_book_store VALUES ('Ex Libris' , 3, 2 ); INSERT INTO book_to_book_store VALUES ('Buchhandlung im Volkshaus', 3, 1 );

3.3. Different use cases for jOOQ Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition jOOQ has originally been created as a library for complete abstraction of JDBC and all database interaction. Various best practices that are frequently encountered in pre-existing software products are applied to this library. This includes: Typesafe database object referencing through generated schema, table, column, record, procedure, type, dao, pojo artefacts (see the chapter about code generation)

Typesafe SQL construction / SQL building through a complete querying DSL API modelling SQL as a domain specific language in Java (see the chapter about the query DSL API)

Convenient query execution through an improved API for result fetching (see the chapters about the various types of data fetching)

SQL dialect abstraction and SQL clause emulation to improve cross-database compatibility and to enable missing features in simpler databases (see the chapter about SQL dialects)

SQL logging and debugging using jOOQ as an integral part of your development process (see the chapters about logging) Effectively, jOOQ was originally designed to replace any other database abstraction framework short of the ones handling connection pooling (and more sophisticated transaction management) Use jOOQ the way you prefer ... but open source is community-driven. And the community has shown various ways of using jOOQ that diverge from its original intent. Some use cases encountered are: Using Hibernate for 70% of the queries (i.e. CRUD) and jOOQ for the remaining 30% where SQL is really needed

Using jOOQ for SQL building and JDBC for SQL execution

Using jOOQ for SQL building and Spring Data for SQL execution

Using jOOQ without the source code generator to build the basis of a framework for dynamic SQL execution. The following sections explain about various use cases for using jOOQ in your application.

3.3.2. jOOQ as a SQL builder with code generation Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition In addition to using jOOQ as a standalone SQL builder, you can also use jOOQ's code generation features in order to compile your SQL statements using a Java compiler against an actual database schema. This adds a lot of power and expressiveness to just simply constructing SQL using the query DSL and custom strings and literals, as you can be sure that all database artefacts actually exist in the database, and that their type is correct. We strongly recommend using this approach. An example is given here: // Fetch a SQL string from a jOOQ Query in order to manually execute it with another tool. Query query = 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)); String sql = query.getSQL(); List<Object> 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 PreparedStatement by default, this will generate a bind variable for "1948". Read more about bind variables here). You can also avoid getting the SQL string and bind values separately: 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: This section contains a lot of information about creating SQL statements using the jOOQ API

Code generation: This section contains the necessary information to run jOOQ's code generator against your developer database

Bind values: This section explains how bind values are managed and/or inlined in jOOQ.

3.3.3. jOOQ as a SQL executor Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition Instead of any tool mentioned in the previous chapters, you can also use jOOQ directly to execute your jOOQ-generated SQL statements. This will add a lot of convenience on top of the previously discussed API for typesafe SQL construction, when you can re-use the information from generated classes to fetch records and custom data types. An example is given here: // Typesafely execute the SQL statement directly with jOOQ Result<Record3<String, String, String>> 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: // 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<Record> result = create.fetch(sql); // Or execute that SQL with JDBC, fetching the ResultSet with jOOQ: ResultSet rs = connection.createStatement().executeQuery(sql); Result<Record> 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: This section contains a lot of information about creating SQL statements using the jOOQ API

Code generation: This section contains the necessary information to run jOOQ's code generator against your developer database

SQL execution: This section contains a lot of information about executing SQL statements using the jOOQ API

Fetching: This section contains some useful information about the various ways of fetching data with jOOQ

3.3.4. jOOQ for CRUD Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition Apart from jOOQ's fluent API for query construction, jOOQ can also help you execute everyday CRUD operations. An example is given here: // Fetch an author AuthorRecord author = create.fetchOne(AUTHOR, AUTHOR.ID.eq(1)); // Create a new author, if it doesn't exist yet if (author == null) { author = create.newRecord(AUTHOR); author.setId(1); author.setFirstName("Dan"); author.setLastName("Brown"); } // Mark the author as a "distinguished" author and store it author.setDistinguished(1); // Executes an update on existing authors, or insert on new ones author.store(); If you wish to use all of jOOQ's features, the following sections of the manual will be of interest to you (including all sub-sections): SQL building: This section contains a lot of information about creating SQL statements using the jOOQ API

Code generation: This section contains the necessary information to run jOOQ's code generator against your developer database

SQL execution: This section contains a lot of information about executing SQL statements using the jOOQ API

3.3.5. jOOQ for PROs Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition jOOQ isn't just a library that helps you build and execute SQL against your generated, compilable schema. jOOQ ships with a lot of tools. Here are some of the most important tools shipped with jOOQ: jOOQ's Execute Listeners: jOOQ allows you to hook your custom execute listeners into jOOQ's SQL statement execution lifecycle in order to centrally coordinate any arbitrary operation performed on SQL being executed. Use this for logging, identity generation, SQL tracing, performance measurements, etc.

Logging: jOOQ has a standard DEBUG logger built-in, for logging and tracing all your executed SQL statements and fetched result sets

Stored Procedures: jOOQ supports stored procedures and functions of your favourite database. All routines and user-defined types are generated and can be included in jOOQ's SQL building API as function references.

Batch execution: Batch execution is important when executing a big load of SQL statements. jOOQ simplifies these operations compared to JDBC

Exporting and Importing: jOOQ ships with an API to easily export/import data in various formats If you're a power user of your favourite, feature-rich database, jOOQ will help you access all of your database's vendor-specific features, such as OLAP features, stored procedures, user-defined types, vendor-specific SQL, functions, etc. Examples are given throughout this manual.

3.4. Tutorials Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition Don't have time to read the full manual? Here are a couple of tutorials that will get you into the most essential parts of jOOQ as quick as possible.

3.4.1. jOOQ in 7 easy steps Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition This manual section is intended for new users, to help them get a running application with jOOQ, quickly.

3.4.1.1. Step 1: Preparation Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition If you haven't already downloaded it, download jOOQ:

http://www.jooq.org/download Alternatively, you can create a Maven dependency to download jOOQ artefacts: Open Source Edition <dependency> <groupId>org.jooq</groupId> <artifactId>jooq</artifactId> <version>3.13.4</version> </dependency> <dependency> <groupId>org.jooq</groupId> <artifactId>jooq-meta</artifactId> <version>3.13.4</version> </dependency> <dependency> <groupId>org.jooq</groupId> <artifactId>jooq-codegen</artifactId> <version>3.13.4</version> </dependency> Commercial Editions (Java 9+) <!-- Note: These aren't hosted on Maven Central. Import them manually from your distribution --> <dependency> <groupId>org.jooq.pro</groupId> <artifactId>jooq</artifactId> <version>3.13.4</version> </dependency> <dependency> <groupId>org.jooq.pro</groupId> <artifactId>jooq-meta</artifactId> <version>3.13.4</version> </dependency> <dependency> <groupId>org.jooq.pro</groupId> <artifactId>jooq-codegen</artifactId> <version>3.13.4</version> </dependency> Commercial Editions (Java 8+) <!-- Note: These aren't hosted on Maven Central. Import them manually from your distribution --> <dependency> <groupId>org.jooq.pro-java-8</groupId> <artifactId>jooq</artifactId> <version>3.13.4</version> </dependency> <dependency> <groupId>org.jooq.pro-java-8</groupId> <artifactId>jooq-meta</artifactId> <version>3.13.4</version> </dependency> <dependency> <groupId>org.jooq.pro-java-8</groupId> <artifactId>jooq-codegen</artifactId> <version>3.13.4</version> </dependency> Commercial Editions (Java 6+) <!-- Note: These aren't hosted on Maven Central. Import them manually from your distribution --> <dependency> <groupId>org.jooq.pro-java-6</groupId> <artifactId>jooq</artifactId> <version>3.13.4</version> </dependency> <dependency> <groupId>org.jooq.pro-java-6</groupId> <artifactId>jooq-meta</artifactId> <version>3.13.4</version> </dependency> <dependency> <groupId>org.jooq.pro-java-6</groupId> <artifactId>jooq-codegen</artifactId> <version>3.13.4</version> </dependency> Commercial Editions (Free Trial) <!-- Note: These aren't hosted on Maven Central. Import them manually from your distribution --> <!-- Replace the groupId by org.jooq.trial-java-6 (Java 6 support), or org.jooq.trial (Java 11 support) if needed --> <dependency> <groupId>org.jooq.trial-java-8</groupId> <artifactId>jooq</artifactId> <version>3.13.4</version> </dependency> <dependency> <groupId>org.jooq.trial-java-8</groupId> <artifactId>jooq-meta</artifactId> <version>3.13.4</version> </dependency> <dependency> <groupId>org.jooq.trial-java-8</groupId> <artifactId>jooq-codegen</artifactId> <version>3.13.4</version> </dependency> 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. Please refer to the manual's section about Code generation 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:

http://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 or https://hub.docker.com/_/mysql now!

3.4.1.2. Step 2: Your database Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition We're going to create a database called "library" and a corresponding "author" table. Connect to MySQL via your command line client and type the following: CREATE DATABASE `library`; USE `library`; CREATE TABLE `author` ( `id` int NOT NULL, `first_name` varchar(255) DEFAULT NULL, `last_name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) );

3.4.1.3. Step 3: Code generation Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition In this step, we're going to use jOOQ's command line tools to generate classes that map to the Author table we just created. More detailed information about how to set up the jOOQ code generator can be found here:

jOOQ manual pages about setting up the code generator The easiest way to generate a schema is to copy the jOOQ jar files (there should be 3) and the MySQL Connector jar file to a temporary directory. Then, create a library.xml that looks like this: <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.13.0.xsd"> <!-- Configure the database connection here --> <jdbc> <driver>com.mysql.cj.jdbc.Driver</driver> <url>jdbc:mysql://localhost:3306/library</url> <user>root</user> <password></password> </jdbc> <generator> <!-- The default code generator. You can override this one, to generate your own code style. Supported generators: - org.jooq.codegen.JavaGenerator - org.jooq.codegen.ScalaGenerator Defaults to org.jooq.codegen.JavaGenerator --> <name>org.jooq.codegen.JavaGenerator</name> <database> <!-- The database type. The format here is: org.jooq.meta.[database].[database]Database --> <name>org.jooq.meta.mysql.MySQLDatabase</name> <!-- The database schema (or in the absence of schema support, in your RDBMS this can be the owner, user, database name) to be generated --> <inputSchema>library</inputSchema> <!-- All elements that are generated from your schema (A Java regular expression. Use the pipe to separate several expressions) Watch out for case-sensitivity. Depending on your database, this might be important! --> <includes>.*</includes> <!-- All elements that are excluded from your schema (A Java regular expression. Use the pipe to separate several expressions). Excludes match before includes, i.e. excludes have a higher priority --> <excludes></excludes> </database> <target> <!-- The destination package of your generated classes (within the destination directory) --> <packageName>test.generated</packageName> <!-- The destination directory of your generated classes. Using Maven directory layout here --> <directory>C:/workspace/MySQLTest/src/main/java</directory> </target> </generator> </configuration> Replace the username with whatever user has the appropriate privileges to query the database meta data. You'll also want to look at the other values and replace as necessary. Here are the two interesting properties: generator.target.package - set this to the parent package you want to create for the generated classes. The setting of test.generated will cause the test.generated.Author and test.generated.AuthorRecord to be created generator.target.directory - the directory to output to. Once you have the JAR files and library.xml in your temp directory, type this on a Windows machine: java -classpath jooq-3.13.4.jar;^ jooq-meta-3.13.4.jar;^ jooq-codegen-3.13.4.jar;^ reactive-streams-1.0.2.jar;^ mysql-connector-java-5.1.18-bin.jar;. ^ org.jooq.codegen.GenerationTool library.xml ... or type this on a UNIX / Linux / Mac system (colons instead of semi-colons): java -classpath jooq-3.13.4.jar:\ jooq-meta-3.13.4.jar:\ jooq-codegen-3.13.4.jar:\ reactive-streams-1.0.2.jar:\ mysql-connector-java-5.1.18-bin.jar:. \ org.jooq.codegen.GenerationTool library.xml Note: jOOQ will try loading the library.xml from your classpath. This is also why there is a trailing period ( . ) on the classpath. If the file cannot be found on the classpath, jOOQ will look on the file system from the current working directory. Replace the filenames with your actual filenames. In this example, jOOQ 3.13.4 is being used. If everything has worked, you should see this in your console output: Nov 1, 2011 7:25:06 PM org.jooq.impl.JooqLogger info INFO: Initialising properties : /library.xml Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info INFO: Database parameters Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info INFO: ---------------------------------------------------------- Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info INFO: dialect : MYSQL Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info INFO: schema : library Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info INFO: target dir : C:/workspace/MySQLTest/src Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info INFO: target package : test.generated Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info INFO: ---------------------------------------------------------- Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info INFO: Emptying : C:/workspace/MySQLTest/src/test/generated Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info INFO: Generating classes in : C:/workspace/MySQLTest/src/test/generated Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info INFO: Generating schema : Library.java Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info INFO: Schema generated : Total: 122.18ms Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info INFO: Sequences fetched : 0 (0 included, 0 excluded) Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info INFO: Tables fetched : 5 (5 included, 0 excluded) Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info INFO: Generating tables : C:/workspace/MySQLTest/src/test/generated/tables Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info INFO: ARRAYs fetched : 0 (0 included, 0 excluded) Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info INFO: Enums fetched : 0 (0 included, 0 excluded) Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info INFO: UDTs fetched : 0 (0 included, 0 excluded) Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info INFO: Generating table : Author.java Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info INFO: Tables generated : Total: 680.464ms, +558.284ms Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info INFO: Generating Keys : C:/workspace/MySQLTest/src/test/generated/tables Nov 1, 2011 7:25:08 PM org.jooq.impl.JooqLogger info INFO: Keys generated : Total: 718.621ms, +38.157ms Nov 1, 2011 7:25:08 PM org.jooq.impl.JooqLogger info INFO: Generating records : C:/workspace/MySQLTest/src/test/generated/tables/records Nov 1, 2011 7:25:08 PM org.jooq.impl.JooqLogger info INFO: Generating record : AuthorRecord.java Nov 1, 2011 7:25:08 PM org.jooq.impl.JooqLogger info INFO: Table records generated : Total: 782.545ms, +63.924ms Nov 1, 2011 7:25:08 PM org.jooq.impl.JooqLogger info INFO: Routines fetched : 0 (0 included, 0 excluded) Nov 1, 2011 7:25:08 PM org.jooq.impl.JooqLogger info INFO: Packages fetched : 0 (0 included, 0 excluded) Nov 1, 2011 7:25:08 PM org.jooq.impl.JooqLogger info INFO: GENERATION FINISHED! : Total: 791.688ms, +9.143ms

3.4.1.4. Step 4: Connect to your database Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition Let's just write a vanilla main class in the project containing the generated classes: // For convenience, always static import your generated tables and jOOQ functions to decrease verbosity: import static test.generated.Tables.*; import static org.jooq.impl.DSL.*; import java.sql.*; public class Main { public static void main(String[] args) { String userName = "root"; String password = ""; String url = "jdbc:mysql://localhost:3306/library"; // Connection is the only JDBC resource that we need // PreparedStatement and ResultSet are handled by jOOQ, internally try (Connection conn = DriverManager.getConnection(url, userName, password)) { // ... } // For the sake of this tutorial, let's keep exception handling simple catch (Exception e) { e.printStackTrace(); } } } This is pretty standard code for establishing a MySQL connection.

3.4.1.5. Step 5: Querying Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition Let's add a simple query constructed with jOOQ's query DSL: DSLContext create = DSL.using(conn, SQLDialect.MYSQL); Result<Record> 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.

3.4.1.6. Step 6: Iterating Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition After the line where we retrieve the results, let's iterate over the results and print out the data: 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); } The full program should now look like this: package test; // For convenience, always static import your generated tables and // jOOQ functions to decrease verbosity: import static test.generated.Tables.*; import static org.jooq.impl.DSL.*; import java.sql.*; import org.jooq.*; import org.jooq.impl.*; public class Main { /** * @param args */ public static void main(String[] args) { String userName = "root"; String password = ""; String url = "jdbc:mysql://localhost:3306/library"; // Connection is the only JDBC resource that we need // PreparedStatement and ResultSet are handled by jOOQ, internally try (Connection conn = DriverManager.getConnection(url, userName, password)) { DSLContext create = DSL.using(conn, SQLDialect.MYSQL); Result<Record> 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(); } } }

3.4.1.7. Step 7: Explore! Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition jOOQ has grown to be a comprehensive SQL library. For more information, please consider the documentation:

http://www.jooq.org/learn ... explore the Javadoc:

http://www.jooq.org/javadoc/latest/ ... or join the news group:

https://groups.google.com/forum/#!forum/jooq-user This tutorial is the courtesy of Ikai Lan. See the original source here:

http://ikaisays.com/2011/11/01/getting-started-with-jooq-a-tutorial/

3.4.2. Using jOOQ in modern IDEs Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition Feel free to contribute a tutorial!

3.4.4. Using jOOQ with Flyway Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition When performing database migrations, we at Data Geekery recommend using jOOQ with Flyway - Database Migrations Made Easy. In this chapter, we're going to look into a simple way to get started with the two frameworks. Philosophy There are a variety of ways how jOOQ and Flyway could interact with each other in various development setups. In this tutorial we're going to show just one variant of such framework team play - a variant that we find particularly compelling for most use cases. The general philosophy behind the following approach can be summarised as this: 1. Database increment

2. Database migration

3. Code re-generation

4. Development The four steps above can be repeated time and again, every time you need to modify something in your database. More concretely, let's consider: 1. Database increment - You need a new column in your database, so you write the necessary DDL in a Flyway script

- You need a new column in your database, so you write the necessary DDL in a Flyway script 2. Database migration - This Flyway script is now part of your deliverable, which you can share with all developers who can migrate their databases with it, the next time they check out your change

- This Flyway script is now part of your deliverable, which you can share with all developers who can migrate their databases with it, the next time they check out your change 3. Code re-generation - Once the database is migrated, you regenerate all jOOQ artefacts (see code generation), locally

- Once the database is migrated, you regenerate all jOOQ artefacts (see code generation), locally 4. Development - You continue developing your business logic, writing code against the udpated, generated database schema Maven Project Configuration - Properties The following properties are defined in our pom.xml, to be able to reuse them between plugin configurations: <properties> <db.url>jdbc:h2:~/flyway-test</db.url> <db.username>sa</db.username> </properties> 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. You will also find the source code of this tutorial on GitHub at https://github.com/jOOQ/jOOQ/tree/main/jOOQ-examples/jOOQ-flyway-example, and the full pom.xml file here. These are the dependencies that we're using in our Maven configuration: <!-- We'll add the latest version of jOOQ and our JDBC driver - in this case H2 --> <dependency> <!-- Use org.jooq for the Open Source edition org.jooq.pro for commercial editions, org.jooq.pro-java-8 for commercial editions with Java 8 support, org.jooq.pro-java-6 for commercial editions with Java 6 support, org.jooq.trial for the free trial edition, org.jooq.trial-java-8 for the free trial edition with Java 8 support, org.jooq.trial-java-6 for the free trial edition with Java 6 support Note: Only the Open Source Edition is hosted on Maven Central. Import the others manually from your distribution --> <groupId>org.jooq</groupId> <artifactId>jooq</artifactId> <version>3.13.4</version> </dependency> <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> <version>1.4.197</version> </dependency> <!-- For improved logging, we'll be using log4j via slf4j to see what's going on during migration and code generation --> <dependency> <groupId>org.apache.logging.log4j</groupId> <artifactId>log4j-slf4j-impl</artifactId> <version>2.11.0</version> </dependency> <!-- To ensure our code is working, we're using JUnit --> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.11</version> <scope>test</scope> </dependency> 0. Maven Project Configuration - Plugins After the dependencies, let's simply add the Flyway and jOOQ Maven plugins like so. The Flyway plugin: <plugin> <groupId>org.flywaydb</groupId> <artifactId>flyway-maven-plugin</artifactId> <version>3.0</version> <!-- Note that we're executing the Flyway plugin in the "generate-sources" phase --> <executions> <execution> <phase>generate-sources</phase> <goals> <goal>migrate</goal> </goals> </execution> </executions> <!-- Note that we need to prefix the db/migration path with filesystem: to prevent Flyway from looking for our migration scripts only on the classpath --> <configuration> <url>${db.url}</url> <user>${db.username}</user> <locations> <location>filesystem:src/main/resources/db/migration</location> </locations> </configuration> </plugin> 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 suggests 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. <plugin> <!-- Use org.jooq for the Open Source edition org.jooq.pro for commercial editions, org.jooq.pro-java-8 for commercial editions with Java 8 support, org.jooq.pro-java-6 for commercial editions with Java 6 support, org.jooq.trial for the free trial edition, org.jooq.trial-java-8 for the free trial edition with Java 8 support, org.jooq.trial-java-6 for the free trial edition with Java 6 support Note: Only the Open Source Edition is hosted on Maven Central. Import the others manually from your distribution --> <groupId>org.jooq</groupId> <artifactId>jooq-codegen-maven</artifactId> <version>${org.jooq.version}</version> <!-- The jOOQ code generation plugin is also executed in the generate-sources phase, prior to compilation --> <executions> <execution> <phase>generate-sources</phase> <goals> <goal>generate</goal> </goals> </execution> </executions> <!-- This is a minimal working configuration. See the manual's section about the code generator for more details --> <configuration> <jdbc> <url>${db.url}</url> <user>${db.username}</user> </jdbc> <generator> <database> <includes>.*</includes> <inputSchema>FLYWAY_TEST</inputSchema> </database> <target> <packageName>org.jooq.example.flyway.db.h2</packageName> <directory>target/generated-sources/jooq-h2</directory> </target> </generator> </configuration> </plugin> 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 -- V1__initialise_database.sql DROP SCHEMA flyway_test IF EXISTS; CREATE SCHEMA flyway_test; -- 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) ); -- 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: mvn clean install And then observing the log output from Flyway... [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: [INFO] --- jooq-codegen-maven:3.13.4:generate (default) @ jooq-flyway-example --- [INFO] --- jooq-codegen-maven:3.13.4: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 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: -- 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 mvn clean install And then observing the log output: [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: [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: 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)); } } } 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 for more information about Flyway.

3.5. jOOQ and Java 8 Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition Java 8 has introduced a great set of enhancements, among which lambda expressions and the new java.util.stream.Stream. These new constructs align very well with jOOQ's fluent API as can be seen in the following examples: jOOQ and lambda expressions jOOQ's RecordMapper API is fully Java-8-ready, which basically means that it is a SAM (Single Abstract Method) type, which can be instanciated using a lambda expression. Consider this example: try (Connection c = getConnection()) { String sql = "select schema_name, is_default " + "from information_schema.schemata " + "order by schema_name"; DSL.using(c) .fetch(sql) // We can use lambda expressions to map jOOQ Records .map(rs -> new Schema( rs.getValue("SCHEMA_NAME", String.class), rs.getValue("IS_DEFAULT", boolean.class) )) // ... and then profit from the new Collection methods .forEach(System.out::println); } The above example shows how jOOQ's Result.map() method can receive a lambda expression that implements RecordMapper to map from jOOQ Records to your custom types. jOOQ and the Streams API jOOQ's Result type extends java.util.List, which opens up access to a variety of new Java features in Java 8. The following example shows how easy it is to transform a jOOQ Result containing INFORMATION_SCHEMA meta data to produce DDL statements: DSL.using(c) .select( COLUMNS.TABLE_NAME, COLUMNS.COLUMN_NAME, COLUMNS.TYPE_NAME ) .from(COLUMNS) .orderBy( COLUMNS.TABLE_CATALOG, COLUMNS.TABLE_SCHEMA, COLUMNS.TABLE_NAME, COLUMNS.ORDINAL_POSITION ) .fetch() // jOOQ ends here .stream() // JDK 8 Streams start here .collect(groupingBy( r -> r.getValue(COLUMNS.TABLE_NAME), LinkedHashMap::new, mapping( r -> new Column( r.getValue(COLUMNS.COLUMN_NAME), r.getValue(COLUMNS.TYPE_NAME) ), toList() ) )) .forEach( (table, columns) -> { // Just emit a CREATE TABLE statement System.out.println( "CREATE TABLE " + table + " ("); // Map each "Column" type into a String // containing the column specification, // and join them using comma and // newline. Done! System.out.println( columns.stream() .map(col -> " " + col.name + " " + col.type) .collect(Collectors.joining(",

")) ); System.out.println(");"); } ); The above example is explained more in depth in this blog post: http://blog.jooq.org/2014/04/11/java-8-friday-no-more-need-for-orms/. For more information about Java 8, consider these resources: Our Java 8 Friday blog series

A great Java 8 resources collection by the folks at Baeldung.com

3.6. jOOQ and JavaFX Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition One of the major improvements of Java 8 is the introduction of JavaFX into the JavaSE. With jOOQ and Java 8 Streams and lambdas, it is now very easy and idiomatic to transform SQL results into JavaFX XYChart.Series or other, related objects: Creating a bar chart from a jOOQ Result As we've seen in the previous section about jOOQ and Java 8, jOOQ integrates seamlessly with Java 8's Streams API. The fluent style can be maintained throughout the data transformation chain. In this example, we're going to use Open Data from the world bank to show a comparison of countries GDP and debts: DROP SCHEMA IF EXISTS world; CREATE SCHEMA world; CREATE TABLE world.countries ( code CHAR(2) NOT NULL, year INT NOT NULL, gdp_per_capita DECIMAL(10, 2) NOT NULL, govt_debt DECIMAL(10, 2) NOT NULL ); INSERT INTO world.countries VALUES ('CA', 2009, 40764, 51.3), ('CA', 2010, 47465, 51.4), ('CA', 2011, 51791, 52.5), ('CA', 2012, 52409, 53.5), ('DE', 2009, 40270, 47.6), ('DE', 2010, 40408, 55.5), ('DE', 2011, 44355, 55.1), ('DE', 2012, 42598, 56.9), ('FR', 2009, 40488, 85.0), ('FR', 2010, 39448, 89.2), ('FR', 2011, 42578, 93.2), ('FR', 2012, 39759,103.8), ('GB', 2009, 35455,121.3), ('GB', 2010, 36573, 85.2), ('GB', 2011, 38927, 99.6), ('GB', 2012, 38649,103.2), ('IT', 2009, 35724,121.3), ('IT', 2010, 34673,119.9), ('IT', 2011, 36988,113.0), ('IT', 2012, 33814,131.1), ('JP', 2009, 39473,166.8), ('JP', 2010, 43118,174.8), ('JP', 2011, 46204,189.5), ('JP', 2012, 46548,196.5), ('RU', 2009, 8616, 8.7), ('RU', 2010, 10710, 9.1), ('RU', 2011, 13324, 9.3), ('RU', 2012, 14091, 9.4), ('US', 2009, 46999, 76.3), ('US', 2010, 48358, 85.6), ('US', 2011, 49855, 90.1), ('US', 2012, 51755, 93.8); Once this data is set up (e.g. in an H2 or PostgreSQL database), we'll run jOOQ's code generator and implement the following code to display our chart: CategoryAxis xAxis = new CategoryAxis(); NumberAxis yAxis = new NumberAxis(); xAxis.setLabel("Country"); yAxis.setLabel("% of GDP"); BarChart<String, Number> bc = new BarChart<String, Number>(xAxis, yAxis); bc.setTitle("Government Debt"); bc.getData().addAll( // SQL data transformation, executed in the database // ------------------------------------------------- DSL.using(connection) .select( COUNTRIES.YEAR, COUNTRIES.CODE, COUNTRIES.GOVT_DEBT) .from(COUNTRIES) .join( table( select(COUNTRIES.CODE, avg(COUNTRIES.GOVT_DEBT).as("avg")) .from(COUNTRIES) .groupBy(COUNTRIES.CODE) ).as("c1") ) .on(COUNTRIES.CODE.eq(field(name("c1", COUNTRIES.CODE.getName()), String.class))) // order countries by their average projected value .orderBy( field(name("avg")), COUNTRIES.CODE, COUNTRIES.YEAR) // The result produced by the above statement looks like this: // +----+----+---------+ // |year|code|govt_debt| // +----+----+---------+ // |2009|RU | 8.70| // |2010|RU | 9.10| // |2011|RU | 9.30| // |2012|RU | 9.40| // |2009|CA | 51.30| // +----+----+---------+ // Java data transformation, executed in application memory // -------------------------------------------------------- // Group results by year, keeping sort order in place .fetchGroups(COUNTRIES.YEAR) // Stream<Entry<Integer, Result<Record3<BigDecimal, String, Integer>>>> .entrySet() .stream() // Map each entry into a { Year -> Projected value } series .map(entry -> new XYChart.Series<>( entry.getKey().toString(), observableArrayList( // Map each country record into a chart Data object entry.getValue() .map(country -> new XYChart.Data<String, Number>( country.getValue(COUNTRIES.CODE), country.getValue(COUNTRIES.GOVT_DEBT) )) ) )) .collect(toList()) ); The above example uses basic SQL-92 syntax where the countries are ordered using aggregate information from a nested SELECT, which is supported in all databases. If you're using a database that supports window functions, e.g. PostgreSQL or any commercial database, you could have also written a simpler query like this:00 DSL.using(connection) .select( COUNTRIES.YEAR, COUNTRIES.CODE, COUNTRIES.GOVT_DEBT) .from(COUNTRIES) // order countries by their average projected value .orderBy( DSL.avg(COUNTRIES.GOVT_DEBT).over(partitionBy(COUNTRIES.CODE)), COUNTRIES.CODE, COUNTRIES.YEAR) .fetch() ; return bc; When executed, we'll get nice-looking bar charts like these:

The complete example can be downloaded and run from GitHub:

https://github.com/jOOQ/jOOQ/tree/main/jOOQ-examples/jOOQ-javafx-example

3.7. jOOQ and Nashorn Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition With Java 8 and the new built-in JavaScript engine Nashorn, a whole new ecosystem of software can finally make easy use of jOOQ in server-side JavaScript. A very simple example can be seen here: // Let's assume these objects were generated // by the jOOQ source code generator var Tables = Java.type("org.jooq.db.h2.information_schema.Tables"); var t = Tables.TABLES; var c = Tables.COLUMNS; // This is the equivalent of Java's static imports var count = DSL.count; var row = DSL.row; // We can now execute the following query: print( DSL.using(conn) .select( t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME) .from(t) .join(c) .on(row(t.TABLE_SCHEMA, t.TABLE_NAME) .eq(c.TABLE_SCHEMA, c.TABLE_NAME)) .orderBy( t.TABLE_SCHEMA.asc(), t.TABLE_NAME.asc(), c.ORDINAL_POSITION.asc()) .fetch() ); More details about how to use jOOQ, JDBC, and SQL with Nashorn can be seen here.

3.8. jOOQ and Scala Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition As any other library, jOOQ can be easily used in Scala, taking advantage of the many Scala language features such as for example: Optional "." to dereference methods from expressions

Optional "(" and ")" to delimit method argument lists

Optional ";" at the end of a Scala statement

Type inference using "var" and "val" keywords

Lambda expressions and for-comprehension syntax for record iteration and data type conversion But jOOQ also leverages other useful Scala features, such as implicit defs for operator overloading

Scala Macros (soon to come) All of the above heavily improve jOOQ's querying DSL API experience for Scala developers. A short example jOOQ application in Scala might look like this: import collection.JavaConversions._ // Import implicit defs for iteration over org.jooq.Result // import java.sql.DriverManager // // import org.jooq._ // import org.jooq.impl._ // import org.jooq.impl.DSL._ // import org.jooq.examples.scala.h2.Tables._ // import org.jooq.scalaextensions.Conversions._ // Import implicit defs for overloaded jOOQ/SQL operators // object Test { // def main(args: Array[String]): Unit = { // val c = DriverManager.getConnection("jdbc:h2:~/test", "sa", ""); // Standard JDBC connection val e = DSL.using(c, SQLDialect.H2); // val x = AUTHOR as "x" // SQL-esque table aliasing // for (r <- e // Iteration over Result. "r" is an org.jooq.Record3 select ( // BOOK.ID * BOOK.AUTHOR_ID, // Using the overloaded "*" operator BOOK.ID + BOOK.AUTHOR_ID * 3 + 4, // Using the overloaded "+" operator BOOK.TITLE || " abc" || " xy" // Using the overloaded "||" operator ) // from BOOK // No need to use parentheses or "." here leftOuterJoin ( // select (x.ID, x.YEAR_OF_BIRTH) // Dereference fields from aliased table from x // limit 1 // asTable x.getName() // ) // on BOOK.AUTHOR_ID === x.ID // Using the overloaded "===" operator where (BOOK.ID <> 2) // Using the olerloaded "<>" operator or (BOOK.TITLE in ("O Alquimista", "Brida")) // Neat IN predicate expression fetch // ) { // println(r) // } // } // } For more details about jOOQ's Scala integration, please refer to the manual's section about SQL building with Scala.

3.9. jOOQ and Groovy Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition As any other library, jOOQ can be easily used in Groovy, taking advantage of the many Groovy language features such as for example: Optional ";" at the end of a Groovy statement

Type inference for local variables A short example jOOQ application in Groovy might look like this: package org.jooq.groovy import static org.jooq.impl.DSL.* import static org.jooq.groovy.example.h2.Tables.* import groovy.sql.Sql import org.jooq.* import org.jooq.impl.DSL sql = Sql.newInstance('jdbc:h2:~/groovy-test', 'sa', '', 'org.h2.Driver'); a = AUTHOR.as("a"); b = BOOK.as("b") DSL.using(sql.connection) .select(a.FIRST_NAME, a.LAST_NAME, b.TITLE) .from(a) .join(b).on(a.ID.eq(b.AUTHOR_ID)) .fetchInto ({ r -> println( "${r.getValue(a.FIRST_NAME)} " + "${r.getValue(a.LAST_NAME)} " + "has written ${r.getValue(b.TITLE)}" ) } as RecordHandler) Note that while Groovy supports some means of operator overloading, we think that these means should be avoided in a jOOQ integration. For instance, a + b in Groovy maps to a formal a.plus(b) method invocation, and jOOQ provides the required synonyms in its API to help you write such expressions. Nonetheless, Groovy only offers little typesafety, and as such, operator overloading can lead to many runtime issues. Another caveat of Groovy 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.

3.10. jOOQ and Kotlin Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition As any other library, jOOQ can be easily used in Kotlin, taking advantage of the many Kotlin language features such as for example: Optional ";" at the end of a Kotlin statement

Type inference for local variables A short example jOOQ application in Kotlin might look like this: package org.jooq.example.kotlin import java.util.Properties import org.jooq.* import org.jooq.impl.DSL import org.jooq.impl.DSL.* import org.jooq.example.db.h2.Tables.* fun main(args: Array<String>) { 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. 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: 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.

3.11. jOOQ and NoSQL Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition jOOQ users often get excited about jOOQ's intuitive API and would then wish for NoSQL support. There are a variety of NoSQL databases that implement some sort of proprietary query language. Some of these query languages even look like SQL. Examples are JCR-SQL2, CQL (Cassandra Query Language), Cypher (Neo4j's Query Language), SOQL (Salesforce Query Language) and many more. Mapping the jOOQ API onto these alternative query languages would be a very poor fit and a leaky abstraction. We believe in the power and expressivity of the SQL standard and its various dialects. Databases that extend this standard too much, or implement it not thoroughly enough are often not suitable targets for jOOQ. It would be better to build a new, dedicated API for just that one particular query language. jOOQ is about SQL, and about SQL alone. Read more about our visions in the manual's preface.

3.12. jOOQ and JPA Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition Just because you're using jOOQ doesn't mean you have to use it for everything! When introducing jOOQ into an existing application that uses JPA, the common question is always: "Should we replace JPA by jOOQ?" and "How do we proceed doing that?" Beware that jOOQ is not a replacement for JPA. Think of jOOQ as a complement. JPA (and ORMs in general) try to solve the object graph persistence problem. In short, this problem is about Loading an entity graph into client memory from a database

Manipulating that graph in the client

Storing the modification back to the database As the above graph gets more complex, a lot of tricky questions arise like: What's the optimal order of SQL DML operations for loading and storing entities?

How can we batch the commands more efficiently?

How can we keep the transaction footprint as low as possible without compromising on ACID?

How can we implement optimistic locking? jOOQ only has some of the answers. While jOOQ does offer updatable records that help running simple CRUD, a batch API, optimistic locking capabilities, jOOQ mainly focuses on executing actual SQL statements. SQL is the preferred language of database interaction, when any of the following are given: You run reports and analytics on large data sets directly in the database

You import / export data using ETL

You run complex business logic as SQL queries Whenever SQL is a good fit, jOOQ is a good fit. Whenever you're operating and persisting the object graph, JPA is a good fit. And sometimes, it's best to combine both

3.13. Dependencies Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition Dependencies are a big hassle in modern software. Many libraries depend on other, non-JDK library parts that come in different, incompatible versions, potentially causing trouble in your runtime environment. jOOQ has no external dependencies on any third-party libraries. However, the above rule has some exceptions: logging APIs are referenced as "optional dependencies". jOOQ tries to find slf4j on the classpath. If it fails, it will use the java.util.logging.Logger

Oracle ojdbc types used for array creation are loaded using reflection. The same applies to SQL Server types and Postgres PG* types.

Small libraries with compatible licenses are incorporated into jOOQ. These include jOOR, jOOU, parts of OpenCSV, json simple, parts of commons-lang

javax.persistence and javax.validation will be needed if you activate the relevant code generation flags

3.14. Build your own Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition In order to build jOOQ (Open Source Edition) yourself, please download the sources from https://github.com/jOOQ/jOOQ and use Maven to build jOOQ, preferably in Eclipse. The jOOQ Open Source Edition requires Java 8+ to compile and run. The commercial jOOQ Editions require Java 8+ or Java 6+ to compile and run, depending on the distribution. Some useful hints to build jOOQ yourself: Get the latest version of Git or EGit

Get the latest version of Maven or M2E

Check out the jOOQ sources from https://github.com/jOOQ/jOOQ

Optionally, import Maven artefacts into an Eclipse workspace using the following command (see the maven-eclipse-plugin documentation for details): mvn eclipse:eclipse

Build the jooq-parent artefact by using any of these commands: mvn clean package

create .jar files in ${project.build.directory} mvn clean install

install the .jar files in your local repository (e.g. ~/.m2 ) mvn clean {goal} -Dmaven.test.skip=true

don't run unit tests when building artefacts

artefact by using any of these commands:

3.15. jOOQ and backwards-compatibility Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition Semantic versioning jOOQ's understanding of backwards compatibility is inspired by the rules of semantic versioning according to http://semver.org. Those rules impose a versioning scheme [X].[Y].[Z] that can be summarised as follows: If a patch release includes bugfixes, performance improvements and API-irrelevant new features, [Z] is incremented by one.

If a minor release includes backwards-compatible, API-relevant new features, [Y] is incremented by one and [Z] is reset to zero.

If a major release includes backwards-incompatible, API-relevant new features, [X] is incremented by one and [Y], [Z] are reset to zero. jOOQ's understanding of backwards-compatibility Backwards-compatibility is important to jOOQ. You've chosen jOOQ as a strategic SQL engine and you don't want your SQL to break. However, there are some elements of API evolution that would be considered backwards-incompatible in other APIs, but not in jOOQ. As discussed later on in the section about jOOQ's query DSL API, much of jOOQ's API is indeed an internal domain-specific language implemented mostly using Java interfaces. Adding language elements to these interfaces means any of these actions: Adding methods to the interface

Overloading methods for convenience

Changing the type hierarchy of interfaces It becomes obvious that it would be impossible to add new language elements (e.g. new SQL functions, new SELECT clauses) to the API without breaking any client code that actually implements those interfaces. Hence, the following rules should be observed: jOOQ's DSL interfaces should not be implemented by client code! Extend only those extension points that are explicitly documented as "extendable" (e.g. custom QueryParts).

Generated code implements such interfaces and extends internal classes, and as such is recommended to be re-generated with a matching code generator version every time the runtime library is upgraded.

Binary compatibility can be expected from patch releases, but not from minor releases as it is not practical to maintain binary compatibility in an internal DSL.

Source compatibility can be expected from patch and minor releases.

Behavioural compatibility can be expected from patch and minor releases.

Any jOOQ SPI XYZ that is meant to be implemented ships with a DefaultXYZ or AbstractXYZ , which can be used safely as a default implementation. jOOQ-codegen and jOOQ-meta While a reasonable amount of care is spent to maintain these two modules under the rules of semantic versioning, it may well be that minor releases introduce backwards-incompatible changes. This will be announced in the respective release notes and should be the exception.

4. SQL building Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition SQL is a declarative language that is hard to integrate into procedural, object-oriented, functional or any other type of programming languages. jOOQ's philosophy is to give SQL the credit it deserves and integrate SQL itself as an "internal domain specific language" directly into Java. With this philosophy in mind, SQL building is the main feature of jOOQ. All other features (such as SQL execution and code generation) are mere convenience built on top of jOOQ's SQL building capabilities. This section explains all about the various syntax elements involved with jOOQ's SQL building capabilities. For a complete overview of all syntax elements, please refer to the manual's sections about SQL to DSL mapping rules.

4.1. The query DSL type Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition jOOQ exposes a lot of interfaces and hides most implementation facts from client code. The reasons for this are: Interface-driven design. This allows for modelling queries in a fluent API most efficiently

Reduction of complexity for client code.

API guarantee. You only depend on the exposed interfaces, not concrete (potentially dialect-specific) implementations. The org.jooq.impl.DSL class is the main class from where you will create all jOOQ objects. It serves as a static factory for table expressions, column expressions (or "fields"), conditional expressions and many other QueryParts. The static query DSL API With jOOQ 2.0, static factory methods have been introduced in order to make client code look more like SQL. Ideally, when working with jOOQ, you will simply static import all methods from the DSL class: import static org.jooq.impl.DSL.*; Note, that when working with Eclipse, you could also add the DSL to your favourites. This will allow to access functions even more fluently: concat(trim(FIRST_NAME), trim(LAST_NAME)); // ... which is in fact the same as: DSL.concat(DSL.trim(FIRST_NAME), DSL.trim(LAST_NAME));

4.1.1. DSL subclasses Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition There are a couple of subclasses for the general query DSL. Each SQL dialect has its own dialect-specific DSL. For instance, if you're only using the MySQL dialect, you can choose to reference the MySQLDSL instead of the standard DSL: The advantage of referencing a dialect-specific DSL lies in the fact that you have access to more proprietary RDMBS functionality. This may include: MySQL's encryption functions

PL/SQL constructs, pgplsql, or any other dialect's ROUTINE-language (maybe in the future)

4.2.2. SQL Dialect Family Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition In jOOQ 3.1, the notion of a SQLDialect.family() was introduced, in order to group several similar SQL dialects into a common family. An example for this is SQL Server, which is supported by jOOQ in various versions: SQL Server: The "version-less" SQL Server version. This always maps to the latest supported version of SQL Server

SQL Server 2012: The SQL Server version 2012

SQL Server 2008: The SQL Server version 2008 In the above list, SQLSERVER is both a dialect and a family of three dialects. This distinction is used internally by jOOQ to distinguish whether to use the OFFSET .. FETCH clause (SQL Server 2012), or whether to emulate it using ROW_NUMBER() OVER() (SQL Server 2008).

4.2.3. Connection vs. DataSource Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition Interact with JDBC Connections While you can use jOOQ for SQL building only, you can also run queries against a JDBC java.sql.Connection. Internally, jOOQ creates java.sql.Statement or java.sql.PreparedStatement objects from such a Connection, in order to execute statements. The normal operation mode is to provide a Configuration with a JDBC Connection, whose lifecycle you will control yourself. This means that jOOQ will not actively close connections, rollback or commit transactions. Note, in this case, jOOQ will internally use a org.jooq.impl.DefaultConnectionProvider, which you can reference directly if you prefer that. The DefaultConnectionProvider exposes various transaction-control methods, such as commit(), rollback(), etc. Interact with JDBC DataSources If you're in a J2EE or Spring context, however, you may wish to use a javax.sql.DataSource instead. Connections obtained from such a DataSource will be closed after query execution by jOOQ. The semantics of such a close operation should be the returning of the connection into a connection pool, not the actual closing of the underlying connection. Typically, this makes sense in an environment using distributed JTA transactions. An example of using DataSources with jOOQ can be seen in the tutorial section about using jOOQ with Spring. Note, in this case, jOOQ will internally use a org.jooq.impl.DataSourceConnectionProvider, which you can reference directly if you prefer that. Inject custom behaviour If your specific environment works differently from any of the above approaches, you can inject your own custom implementation of a ConnectionProvider into jOOQ. This is the API contract you have to fulfil: public interface ConnectionProvider { // Provide jOOQ with a connection Connection acquire() throws DataAccessException; // Get a connection back from jOOQ void release(Connection connection) throws DataAccessException; }

4.2.4. Custom data Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition In advanced use cases of integrating your application with jOOQ, you may want to put custom data into your Configuration, which you can then access from your... Custom ExecuteListeners

Custom QueryParts Here is an example of how to use the custom data API. Let's assume that you have written an ExecuteListener, that prevents INSERT statements, when a given flag is set to true : // Implement an ExecuteListener public class NoInsertListener extends DefaultExecuteListener { @Override public void start(ExecuteContext ctx) { // This listener is active only, when your custom flag is set to true if (Boolean.TRUE.equals(ctx.configuration().data("com.example.my-namespace.no-inserts"))) { // If active, fail this execution, if an INSERT statement is being executed if (ctx.query() instanceof Insert) { throw new DataAccessException("No INSERT statements allowed"); } } } } See the manual's section about ExecuteListeners to learn more about how to implement an ExecuteListener . Now, the above listener can be added to your Configuration, but you will also need to pass the flag to the Configuration , in order for the listener to work: // Create your Configuration Configuration configuration = new DefaultConfiguration().set(connection).set(dialect); // Set a new execute listener provider onto the configuration: configuration.set(new DefaultExecuteListenerProvider(new NoInsertListener())); // Use any String literal to identify your custom data configuration.data("com.example.my-namespace.no-inserts", true); // Try to execute an INSERT statement try { DSL.using(configuration) .insertInto(AUTHOR, AUTHOR.ID, AUTHOR.LAST_NAME) .values(1, "Orwell") .execute(); // You shouldn't get here Assert.fail(); } // Your NoInsertListener should be throwing this exception here: catch (DataAccessException expected) { Assert.assertEquals("No INSERT statements allowed", expected.getMessage()); } Using the data() methods, you can store and retrieve custom data in your Configurations .

4.2.5. Custom ExecuteListeners Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition ExecuteListeners are a useful tool to... implement custom logging

apply triggers written in Java

collect query execution statistics ExecuteListeners are hooked into your Configuration by returning them from an org.jooq.ExecuteListenerProvider: // Create your Configuration Configuration configuration = new DefaultConfiguration().set(connection).set(dialect); // Hook your listener providers into the configuration: configuration.set( new DefaultExecuteListenerProvider(new MyFirstListener()), new DefaultExecuteListenerProvider(new PerformanceLoggingListener()), new DefaultExecuteListenerProvider(new NoInsertListener()) ); See the manual's section about ExecuteListeners to see examples of such listener implementations.

4.2.6. Custom Unwrappers Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition JDBC knows the java.sql.Wrapper API, which is implemented by all JDBC types in order to be able to "unwrap" a native driver implementation for any given type. For example: // This may be some proxy from a connection pool Connection c = getConnection(); // Sometimes, we want the native driver connection instance OracleConnection oc = c.unwrap(OracleConnection.class); Array array = oc.createARRAY("ARRAY_TYPE", new Object[] { "a", "b" }); jOOQ internally makes similar calls occasionally. For this, it needs to unwrap the native java.sql.Connection or java.sql.PreparedStatement instance. Unfortunately, not all third party libraries correctly implement the Wrapper API contract, so this unwrapping might not work. The org.jooq.Unwrapper SPI is designed to allow for custom implementations to be injected into jOOQ configurations: // Your jOOQ configuration Configuration c1 = getConfiguration(); Configuration c2 = c.derive(new Unwrapper() { @Override public <T> T unwrap(Wrapper wrapper, Class<T> 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("...");

4.2.7. Custom Settings Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition The jOOQ Configuration allows for some optional configuration elements to be used by advanced users. The org.jooq.conf.Settings class is a JAXB-annotated type, that can be provided to a Configuration in several ways: In the DSLContext constructor ( DSL.using() ). This will override default settings below

). This will override default settings below in the org.jooq.impl.DefaultConfiguration constructor. This will override default settings below

From a location specified by a JVM parameter: -Dorg.jooq.settings

From the classpath at /jooq-settings.xml

From the settings defaults, as specified in http://www.jooq.org/xsd/jooq-runtime-3.13.4.xsd The most specific settings for a given context will apply. If you wish to configure your settings through XML, but explicitly load them for a given Configuration , you can do so as well, using JAXB: Settings settings = JAXB.unmarshal(new File("/path/to/settings.xml"), Settings.class); Example For example, if you want to indicate to jOOQ, that it should inline all bind variables, and execute static java.sql.Statement instead of binding its variables to java.sql.PreparedStatement, you can do so by creating the following DSLContext: Settings settings = new Settings(); settings.setStatementType(StatementType.STATIC_STATEMENT); DSLContext create = DSL.using(connection, dialect, settings); More details Please refer to the jOOQ runtime configuration XSD for more details:

http://www.jooq.org/xsd/jooq-runtime-3.13.4.xsd

4.2.7.1. Object qualification Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition By default, jOOQ fully qualifies all objects with their catalog and schema names, if such qualification is made available by the code generator. For instance, the following SQL statement containing full qualification may be produced by jOOQ code with seemingly no qualification: -- Full qualification on columns and tables SELECT catalog.schema.table.column FROM catalog.schema.table DSL.using(configuration) .select(TABLE.COLUMN) // Column only qualified with table .from(TABLE) // No qualification on table While the jOOQ code is also implicitly fully qualified (see implied imports), it may not be desireable to use fully qualified object names in SQL. The renderCatalog and renderSchema settings are used for this. Example configuration new Settings() .withRenderCatalog(false) // Defaults to true .withRenderSchema(false) // Defaults to true More sophisticated multitenancy approaches are available through the render mapping feature.

4.2.7.2. Runtime catalog, schema and table mapping Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition Mapping your DEV schema to a productive environment You may wish to design your database in a way that you have several instances of your schema. This is useful when you want to cleanly separate data belonging to several customers / organisation units / branches / users and put each of those entities' data in a separate database or schema. In our AUTHOR example this would mean that you provide a book reference database to several companies, such as My Book World and Books R Us. In that case, you'll probably have a schema setup like this: DEV: Your development schema. This will be the schema that you base code generation upon, with jOOQ

MY_BOOK_WORLD: The schema instance for My Book World

BOOKS_R_US: The schema instance for Books R Us Mapping DEV to MY_BOOK_WORLD with jOOQ When a user from My Book World logs in, you want them to access the MY_BOOK_WORLD schema using classes generated from DEV. This can be achieved with the org.jooq.conf.RenderMapping class, that you can equip your Configuration's settings with. Take the following example: Example configuration Settings settings = new Settings() .withRenderMapping(new RenderMapping() .withSchemata( new MappedSchema().withInput("DEV") .withOutput("MY_BOOK_WORLD"), new MappedSchema().withInput("LOG") .withOutput("MY_BOOK_WORLD_LOG"))); The query executed with a Configuration equipped with the above mapping will in fact produce this SQL statement: SELECT * FROM MY_BOOK_WORLD.AUTHOR DSL.using(connection, dialect, settings) .selectFrom(DEV.AUTHOR) This works because AUTHOR was generated from the DEV schema, which is mapped to the MY_BOOK_WORLD schema by the above settings. Mapping of tables Not only schemata can be mapped, but also tables. If you are not the owner of the database your application connects to, you might need to install your schema with some sort of prefix to every table. In our examples, this might mean that you will have to map DEV.AUTHOR to something MY_BOOK_WORLD.MY_APP__AUTHOR, where MY_APP__ is a prefix applied to all of your tables. This can be achieved by creating the following mapping: Example configuration Settings settings = new Settings() .withRenderMapping(new RenderMapping() .withSchemata( new MappedSchema().withInput("DEV") .withTables( new MappedTable().withInput("AUTHOR") .withOutput("MY_APP__AUTHOR")))); The query executed with a Configuration equipped with the above mapping will in fact produce this SQL statement: SELECT * FROM DEV.MY_APP__AUTHOR Table mapping and schema mapping can be applied independently, by specifying several MappedSchema entries in the above configuration. jOOQ will process them in order of appearance and map at first match. Note that you can always omit a MappedSchema's output value, in case of which, only the table mapping is applied. If you omit a MappedSchema's input value, the table mapping is applied to all schemata! Mapping of catalogs For databases like SQL Server, it is also possible to map catalogs in addition to schemata. The mechanism is exactly the same. So let's assume that we generated code for a table [dev].[dbo].[author] and want to map it to [my_book_world].[dbo].[author] at runtime. This can be achieved as follows: Example configuration Settings settings = new Settings() .withRenderMapping(new RenderMapping() .withCatalogs( new MappedCatalog().withInput("DEV") .withOutput("MY_BOOK_WORLD"))); To give you full control of how each and every table gets mapped, a MappedCatalog object can contain MappedSchema (and thus also MappedTable) definitions. Using regular expressions All of the above examples were using 1:1 constant name mappings where the input and output schema or table names are fixed by the configuration. With jOOQ 3.8, regular expression can be used as well for mapping, for example: Example configuration Settings settings = new Settings() .withRenderMapping(new RenderMapping() .withSchemata( new MappedSchema().withInputExpression(Pattern.compile("DEV_(.*)")) .withOutput("PROD_$1") .withTables( new MappedTable().withInputExpression(Pattern.compile("DEV_(.*)")) .withOutput("PROD_$1")))); The only difference to the constant version is that the input field is replaced by the inputExpression field of type java.util.regex.Pattern, in case of which the meaning of the output field is a pattern replacement, not a constant replacement. Hard-wiring mappings at code-generation time Note that the manual's section about code generation schema mapping explains how you can hard-wire your catalog, schema and table mappings at code generation time.

4.2.7.3. Identifier style Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition By default, jOOQ will always generate quoted names for all identifiers (even if this manual omits this for readability). For instance: SELECT "TABLE"."COLUMN" FROM "TABLE" -- SQL standard style SELECT `TABLE`.`COLUMN` FROM `TABLE` -- MySQL style SELECT [TABLE].[COLUMN] FROM [TABLE] -- SQL Server style Quoting has the following effect on identifiers in most (but not all) databases: It allows for using reserved names as object names, e.g. a table called "FROM" is usually possible only when quoted.

is usually possible only when quoted. It allows for using special characters in object names, e.g. a column called "FIRST NAME" can be achieved only with quoting.

can be achieved only with quoting. It turns what are mostly case-insensitive identifiers into case-sensitive ones, e.g. "name" and "NAME" are different identifiers, whereas name and NAME are not. Please consider your database manual to learn what the proper default case and default case sensitivity is. The renderQuotedNames and renderNameCase settings allow for overriding the name of all identifiers in jOOQ to a consistent style. Possible options are: RenderQuotedNames ALWAYS : This will quote all identifiers.

: This will quote all identifiers. EXPLICIT_DEFAULT_QUOTED : This will quote all identifiers, which are not explicitly unquoted using DSL.unquotedName().

: This will quote all identifiers, which are not explicitly unquoted using DSL.unquotedName(). EXPLICIT_DEFAULT_UNQUOTED : This will quote all identifiers, which are not explicitly quoted using DSL.quotedName().

: This will quote all identifiers, which are not explicitly quoted using DSL.quotedName(). NEVER : This will not quote any identifiers. RenderNameCase AS_IS : This will generate all names in their proper case.

: This will generate all names in their proper case. LOWER : This will transform all names to lower case.

: This will transform all names to lower case. LOWER_IF_UNQUOTED : This will transform all names to lower case if the name is unquoted.

: This will transform all names to lower case if the name is unquoted. UPPER : This will transform all names to upper case.

: This will transform all names to upper case. UPPER_IF_UNQUOTED : This will transform all names to upper case if the name is unquoted. The two flags are independent of one another. If your database supports quoted, case sensitive identifiers, then using LOWER or UPPER on quoted identifiers may not work. Example configuration Settings settings = new Settings() .withRenderQuotedNames(RenderQuotedNames.EXPLICIT_DEFAULT_UNQUOTED); // Defaults to EXPLICIT_DEFAULT_QUOTED .withRenderNameCase(RenderNameCase.LOWER_IF_UNQUOTED); // Defaults to AS_IS The behaviour of this setting is influenced by the renderLocale setting.

4.2.7.4. Keyword style Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition In all SQL dialects, keywords are case insensitive, and this is also the default in jOOQ, which mostly generates lower-case keywords. Users may wish to adapt this and they have these options for the renderKeywordCase setting: AS_IS (the default): Generate keywords as they are defined in the codebase (mostly lower case).

(the default): Generate keywords as they are defined in the codebase (mostly lower case). LOWER : Generate keywords in lower case.

: Generate keywords in lower case. UPPER : Generate keywords in upper case.

: Generate keywords in upper case. PASCAL : Generate keywords in pascal case. Example configuration Settings settings = new Settings() .withRenderKeywordCase(RenderKeywordCase.UPPER); // Defaults to AS_IS

4.2.7.5. Locales Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition When doing locale sensitive operations, such as upper casing or lower casing a name (see Name styles), then it may be important in some areas to be able to specify the java.util.Locale for the operation. Example configuration // All of these default to Locale.getDefault(), if not specified explicitly Settings settings = new Settings() .withLocale(Locale.forLanguageTag("de")) // The default locale if no more specific locales are specified .withRenderLocale(Locale.forLanguageTag("de")) // The locale used when rendering SQL .withParseLocale(Locale.forLanguageTag("de")) // The locale used when parsing SQL .withInterpreterLocale(Locale.forLanguageTag("de")) // The locale used when interpreting SQL

4.2.7.6. Parameter types Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition Bind values or bind parameters come in different flavours in different SQL databases. JDBC standardises on their syntax by allowing only ? (question mark) characters as placeholders for bind variables. Thus, jOOQ, by default, generates ? placeholders for JDBC consumptions. Users who wish to use jOOQ with a different backend than JDBC can specify that all jOOQ bind values, including indexed parameters and named parameters generate alternative strings, other than ? . These are the current options: INDEXED (the default): Generates indexed parameter placeholders using ? .

(the default): Generates indexed parameter placeholders using . NAMED : Generates named parameter placeholders, such as :param for parameters that are named explicitly or :1 for unnamed, indexed parameters.

: Generates named parameter placeholders, such as for parameters that are named explicitly or for unnamed, indexed parameters. NAMED_OR_INLINED: Generates named parameter placeholders for parameters that are named explicitly and inlines all unnamed parameters.

Generates named parameter placeholders for parameters that are named explicitly and inlines all unnamed parameters. INLINED : Inlines all parameters. An example: -- INDEXED SELECT FIRST_NAME || ? FROM AUTHOR WHERE ID = ? -- NAMED SELECT FIRST_NAME || :1 FROM AUTHOR WHERE ID = :x -- NAMED_OR_INLINED SELECT FIRST_NAME || 'x' FROM AUTHOR WHERE ID = :x -- INLINED SELECT FIRST_NAME || 'x' FROM AUTHOR WHERE ID = 42 Param<String> x = val("x"); Param<Integer> i = param("x", 42); DSL.using(configuration) .select(FIRST_NAME.concat(x)) .from(AUTHOR) .where(ID.eq(i)) .fetch(); Example configuration Settings settings = new Settings() .withParamType(ParamType.NAMED); // Defaults to INDEXED The following setting statementType may override this setting.

4.2.7.7. Parameter name prefix Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition When choosing a ParameterType.NAMED to produce named parameters, the default is to use a colon as a prefix to the parameter name, for example: -- NAMED SELECT FIRST_NAME || :1 FROM AUTHOR WHERE ID = :x Depending on how the named parameters are interpreted, this default is not optimal. A better character might be the $ sign, e.g. in PostgreSQL or R2DBC. For this, the renderNamedParamPrefix setting can be used: Example configuration Settings settings = new Settings() .withRenderNamedParamPrefix("$"); // Defaults to ":"

4.2.7.8. Statement Type Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition JDBC knows two types of statements: java.sql.PreparedStatement: This allows for sending bind variables to the server. jOOQ uses prepared statements by default.

jav