atlas news
  Jooq
27  march     12h45
Managing Sensitive Data in jOOQ 3.21 Logs
lukaseder    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...
11  august     12h43
Consider using JSON arrays instead of JSON objects for serialisation
lukaseder    When implementing the awesome MULTISET operator in jOOQ, its implementation mostly relied on SQL JSON support of various RDBMS. In short, while...
27  march     13h04
When SQL Meets Lambda Expressions
lukaseder    ARRAY types are a part of the ISO IEC 9075 SQL standard. The standard specifies how to: But it is very unopinionated when it comes to function...
13  march     14h45
Think About SQL MERGE in Terms of a RIGHT JOIN
lukaseder    RIGHT JOIN is an esoteric feature in the SQL language, and hardly ever seen in the real world, because almost every RIGHT JOIN can just be expressed...
28  february     10h39
Resisting the Urge to Document Everything Everywhere
lukaseder    Every product manager knows this situation: This is such a common pattern, and while it’s perfectly understandable for such a user to request this,...
20  february     10h27
jOOQ 3.20 released with ClickHouse, Databricks, and much more DuckDB support, new modules, Oracle type hierarchies, more spatial support, decfloat and synonym support, hidden columns, Scala 3, Kotlin 2, and much more
lukaseder    New dialects: jOOQ 3.20 ships with 2 new experimental dialects: ClickHouse is a fast-moving SQL dialect with a historic vendor-specific syntax that...
03  june     12h17
Emulating SQL FILTER with Oracle JSON Aggregate Functions
lukaseder    A cool standard SQL:2003 feature is the aggregate FILTER clause, which is supported natively by at least these RDBMS: The following aggregate...
01  march     09h55
Getting Top 1 Values Per Group in Oracle
lukaseder    I’ve blogged about generic ways of getting top 1 or top n per category queries before on this blog. An Oracle specific version in that post used the...
16  february     14h16
An Efficient Way to Check for Existence of Multiple Values in SQL
lukaseder    In a previous blog post, we’ve advertised the use of SQL EXISTS rather than COUNT( ) to check for existence of a value in SQL. I.e. to check if in...
10  january     14h19
A Hidden Benefit of Implicit Joins: Join Elimination
lukaseder    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...
28  december     14h35
jOOQ 3.19’s new Explicit and Implicit to-many path joins
lukaseder    jOOQ 3.19 finally delivers on a set of features that will greatly simplify your queries further, after jOOQ 3.11 introduced implicit to-one joins...
20  december     14h20
Workaround for MySQL’s can’t specify target table for update in FROM clause Error
lukaseder    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...
15  december     16h30
jOOQ 3.19.0 Released with DuckDB, Trino, Oracle 23c support, join path improvements, an official gradle plugin, commercial maven repositories, policies, UDT paths, trigger meta data, hierarchies, and much more
lukaseder    New Dialects It’s been a few releases since we’ve added support for new dialects, but finally some very interesting RDBMS of increasing popularity...
13  december     11h25
Maven Coordinates of the most popular JDBC Drivers
lukaseder    Do you need to add a JDBC driver to your application, and don’t know its Maven coordinates? This blog post lists the most popular drivers from the...
06  december     13h41
To DAO or not to DAO
lukaseder    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...
01  december     11h12
JDBC Connection URLs of the Most Popular RDBMS
lukaseder    Need to connect to your RDBMS with JDBC and don’t have the JDBC connection URL or driver name at hand? No problem, just look up your RDBMS below:
28  june     15h54
How to Generate Package Private Code with jOOQ’s Code Generator
lukaseder    Java’s package private visibility is an underrated feature. When you omit any visibility modifier in Java, then the default (for most objects) is...
25  april     12h16
How to Pass a Table Valued Parameter to a T-SQL Function with jOOQ
lukaseder    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...
24  march     15h45
How to Turn a List of Flat Elements into a Hierarchy in Java, SQL, or jOOQ
lukaseder    Occasionally, you want to write a SQL query and fetch a hierarchy of data, whose flat representation may look like this: The result might be: id...
08  march     16h34
3.18.0 Release with Support for more Diagnostics, SQL JSON, Oracle Associative Arrays, Multi dimensional Arrays, R2DBC 1.0
lukaseder    DiagnosticsListener improvements A lot of additional diagnostics have been added, including the automated detection of pattern replacements, helping...
02  march     11h18
How to use jOOQ’s Converters with UNION Operations
lukaseder    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...
24  february     09h18
How to Write a Derived Table in jOOQ
lukaseder    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...
06  february     07h51
The Performance Impact of SQL’s FILTER Clause
lukaseder    I’ve found an interesting question on Twitter, recently. Is there any performance impact of using FILTER in SQL (PostgreSQL, specifically), or is it...
20  january     11h07
Emulating Window Functions in MySQL 5.7
lukaseder    One of MySQL 8’s biggest improvements is the support of window functions. As I always said in conferences, there’s SQL before window functions and...
18  january     11h46
Why You Should Execute jOOQ Queries With jOOQ
lukaseder    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....
17  january     10h12
jOOQ’s R2DBC LoggingConnection to log all SQL statements
lukaseder    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...
08  december     13h53
When to Use jOOQ and When to Use Native SQL
lukaseder    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...
04  november     08h56
LATERAL is Your Friend to Create Local Column Variables in SQL
lukaseder    The standard SQL WITH clause has been tremendously helpful in structuring SQL queries. Instead of nesting everything in unreadable derived tables...
21  october     14h35
Calling Procedures with Default Parameters using JDBC or jOOQ
lukaseder    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...
13  september     07h29
Using jOOQ’s Implicit Join From Within the JOIN .. ON Clause
lukaseder    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...
06  september     12h30
A Brief Overview over the Most Common jOOQ Types
lukaseder    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...
01  september     08h25
How to Plot an ASCII Bar Chart with SQL
lukaseder    No need for expensive Tableau subscriptions. Ditch Microsoft Excel. Just use native PostgreSQL to quickly visualise your data Here’s an idea I had...
30  august     14h48
The Second Best Way to Fetch a Spring Data JPA DTO Projection
lukaseder    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...
    06h56
Cannot resolve symbol VERSION 3 17’ in jOOQ generated code
lukaseder    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...
25  august     08h07
jOOQ 3.17 Supports Implicit Join also in DML
lukaseder    Since jOOQ 3.11, implicit joins have been supported. An implicit join is a JOIN (mostly a LEFT JOIN) that is generated implicitly because of the...
24  august     09h09
A Condition is a Field
lukaseder    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...
23  august     15h02
The Many Ways to Return Data From SQL DML
lukaseder    Probably the hardest thing to standardise in SQL is RETURNING data from DML statements. In this article, we’ll look at various ways of doing that...
22  august     12h36
How to Integration Test Stored Procedures with jOOQ
lukaseder    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...
19  august     07h25
Using H2 as a Test Database Product with jOOQ
lukaseder    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...
28  july     13h11
The Best Way to Call Stored Procedures from Java: With jOOQ
lukaseder    jOOQ is mainly known for its powerful type safe, embedded, dynamic SQL capabilities that are made available through code generation. However, a...
30  june     14h46
Create Dynamic Views with jOOQ 3.17’s new Virtual Client Side Computed Columns
lukaseder    One of jOOQ 3.17 s coolest new features are client side computed columns. jOOQ 3.16 already added support for server side computed columns, which...
22  june     15h15
3.17.0 Release with Computed Columns, Audit Columns, Pattern Matching, Reactive Transactions and Kotlin Coroutine Support
lukaseder    This release contiues the work from previous releases around more sophisticated SQL transformation capabilities, including: Client side computed...
10  june     14h32
How to Filter a SQL Nested Collection by a Value
lukaseder    I stumbled upon a very interesting question on Stack Overflow about how to use jOOQ’s MULTISET operator to nest a collection, and then filter the...
09  june     10h15
The Performance of Various To-Many Nesting Algorithms
lukaseder    It’s been a while since jOOQ 3.15 has been released with its revolutionary standard SQL MULTISET emulation feature. A thing that has been long...
31  may     08h39
Changing SELECT .. FROM Into FROM .. SELECT Does Not Fix SQL
lukaseder    Every now and then, I see folks lament the SQL syntax’s peculiar disconnect between the lexical order of operations (SELECT .. FROM) the logical...
19  may     07h29
The Many Different Ways to Fetch Data in jOOQ
lukaseder    The jOOQ API is all about convenience, and as such, an important operation (the most important one?) like fetch() must come with convenience, too....
11  may     08h55
Setting the JDBC Statement.setFetchSize() to 1 for Single Row Queries
lukaseder    An interesting hint by Vladimir Sitnikov has made me think about a new benchmark for jOOQ: The benchmark should check whether single row queries...
09  may     10h06
How to Typesafely Map a Nested SQL Collection into a Nested Java Map with jOOQ
lukaseder    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...
04  may     07h18
A Quick and Dirty Way to Concatenate Two Vaguely Related Tables in SQL
lukaseder    Every now and then I run across a use case for the arcane NATURAL JOIN SQL operator, and I’m even more delighted when I can make that a NATURAL FULL...
28  april     15h30
Nested Transactions in jOOQ
lukaseder    Since jOOQ 3.4, we have an API that simplifies transactional logic on top of JDBC in jOOQ, and starting from jOOQ 3.17 and #13502, an equivalent API...