One of jOOQ's key features so far has always been to render pretty much exactly the SQL that users expect, without any surprises - unless some emulation is required to make a query work, of course. This means that while join elimination is a powerful feature of many RDBMS, it isn't part of jOOQ's feature … Continue reading A Hidden Benefit of Implicit Joins: Join Elimination
Category: jooq-in-use
This category contains posts about “jOOQ seen in the wild”, interesting feedback, success stories, articles by end users
Workaround for MySQL’s “can’t specify target table for update in FROM clause” Error
In MySQL, you cannot do this: create table t (i int primary key, j int); insert into t values (1, 1); update t set j = (select max(j) from t) + 1; The UPDATE statement will raise an error as follows: SQL Error [1093] [HY000]: You can't specify target table 't' for update in FROM … Continue reading Workaround for MySQL’s “can’t specify target table for update in FROM clause” Error
To DAO or not to DAO
jOOQ's DAO API is one of jOOQ's most controversial features. When it was first implemented, it was implemented merely: Because it was so easy to implement Because it seemed so useful for simple CRUD tasks Because that's what many developers want There's a strong hint about the third bullet given how popular Spring Data's repository … Continue reading To DAO or not to DAO
How to Generate Package Private Code with jOOQ’s Code Generator
Java's package private visibility is an underrated feature. When you omit any visibility modifier in Java, then the default (for most objects) is package private, i.e. the object is visible only to types in the same package: class YouDontSeeMe {} class YouDontSeeMeEither {} In fact, a compilation unit (the .java file) can contain multiple such … Continue reading How to Generate Package Private Code with jOOQ’s Code Generator
How to Pass a Table Valued Parameter to a T-SQL Function with jOOQ
Microsoft T-SQL supports a language feature called table-valued parameter (TVP), which is a parameter of a table type that can be passed to a stored procedure or function. For example, you may write: CREATE TYPE u_number_table AS TABLE (column_value INTEGER); CREATE FUNCTION f_cross_multiply ( @numbers u_number_table READONLY ) RETURNS @result TABLE ( i1 INTEGER, i2 … Continue reading How to Pass a Table Valued Parameter to a T-SQL Function with jOOQ
How to use jOOQ’s Converters with UNION Operations
jOOQ 3.15 introduced the concept of an ad-hoc converter, a converter that is applied "ad-hoc" to a single query. It uses the same underlying mechanisms as any ordinary Converter that is attached to generated code for use in every query. An example of such an ad-hoc converter is this: // Without the converter, assuming BOOK.ID … Continue reading How to use jOOQ’s Converters with UNION Operations
How to Write a Derived Table in jOOQ
One of the more frequent questions about jOOQ is how to write a derived table (or a CTE). The jOOQ manual shows a simple example of a derived table: In SQL: SELECT nested.* FROM ( SELECT AUTHOR_ID, count(*) books FROM BOOK GROUP BY AUTHOR_ID ) nested ORDER BY nested.books DESC In jOOQ: // Declare the … Continue reading How to Write a Derived Table in jOOQ
Why You Should Execute jOOQ Queries With jOOQ
Previously on this blog, I've written a post explaining why you should use jOOQ's code generator, despite the possibility of using jOOQ without it. In a similar fashion, as I've answered numerous jOOQ questions on Stack Overflow, where someone used jOOQ to build a query, but then executed it elsewhere, including on: JPA JDBC / … Continue reading Why You Should Execute jOOQ Queries With jOOQ
jOOQ’s R2DBC LoggingConnection to log all SQL statements
jOOQ already has a LoggingConnection (see also the manual), which acts as a JDBC proxy Connection to log all SQL statements that are executed by any JDBC client (including Hibernate, MyBatis, JdbcTemplate, native JDBC, etc.). Starting from jOOQ 3.18.0, 3.17.7, and 3.16.13, a LoggingConnection is now also available for R2DBC clients to log all reactive … Continue reading jOOQ’s R2DBC LoggingConnection to log all SQL statements
When to Use jOOQ and When to Use Native SQL
A frequently encountered doubt people have when using jOOQ is to decide when a "complex" query should be written using jOOQ API vs. when it should be implemented using native SQL. The jOOQ manual is full of side by side examples of the same query, e.g. Using jOOQ: ctx.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count()) .from(AUTHOR) .join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID)) .groupBy(AUTHOR.ID, AUTHOR.FIRST_NAME, … Continue reading When to Use jOOQ and When to Use Native SQL