I've blogged about generic ways of getting top 1 or top n per category queries before on this blog. An Oracle specific version in that post used the arcane KEEP syntax: SELECT max(actor_id) KEEP (DENSE_RANK FIRST ORDER BY c DESC, actor_id), max(first_name) KEEP (DENSE_RANK FIRST ORDER BY c DESC, actor_id), max(last_name) KEEP (DENSE_RANK FIRST ORDER … Continue reading Getting Top 1 Values Per Group in Oracle
An Efficient Way to Check for Existence of Multiple Values in SQL
In a previous blog post, we've advertised the use of SQL EXISTS rather than COUNT(*) to check for existence of a value in SQL. I.e. to check if in the Sakila database, actors called WAHLBERG have played in any films, instead of: SELECT count(*) FROM actor a JOIN film_actor fa USING (actor_id) WHERE a.last_name = … Continue reading An Efficient Way to Check for Existence of Multiple Values in SQL
A Hidden Benefit of Implicit Joins: Join Elimination
One of jOOQ's key features so far has always been to render pretty much exactly the SQL that users expect, without any surprises - unless some emulation is required to make a query work, of course. This means that while join elimination is a powerful feature of many RDBMS, it isn't part of jOOQ's feature … Continue reading A Hidden Benefit of Implicit Joins: Join Elimination
jOOQ 3.19’s new Explicit and Implicit to-many path joins
jOOQ 3.19 finally delivers on a set of features that will greatly simplify your queries further, after jOOQ 3.11 introduced implicit to-one joins: Explicit path joins To-many path joins Implicit join path correlation What are these features? Many ORMs (e.g. JPA, Doctrine, jOOQ 3.11 and others) support "path joins" (they may have different names for … Continue reading jOOQ 3.19’s new Explicit and Implicit to-many path joins
Workaround for MySQL’s “can’t specify target table for update in FROM clause” Error
In MySQL, you cannot do this: create table t (i int primary key, j int); insert into t values (1, 1); update t set j = (select max(j) from t) + 1; The UPDATE statement will raise an error as follows: SQL Error [1093] [HY000]: You can't specify target table 't' for update in FROM … Continue reading Workaround for MySQL’s “can’t specify target table for update in FROM clause” Error
jOOQ 3.19.0 Released with DuckDB, Trino, Oracle 23c support, join path improvements, an official gradle plugin, commercial maven repositories, policies, UDT paths, trigger meta data, hierarchies, and much more
New Dialects It's been a few releases since we've added support for new dialects, but finally some very interesting RDBMS of increasing popularity have joined the jOOQ family including: DuckDB (experimental support) Trino These dialects are available in all jOOQ editions. New dialect versions In addition to these entirely new dialects, big new CockroachDB and … Continue reading jOOQ 3.19.0 Released with DuckDB, Trino, Oracle 23c support, join path improvements, an official gradle plugin, commercial maven repositories, policies, UDT paths, trigger meta data, hierarchies, and much more
Maven Coordinates of the most popular JDBC Drivers
Do you need to add a JDBC driver to your application, and don't know its Maven coordinates? This blog post lists the most popular drivers from the jOOQ integration tests. Look up the latest versions directly on https://central.sonatype.com/ with parameters g:groupId a:artifactId, for example, the H2 database and driver: https://central.sonatype.com/search?q=g%3Acom.h2database+a%3Ah2 The list only includes drivers … Continue reading Maven Coordinates of the most popular JDBC Drivers
To DAO or not to DAO
jOOQ's DAO API is one of jOOQ's most controversial features. When it was first implemented, it was implemented merely: Because it was so easy to implement Because it seemed so useful for simple CRUD tasks Because that's what many developers want There's a strong hint about the third bullet given how popular Spring Data's repository … Continue reading To DAO or not to DAO
JDBC Connection URLs of the Most Popular RDBMS
Need to connect to your RDBMS with JDBC and don't have the JDBC connection URL or driver name at hand? No problem, just look up your RDBMS below: // BigQuery driver = "com.simba.googlebigquery.jdbc42.Driver"; url = "jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=<project-id>;OAuthType=0;OAuthServiceAcctEmail=<service-account>;OAuthPvtKeyPath=path-to-key.json"; // CockroachDB driver = "org.postgresql.Driver"; url = "jdbc:postgresql://<host>/<database>"; // Db2 driver = "com.ibm.db2.jcc.DB2Driver"; url = "jdbc:db2://<host>:50000/<database>"; // Derby driver … Continue reading JDBC Connection URLs of the Most Popular RDBMS
How to Generate Package Private Code with jOOQ’s Code Generator
Java's package private visibility is an underrated feature. When you omit any visibility modifier in Java, then the default (for most objects) is package private, i.e. the object is visible only to types in the same package: class YouDontSeeMe {} class YouDontSeeMeEither {} In fact, a compilation unit (the .java file) can contain multiple such … Continue reading How to Generate Package Private Code with jOOQ’s Code Generator