A cool standard SQL:2003 feature is the aggregate FILTER clause, which is supported natively by at least these RDBMS: The following aggregate function computes the number of rows per group which satifsy the FILTER clause: This is useful for pivot style queries, where multiple agg … | Continue reading
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 arcane KEEP syntax: This is a bit difficult to read when you see it for the first time. Think of it as a complicated way to say … C … | Continue reading
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 the Sakila database, actors called WAHLBERG have played in any films, instead of: Do this: (Depending on your dialect you may require … | Continue reading
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 … | Continue reading
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: What are these features? Many ORMs (e.g. JPA, Doctrine, jOOQ 3.11 and others) support “path joins” (they may have different names for … | Continue reading
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, … | Continue reading
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 have joined the jOOQ family including: These dialects are available in all jOOQ editions. New dialect versions In addition to these e … | Continue reading
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 jOOQ integration tests. Look up the latest versions directly on https://central.sonatype.com/ with parameters g:groupId a:artifactId, … | Continue reading
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 da … | Continue reading
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: // BigQuery driver = “com.simba.googleb… | Continue reading
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 contai … | Continue reading
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 co … | Continue reading
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 |parent_id|label | |---|---------|-------------------| |1 | |C: | |2 |1 |eclipse | |3 |2 |configuration | |4 |2 |dropins | |5 |2 |feat … | Continue reading
DiagnosticsListener improvements A lot of additional diagnostics have been added, including the automated detection of pattern replacements, helping you lint your SQL queries irrespective of whether you’re using jOOQ to write your SQL, or if you’re using it as a JDBC / R2DBC prox … | Continue reading
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 t … | Continue reading
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 … | Continue reading
I’ve found an interesting question on Twitter, recently. Is there any performance impact of using FILTER in SQL (PostgreSQL, specifically), or is it just syntax sugar for a CASE expression in an aggregate function? As a quick reminder, FILTER is an awesome standard SQL extension … | Continue reading
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 SQL after window functions. Once you start using them, you’ll use them everywhere. Some of you poor souls are unfortunate enough to b … | Continue reading
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 … | Continue reading
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 Logg … | Continue reading
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 SQ … | Continue reading
The standard SQL WITH clause has been tremendously helpful in structuring SQL queries. Instead of nesting everything in unreadable derived tables like this: People have started moving the logic up front, just like in any other programming language, where we declare things first, … | Continue reading
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 o … | Continue reading
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 fr … | Continue reading
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 … | Continue reading
No need for expensive Tableau subscriptions. Ditch Microsoft Excel. Just use native PostgreSQL to quickly visualise your data! Here’s an idea I had for a while. As you may know, jOOQ can produce fancy charts from your jOOQ results. But that requires you use jOOQ, and you may not … | Continue reading
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 j … | Continue reading
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 … | Continue reading
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 presence of a path expression. If SQL supported the syntax natively, it would look like this: All that is is convenience for a bunch of … | Continue reading
Starting with jOOQ 3.17, the Condition type extends the Field 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 (which is a Condition in jOOQ) can be used wherever a can be used, which a … | Continue reading
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 with jOOQ, in many of jOOQ’s supported dialects, and with JDBC directly. How to do it with jOOQ Assuming the usual table from the saki … | Continue reading
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 lik … | Continue reading
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 Me … | Continue reading
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 … | Continue reading
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 many of you appreciate for various reasons. What’s a computed column? A computed column is a column that is derived (“computed”) from … | Continue reading
This release contiues the work from previous releases around more sophisticated SQL transformation capabilities, including: Client side computed columns for both read and write operations Audit columns Pattern matching SQL transformations More implicit JOIN capabilities Client si … | Continue reading
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 result by whether that nested collection contains a value. The question is jOOQ specific, but imagine, you have a query that nests col … | Continue reading
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 overdue and which I promised on twitter a few times is to run a few benchmarks comparing the performance of various approaches to nesting … | Continue reading
Every now and then, I see folks lament the SQL syntax’s peculiar disconnect between the lexical order of operations (SELECT .. FROM) the logical order of operations (FROM .. SELECT) Most recently here in a Youtube comment reply to a recent jOOQ/kotlin talk. Let’s look at why jOOQ … | Continue reading
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. … | Continue reading
An interesting hint by Vladimir Sitnikov has made me think about a new benchmark for jOOQ: The benchmark should check whether single row queries should have a JDBC Statement.setFetchSize(1) call made to them by default. The Javadoc of the method says: Gives the JDBC driver a hint … | Continue reading
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 … | Continue reading
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 JOIN. A few past blog posts on the subject include: Use NATURAL FULL JOIN to compare two tables in SQL Impress Your Coworkers With … | Continue reading
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 will also be made available on top of R2DBC, for reactive applications. As with everything jOOQ, transactions are implemented using … | Continue reading
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 d … | Continue reading
For SQL beginners, there’s a bit of an esoteric syntax named PARTITION BY, which appears all over the place in SQL. It always has a similar meaning, though in quite different contexts. The me… | Continue reading
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 databa… | Continue reading
A long standing feature request has seen little love from the jOOQ community, despite a lot of people probably wanting it. It goes by the unimpressive title Let Table extend SelectField: https://github.com/jOOQ/jOOQ/issues/4727 What does the feature mean, specifically? The awesom … | Continue reading