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 Statically Override the Default Settings in jOOQ
How to Calculate a Cumulative Percentage in SQL
A fun report to write is to calculate a cumulative percentage. For example, when querying the Sakila database, we might want to calculate the percentage of our total revenue at any given date. The result might look like this: Notice the beautifully generated data. Or as raw data: payment_date |amount |percentage -------------|--------|---------- 2005-05-24 |29.92 |0.04 … Continue reading How to Calculate a Cumulative Percentage in SQL
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, … Continue reading Lesser Known jOOλ Features: Useful Collectors
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: DialectAs aggregate functionAs window functionMariaDB 10.3.3NoYesOracle 18cYesYesPostgreSQL 11YesNoSQL Server 2017NoYesTeradata 16YesNo … Continue reading How to Emulate PERCENTILE_DISC in MySQL and Other RDBMS
Calculate Percentiles to Learn About Data Set Skew in SQL
B-Tree indexes are perfect when your data is uniformly distributed. They are not really useful, when you have skewed data. I'll explain later why this is the case, but let's first learn how to detect "skew" What is skew? Skew is a term from statistics when a normal distribution is not symmetric. The example given … Continue reading Calculate Percentiles to Learn About Data Set Skew in SQL
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) … Continue reading How to Work Around ORA-38104: Columns referenced in the ON Clause cannot be updated
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. 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 … Continue reading How to Unit Test Your Annotation Processor using jOOR
How to Create a Good MCVE (Minimal Complete Verifiable Example)
Reporting a bug takes time, and trust me, every vendor appreciates your reporting of a bug! Your voice counts as many voices, for all the other customers of a product who do not want to or cannot take the time to report the same bug are numerous. So, first off, thanks for taking that time … Continue reading How to Create a Good MCVE (Minimal Complete Verifiable Example)
How to Aggregate an Archive Log’s Deltas into a Snapshot with SQL
A customer of my popular SQL training (which you should book!) has recently challenged me to optimise a hierarchical query that merges an archive log's deltas in order to obtain a snapshot of some record at a given point in time. In this article, I will reproduce their problem statement in a simplified version and … Continue reading How to Aggregate an Archive Log’s Deltas into a Snapshot with SQL
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 … Continue reading How to Use jOOQ’s UpdatableRecord for CRUD to Apply a Delta