跟随,学习,进步

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.

转到作者网站

Stop Mapping Stuff in Your Middleware. Use SQL’s XML or JSON Operators Instead

It’s been a while since I’ve ranted on this blog, but I was recently challenged by a reddit thread to write about this topic, so here goes… So, you’re writing a service that produces some JSON from your database model. What do you need? Let’s see: Read a book on DDD Read another book on … Continue reading Stop Mapping Stuff in Your Middleware. Use SQL’s XML or JSON Operators Instead →


A Guide to SQL Naming Conventions

One of Java’s big strengths, in my opinion, is the fact that most naming conventions have been established by the creators of the language. For example: Class names are in PascalCase Member names are in camelCase Constants are in SNAKE_CASE If someone does not adhere to these conventions, the resulting code quickly looks non-idiomatic. What … Continue reading A Guide to SQL Naming Conventions →


Dogfooding in Product Development

Dogfooding, or eating your own dog food, is a practice that all product developers should implement all the time. According to wikipedia: Dogfooding, occurs when an organization uses its own product. This can be a way for an organization to test its products in real-world usage. Hence dogfooding can act as quality control, and eventually … Continue reading Dogfooding in Product Development →


How to Simulate a Liquibase Migration using H2

This post is part of a new blog series about database migrations, which will cover a variety of database change management topics. In the near future, we’ll look much more into these topics, hoping to add more value to our users’ existing Flyway, Liquibase, and other integrations where the migration tools can profit a lot … Continue reading How to Simulate a Liquibase Migration using H2 →


A Quick Trick to Make a Java Stream Construction Lazy

One of the Stream APIs greatest features is its laziness. The whole pipeline is constructed lazily, stored as a set of instructions, akin to a SQL execution plan. Only when we invoke a terminal operation, the pipeline is started. It is still lazy, meaning that some operations may be short circuited. Some third party libraries … Continue reading A Quick Trick to Make a Java Stream Construction Lazy →


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: The above produces: TABLE_NAME|COLUMN_NAME|DATA_TYPE|COLUMN_TYPE| ----------|-----------|---------|-----------| t |b |tinyint |tinyint(1) | Notice that BOOL translates to a specific “type” of TINYINT, a TINYINT(1), where we might be … Continue reading How to Map MySQL’s TINYINT(1) to Boolean in jOOQ →


What’s Faster? COUNT(*) or COUNT(1)?

One of the biggest and undead myths in SQL is that COUNT(*) is faster than COUNT(1). Or was it that COUNT(1) is faster than COUNT(*)? Impossible to remember, because there’s really no reason at all why one should be faster than the other. But is the myth justified? Let’s measure! How does COUNT(…) work? But … Continue reading What’s Faster? COUNT(*) or COUNT(1)? →


Oracle’s BINARY_DOUBLE Can Be Much Faster Than NUMBER

Using the right data type for some calculation sounds like some obvious advice. There are many blogs about using temporal data types for temporal data, instead of strings. An obvious reason is data integrity and correctness. We don’t gain much in storing dates as 2019-09-10 in one record, and as Nov 10, 2019 in the … Continue reading Oracle’s BINARY_DOUBLE Can Be Much Faster Than NUMBER →


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 … Continue reading Using DISTINCT ON in Non-PostgreSQL Databases →


Quantified LIKE ANY predicates in jOOQ 3.12

Quantified comparison predicates One of SQL’s weirdes features are quantified comparison predicates. I’ve hardly ever seen these in the wild: The above example is equivalent to using the much more readable IN predicate: This equivalence is defined in the SQL standard. There are more esoteric cases that could be solved using such quantified comparison predicates … Continue reading Quantified LIKE ANY predicates in jOOQ 3.12 →


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 … Continue reading jOOQ 3.12 Released With a new Procedural Language API →


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: Which produces COL1 ---- 1 2 3 COL2 ---- 1 For unit testing against our database, we might want to know what “state” our identities … Continue reading How to Fetch All Current Identity Values in Oracle →


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 … Continue reading How to Use jOOQ’s Commercial Distributions with Spring Boot →


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