In our opinion, any Iterable<T> should offer a <R> collect(Collector<T, ?, R>) method to allow for transforming the the content to something else using standard JDK collectors, jOOλ collectors from org.jooq.lambda.Agg or your own. When using jOOQ, you don't have to wait for the JDK to finally add these useful utilities to the Iterable API. … Continue reading Use ResultQuery.collect() to Implement Powerful Mappings
Category: jooq-in-use
This category contains posts about “jOOQ seen in the wild”, interesting feedback, success stories, articles by end users
Use IN List Padding to Your JDBC Application to Avoid Cursor Cache Contention Problems
A problem few developers are aware of is the possibility of running into "cursor cache contention" or "execution plan cache contention" problems when using IN lists in SQL. The problem that is described in lengths in previous articles, can be summarised as this. All of these are distinct SQL queries and need to be parsed … Continue reading Use IN List Padding to Your JDBC Application to Avoid Cursor Cache Contention Problems
Using jOOQ 3.14 Synthetic Foreign Keys to Write Implicit Joins on Views
jOOQ has supported one of JPQL's most cool features for a while now: implicit joins. Using jOOQ, you can navigate your to-one relationships in a type safe way, generating LEFT JOIN operations implicitly without the effort of having to keep thinking about join predicates, and the correct join order. Consider this Sakila database query here, … Continue reading Using jOOQ 3.14 Synthetic Foreign Keys to Write Implicit Joins on Views
What’s a “String” in the jOOQ API?
One of jOOQ's biggest strength is the fact that it is a type safe SQL API. "Type safe", in this context, means that every object that you put in a jOOQ query has a well defined type, such as: Condition Field Table These can be used in jOOQ in a type safe way as such: … Continue reading What’s a “String” in the jOOQ API?
Create Empty Optional SQL Clauses with jOOQ
When using jOOQ to create dynamic SQL statements (one of jOOQ's core value propositions), it is often necessary to add query elements conditionally, with a default "No-op" behaviour. For first time users, this default "no-op" behaviour is not always obvious as the jOOQ API is vast, and as with any vast API, there are many … Continue reading Create Empty Optional SQL Clauses with jOOQ
Using Java 13+ Text Blocks for Plain SQL with jOOQ
Most jOOQ users use the jOOQ DSL API, which provides compile time type safety and an easy way to write dynamic SQL. But occasionally, this DSL get in the way, because it might be Overkill for some very simple quick and dirty SQL queries Too limited when running very advanced vendor specific SQL, such as … Continue reading Using Java 13+ Text Blocks for Plain SQL with jOOQ
Never Concatenate Strings With jOOQ
jOOQ supports a vast amount of SQL syntax out of the box. As such, most users will not think of resorting to string concatenation like in the old days when writing dynamic SQL with JDBC. But every now and then, a vendor specific feature is not supported by jOOQ (yes, it happens). In that case, … Continue reading Never Concatenate Strings With jOOQ
jOOQ 3.13 Released with More API and Tooling for DDL Management
jOOQ 3.13 has been released with CockroachDB support, much more API and tooling for DDL management, and SQL:2011 temporal table support Starting with this release, we will further embrace our support for parsing, translating, executing, and now also interpreting DDL statements. The driving force is better code generation support, but in the future, also better … Continue reading jOOQ 3.13 Released with More API and Tooling for DDL Management
How to Map MySQL’s TINYINT(1) to Boolean in jOOQ
MySQL 8 does not yet support the BOOLEAN type as specified in the SQL standard. There is a DDL "type" called BOOL, which is just an alias for TINYINT: create table t(b bool); select table_name, column_name, data_type, column_type from information_schema.columns where table_name = 't'; The above produces: TABLE_NAME|COLUMN_NAME|DATA_TYPE|COLUMN_TYPE| ----------|-----------|---------|-----------| t |b |tinyint |tinyint(1) | Notice … Continue reading How to Map MySQL’s TINYINT(1) to Boolean in jOOQ
Quantified LIKE ANY predicates in jOOQ 3.12
Quantified comparison predicates One of SQL's weirdes features are quantified comparison predicates. I've hardly ever seen these in the wild: SELECT * FROM t WHERE id = ANY (1, 2, 3) The above example is equivalent to using the much more readable IN predicate: SELECT * FROM t WHERE id IN (1, 2, 3) This … Continue reading Quantified LIKE ANY predicates in jOOQ 3.12