跟随,学习,进步

blog.jooq.org

Java, SQL and jOOQ.

https://blog.jooq.org

Best Practices and Lessons Learned from Writing Awesome Java and SQL Code. Get some hands-on insight on what's behind developing jOOQ.

转到作者网站

How to Write a Simple, yet Extensible API

How to write a simple API is already an art on its own. I didn’t have time to write a short letter, so I wrote a long one instead. ― Mark Twain But keeping an API simple for beginners and most users, and making it extensible for power users seems even more of a challenge. … Continue reading How to Write a Simple, yet Extensible API →


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:…Read more Using IGNORE NULLS With SQL Window Functions to Fill Gaps


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: From PL/SQL, we can…Read more Calling an Oracle Function with PL/SQL BOOLEAN Type from SQL


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…Read more The Difference Between SQL’s JOIN .. ON Clause and the Where Clause


The Cost of Useless Surrogate Keys in Relationship Tables

What’s a good natural key? This is a very difficult question for most entities when you design your schema. In some rare cases, there seems to be an “obvious” candidate, such as a variety of ISO standards, including: ISO 639 language codes ISO 3166 country codes ISO 4217 currency codes But even in those cases,…Read more The Cost of Useless Surrogate Keys in Relationship Tables


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): As can be seen, this schema is slightly denormalised as the number of lines per transaction are precalculated in the transactions.lines column. This…Read more Calculating Weighted Averages When Joining Tables in SQL


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…Read more 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…Read more 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: … and many more. Collectors But that’s not the only thing…Read more 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: Dialect As aggregate function As window function MariaDB 10.3.3 No Yes…Read more 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…Read more 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: Now, in MySQL, we can run a non-standard INSERT .. ON DUPLICATE KEY UPDATE statement like this:…Read more 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. 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…Read more 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…Read more 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…Read more 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…Read more How to Use jOOQ’s UpdatableRecord for CRUD to Apply a Delta


Imperative Loop or Functional Stream Pipeline? Beware of the Performance Impact!

I like weird, yet concise language constructs and API usages Because you're evil. — Nicolai Parlog (@nipafx) October 25, 2018 Yes. I am guilty. Evil? Don’t know. But guilty. I heavily use and abuse the java.lang.Boolean type to implement three valued logic in Java: Boolean.TRUE means true (duh) Boolean.FALSE means false null can mean anything…Read more Imperative Loop or Functional Stream Pipeline? Beware of the Performance Impact!


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…Read more Writing Custom Aggregate Functions in SQL Just Like a Java 8 Stream Collector


How to Use SQL UPDATE .. RETURNING to Run DML More Efficiently

At a customer site, I recently refactored a “slow-by-slow” PL/SQL loop and turned that into an efficient set based UPDATE statement saving many lines of code and running much faster. In this blog post, I will show how that can be done. The blog post will focus on Oracle and UPDATE, but rest assured, this…Read more How to Use SQL UPDATE .. RETURNING to Run DML More Efficiently


How to Write a Multiplication Aggregate Function in SQL

Everyone knows the SQL SUM() aggregate function (and many people also know its window function variant). When querying the Sakila database, we can get the daily revenue (using PostgreSQL syntax): The result will look something like this: date |daily_revenue |cumulative_revenue -----------|--------------|------------------- 2005-05-24 |29.92 |29.92 2005-05-25 |573.63 |603.55 2005-05-26 |754.26 |1357.81 2005-05-27 |685.33 |2043.14 2005-05-28 |804.04…Read more How to Write a Multiplication Aggregate Function in SQL