ANTI JOIN is a very useful operator from relational algebra. Regrettably, only few dialects support it in terms of SQL syntax, as we’ve written earlier. In jOOQ, you can write it as follows: If your RDBMS supports this natively (e.g. ClickHouse, Databricks), then it is rendered as such. Otherwise, jOOQ will translate this to: But … Continue reading Simplifying ANTI…
#jooq-in-use
69 posts
7 May
27 Mar
One of jOOQ’s most popular feature is the out-of-the-box debug logging experience. jOOQ developers find this feature very useful when developing their applications. Assuming you run a jOOQ query and configure your logger to print DEBUG log output: When this query is executed, your log output might contain something like this: Executing query : select … Continue reading Managing Sensitive…
10 Jan 2024
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…
20 Dec 2023
In MySQL, you cannot do this: The UPDATE statement will raise an error as follows: SQL Error [1093] [HY000]: You can’t specify target table ‘t’ for update in FROM clause People have considered this to be a bug in MySQL for ages, as most other RDBMS can do this without any issues, including MySQL clones: … Continue reading Workaround for…
6 Dec 2023
jOOQ’s DAO API is one of jOOQ’s most controversial features. When it was first implemented, it was implemented merely: There’s a strong hint about the third bullet given how popular Spring Data’s repository “pattern” is. A lot of developers just want to quickly fetch and store data, without giving individual queries much thought. A fun … Continue reading To DAO…
28 Jun 2023
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: In fact, a compilation unit (the .java file) can contain multiple such classes. You don’t have to create … Continue reading How to…
25 Apr 2023
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: This function takes a table-valued parameter (TVP), and produces a result set containing the cross product of the parameter table with itself. … Continue reading How to…
2 Mar 2023
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: While there are other ways to … Continue reading How to…
24 Feb 2023
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: In jOOQ: And that’s pretty much it. The question usually arises from the fact that there’s a surprising lack of type safety when working … Continue reading How to…
18 Jan 2023
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: jOOQ itself isn’t … Continue reading Why You…
17 Jan 2023
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…
8 Dec 2022
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: Using native SQL: In the native SQL … Continue reading When to…
21 Oct 2022
Using jOOQ’s code generator to call stored procedures is a popular reason to use jOOQ. For example, when you have a procedure like the following Oracle PL/SQL procedure: jOOQ will generate code for you to call very simply, like this: This will execute the following, taking care of binding all IN and OUT parameters for … Continue reading Calling Procedures…
13 Sept 2022
Starting with jOOQ 3.11, type safe implicit JOIN have been made available, and they’ve been enhanced to be supported also in DML statements in jOOQ 3.17. Today, I’d like to focus on a somewhat weird but really powerful use-case for implicit JOIN, when joining additional tables from within an explicit JOIN‘s ON clause. The use … Continue reading Using jOOQ’s…
6 Sept 2022
For new users working with jOOQ for the first time, the number of types in the jOOQ API can be overwhelming. The SQL language doesn’t have many such “visible” types, although if you think about SQL the way jOOQ does, then they’re there just the same, but hidden from users via an English style syntax. … Continue reading A Brief…
30 Aug 2022
I’ve just stumbled upon this great post by Vlad Mihalcea, titled The Best Way to Fetch a Spring Data JPA DTO Projection. It got some nice traction on reddit, too. This is such a nice use-case and apt solution, I wanted to quickly show the second best way of doing the same, with jOOQ this … Continue reading The Second…
Starting with jOOQ 3.16 and #12601, there may be a compilation error with a message like this in your jOOQ generated code: [ERROR] …/DefaultCatalog.java:[53,73] cannot find symbol[ERROR] symbol: variable VERSION_3_17[ERROR] location: class org.jooq.Constants Typically, this error is mixed with other compilation errors in generated code. Its purpose is to help troubleshoot these other compilation errors. … Continue reading Cannot resolve…
24 Aug 2022
Starting with jOOQ 3.17, the Condition type extends the Field<Boolean> type. Because, that’s what the SQL standard thinks it is, in sorts: The exact definition contains intermediate rules, but you get the idea. A <predicate> (which is a Condition in jOOQ) can be used wherever a <boolean value expression> can be used, which again can … Continue reading A Condition…
22 Aug 2022
When you write stored procedures and functions in your database, you want to ensure their correctness, just like with your Java code. In Java, this is done with unit tests, typically with JUnit. For example, if you have the following code in Java: Then, you might write a test like this: But how do we … Continue reading How to…
19 Aug 2022
The H2 database is an immensely popular in-memory database product mostly used by Java developers for testing. If you check out the DB-Engines ranking, it ranks 50th, which is quite impressive, as this rank outperforms products like: CockroachDB Ignite Single Store (previously MemSQL) Interbase (which was forked as Firebird) Ingres (which is a predecessor to … Continue reading Using H2…
28 Jul 2022
jOOQ is mainly known for its powerful type safe, embedded, dynamic SQL capabilities that are made available through code generation. However, a secondary use case of code generation is to use it for stored procedures (possibly exclusively for stored procedures). Stored procedures are powerful ways of moving complex data processing logic to the server. This … Continue reading The Best…
19 May 2022
The jOOQ API is all about convenience, and as such, an important operation (the most important one?) like fetch() must come with convenience, too. The default way to fetch data is this: It fetches the entire result set into memory and closes the underlying JDBC resources eagerly. But what other options do we have? Iterable … Continue reading The Many…
9 May 2022
A really cool, recent question on Stack Overflow was about how to map a nested collection into a Java Map with jOOQ. In the past, I’ve blogged about the powerful MULTISET operator many times, which allows for nesting collections in jOOQ. This time, instead of nesting data into a List<UserType>, why not nest it in … Continue reading How to…
1 Mar 2022
A lot of RDBMS support standard SQL sequences of some form. The standard SQL syntax to create a sequence is: The following is how you could fetch a value from this sequence, using jOOQ, assuming you’re using the code generator: The sequence expression translates to a variety of dialects: You can also embed the S.nextval() … Continue reading How to…
22 Feb 2022
Questions that might be a bit more difficult to solve using ordinary SQL are questions of the kind: What films have the same actors as a given film X? As always, we’re using the sakila database for this example. What would be a possible way to solve this with SQL (for example, PostgreSQL, to be … Continue reading Use MULTISET…
8 Feb 2022
I’ve recently stumbled upon this interesting Stack Overflow question about Hibernate’s popular MultipleBagFetchException. The question is super popular, and the answers are plenty. The various limitations are discussed throughout the question, it all boils down to a simple fact: Joins are the wrong tool to nest collections. Given a schema like the Sakila database: There … Continue reading No More…
11 Jan 2022
N+1 queries are a popular problem in many applications that run SQL queries. The problem can be described easily as follows: 1 query fetching a parent value is run N queries fetching each individual child values are run This problem isn’t limited to SQL, it can happen with any poorly designed API that does not … Continue reading Using jOOQ’s…
7 Jan 2022
One of the coolest things about using and making jOOQ is that we get to discover the best extensions to the standard SQL language by vendors, and add support for those clauses in jOOQ via emulations. One of these syntaxes is BigQuery’s * EXCEPT syntax. Everyone who ever wrote ad-hoc SQL queries would have liked … Continue reading The Useful…
16 Dec 2021
Starting from Spring Boot 2.5, there’s a handy new callback that you can implement, called DefaultConfigurationCustomizer, where the word DefaultConfiguration corresponds to jOOQ’s DefaultConfiguration. You can simply create a class like this in your project: The above callback receives the DefaultConfiguration at its initialisation stage, during which you can still safely mutate it to change … Continue reading How to…
9 Dec 2021
In classic SQL (i.e. before jOOQ’s awesome MULTISET operator), nested collections were fetched using ordinary (outer) joins. An example of such a query would be a query running against the sakila database to fetch actors and their films. Using jOOQ: The result from the jOOQ debug log would look something like this: +--------+----------+---------+-------+---------------------+ |actor_id|first_name|last_name|film_id|title | … Continue reading Using JDK…
6 Dec 2021
I’m answering many jOOQ questions on Stack Overflow, and a lot of times. The problem has the same cause: People not using jOOQ’s code generator. The main reason people seem not to be using it, is because it takes some extra time to set up, but as with anything well designed, the initial investment will … Continue reading Why You…
19 Oct 2021
jOOQ’s parser can’t parse every possible SQL syntax. Try this random PostgreSQL syntax: And the jOOQ parser will complain: DOMAIN, INDEX, SCHEMA, SEQUENCE, SESSION, TABLE, TYPE, or VIEW expected: [1:7] ALTER [*]SYSTEM RESET ALL That’s perfectly fine. The goal of the jOOQ parser isn’t to understand all vendor specific syntax. The goal is to offer … Continue reading The jOOQ…
27 Aug 2021
Database first is at the core of jOOQ’s design. jOOQ has been made primarily for classic systems the database is always there and always has been and will never leave. This is because we think “data have mass” This not only translates to moving logic closer to the data (see our previous posts about the … Continue reading Using Testcontainers…
26 Aug 2021
If your legacy JPA application is using occasional native queries or Hibernate @Formula or Spring Data @Query annotation with vendor specific native SQL embedded in it, you can use jOOQ’s parsing connection and parsing data source to translate between dialects, without having to go all in on your jOOQ adoption – though I think it’s … Continue reading Using jOOQ…
25 Aug 2021
One of the strengths of modern RDBMS is the capability to mix the powerful SQL language with procedural code. SQL is a 4th generation programming language (4GL), and as such, extremely well suited for querying and bulk data manipulation. Its functional-declarative nature allows for it to be optimised in highly efficient ways using cost-based optimisation, … Continue reading Vendor Agnostic,…
23 Aug 2021
MySQL’s JDBC connector has a security feature called allowMultiQueries, which defaults to false. When turned off, it prevents using a useful, but potentially dangerous feature in MySQL via JDBC: By default, the above produces a syntax error: Exception in thread "main" java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds … Continue reading MySQL’s allowMultiQueries…
19 Aug 2021
A very little known feature in jOOQ is the Formattable.formatChart() capability, which allows for formatting any jOOQ result as an ASCII chart. This can be useful for quick plotting of results in your console application. Assuming you have a result set of this form (which is what you’re getting when you call result.format() or just … Continue reading Formatting ASCII…
20 Jul 2021
jOOQ 3.15 shipped with a ton of new features, the most important ones being: MULTISET support (type safe, nested collections) Reactive SQL support via R2DBC A very useful, lesser known new feature is “ad-hoc data type conversion”. Data type converters and bindings have been around in jOOQ for a long time. Their goal is to … Continue reading Ad-hoc Data…
24 Jun 2021
jbang is a relatively new utility that … … lets students, educators and professional developers create, edit and run self-contained source-only Java programs with unprecedented ease. Sounds exciting. How does it work with jOOQ? Very easy! Set it up like this (other installation options here): curl -Ls https://sh.jbang.dev | bash -s - app setup And … Continue reading Quickly Trying…
4 Jun 2021
One of the biggest advantages of using jOOQ is that you can change all of your complex application’s generated SQL with just a few lines of code. In this article, we’ll look into how to solve some common bind peeking issues just like that, without touching your application code, without the need to explain this … Continue reading How to…
17 May 2021
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()…
22 Apr 2021
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…
13 Oct 2020
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 Apr 2020
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…
6 Mar 2020
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…
5 Mar 2020
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 In such cases, you can still benefit from jOOQ’s many secondary features, including for example its nice integration with the Stream … Continue reading Using Java…
4 Mar 2020
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…
14 Feb 2020
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…
27 Sept 2019
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: The above produces: TABLE_NAME|COLUMN_NAME|DATA_TYPE|COLUMN_TYPE| ----------|-----------|---------|-----------| t |b |tinyint |tinyint(1) | Notice that BOOL translates to a specific “type” of TINYINT, a TINYINT(1), where we might be … Continue reading How to…
5 Sept 2019
Quantified comparison predicates One of SQL’s weirdes features are quantified comparison predicates. I’ve hardly ever seen these in the wild: The above example is equivalent to using the much more readable IN predicate: This equivalence is defined in the SQL standard. There are more esoteric cases that could be solved using such quantified comparison predicates … Continue reading Quantified LIKE…
29 Aug 2019
jOOQ 3.12 has been released with a new procedural language API, new data types, MemSQL support, formal Java 11+ support, a much better parser, and reactive stream API support In this release, we’ve focused on a lot of minor infrastructure tasks, greatly improving the overall quality of jOOQ. We’ve reworked some of our automated integration … Continue reading jOOQ 3.12…
26 Jun 2019
Spring Boot is great to get started very quickly with what the Spring Boot authors have evaluated to be useful defaults. This can be a lot of help when you’re doing things for the first time, and have no way to copy paste working Maven pom.xml files from existing projects, for example. When working with … Continue reading How to…
14 Mar 2019
When configuring a jOOQ runtime Configuration, you may add an explicit Settings instance, which contains a set of useful flags that change jOOQ’s SQL generation behaviour and other things. Example settings include: Object qualification (generate schema.table.column or just table.column) Identifier style (to quote or not to quote) Keyword style (UPPER, lower, or Pascal Case for … Continue reading How to…
28 Aug 2018
One of the more frequent questions people have when switching from JPA to jOOQ is how to migrate from using JPA’s first level cache? Speaking of that, are there any jOOQ guides on how to replace JPA? Any patterns on how to work without EntityManager to manage in-memory state (EM#merge()), 1st level cache, etc. — … Continue reading A Frequent…
10 Apr 2018
In a previous blog post, I’ve shown how the programmatic MockDataProvider can be used to mock the entire JDBC API through a single functional interface: Writing the provider manually can be tedious in some cases, especially when a few static SQL strings need to be mocked and constant result sets would be OK. In that … Continue reading Mocking JDBC…
27 Feb 2018
Sounds fancy, right? But it’s a really nice and reasonable approach to doing dynamic SQL with jOOQ. This blog post is inspired by a Stack Overflow question, where a user wanted to turn a set of values into a dynamic UNION query like this: Note, both the Stack Overflow user and I are well aware … Continue reading Map Reducing…
12 Jan 2018
jOOQ’s main value proposition is obvious: Type safe embedded SQL in Java. People who actively look for such a SQL builder will inevitably stumble upon jOOQ and love it, of course. But a lot of people don’t really need a SQL builder – yet, jOOQ can still be immensely helpful in other situations, through its … Continue reading Top 5…
24 Aug 2017
One of the cooler hidden features in jOOQ is the JPADatabase, which allows for reverse engineering a pre-existing set of JPA-annotated entities to generate jOOQ code. For instance, you could write these entities here: (Just a simple example. Let’s not discuss the caveats of @ManyToMany mapping). For more info, the full example can be found … Continue reading jOOQ 3.10…
6 Feb 2017
Some databases (in particular MySQL and T-SQL databases like SQL Server and Sybase) support a very nice feature: They allow for running a “batch” of statements in a single statement. For instance, in SQL Server, you can do something like this: This is a batch of 4 statements, and it can be executed as a … Continue reading How to…
16 Jan 2017
Typesafe embedded DSLs like jOOQ are extremely powerful for dynamic SQL, because the query you’re constructing with the jOOQ DSL is a dynamic query by nature. You’re constructing a query expression tree using a convenient API (the “DSL”), even if you think your SQL statement is static. For instance: The above query looks like a … Continue reading A Functional…
10 Aug 2016
Pagination is one of those things that almost everyone gets wrong for two reasons: Here’s why. What’s wrong with pagination? Most applications blindly produce pagination like this: This is how GMail implements pagination. With my current settings, it displays 100 E-Mails at a time and also shows how many E-Mails there are in total, namely … Continue reading Why Most…
12 Apr 2016
Security is important, especially on the data access layer. Most commercial databasese allow for fine-grained privilege control using database access grants. For instance, you would be restricting access from a user to a certain set of tables (or even better: views), via GRANT statements: With this fine-grained access control, write operations on certain database objects … Continue reading Using jOOQ’s…
2 Dec 2015
A nice feature of the Java 7 try-with-resources statement and the AutoCloseable type that was introduced to work with this statement is the fact that static code analysis tools can detect resource leaks. For instance, Eclipse: When you have the above configuration and you try running the following program, you’ll get three warnings: The output … Continue reading A Subtle…
10 Mar 2015
Introduction Grails is a web framework aimed to boost development productivity. One of the main features is domain centric database schema generation. Applications built with Grails are able to update existing schema just before they start. To do this, Grails is using built-in domain mappers or migrations in more advanced cases. The goal of the … Continue reading Integrating jOOQ…
14 Nov 2014
We’ve recently encountered this interesting use-case on the jOOQ user group. How do you discover all primary keys of your schema via the jOOQ API? There are two ways: Using the generated meta data Using runtime meta data Let’s see how it works: Using the generated meta data This is straightforward. If you’re using Java … Continue reading jOOQ Tip…
4 Nov 2014
A procedural language combined with SQL can do miracles in terms of productiveness, performance and expressivity. In this article, we’ll see later on, how we can achieve the same with SQL (and PL/SQL) in Java, using jOOQ, which offers much more functionality than Oracle’s own now desupported JPublisher. But first, a little bit of history… … Continue reading Access PL/SQL…
31 Oct 2014
The jOOQ ecosystem and community is continually growing. We’re personally always thrilled to see other Open Source projects built on top of jOOQ. Today, we’re very happy to introduce you to a very interesting approach at combining REST and RDBMS by Björn Harrtell. Björn Harrtell is a swedish programmer since childhood. He is usually busy … Continue reading A RESTful…
26 Aug 2014
Introduction jOOQ is a great framework when you want to work with SQL in Java without having too much ORM in your way. At the same time, it can be integrated into many environments as it is offering you support for many database-specific features. One such database-specific feature is partitioning in PostgreSQL. Partitioning in PostgreSQL … Continue reading Integrating jOOQ…
28 Jul 2014
jOOQ implements your SQL statements as AST (Abstract Syntax Tree). This means that your SQL statement is modelled in a non-text form prior to serialising it as a textual SQL statement to your JDBC driver. One advantage of this is that you can freely manipulate this AST any way you want. This can be done … Continue reading jOOQ Tip…