An interesting hint by Vladimir Sitnikov has made me think about a new benchmark for jOOQ: https://twitter.com/lukaseder/status/1407662449331949568 The benchmark should check whether single row queries should have a JDBC Statement.setFetchSize(1) call made to them by default. The Javadoc of the method says: Gives the JDBC driver a hint as to the number of rows that … Continue reading Setting the JDBC Statement.setFetchSize() to 1 for Single Row Queries
Tag: DB2
Vendor Agnostic, Dynamic Procedural Logic with jOOQ
One of the strengths of modern RDBMS is the capability to mix the powerful SQL language with procedural code. SQL is a 4th generation programming language (4GL), and as such, extremely well suited for querying and bulk data manipulation. Its functional-declarative nature allows for it to be optimised in highly efficient ways using cost-based optimisation, … Continue reading Vendor Agnostic, Dynamic Procedural Logic with jOOQ
jOOQ 3.15’s New Multiset Operator Will Change How You Think About SQL
This is how SQL should have been used all along. They called it The Third Manifesto, ORDBMS, or other things. Regrettably, it never really took off. Because most vendors didn't adopt it. And those who did, didn't agree on syntax. But this is about to change. Thanks to the now ubiquitous SQL/JSON support (which jOOQ … Continue reading jOOQ 3.15’s New Multiset Operator Will Change How You Think About 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
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
JOIN Elimination: An Essential Optimiser Feature for Advanced SQL Usage
The SQL language has one great advantage over procedural, object oriented, and "ordinary" functional programming languages. The fact that it is truly declarative (i.e. a 4GL / fourth generation programming language) means that a sophisticated optimiser can easily transform one SQL expression into another, equivalent SQL expression, which might be faster to execute. How does … Continue reading JOIN Elimination: An Essential Optimiser Feature for Advanced SQL Usage
jOOQ Tuesdays: Glenn Paulley Gives Insight into SQL’s History
Welcome to the jOOQ Tuesdays series. In this series, we’ll publish an article on the third Tuesday every other month where we interview someone we find exciting in our industry from a jOOQ perspective. This includes people who work with SQL, Java, Open Source, and a variety of other related topics. I'm very excited to … Continue reading jOOQ Tuesdays: Glenn Paulley Gives Insight into SQL’s History
GROUP BY ROLLUP / CUBE
Every now and then, you come across a requirement that will bring you to your SQL limits. Many of us probably give up early and calculate stuff in Java / [or your language]. Instead, it might've been so easy and fast to do with SQL. If you're working with an advanced database, such as DB2, … Continue reading GROUP BY ROLLUP / CUBE