One of the main features of ORMs is M as in Mapping. Libraries like jOOQ help auto-mapping flat or nested database records onto Java classes that have the same structure as the SQL result set. The following has always been possible in jOOQ, assuming PostgreSQL's INFORMATION_SCHEMA (using the generated code from the jOOQ-meta module): class … Continue reading Nesting Collections With jOOQ 3.14’s SQL/XML or SQL/JSON support
Tag: sql
Having “constant” columns in foreign keys
I was asked a very interesting question on Twitter just now: https://twitter.com/connolly_s/status/1303957373107818497 Can we have "constant" foreign key columns in (PostgreSQL) tables? Luckily, yes, we can. Using a nice standard feature that is "computed columns" or "generated columns" Sometimes, you cannot completely normalise your schema for whatever reason. There may be a case where you … Continue reading Having “constant” columns in foreign keys
Use NATURAL FULL JOIN to compare two tables in SQL
There are a few ways to compare two similar tables in SQL. Assuming PostgreSQL syntax, we might have this schema: CREATE TABLE t1 (a INT, b INT, c INT); CREATE TABLE t2 (a INT, b INT, c INT); INSERT INTO t1 VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9); INSERT INTO t2 VALUES … Continue reading Use NATURAL FULL JOIN to compare two tables in SQL
Using SQL Server FOR XML and FOR JSON Syntax on Other RDBMS With jOOQ
SQL Server supports transforming flat tabular SQL result sets into hierarchical structures by convention using the convenient FOR XML or FOR JSON syntaxes. This is really convenient and less verbose than the standard SQL/XML or SQL/JSON APIs - although the standard ones are more powerful. In this blog post, I'd like to show a few … Continue reading Using SQL Server FOR XML and FOR JSON Syntax on Other RDBMS With jOOQ
The Many Flavours of the Arcane SQL MERGE Statement
The SQL MERGE statement is a device whose mystery is only exceeded by its power. A simple example shows its full power according to standard SQL. Imagine you have a production table for product prices, and a staging table from which you want to load the latest prices. For once, I'm using the Db2 LuW … Continue reading The Many Flavours of the Arcane SQL MERGE Statement
Create Empty Optional SQL Clauses with jOOQ
When using jOOQ to create dynamic SQL statements (one of jOOQ's core value propositions), it is often necessary to add query elements conditionally, with a default "No-op" behaviour. For first time users, this default "no-op" behaviour is not always obvious as the jOOQ API is vast, and as with any vast API, there are many … Continue reading Create Empty Optional SQL Clauses with jOOQ
5 Ways to Better Understand SQL by Adding Optional Parentheses
It appears that our recent beginner SQL articles explaining SQL syntax were quite popular. These include: A Beginner’s Guide to the True Order of SQL Operations A Probably Incomplete, Comprehensive Guide to the Many Different Ways to JOIN Tables in SQL 10 Easy Steps to a Complete Understanding of SQL How SQL DISTINCT and ORDER … Continue reading 5 Ways to Better Understand SQL by Adding Optional Parentheses
SQL DISTINCT is not a function
A very common misconception I often encounter with SQL users is the idea that DISTINCT is something like a function, and that it can take parenthesised arguments. Just recently, I've seen this Stack Overflow question where the OP was looking for a way to express this in jOOQ: SELECT DISTINCT (emp.id), emp.fname, emp.name FROM employee … Continue reading SQL DISTINCT is not a function
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