blog.jooq.org

Using DISTINCT ON in Non-PostgreSQL Databases

A nice little gem in PostgreSQL’s SQL syntax is the DISTINCT ON clause, which is as powerful as it is esoteric. In a previous post, we’ve blogged about some caveats to think of when DISTINCT and ORDER BY are used together. The bigger picture can be seen in our article about the logical order of operations in SQL SELECT. The PostgreSQL documentation explains it well: SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are...

blog.jooq.org

jOOQ 3.12 Released With a new Procedural Language API

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 tests, which has helped us fix a big number of not yet discovered issues, including a much better coverage of our 26 supported RDBMS dialects. We’re excited about these internal changes, as they will...

blog.jooq.org

How to Fetch All Current Identity Values in Oracle

Oracle 12c has introduced the useful SQL standard IDENTITY feature, which is essentially just syntax sugar for binding a sequence to a column default. We can use it like this: create table t1 (col1 number generated always as identity); create table t2 (col2 number generated always as identity); insert into t1 values (default); insert into t1 values (default); insert into t1 values (default); insert into t2 values (default); select * from t1; select * from t2; Which produces COL1 ---- 1 2 3 COL2 ---- 1 For unit testing against...

blog.jooq.org

How to Use jOOQ’s Commercial Distributions with Spring Boot

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 the jOOQ Open Source Edition, just go to https://start.spring.io, add the jOOQ dependency, and start working! It is a bit different when you want to work with the commercial distributions of jOOQ, for two reasons: They are not on Maven...

blog.jooq.org

Using IGNORE NULLS With SQL Window Functions to Fill Gaps

I found a very interesting SQL question on Twitter recently: Hi @sfonplsql we have some scenario, Let us 01Jan Mkt Value 100, 02Jan 120, next entry available 25th Jan 125, from 3rd Jan 24 Jan, our value should be 120. How to arrive ? Thanks @oraclebase — Vikki (@vikkiarul) April 23, 2019 Rephrasing the question: We have a set of sparse data points: +------------+-------+ | VALUE_DATE | VALUE | +------------+-------+ | 2019-01-01 | 100 | | 2019-01-02 | 120 | | 2019-01-05 | 125 | | 2019-01-06 | 128 | | 2019-01-10 | 130...

blog.jooq.org

Calling an Oracle Function with PL/SQL BOOLEAN Type from SQL

One of the most wanted features in the Oracle database is the BOOLEAN type. The SQL standard specified it a while ago, and RDBMS like PostgreSQL show how powerful it can be, e.g. when using the EVERY() aggregate function. The PL/SQL language already has support for boolean types. We can write: CREATE OR REPLACE FUNCTION number_to_boolean (i NUMBER) RETURN BOOLEAN IS BEGIN RETURN NOT i = 0; END number_to_boolean; / CREATE OR REPLACE FUNCTION boolean_to_number (b BOOLEAN) RETURN NUMBER IS BEGIN RETURN CASE WHEN b THEN 1 WHEN NOT b THEN...

blog.jooq.org

The Difference Between SQL’s JOIN .. ON Clause and the Where Clause

A question that is frequently occurring among my SQL training‘s participants is: What’s the difference between putting a predicate in the JOIN .. ON clause and the WHERE clause? I can definitely see how that’s confusing some people, as there seems to be no difference at first sight, when running queries like these, e.g. in Oracle. I’m using the Sakila database, as always: -- First query SELECT a.actor_id, a.first_name, a.last_name, count(fa.film_id) FROM actor a LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id WHERE fa.film_id <...

blog.jooq.org

Calculating Weighted Averages When Joining Tables in SQL

I stumbled upon a very interesting jOOQ question on Stack Overflow that required the calculation of a weighted average. Why is that. Problem description Assuming you have this database (using PostgreSQL syntax): create table transactions ( id bigint not null primary key, lines bigint not null, price numeric(18, 2) not null, profit numeric(18, 2) not null ); create table lines ( id bigint not null primary key, transaction_id bigint not null references transactions, total ...

blog.jooq.org

How to Statically Override the Default Settings in jOOQ

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 the conoisseur) Whether to run static or prepared statements Whether to turn on execute logging … and much more. Your configuration will probably include an...

blog.jooq.org

Lesser Known jOOλ Features: Useful Collectors

jOOλ is our second most popular library. It implements a set of useful extensions to the JDK’s Stream API, which are useful especially when streams are sequential only, which according to our assumptions is how most people use streams in Java. Such extensions include: // (1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, ...) Seq.of(1, 2, 3).cycle(); // tuple((1, 2, 3), (1, 2, 3)) Seq.of(1, 2, 3).duplicate(); // (1, 0, 2, 0, 3, 0, 4) Seq.of(1, 2, 3, 4).intersperse(0); // (4, 3, 2, 1) Seq.of(1, 2, 3, 4).reverse(); … and many more. Collectors But...

blog.jooq.org

How to Emulate PERCENTILE_DISC in MySQL and Other RDBMS

In my previous article, I showed what the very useful percentile functions (also known as inverse distribution functions) can be used for. Unfortunately, these functions are not ubiquitously available in SQL dialects. As of jOOQ 3.11, they are known to work in these dialects: Dialect As aggregate function As window function MariaDB 10.3.3 No Yes Oracle 18c Yes Yes PostgreSQL 11 Yes No SQL Server 2017 No Yes Teradata 16 Yes No Oracle has the most sophisticated implementation, which supports both the ordered set aggregate function,...

blog.jooq.org

How to Work Around ORA-38104: Columns referenced in the ON Clause cannot be updated

Standard SQL is a beautiful language. Vendor specific implementations, however, have their warts. In Oracle, for example, it’s not possible to update any columns in a MERGE statement, which have been referenced by the ON clause. For example: CREATE TABLE person ( id NUMBER(18) NOT NULL PRIMARY KEY, user_name VARCHAR2(50) NOT NULL UNIQUE, score NUMBER(18) ); Now, in MySQL, we can run a non-standard INSERT .. ON DUPLICATE KEY UPDATE statement like this: INSERT INTO person (id, user_name, score) VALUES (1, 'foo', 100) ON DUPLICATE KEY...

blog.jooq.org

How to Unit Test Your Annotation Processor using jOOR

Annotation processors can be useful as a hacky workaround to get some language feature into the Java language. The best example is Lombok, which enhances the Java language with quite a few annotation-based features. jOOQ also has an annotation processor that helps validate SQL syntax for: Plain SQL usage (SQL injection risk) SQL dialect support (prevent using an Oracle only feature on MySQL) You can read about it more in detail here. Unit testing annotation processors Unit testing annotation processors is a bit more tricky than using them....

blog.jooq.org

How to Use jOOQ’s UpdatableRecord for CRUD to Apply a Delta

While jOOQ is not a full fledged ORM (as in an object graph persistence framework), there is still some convenience available to avoid hand-writing boring SQL for every day CRUD. That’s the UpdatableRecord API. It has a few very useful features, including: A 1:1 mapping to the underlying table Every UpdatableRecord is mapped on a 1:1 basis to the underlying table (or view) of your database. For example, if you create a table like this (using PostgreSQL syntax): CREATE TABLE customer ( id BIGSERIAL NOT NULL PRIMARY KEY, first_name TEXT...

blog.jooq.org

Writing Custom Aggregate Functions in SQL Just Like a Java 8 Stream Collector

All SQL databases support the standard aggregate functions COUNT(), SUM(), AVG(), MIN(), MAX(). Some databases support other aggregate functions, like: EVERY() STDDEV_POP() STDDEV_SAMP() VAR_POP() VAR_SAMP() ARRAY_AGG() STRING_AGG() But what if you want to roll your own? Java 8 Stream Collector When using Java 8 streams, we can easily roll our own aggregate function (i.e. a Collector). Let’s assume we want to find the second highest value in a stream. The highest value can be obtained like this: System.out.println( Stream.of(1, 2, 3,...