THE COMPANY INDEX TRACKED BLOG
Ideas, decisions, and lessons from the team.
blog.jooq.org (opens on the source site)XANTI JOIN is a very useful operator from relational algebra. Regrettably, only few dialects support it in terms of SQL syntax, as we’ve written earlier. In jOOQ, you can write it as follows: If your RDBMS supports this natively (e.g. ClickHouse, Databricks), then it is rendered as such. Otherwise, jOOQ will translate this to: But … Continue reading Simplifying ANTI JOIN with jOOQ Syntax →
Read at the sourceSome SQL operators are as esoteric as they’re powerful. One of the oldest operator that you’ve likely hardly ever used in real world applications is NATURAL JOIN which is the default in relational algebra. We’ve covered a funky use-case for NATURAL JOIN earlier on this blog. The main reason why it’s not very useful is … Continue reading Why JOIN USING Can Lead to Errors in SQL →
Read at the sourceOne of jOOQ’s most popular feature is the out-of-the-box debug logging experience. jOOQ developers find this feature very useful when developing their applications. Assuming you run a jOOQ query and configure your logger to print DEBUG log output: When this query is executed, your log output might contain something like this: Executing query : select … Continue reading Managing Sensitive Data in jOOQ 3.21+ Logs →
Read at the sourceWhen implementing the awesome MULTISET operator in jOOQ, its implementation mostly relied on SQL/JSON support of various RDBMS. In short, while standard SQL supports nested collections via ARRAY or MULTISET operators like this: This is poorly supported in most RDBMS, so jOOQ emulates it using SQL/JSON as follows (or similar): Wait a second. A JSON … Continue reading Consider using JSON arrays instead of JSON objects for serialisation →
Read at the sourceARRAY types are a part of the ISO/IEC 9075 SQL standard. The standard specifies how to: But it is very unopinionated when it comes to function support. The ISO/IEC 9075-2:2023(E) 6.47 specifies concatenation of arrays, whereas the 6.48 section lists a not extremely useful TRIM_ARRAY function, exclusively (using which … Continue reading When SQL Meets Lambda Expressions →
Read at the sourceRIGHT JOIN is an esoteric feature in the SQL language, and hardly ever seen in the real world, because almost every RIGHT JOIN can just be expressed as an equivalent LEFT JOIN. The following two statements are equivalent: It’s not unreasonable to expect these two statements to produce the same execution plan on most RDBMS, … Continue reading Think About SQL MERGE in Terms of a RIGHT JOIN →
Read at the sourceEvery product manager knows this situation: This is such a common pattern, and while it’s perfectly understandable for such a user to request this, it is so terribly wrong to give in to this user’s request. Why is it wrong? The features are unrelated Most of the time, the two features X1 and X2 are … Continue reading Resisting the Urge to Document Everything Everywhere →
Read at the sourceA cool standard SQL:2003 feature is the aggregate FILTER clause, which is supported natively by at least these RDBMS: The following aggregate function computes the number of rows per group which satifsy the FILTER clause: This is useful for pivot style queries, where multiple aggregate values are computed in one go. For most basic types … Continue reading Emulating SQL FILTER with Oracle JSON Aggregate Functions →
Read at the sourceI’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: This is a bit difficult to read when you see it for the first time. Think of it as a complicated way to say … Continue reading Getting Top 1 Values Per Group in Oracle →
Read at the sourceIn 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: Do this: (Depending on your dialect you may require a FROM DUAL clause, … Continue reading An Efficient Way to Check for Existence of Multiple Values in SQL →
Read at the sourceOne 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 →
Read at the sourcejOOQ 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: What are these features? Many ORMs (e.g. JPA, Doctrine, jOOQ 3.11 and others) support “path joins” (they may have different names for this concept). A path join is a join derived from … Continue reading jOOQ 3.19’s new Explicit and Implicit to-many path joins →
Read at the sourceIn MySQL, you cannot do this: The UPDATE statement will raise an error as follows: SQL Error [1093] [HY000]: You can’t specify target table ‘t’ for update in FROM clause People have considered this to be a bug in MySQL for ages, as most other RDBMS can do this without any issues, including MySQL clones: … Continue reading Workaround for MySQL’s “can’t specify target table for update in FROM clause” Error →
Read at the sourceDo 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 →
Read at the sourcejOOQ’s DAO API is one of jOOQ’s most controversial features. When it was first implemented, it was implemented merely: There’s a strong hint about the third bullet given how popular Spring Data’s repository “pattern” is. A lot of developers just want to quickly fetch and store data, without giving individual queries much thought. A fun … Continue reading To DAO or not to DAO →
Read at the sourceNeed 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:
Read at the sourceJava’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: In fact, a compilation unit (the .java file) can contain multiple such classes. You don’t have to create … Continue reading How to Generate Package Private Code with jOOQ’s Code Generator →
Read at the sourceMicrosoft T-SQL supports a language feature called table-valued parameter (TVP), which is a parameter of a table type that can be passed to a stored procedure or function. For example, you may write: This function takes a table-valued parameter (TVP), and produces a result set containing the cross product of the parameter table with itself. … Continue reading How to Pass a Table Valued Parameter to a T-SQL Function with jOOQ →
Read at the sourceOccasionally, you want to write a SQL query and fetch a hierarchy of data, whose flat representation may look like this: The result might be: |id |parent_id|label | |---|---------|-------------------| |1 | |C: | |2 |1 |eclipse | |3 |2 |configuration | |4 |2 |dropins | |5 |2 |features | |7 |2 |plugins | |8 |2 … Continue reading How to Turn a List of Flat Elements into a Hierarchy in Java, SQL, or jOOQ →
Read at the sourceDiagnosticsListener improvements A lot of additional diagnostics have been added, including the automated detection of pattern replacements, helping you lint your SQL queries irrespective of whether you’re using jOOQ to write your SQL, or if you’re using it as a JDBC / R2DBC proxy for an existing application. A lot of these diagnostics are available … Continue reading 3.18.0 Release with Support for more Diagnostics, SQL/JSON, Oracle Associative Arrays, Multi dimensional Arrays, R2DBC 1.0 →
Read at the source