blog.jooq.org blog.jooq.org

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 ...

blog.jooq.org 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 *...

blog.jooq.org 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...

blog.jooq.org 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...

blog.jooq.org 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,...

blog.jooq.org 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...

blog.jooq.org 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...