ANTI 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…
jOOQ
https://blog.jooq.org/ · 500 posts · history since 2016 · active
7 May
4 May
Some 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…
27 Mar
One 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…
11 Aug 2025
When 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…
27 Mar 2025
ARRAY 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 <array value expression> specifies concatenation of arrays, whereas the 6.48 <array value function> section lists a not extremely useful TRIM_ARRAY function, exclusively (using which … Continue reading When SQL…
13 Mar 2025
RIGHT 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…
28 Feb 2025
Every 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…
20 Feb 2025
New dialects: jOOQ 3.20 ships with 2 new experimental dialects: ClickHouse is a fast-moving SQL dialect with a historic vendor-specific syntax that is gradually migrated to a more standards compliant alternative, which is why our support is still experimental. A lot of behaviours differ from what one would expect elsewhere, including NULL handling, which is … Continue reading jOOQ 3.20…
3 Jun 2024
A 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…
1 Mar 2024
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: 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…
16 Feb 2024
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: Do this: (Depending on your dialect you may require a FROM DUAL clause, … Continue reading An Efficient…
10 Jan 2024
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…
28 Dec 2023
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: 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…
20 Dec 2023
In 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…
15 Dec 2023
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: These dialects are available in all jOOQ editions. New dialect versions In addition to these entirely new dialects, big new CockroachDB and Oracle versions have shipped: … Continue reading jOOQ 3.19.0…
13 Dec 2023
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…
6 Dec 2023
jOOQ’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…
1 Dec 2023
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:
28 Jun 2023
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: In fact, a compilation unit (the .java file) can contain multiple such classes. You don’t have to create … Continue reading How to…
25 Apr 2023
Microsoft 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…
24 Mar 2023
Occasionally, 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…
8 Mar 2023
DiagnosticsListener 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…
2 Mar 2023
jOOQ 3.15 introduced the concept of an ad-hoc converter, a converter that is applied “ad-hoc” to a single query. It uses the same underlying mechanisms as any ordinary Converter that is attached to generated code for use in every query. An example of such an ad-hoc converter is this: While there are other ways to … Continue reading How to…
24 Feb 2023
One of the more frequent questions about jOOQ is how to write a derived table (or a CTE). The jOOQ manual shows a simple example of a derived table: In SQL: In jOOQ: And that’s pretty much it. The question usually arises from the fact that there’s a surprising lack of type safety when working … Continue reading How to…
6 Feb 2023
I’ve found an interesting question on Twitter, recently. Is there any performance impact of using FILTER in SQL (PostgreSQL, specifically), or is it just syntax sugar for a CASE expression in an aggregate function? As a quick reminder, FILTER is an awesome standard SQL extension to filter out values before aggregating them in SQL. This … Continue reading The Performance…
20 Jan 2023
One of MySQL 8’s biggest improvements is the support of window functions. As I always said in conferences, there’s SQL before window functions and SQL after window functions. Once you start using them, you’ll use them everywhere. Some of you poor souls are unfortunate enough to be stuck on MySQL 5.7, either of your own … Continue reading Emulating Window…
18 Jan 2023
Previously on this blog, I’ve written a post explaining why you should use jOOQ’s code generator, despite the possibility of using jOOQ without it. In a similar fashion, as I’ve answered numerous jOOQ questions on Stack Overflow, where someone used jOOQ to build a query, but then executed it elsewhere, including on: jOOQ itself isn’t … Continue reading Why You…
17 Jan 2023
jOOQ already has a LoggingConnection (see also the manual), which acts as a JDBC proxy Connection to log all SQL statements that are executed by any JDBC client (including Hibernate, MyBatis, JdbcTemplate, native JDBC, etc.). Starting from jOOQ 3.18.0, 3.17.7, and 3.16.13, a LoggingConnection is now also available for R2DBC clients to log all reactive … Continue reading jOOQ’s R2DBC…
8 Dec 2022
A frequently encountered doubt people have when using jOOQ is to decide when a “complex” query should be written using jOOQ API vs. when it should be implemented using native SQL. The jOOQ manual is full of side by side examples of the same query, e.g. Using jOOQ: Using native SQL: In the native SQL … Continue reading When to…
4 Nov 2022
The standard SQL WITH clause has been tremendously helpful in structuring SQL queries. Instead of nesting everything in unreadable derived tables like this: People have started moving the logic up front, just like in any other programming language, where we declare things first, lexically, then use them: Both queries will produce the 5 actors with … Continue reading LATERAL is…
21 Oct 2022
Using jOOQ’s code generator to call stored procedures is a popular reason to use jOOQ. For example, when you have a procedure like the following Oracle PL/SQL procedure: jOOQ will generate code for you to call very simply, like this: This will execute the following, taking care of binding all IN and OUT parameters for … Continue reading Calling Procedures…
13 Sept 2022
Starting with jOOQ 3.11, type safe implicit JOIN have been made available, and they’ve been enhanced to be supported also in DML statements in jOOQ 3.17. Today, I’d like to focus on a somewhat weird but really powerful use-case for implicit JOIN, when joining additional tables from within an explicit JOIN‘s ON clause. The use … Continue reading Using jOOQ’s…
6 Sept 2022
For new users working with jOOQ for the first time, the number of types in the jOOQ API can be overwhelming. The SQL language doesn’t have many such “visible” types, although if you think about SQL the way jOOQ does, then they’re there just the same, but hidden from users via an English style syntax. … Continue reading A Brief…
1 Sept 2022
No need for expensive Tableau subscriptions. Ditch Microsoft Excel. Just use native PostgreSQL to quickly visualise your data! Here’s an idea I had for a while. As you may know, jOOQ can produce fancy charts from your jOOQ results. But that requires you use jOOQ, and you may not be using jOOQ, because you’re not … Continue reading How to…
30 Aug 2022
I’ve just stumbled upon this great post by Vlad Mihalcea, titled The Best Way to Fetch a Spring Data JPA DTO Projection. It got some nice traction on reddit, too. This is such a nice use-case and apt solution, I wanted to quickly show the second best way of doing the same, with jOOQ this … Continue reading The Second…
Starting with jOOQ 3.16 and #12601, there may be a compilation error with a message like this in your jOOQ generated code: [ERROR] …/DefaultCatalog.java:[53,73] cannot find symbol[ERROR] symbol: variable VERSION_3_17[ERROR] location: class org.jooq.Constants Typically, this error is mixed with other compilation errors in generated code. Its purpose is to help troubleshoot these other compilation errors. … Continue reading Cannot resolve…
25 Aug 2022
Since jOOQ 3.11, implicit joins have been supported. An implicit join is a JOIN (mostly a LEFT JOIN) that is generated implicitly because of the presence of a path expression. If SQL supported the syntax natively, it would look like this: All that is is convenience for a bunch of explicitly written LEFT JOIN expressions: … Continue reading jOOQ 3.17…
24 Aug 2022
Starting with jOOQ 3.17, the Condition type extends the Field<Boolean> type. Because, that’s what the SQL standard thinks it is, in sorts: The exact definition contains intermediate rules, but you get the idea. A <predicate> (which is a Condition in jOOQ) can be used wherever a <boolean value expression> can be used, which again can … Continue reading A Condition…
23 Aug 2022
Probably the hardest thing to standardise in SQL is RETURNING data from DML statements. In this article, we’ll look at various ways of doing that with jOOQ, in many of jOOQ’s supported dialects, and with JDBC directly. How to do it with jOOQ Assuming the usual table from the sakila database: jOOQ took syntactic inspiration … Continue reading The Many…
22 Aug 2022
When you write stored procedures and functions in your database, you want to ensure their correctness, just like with your Java code. In Java, this is done with unit tests, typically with JUnit. For example, if you have the following code in Java: Then, you might write a test like this: But how do we … Continue reading How to…
19 Aug 2022
The H2 database is an immensely popular in-memory database product mostly used by Java developers for testing. If you check out the DB-Engines ranking, it ranks 50th, which is quite impressive, as this rank outperforms products like: CockroachDB Ignite Single Store (previously MemSQL) Interbase (which was forked as Firebird) Ingres (which is a predecessor to … Continue reading Using H2…
28 Jul 2022
jOOQ is mainly known for its powerful type safe, embedded, dynamic SQL capabilities that are made available through code generation. However, a secondary use case of code generation is to use it for stored procedures (possibly exclusively for stored procedures). Stored procedures are powerful ways of moving complex data processing logic to the server. This … Continue reading The Best…
30 Jun 2022
One of jOOQ 3.17‘s coolest new features are client side computed columns. jOOQ 3.16 already added support for server side computed columns, which many of you appreciate for various reasons. What’s a computed column? A computed column is a column that is derived (“computed”) from an expression. It cannot be written to. It works like … Continue reading Create Dynamic…
22 Jun 2022
This release contiues the work from previous releases around more sophisticated SQL transformation capabilities, including: Client side computed columns for both read and write operations Audit columns Pattern matching SQL transformations More implicit JOIN capabilities Client side computed columns A ground breaking new core feature available in all commercial distributions isthe new client side computed … Continue reading 3.17.0 Release…
10 Jun 2022
I stumbled upon a very interesting question on Stack Overflow about how to use jOOQ’s MULTISET operator to nest a collection, and then filter the result by whether that nested collection contains a value. The question is jOOQ specific, but imagine, you have a query that nests collections using JSON in PostgreSQL. Assuming, as always, … Continue reading How to…
9 Jun 2022
It’s been a while since jOOQ 3.15 has been released with its revolutionary standard SQL MULTISET emulation feature. A thing that has been long overdue and which I promised on twitter a few times is to run a few benchmarks comparing the performance of various approaches to nesting to-many relationships with jOOQ. This article will … Continue reading The Performance…
31 May 2022
Every now and then, I see folks lament the SQL syntax’s peculiar disconnect between the lexical order of operations (SELECT .. FROM) the logical order of operations (FROM .. SELECT) Most recently here in a Youtube comment reply to a recent jOOQ/kotlin talk. Let’s look at why jOOQ didn’t fall into this trap of trying … Continue reading Changing SELECT…
19 May 2022
The jOOQ API is all about convenience, and as such, an important operation (the most important one?) like fetch() must come with convenience, too. The default way to fetch data is this: It fetches the entire result set into memory and closes the underlying JDBC resources eagerly. But what other options do we have? Iterable … Continue reading The Many…
11 May 2022
An interesting hint by Vladimir Sitnikov has made me think about a new benchmark for jOOQ: 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 should … Continue reading Setting the…
9 May 2022
A really cool, recent question on Stack Overflow was about how to map a nested collection into a Java Map with jOOQ. In the past, I’ve blogged about the powerful MULTISET operator many times, which allows for nesting collections in jOOQ. This time, instead of nesting data into a List<UserType>, why not nest it in … Continue reading How to…
4 May 2022
Every now and then I run across a use case for the arcane NATURAL JOIN SQL operator, and I’m even more delighted when I can make that a NATURAL FULL JOIN. A few past blog posts on the subject include: Use NATURAL FULL JOIN to compare two tables in SQL Impress Your Coworkers With the … Continue reading A Quick…
28 Apr 2022
Since jOOQ 3.4, we have an API that simplifies transactional logic on top of JDBC in jOOQ, and starting from jOOQ 3.17 and #13502, an equivalent API will also be made available on top of R2DBC, for reactive applications. As with everything jOOQ, transactions are implemented using explicit, API based logic. The implicit logic implemented … Continue reading Nested Transactions…
1 Mar 2022
A lot of RDBMS support standard SQL sequences of some form. The standard SQL syntax to create a sequence is: The following is how you could fetch a value from this sequence, using jOOQ, assuming you’re using the code generator: The sequence expression translates to a variety of dialects: You can also embed the S.nextval() … Continue reading How to…
24 Feb 2022
For SQL beginners, there’s a bit of an esoteric syntax named PARTITION BY, which appears all over the place in SQL. It always has a similar meaning, though in quite different contexts. The meaning is similar to that of GROUP BY, namely to group/partition data sets by some grouping/partitioning criteria. For example, when querying the … Continue reading Various Meanings…
22 Feb 2022
Questions that might be a bit more difficult to solve using ordinary SQL are questions of the kind: What films have the same actors as a given film X? As always, we’re using the sakila database for this example. What would be a possible way to solve this with SQL (for example, PostgreSQL, to be … Continue reading Use MULTISET…
21 Feb 2022
A long standing feature request has seen little love from the jOOQ community, despite a lot of people probably wanting it. It goes by the unimpressive title Let Table<R> extend SelectField<R>: https://github.com/jOOQ/jOOQ/issues/4727 What does the feature mean, specifically? The awesome PostgreSQL Let’s have a look at a really cool PostgreSQL feature. In PostgreSQL, it is … Continue reading Projecting Type…
14 Feb 2022
A tidal wave is rippling through the Java ecosystem. It is the renaming of javax to jakarta package names. Now, while we’ve all been whining and complaining and shaking our heads due the clash between corporate legal and engineering interests, eventually it’s time to move on and learn what this means specifically, for jOOQ. jOOQ … Continue reading jOOQ 3.16…
8 Feb 2022
I’ve recently stumbled upon this interesting Stack Overflow question about Hibernate’s popular MultipleBagFetchException. The question is super popular, and the answers are plenty. The various limitations are discussed throughout the question, it all boils down to a simple fact: Joins are the wrong tool to nest collections. Given a schema like the Sakila database: There … Continue reading No More…
4 Feb 2022
If you’re running on PostgreSQL, you could try the following cool query: What does it print (after a while)? It prints e (almost). Here are some sample results: 2.7169115477960698 2.7164145522690296 2.7172065451410937 2.7170815462660836 Not perfect, sure, here’s a better approximation written in SQL: Producing: 2.718281828459045 Close enough… How does it work? It’s a cool approximation that … Continue reading Approximating e…
2 Feb 2022
Starting from jOOQ 3.16, we’re investing a lot into opening up our internal query object model (QOM) as a public API. This is mainly useful for people who use jOOQ’s parser and wish to access the parsed expression tree, or to transform SQL, e.g. to implement row level security in jOOQ. But occasionally, even with … Continue reading Traversing jOOQ…
28 Jan 2022
A while ago, jOOQ has added the org.jetbrains:annotations dependency to the jOOQ API, in order to annotate return types with nullability information. For example, the entire DSL is non-nullable: It makes sense to give this guarantee especially to kotlin users, as they can get rid of some of the more complex types involving things like … Continue reading Detect Accidental…
14 Jan 2022
I recently stumbled upon a standard SQL feature that was implemented, to my surprise, in HSQLDB. The keyword is CORRESPONDING, and it can be used with all set operations, including UNION, INTERSECT, and EXCEPT. Let’s look at the sakila database. It has 3 tables with people in it: Similar, but not the same. What if … Continue reading A Rarely…
11 Jan 2022
N+1 queries are a popular problem in many applications that run SQL queries. The problem can be described easily as follows: 1 query fetching a parent value is run N queries fetching each individual child values are run This problem isn’t limited to SQL, it can happen with any poorly designed API that does not … Continue reading Using jOOQ’s…
7 Jan 2022
One of the coolest things about using and making jOOQ is that we get to discover the best extensions to the standard SQL language by vendors, and add support for those clauses in jOOQ via emulations. One of these syntaxes is BigQuery’s * EXCEPT syntax. Everyone who ever wrote ad-hoc SQL queries would have liked … Continue reading The Useful…
5 Jan 2022
This release tackles two long standing and complex feature requests that usershave asked us to offer for a long time: a public API for manipulating jOOQ’squery object model (QOM), and spatial support. New Query Object Model (QOM) Every jOOQ query is modeled as an expression tree constructed via our intuitiveDSL. For some use-cases there exist … Continue reading 3.16.0 Release…
16 Dec 2021
Starting from Spring Boot 2.5, there’s a handy new callback that you can implement, called DefaultConfigurationCustomizer, where the word DefaultConfiguration corresponds to jOOQ’s DefaultConfiguration. You can simply create a class like this in your project: The above callback receives the DefaultConfiguration at its initialisation stage, during which you can still safely mutate it to change … Continue reading How to…
9 Dec 2021
In classic SQL (i.e. before jOOQ’s awesome MULTISET operator), nested collections were fetched using ordinary (outer) joins. An example of such a query would be a query running against the sakila database to fetch actors and their films. Using jOOQ: The result from the jOOQ debug log would look something like this: +--------+----------+---------+-------+---------------------+ |actor_id|first_name|last_name|film_id|title | … Continue reading Using JDK…
6 Dec 2021
I’m answering many jOOQ questions on Stack Overflow, and a lot of times. The problem has the same cause: People not using jOOQ’s code generator. The main reason people seem not to be using it, is because it takes some extra time to set up, but as with anything well designed, the initial investment will … Continue reading Why You…
15 Nov 2021
The upcoming jOOQ 3.16 will finally offer support for the various RDBMS GIS extensions via issue #982. This is great news per se, and will be covered in a future blog post, when the integration is ready. This post here is about something else. Adding support for such a feature is a great source of … Continue reading Fun with…
5 Nov 2021
I’ve recently discovered a pleasant new addition to PostgreSQL 14, the new enable_memoize flag that improves the performance of some nested loop joins where statistics hint at this being appropriate. I mean, who can resist this temptation: Improving query speed by 1000x hints at something very suboptimal having been going on before, and a tool … Continue reading PostgreSQL 14’s…
1 Nov 2021
This fun fact has been on my mind for a while, and a recent reddit thread about “Smuggling Checked Exceptions with Sealed Interfaces” made me write this post here. Namely, Java had union types before it was cool! (If you squint hard). What are union types? Ceylon is an underrated JVM language that never really … Continue reading Java’s Checked…
29 Oct 2021
The SQL standard knows an interesting feature where you can project any functional dependencies of a primary (or unique) key that is listed in the GROUP BY clause without having to add that functional dependency to the GROUP BY clause explicitly. What does this mean? Consider this simple schema: In order to count the number … Continue reading Functional Dependencies…
22 Oct 2021
Java 16 includes an improvement that makes the language a bit more regular via JEP 395. The JEP says: Static members of inner classes It is currently specified to be a compile-time error if an inner class declares a member that is explicitly or implicitly static, unless the member is a constant variable. This means … Continue reading Write C-Style…
19 Oct 2021
jOOQ’s parser can’t parse every possible SQL syntax. Try this random PostgreSQL syntax: And the jOOQ parser will complain: DOMAIN, INDEX, SCHEMA, SEQUENCE, SESSION, TABLE, TYPE, or VIEW expected: [1:7] ALTER [*]SYSTEM RESET ALL That’s perfectly fine. The goal of the jOOQ parser isn’t to understand all vendor specific syntax. The goal is to offer … Continue reading The jOOQ…
7 Oct 2021
Don’t you hate how you have to wrap checked exception throwing code in static initialisers? E.g. you cannot write this in Java: There’s an unhandled ClassNotFoundException, and you can’t catch / rethrow it simply. A static initialiser is needed: Yuck. Luckily, one of jOOλ’s lesser known features is the Sneaky class, which contains a bunch … Continue reading Use jOOλ’s…
27 Aug 2021
Database first is at the core of jOOQ’s design. jOOQ has been made primarily for classic systems the database is always there and always has been and will never leave. This is because we think “data have mass” This not only translates to moving logic closer to the data (see our previous posts about the … Continue reading Using Testcontainers…
26 Aug 2021
If your legacy JPA application is using occasional native queries or Hibernate @Formula or Spring Data @Query annotation with vendor specific native SQL embedded in it, you can use jOOQ’s parsing connection and parsing data source to translate between dialects, without having to go all in on your jOOQ adoption – though I think it’s … Continue reading Using jOOQ…
25 Aug 2021
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,…
23 Aug 2021
MySQL’s JDBC connector has a security feature called allowMultiQueries, which defaults to false. When turned off, it prevents using a useful, but potentially dangerous feature in MySQL via JDBC: By default, the above produces a syntax error: Exception in thread "main" java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds … Continue reading MySQL’s allowMultiQueries…
20 Aug 2021
jOOQ has been around for a while – since around 2009 as a publicly available library, and since 2013 as a commercially licensed product. A lot of things have happened in 12 years. Here are 10 things that you maybe didn’t know about jOOQ. 1. eq, ne, gt, ge, lt, le are inspired by XSLT … Continue reading 10 Things…
19 Aug 2021
A very little known feature in jOOQ is the Formattable.formatChart() capability, which allows for formatting any jOOQ result as an ASCII chart. This can be useful for quick plotting of results in your console application. Assuming you have a result set of this form (which is what you’re getting when you call result.format() or just … Continue reading Formatting ASCII…
27 Jul 2021
It’s been almost 1 year now since jOOQ 3.14 was released in October 19, 2020 with SQL/JSON (and SQL/XML) support. Half a year later, we’ve released jOOQ 3.15 with MULTISET support, which builds on top of these features to offer type-safe nested collections, the way every ORDBMS should implement them. Building (dogfooding) on top of … Continue reading Standard SQL/JSON…
20 Jul 2021
jOOQ 3.15 shipped with a ton of new features, the most important ones being: MULTISET support (type safe, nested collections) Reactive SQL support via R2DBC A very useful, lesser known new feature is “ad-hoc data type conversion”. Data type converters and bindings have been around in jOOQ for a long time. Their goal is to … Continue reading Ad-hoc Data…
15 Jul 2021
One of the biggest new features of the recently released jOOQ 3.15 is its new support for reactive querying via R2DBC. This has been a highly popular feature request, and we finally delivered on it. You can continue using jOOQ the way you were used to, providing you with type safe, embedded SQL in Java, … Continue reading Reactive SQL…
6 Jul 2021
R2DBC What a lot of users have been waiting for: jOOQ 3.15 is reactive, thanks to the new native R2DBC integration. Recent versions already implemented the reactive streams Publisher SPI, but now we’re not cheating anymore. We’re not longer blocking. Just wrap your R2DBC ConnectionFactory configured jOOQ query in a Flux (or any reactive streams … Continue reading 3.15.0 Release…
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…
24 Jun 2021
jbang is a relatively new utility that … … lets students, educators and professional developers create, edit and run self-contained source-only Java programs with unprecedented ease. Sounds exciting. How does it work with jOOQ? Very easy! Set it up like this (other installation options here): curl -Ls https://sh.jbang.dev | bash -s - app setup And … Continue reading Quickly Trying…
4 Jun 2021
One of the biggest advantages of using jOOQ is that you can change all of your complex application’s generated SQL with just a few lines of code. In this article, we’ll look into how to solve some common bind peeking issues just like that, without touching your application code, without the need to explain this … Continue reading How to…
17 May 2021
In our opinion, any Iterable<T> should offer a <R> collect(Collector<T, ?, R>) method to allow for transforming the the content to something else using standard JDK collectors, jOOλ collectors from org.jooq.lambda.Agg or your own. When using jOOQ, you don’t have to wait for the JDK to finally add these useful utilities to the Iterable API. … Continue reading Use ResultQuery.collect()…
12 May 2021
Do you need to know what RDBMS Server version you’re on, and you only have SQL at your disposal? No problem. Most RDBMS provide you with that information in some form of meta data table. Here’s how: Missing this info for your own RDBMS? Feel free to comment.
22 Apr 2021
A problem few developers are aware of is the possibility of running into “cursor cache contention” or “execution plan cache contention” problems when using IN lists in SQL. The problem that is described in lengths in previous articles, can be summarised as this. All of these are distinct SQL queries and need to be parsed … Continue reading Use IN…
30 Mar 2021
jOOQ’s DSL, like any fluent API, has one big caveat. It’s very easy to forget to call .execute(). And when you do, chances are, you’re going to be staring at your code for minutes, because everything looks perfect: Staring… staring… staring… Why is it not inserting that row? “Aaaah, not again!!” This is how it’s … Continue reading Never Again…
11 Mar 2021
When paginating results in SQL, we use standard SQL OFFSET .. FETCH or a vendor specific version of it, such as LIMIT .. OFFSET. For example: As always, we’re using the Sakila database for this example. This is rather straightforward. It will give us page 2 out of N pages, with a page size of … Continue reading Calculating Pagination…
15 Feb 2021
I’ve run across a fun little trick to simulate latency in your development environments when testing some SQL queries. Possible use-cases including to validate that backend latency won’t bring down your frontend, or that your UX is still bearable, etc. The solution is PostgreSQL and Hibernate specific, though to doesn’t have to be. Besides, it … Continue reading Simulating Latency…
10 Feb 2021
In the past years, we’ve invested a lot of effort into improving our procedural language capabilities in jOOQ. What started with a simple internal API to support the emulations of DDL clauses like these: … evolved into a full fledged API for all sorts of procedural logic executed in your database server. Anonymous blocks The … Continue reading Translating Stored…
8 Feb 2021
So, @rotnroll666 nerd sniped me again. Apparently, the Neo4j Cypher query language supports arbitrary reductions, just like any functional collection API, oh say, the JDK Stream API: SQL doesn’t have this, yet it would be very useful to be able to occasionally do that. An arbitrary reduction can be implemented “easily” in SQL. Let’s look … Continue reading Implementing a…
4 Feb 2021
Over the past 13 years, jOOQ has accrued quite some internal features, which you, the user, are not exposed to. One very interesting feature is the capability for any arbitrary jOOQ expression tree element to push a SQL fragment up to a higher level. How does it work? The jOOQ expression tree model When you … Continue reading jOOQ Internals:…
17 Nov 2020
While jOOQ is mostly being used as an internal SQL DSL for embedded, dynamic SQL in Java, where it offers the best solution on the market, jOOQ is increasingly also used for one of its secondary features: Its parser. Having been introduced in jOOQ 3.9 primarly for the purpose of being able to parse DDL … Continue reading Automatically Transform…
20 Oct 2020
jOOQ 3.14 has been released with support for SQL/XML, SQL/JSON, Kotlin code generation, embeddable types, and domain types, synthetic constraints, better MERGE support, and more SQL transformations. In this release, we’ve sorted our github issues according to user feedback and finally implemented some of the most wanted features, which include better Kotlin support, embeddable types, … Continue reading jOOQ 3.14…
13 Oct 2020
jOOQ has supported one of JPQL’s most cool features for a while now: implicit joins. Using jOOQ, you can navigate your to-one relationships in a type safe way, generating LEFT JOIN operations implicitly without the effort of having to keep thinking about join predicates, and the correct join order. Consider this Sakila database query here, … Continue reading Using jOOQ…
9 Oct 2020
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): The … Continue reading Nesting Collections…
10 Sept 2020
I was asked a very interesting question on Twitter just now: @lukaseder quick q: in pg can I have a composite foreign key where one value is a constant… or do I have to store the constant in the table? constraint foreign key (foo_id, ‘bar_subtype’) references foo(foo_id,foo_type) ? — Look! The Emperor is NAKED V⃝ … Continue reading Having “constant”…
5 Aug 2020
There are a few ways to compare two similar tables in SQL. Assuming PostgreSQL syntax, we might have this schema: It is now possible to use UNION and EXCEPT as suggested by Chris Saxon: In PostgreSQL, we can write: Notice how TABLE x is just standard SQL, and PostgreSQL, syntax sugar for SELECT * FROM … Continue reading Use NATURAL…
22 Jul 2020
I just fixed a bug. The fix required me to initialise an Object[] array with the init values for each type, instead of just null, i.e. false for boolean, 0 for int, 0.0 for double, etc. So, instead of just doing: I needed: For the subjective 8E17th time, I wrote a loop. A loop that … Continue reading Could we…
5 May 2020
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…
10 Apr 2020
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…
3 Apr 2020
One of jOOQ’s biggest strength is the fact that it is a type safe SQL API. “Type safe”, in this context, means that every object that you put in a jOOQ query has a well defined type, such as: Condition Field Table These can be used in jOOQ in a type safe way as such: … Continue reading What’s a…
6 Mar 2020
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…
5 Mar 2020
Most jOOQ users use the jOOQ DSL API, which provides compile time type safety and an easy way to write dynamic SQL. But occasionally, this DSL get in the way, because it might be In such cases, you can still benefit from jOOQ’s many secondary features, including for example its nice integration with the Stream … Continue reading Using Java…
4 Mar 2020
jOOQ supports a vast amount of SQL syntax out of the box. As such, most users will not think of resorting to string concatenation like in the old days when writing dynamic SQL with JDBC. But every now and then, a vendor specific feature is not supported by jOOQ (yes, it happens). In that case, … Continue reading Never Concatenate…
3 Mar 2020
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…
2 Mar 2020
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: Notice the parentheses around (emp.id), which look … Continue reading SQL DISTINCT…
25 Feb 2020
Deprecation notice After encountering numerous problems building the jOOQ-refaster module in various JDK versions and after receiving no feedback from the community about this feature, we have decided to remove it again in jOOQ 3.15: https://github.com/jOOQ/jOOQ/issues/10803 Starting with jOOQ 3.13, we’re offering a new module called jOOQ Refaster, which provides refaster templates for automatic API … Continue reading Use the…
14 Feb 2020
jOOQ 3.13 has been released with CockroachDB support, much more API and tooling for DDL management, and SQL:2011 temporal table support Starting with this release, we will further embrace our support for parsing, translating, executing, and now also interpreting DDL statements. The driving force is better code generation support, but in the future, also better … Continue reading jOOQ 3.13…
13 Nov 2019
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: STOP IT No, seriously. Just stop it right there! … Continue reading Stop Mapping…
29 Oct 2019
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: If someone does not adhere to these conventions, the resulting code quickly looks non-idiomatic. What about SQL? Many do not agree on the “correct” case: There seems to be … Continue reading A Guide…
25 Oct 2019
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…
30 Sept 2019
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…
27 Sept 2019
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…
19 Sept 2019
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?…
11 Sept 2019
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…
9 Sept 2019
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…
5 Sept 2019
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…
29 Aug 2019
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…
16 Jul 2019
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…
26 Jun 2019
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…
6 Jun 2019
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…
24 Apr 2019
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: … Continue reading Using IGNORE…
16 Apr 2019
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. Before you move on reading this article, note that starting with Oracle 23c, the standard BOOLEAN … Continue reading Calling an…
9 Apr 2019
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 … Continue reading The Difference…
26 Mar 2019
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, … Continue reading The Cost…
15 Mar 2019
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 … Continue reading Calculating Weighted…
14 Mar 2019
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 … Continue reading How to…
14 Feb 2019
A fun report to write is to calculate a cumulative percentage. For example, when querying the Sakila database, we might want to calculate the percentage of our total revenue at any given date. The result might look like this: Notice the beautifully generated data. Or as raw data: payment_date |amount |percentage -------------|--------|---------- 2005-05-24 |29.92 |0.04 … Continue reading How to…
11 Feb 2019
jOOλ is our second most popular library. It implements a set of useful extensions to the JDK’s Stream API, which are useful especially when streams are sequential only, which according to our assumptions is how most people use streams in Java. Such extensions include: … and many more. Collectors But that’s not the only thing … Continue reading Lesser Known…
28 Jan 2019
In my previous article, I showed what the very useful percentile functions (also known as inverse distribution functions) can be used for. Unfortunately, these functions are not ubiquitously available in SQL dialects. As of jOOQ 3.11, they are known to work in these dialects: Dialect As aggregate function As window function MariaDB 10.3.3 No Yes … Continue reading How to…
22 Jan 2019
B-Tree indexes are perfect when your data is uniformly distributed. They are not really useful, when you have skewed data. I’ll explain later why this is the case, but let’s first learn how to detect “skew” What is skew? Skew is a term from statistics when a normal distribution is not symmetric. The example given … Continue reading Calculate Percentiles…
2 Jan 2019
Standard SQL is a beautiful language. Vendor specific implementations, however, have their warts. In Oracle, for example, it’s not possible to update any columns in a MERGE statement, which have been referenced by the ON clause. For example: Now, in MySQL, we can run a non-standard INSERT .. ON DUPLICATE KEY UPDATE statement like this: … Continue reading How to…
7 Dec 2018
Annotation processors can be useful as a hacky workaround to get some language feature into the Java language. jOOQ also has an annotation processor that helps validate SQL syntax for: Plain SQL usage (SQL injection risk) SQL dialect support (prevent using an Oracle only feature on MySQL) You can read about it more in detail … Continue reading How to…
22 Nov 2018
Reporting a bug takes time, and trust me, every vendor appreciates your reporting of a bug! Your voice counts as many voices, for all the other customers of a product who do not want to or cannot take the time to report the same bug are numerous. So, first off, thanks for taking that time … Continue reading How to…
16 Nov 2018
A customer of my popular SQL training (which you should book!) has recently challenged me to optimise a hierarchical query that merges an archive log’s deltas in order to obtain a snapshot of some record at a given point in time. In this article, I will reproduce their problem statement in a simplified version and … Continue reading How to…
5 Nov 2018
While jOOQ is not a full fledged ORM (as in an object graph persistence framework), there is still some convenience available to avoid hand-writing boring SQL for every day CRUD. That’s the UpdatableRecord API. It has a few very useful features, including: A 1:1 mapping to the underlying table Every UpdatableRecord is mapped on a … Continue reading How to…
29 Oct 2018
I like weird, yet concise language constructs and API usages Yes. I am guilty. Evil? Don’t know. But guilty. I heavily use and abuse the java.lang.Boolean type to implement three valued logic in Java: I know – a lot of enterprise developers will bikeshed and cargo cult the old saying: Code is read more often … Continue reading Imperative Loop…
9 Oct 2018
All SQL databases support the standard aggregate functions COUNT(), SUM(), AVG(), MIN(), MAX(). Some databases support other aggregate functions, like: EVERY() STDDEV_POP() STDDEV_SAMP() VAR_POP() VAR_SAMP() ARRAY_AGG() STRING_AGG() But what if you want to roll your own? Java 8 Stream Collector When using Java 8 streams, we can easily roll our own aggregate function (i.e. a … Continue reading Writing Custom…
26 Sept 2018
At a customer site, I recently refactored a “slow-by-slow” PL/SQL loop and turned that into an efficient set based UPDATE statement saving many lines of code and running much faster. In this blog post, I will show how that can be done. The blog post will focus on Oracle and UPDATE, but rest assured, this … Continue reading How to…
21 Sept 2018
Everyone knows the SQL SUM() aggregate function (and many people also know its window function variant). When querying the Sakila database, we can get the daily revenue (using PostgreSQL syntax): The result will look something like this: date |daily_revenue |cumulative_revenue -----------|--------------|------------------- 2005-05-24 |29.92 |29.92 2005-05-25 |573.63 |603.55 2005-05-26 |754.26 |1357.81 2005-05-27 |685.33 |2043.14 2005-05-28 |804.04 … Continue reading How to…
20 Sept 2018
SQL is a verbose language, and one of the most verbose features are window functions. In a stack overflow question that I’ve encountered recently, someone asked to calculate the difference between the first and the last value in a time series for any given day: Input volume tstamp --------------------------- 29011 2012-12-28 09:00:00 28701 2012-12-28 10:00:00 … Continue reading How to…
7 Sept 2018
I recently stumbled upon a curious query on a customer’s productive Oracle database: Two things caught my attention: The query was executed many billions of times per month, accounting for about 0.3% of that system’s load. That’s 0.3% for something extremely silly! I don’t think that customer would ever qualify the DUAL table as SYS.DUAL, … Continue reading Beware of…
3 Sept 2018
I’ve stumbled across this fun SQL question on reddit, recently. The question was looking at a time series of data points where some events happened. For each event, we have the start time and the end time timestamp start end ----------------------------------- 2018-09-03 07:00:00 1 null 2018-09-03 08:00:00 null null 2018-09-03 09:00:00 null null 2018-09-03 10:00:00 … Continue reading Find the…
28 Aug 2018
One of the more frequent questions people have when switching from JPA to jOOQ is how to migrate from using JPA’s first level cache? Speaking of that, are there any jOOQ guides on how to replace JPA? Any patterns on how to work without EntityManager to manage in-memory state (EM#merge()), 1st level cache, etc. — … Continue reading A Frequent…
15 Aug 2018
Oracle is one of the few databases that implements the SQL standard ORDBMS extensions, which essentially allow for nested collections. Other databases that have these features to some extent are CUBRID, Informix, PostgreSQL. Oracle has two types of nested collections: The main difference at first is that a nested table can be of arbitrary size, … Continue reading How to…
13 Jul 2018
One of the things that confuse SQL users all the time is how DISTINCT and ORDER BY are related in a SQL query. The Basics Running some queries against the Sakila database, most people quickly understand: This returns results in an arbitrary order, because the database can (and might apply hashing rather than ordering to … Continue reading How SQL…
5 Jul 2018
Exciting discovery when playing around with PostgreSQL 11! New SQL standard window function clauses have been supported. If you want to play with this, you can do so very easily using docker: docker pull postgres:11 docker run --name POSTGRES11 -e POSTGRES_PASSWORD=postgres -d postgres:11 docker run -it --rm --link POSTGRES11:postgres postgres psql -h postgres -U postgres … Continue reading PostgreSQL 11’s…
27 Jun 2018
Imagine you have a configuration table like the following: It specifies a set of rules that Can be enabled / disabled Can be given a priority among themselves Include a set of flags which correspond to the thing you want to configure (e.g. some check to execute) Those flags can be ordered as well So, … Continue reading Using UNPIVOT…
25 Jun 2018
One of Oracle 12c’s coolest features was the introduction of the SQL standard OFFSET .. FETCH clause, as we can now write things like: This is querying the Sakila database. Most other databases had this clause (or a non-standard version of it) for ages, e.g. MySQL with LIMIT. For all the different LIMIT syntaxes, check … Continue reading Oracle’s OFFSET…
15 Jun 2018
Clock’s ticking. JDK 11 will remove a bunch of deprecated modules through JEP 320, which includes the Java EE modules, which again includes JAXB, a dependency of many libraries, including jOOQ. Thus far, few people have upgraded to Java 9 or 10, as these aren’t LTS releases. Unlike in the old days, however, people will … Continue reading How to…
7 Jun 2018
Today, jOOQ 3.11 has been released with support for 4 new databases, implicit joins, diagnostics, and much more New Databases Supported At last, 4 new SQL dialects have been added to jOOQ! These are: jOOQ Professional Edition Aurora MySQL Edition Aurora PostgreSQL Edition Azure SQL Data Warehouse jOOQ Enterprise Edition Teradata Implicit Joins One of … Continue reading jOOQ 3.11…
6 Jun 2018
In this much overdue article, I will explain why I think that in almost all cases, you should implement a “database first” design in your application’s data models, rather than a “Java first” design (or whatever your client language is), the latter approach leading to a long road of pain and suffering, once your project … Continue reading Truth First,…
25 May 2018
The SQL standard knows a lesser known feature called GROUPING SETS. One particular side-effect of that feature is that we can group by “nothing” in SQL. E.g. when querying the Sakila database: This will yield: count | ------| 1000 | What’s the point, you’re asking? Can’t we just omit the GROUP BY clause? Of course, … Continue reading How to…
14 May 2018
Google’s BigQuery has a very interesting SQL language feature, which I’ve missed many times in other databases: select: SELECT [{ ALL | DISTINCT }] { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ] [ REPLACE ( expression [ AS ] column_name [, ...] ) ] | expression [ [ AS ] alias … Continue reading Selecting all…
2 May 2018
When inserting records into SQL databases, we often want to fetch back generated IDs and possibly other trigger, sequence, or default generated values. Let’s assume we have the following table: DB2 DB2 is the only database currently supported by jOOQ, which implements the SQL standard according to which we can SELECT from any INSERT statement, … Continue reading How to…
19 Apr 2018
Something that has been said many times, but needs constant repeating until every developer is aware of the importance of this is the performance difference between row-by-row updating and bulk updating. If you cannot guess which one will be much faster, remember that row-by-row kinda rhymes with slow-by-slow (hint hint). Disclaimer: This article will discuss … Continue reading The Performance…
13 Apr 2018
In a previous blog post, I wrote about why you should (almost) always default to using bind variables. There are some exceptions, which I will cover in another follow-up post, but by default, bind variables are the right choice, both from a performance and from a security perspective. In this article, I will show an … Continue reading When Using…
12 Apr 2018
A common problem with dynamic SQL is parsing performance in production. What makes matters worse is that many developers do not have access to production environments, so they are unaware of the problem (even if there’s nothing new about this topic). What exactly is the problem? Execution plan caches Most database vendors these days ship … Continue reading Why SQL…
10 Apr 2018
In a previous blog post, I’ve shown how the programmatic MockDataProvider can be used to mock the entire JDBC API through a single functional interface: Writing the provider manually can be tedious in some cases, especially when a few static SQL strings need to be mocked and constant result sets would be OK. In that … Continue reading Mocking JDBC…
3 Apr 2018
In some cases, it’s really useful to be able to compile a class at runtime using the java.compiler module. You can e.g. load a Java source file from the database, compile it on the fly, and execute its code as if it were part of your application. In the upcoming jOOR 0.9.8, this will be … Continue reading How to…
1 Apr 2018
If you’ve been following the fast paced JDK 9+ projects, you may have noticed an exciting, first big change that has been made possible thanks to Java 9’s Jigsaw feature. In JDK 11, JEP 320 will ship, or rather: it will no longer ship, as JEP 320 means that both CORBA and Java EE modules … Continue reading A Completely…
28 Mar 2018
When performing reflective access to default methods in Java, Google seems to fail us. The solutions presented on Stack Overflow, for instance, seem to work only in a certain set of cases, and not on all Java versions. This article will illustrate different approaches to calling interface default methods through reflection, as may be required … Continue reading Correct Reflective…
13 Mar 2018
The SQL standard is a nice thing. But it’s a standard. And as such, while it may provide good guidelines on how to implement some SQL features, most dialects deviate from it in one way or another (sometimes drastically, cheers, MySQL). But that doesn’t have to be a bad thing. Innovation is not driven by … Continue reading Top 10…
27 Feb 2018
Sounds fancy, right? But it’s a really nice and reasonable approach to doing dynamic SQL with jOOQ. This blog post is inspired by a Stack Overflow question, where a user wanted to turn a set of values into a dynamic UNION query like this: Note, both the Stack Overflow user and I are well aware … Continue reading Map Reducing…
23 Feb 2018
In most cases, natural sorting by sorting lexicographically is useful as a default in Java. This includes sorting file names, which are sorted lexicographically as well. However, when we have version numbers in our files (such as a set of SQL migration scripts), then we prefer the files to be sorted in a more intuitive … Continue reading How to…
20 Feb 2018
One of the biggest contributors to SQL syntax verbosity is the need to explicitly JOIN every table that somehow contributes to the query, even if that contribution is “trivial”. When looking at the Sakila database, an example could be seen easily when fetching customer data: That single access to the country information cost us 3 … Continue reading Type Safe…
19 Feb 2018
When you work with JAXB to generate Java code from XSD (or *ghasp* WSDL) files, you’re going to use the XJC tool, which is shipped with the JDK (at least until JDK 9 – with Jigsaw, it will soon be externalised into its own external dependency). Adding plugins to XJC when running it via Maven … Continue reading How to…
5 Feb 2018
A really geeky way to start a Monday morning is to be nerd-sniped by the cool Fermat’s Library twitter account… … reading up on the cool Tupper’s Self-Referential Formula thinking “Can This be Done in SQL?™” As we all know from a previous article, SQL is turing complete, so the answer must be yes. And … Continue reading Calculating Tupper’s…
12 Jan 2018
jOOQ’s main value proposition is obvious: Type safe embedded SQL in Java. People who actively look for such a SQL builder will inevitably stumble upon jOOQ and love it, of course. But a lot of people don’t really need a SQL builder – yet, jOOQ can still be immensely helpful in other situations, through its … Continue reading Top 5…
22 Dec 2017
Thanks to the generous contributions of Timur Shaidullin, jOOQ 3.11 will now support GRANT and REVOKE statements through #6812. While implementing integration tests for these new features, I had researched the different ways how these statements work on a variety of databases, and the good news is, they’re all mostly quite standardised (in fact, they’re … Continue reading Do not…
This answer to a beautiful Stack Overflow question I’ve given recently needs further explanation in a blog post. When working with Microsoft Excel, we can create beautiful and also very insightful Pivot Tables with grand totals. What are they? This is best explained visually. Assuming you have this normalised form for your raw data. As … Continue reading Creating a…
18 Dec 2017
Or: Move That Loop into the Server Already! This article will illustrate the significance of something that I always thought to be common sense, but I keep seeing people getting this (very) wrong in their productive systems. Chances are, in fact, that most applications out there suffer from this performance problem – and the fix … Continue reading The Cost…
15 Dec 2017
When working with Oracle stored procedures, it is not uncommon to have debug log information available from DBMS_OUTPUT commands. For instance, if we have a procedure like this: The procedure works just the same, regardless if we’re reading the output from the DBMS_OUTPUT call. It is there purely for logging purposes. Now, if we call … Continue reading How to…
14 Dec 2017
Caching is hard in various ways. Whenever you’re caching things, you have to at least think of: Memory consumption Invalidation In this article, I want to show a flaw that often sneaks into custom cache implementations, making them inefficient for some execution paths. I’ve encountered this flaw in Eclipse, recently. What did Eclipse do wrong? … Continue reading A Common…
28 Nov 2017
jOOQ is a very backwards compatible product. This doesn’t only mean that we keep our own API backwards compatible as well as possible, but we also still support Java 6 in our commercial distributions. In a previous blog post, I’ve shown how we manage to support Java 6 while at the same time not missing … Continue reading How to…
20 Nov 2017
After the successful release of JDK 9, we can already look forward, and play around with early access releases of JDK 10. The list of JEPs currently targeted for JDK 10 is quite manageable so far. JEP 286 is probably the most exciting one for most Java developers: Local variable type inference (which we’ve blogged … Continue reading Using JDK…
6 Nov 2017
Usually, this blog is 100% pro window functions and advocates using them at any occasion. But like any tool, window functions come at a price and we must carefully evaluate if that’s a price we’re willing to pay. That price can be a sort operation. And as we all know, sort operations are expensive. They … Continue reading How to…
1 Nov 2017
In this post, we’re going to discuss a couple of recent efforts to squeeze roughly 10% in terms of speed out of jOOQ by iterating on hotspots that were detected using JMC (Java Mission Control) and then validated using JMH (Java Microbenchmark Harness). This post shows how to apply micro optimisations to algorithms where the … Continue reading Squeezing Another…
17 Oct 2017
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:…
11 Oct 2017
What’s better? Using the JDK’s String.replace() or something like Apache Commons Lang’s Apache Commons Lang’s StringUtils.replace()? In this article, I’ll compare the two, first in a profiling session using Java Mission Control (JMC), then in a benchmark using JMH, and we’ll see that Java 9 heavily improved things in this area. Profiling using JMC In … Continue reading Benchmarking JDK…
28 Sept 2017
Cost Based Optimisation is the de-facto standard way to optimise SQL queries in most modern databases. It is the reason why it is really really hard to implement a complex, hand-written algorithm in a 3GL (third generation programming language) such as Java that outperforms a dynamically calculated database execution plan, that has been generated from … Continue reading 10 Cool…
22 Sept 2017
A very common type of SQL query is the TOP-N query, where we need the “TOP N” records ordered by some value, possibly per category. In this blog post, we’re going to look into a variety of different aspects to this problem, as well as how to solve them with standard and non-standard SQL. These … Continue reading How to…
1 Sept 2017
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:…
24 Aug 2017
One of the cooler hidden features in jOOQ is the JPADatabase, which allows for reverse engineering a pre-existing set of JPA-annotated entities to generate jOOQ code. For instance, you could write these entities here: (Just a simple example. Let’s not discuss the caveats of @ManyToMany mapping). For more info, the full example can be found … Continue reading jOOQ 3.10…
22 Aug 2017
SQL is a really cool language. I can write really complex business logic with this logic programming language. I was again thrilled about SQL recently, at a customer site: Writing some nifty SQL queries for a customer. Wondering why anyone would even consider using a 3GL for any business logic at all?? — Lukas Eder … Continue reading Finding all…
15 Aug 2017
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:…
25 Jul 2017
jOOQ has been around for a while now (since 2009!) and by now we can say we’ve seen quite a bit of things about the SQL and Java languages. Some of our design decisions are particular in the way jOOQ thinks about programming with SQL. These include: Nullability (let’s stop fighting it) Value types (let’s … Continue reading 5 Things…
24 Jul 2017
In recent months, there had been some really exciting news from the MySQL team: (Recursive) Common Table Expressions in MySQL Introducing Window Functions These two SQL standard language features are among the most powerful SQL features that are available from most other databases. I frequently include them in conference talks about SQL (see my article … Continue reading jOOQ 3.10…
20 Jul 2017
Java’s visibility rules are tricky at times. Do you know what this will print? It will print (highlight to see the solution): B.x Because: The super type B's members hide the enclosing type C's members, which again hide the static import from A. How can this lead to bugs? The problem isn’t that the above … Continue reading A Curious…
18 Jul 2017
As we all agree, GOTO is evil, right? Relevant XKCD Or even funnier: New Intern Knows Best Of course, GOTO isn’t evil Of course, somewhere deep down in our professional selves, we know that GOTO isn’t evil, it’s just a very basic processor instruction that was available since the early days of assembly code. GOTO … Continue reading Don’t Overdo…
14 Jul 2017
Every now and then, I tweet something like this, just to piss off some clean coders: Call me what you want, but I like the occasional break-out-of-if pic.twitter.com/kQ0BIkijz0 — Lukas Eder (@lukaseder) July 13, 2017 Apart from the obvious trolling factor (why can’t I ever resist?), I do think there’s something thought provoking in such … Continue reading Don’t Extract…
13 Jul 2017
You know JDBC, right? It’s that really easy, concise API that we love to use to work with virtually any database, relational or not. It has essentially three types that you need to care about: Connection Statement (and its subtypes) ResultSet All the other types some sort of utilities. Now, with the above three, we … Continue reading How I…
3 Jul 2017
Notice, this issue has been fixed in Java 8 (8u222), thanks for the comment Zheka Kozlov In a recent article, I’ve shown that programmers should always apply a filter first, map later strategy with streams. The example I made there was this one: In this case, the limit() operation implements the filtering, which should take … Continue reading Are Java…
29 Jun 2017
In recent days, I’ve seen a bit too much of this: Something is very wrong with the above example. Can you see it? No? Let me rename those variables for you. Better now? Exactly. The above algorithm is O(N) when it could be O(1): (Let’s assume the lack of explicit ordering is irrelevant) I’m working … Continue reading A Basic…
28 Jun 2017
In this article, I will establish how the SQL language and its implementations distinguish between changed values and modified values, where a changed value is a value that has been “touched”, but not necessarily modified, i.e. the value might be the same before and after the change. Many ORMs, unfortunately, either update all of a … Continue reading ORMs Should…
20 Jun 2017
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:…
9 Jun 2017
It was hard to limit ourselves to 10 Nice Examples of Writing SQL in Kotlin With jOOQ, recently, because the Kotlin language has many nice little features that really help a lot when working with Java libraries. We’ve talked about the nice with() stdlib function, which allows to “import” a namespace for a local scope … Continue reading Using Kotlin’s…
2 Jun 2017
ANOTHER SQL Post this week? I got nerd-sniped: Lazy Internet: In Oracle, how do i query for where (A, B, C) in (('a', 'b', null), 'a', null, 'c') considering Oracle's null handling? — Rafael Winterhalter (@rafaelcodes) June 2, 2017 Oooooh, challenge accepted! So, let’s assume we have a table T with columns (A, B, C) … Continue reading How to…
1 Jun 2017
The following two indexes are redundant in most SQL databases: It is usually safe to drop the first index, because all queries that query the LAST_NAME column only can still profit from the second index I_ACTOR_2. The reason being that LAST_NAME is the first column of the composite index I_ACTOR_2 (it would be a different … Continue reading How to…
31 May 2017
I stumbled upon an interesting question on Stack Overflow recently. A user wanted to query a table for a given predicate. If that predicate returns no rows, they wanted to run another query using a different predicate. Preferably in a single query. Challenge accepted! Canonical Idea: Use a Common Table Expression We’re querying the Sakila … Continue reading How to…
30 May 2017
Users of jOOQ, PL/SQL, T-SQL are spoiled as they hardly ever need to worry about bind values. Consider the following statements: Using jOOQ The method parameters firstName and lastName will be automatically mapped to bind values in the generated SQL statement. Here’s the debug log output when running the above, where the first statement is … Continue reading When to…
22 May 2017
There are some situations where you would like to have at least one (empty) row in your result set in SQL. Imagine the following situation. We’re querying the Sakila database for actors and their films: yielding something like: +------------+-----------+---------------------+ | FIRST_NAME | LAST_NAME | TITLE | +------------+-----------+---------------------+ | ... | ... | ... | | … Continue reading How to…
18 May 2017
Kotlin is the next big thing. With Google announcing official support for Kotlin on Android, we’ll see a lot more traction for this lovely language. After today's #kotlin announcement, we're betting this tag will blow up just like Swift did. #io17. https://t.co/whOcJlJfWe pic.twitter.com/2QKEJIRYVi — Stack Overflow (@StackOverflow) May 17, 2017 We’ve already blogged about the … Continue reading 10 Nice…
9 May 2017
I was recently asked in an interview about my opinion on how to be a great programmer. That’s an interesting question, and I think we can all be great programmers, regardless of our talent, if we follow a couple of rules that – I believe – should be common sense. In fact, these rules don’t … Continue reading 10 Tips…
8 May 2017
A large-ish customer in banking (largest tables on that particular system: ~1 billion rows) once decided to separate the OLTP database from the “log database” in order to better use resources and prevent contention on some tables, as the append-only log database is used heavily for analytic querying of all sorts. That seems to make … Continue reading The Difficulty…
3 May 2017
In SQL, quite often, we want to compare several values with each other. For instance, when we’re looking for a specific user by their first and last names, we’ll write a query like this one: We’re getting: CUSTOMER_ID FIRST_NAME LAST_NAME ------------------------------------ 8 SUSAN WILSON Surely, everyone agrees that this is correct and perfectly fine as … Continue reading Don’t Use…
20 Apr 2017
At a customer site, I’ve recently encountered a report where a programmer needed to count quite a bit of stuff from a single table. The counts all differed in the way they used specific predicates. The report looked roughly like this (as always, I’m using the Sakila database for illustration): And then, unsurprisingly, combinations of … Continue reading How to…
18 Apr 2017
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:…
31 Mar 2017
When looking at execution plans in Oracle, we’ll have to do several steps to be able to call the DBMS_XPLAN package functions. In fact, we have to find out the SQL_ID for a given statement first, and only then we can get its plan. I’ve blogged about this previously, here. However, thanks to lateral unnesting, … Continue reading How to…
30 Mar 2017
Hah! Got nerd-sniped again: https://stackoverflow.com/questions/43099226/how-to-make-jooq-to-use-arrays-in-the-in-clause/43102102 A jOOQ user was wondering why jOOQ would generate an IN list for a predicate like this: Java SQL … when in fact there could have been the following predicate being generated, instead: In the second case, there would have been only one single bind variable instead of 4, and … Continue reading SQL IN…
29 Mar 2017
Tuning SQL isn’t always easy, and it takes a lot of practice to recognise how any given query can be optimised. One of the most important slides of my SQL training is the one summarising “how to be fast”: Some of these bullets were already covered on this blog. For instance avoiding needless, mandatory work, … Continue reading How to…
20 Mar 2017
jOOQ is a library that loves making everything internal final and package private. We have tons of classes like these: The class implements the semantics of SQL string concatenation. Clearly, you shouldn’t need to tamper with it (or even know about it), because it is “protected” behind the corresponding public API in the DSL class: … Continue reading The Open-Closed…
17 Mar 2017
I was nerd-sniped: So tables dee and dum are two theoretical tables in SQL, and they can be characterised as such: [Dee] is the relation that has no attributes and a single tuple. It plays the role of True. [Dum] is the relation that has no attributes and no tuples. It plays the role of … Continue reading Creating Tables…
With Java 8 lambdas being available to us as a programming tool, there is a “new” and elegant way of constructing objects. I put “new” in quotes, because it’s not new. It used to be called the strategy pattern, but as I’ve written on this blog before, many GoF patterns will no longer be implemented … Continue reading A Nice…
16 Mar 2017
There are many many opinions out there regarding the old surrogate key vs. natural key debate. Most of the times, surrogate keys (e.g. sequence generated IDs) win because they’re much easier to design: They’re easy to keep consistent across a schema (e.g. every table has an ID column, and that’s always the primary key) They’re … Continue reading Faster SQL…
8 Mar 2017
Probably the most impactful thing you could learn about when writing efficient SQL is indexing. A very close runner-up, however, is the fact that a lot of SQL clients demand tons of “unnecessary, mandatory work” from the database. Repeat this after me: Unnecessary, Mandatory Work What is “unnecessary, mandatory work”? It’s two things (duh): Unnecessary … Continue reading Many SQL…
7 Mar 2017
There are already only very few real-world use-cases for FULL [ OUTER ] JOIN, but maybe, you have run into this beast in the past. But when was the last time you’ve seen a NATURAL JOIN? Right. A quick reminder from our article about JOINs: FULL JOIN A FULL JOIN is a type of OUTER … Continue reading Impress Your…
21 Feb 2017
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:…
17 Feb 2017
An interesting question was asked on reddit’s /r/java recently: Should Iterators be used to modify a custom Collection? Paraphrasing the question: The author wondered whether a custom java.util.Iterator that is returned from a mutable Collection.iterator() method should implement the weird Iterator.remove() method. A totally understandable question. What does Iterator.remove() do? Few people ever use this … Continue reading Should I…
10 Feb 2017
Sometimes, SQL can just be so beautiful. One of the less mainstream features in SQL is the array type (or nested collections). In fact, it’s so not mainstream that only 2 major databases actually support it: Oracle and PostgreSQL (and HSQLDB and H2 in the Java ecosystem). In PostgreSQL, you can write: Or in Oracle: … Continue reading Beautiful SQL:…
8 Feb 2017
Earlier this week, I’ve blogged about how to execute SQL batches with JDBC and jOOQ. This was useful for the MySQL, SQL Server, and Sybase users among you. Today, we’ll discuss a slightly more difficult task, how to fetch Oracle 12c implicit cursors – which are essentially the same thing. What’s an implicit cursor? Oracle … Continue reading How to…
6 Feb 2017
Some databases (in particular MySQL and T-SQL databases like SQL Server and Sybase) support a very nice feature: They allow for running a “batch” of statements in a single statement. For instance, in SQL Server, you can do something like this: This is a batch of 4 statements, and it can be executed as a … Continue reading How to…
18 Jan 2017
A very interesting feature of the SQL Server and PostgreSQL databases (and some others, including SQLite) is the partial index (sometimes also called “filtered index”). That’s an index that contains only “parts” of the table data. For instance, we can write the following index in SQL Server and PostgreSQL: Let’s imagine you have a house … Continue reading How to…
16 Jan 2017
Typesafe embedded DSLs like jOOQ are extremely powerful for dynamic SQL, because the query you’re constructing with the jOOQ DSL is a dynamic query by nature. You’re constructing a query expression tree using a convenient API (the “DSL”), even if you think your SQL statement is static. For instance: The above query looks like a … Continue reading A Functional…
12 Jan 2017
Perhaps the most powerful SQL feature is the JOIN operation. It is the envy of all non-relational databases, because the concept is so simple, yet so universally applicable, when you want to “combine” two data sets. Put simply, when joining two tables, you’re combining every row from one table with every row from another table, … Continue reading A Probably…
10 Jan 2017
SQL Server has this nice feature called table-valued parameters (TVP), where users can pass table variables to a stored procedure for bulk data processing. This is particularly nice when the stored procedure is an inline table valued function, i.e. a function that returns a table as well. For instance: The above function creates a cross … Continue reading jOOQ 3.10…
6 Jan 2017
One of jOOQ‘s most powerful features is the capability of introducing custom data types, pretending the database actually understands them. For instance, when working with SQL TIMESTAMP types, users mostly want to use the new JSR-310 LocalDateTime, rather than the JDBC java.sql.Timestamp type. In jOOQ 3.9+, this is a no brainer, as we’ve finally introduced … Continue reading How to…
5 Jan 2017
In a recent consulting gig, I was analysing a client’s connection pool issue in a productive system, where during some peak loads, all the Java processes involving database interactions just started queueing up until nothing really worked anymore. No exceptions, though, and when the peak load was gone in the evening, everything returned back to … Continue reading How to…
20 Dec 2016
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:…
If you’ve followed the recent (fake) news, you’ve probably already heard it. Oracle is “massively ramping up audits of Java customers it claims are in breach of its licences” After a quick check on the source (The Register), here’s a more realistic, probably more accurate version of that headline: Oracle is thinking about auditing 1-2 … Continue reading What we…
15 Dec 2016
This is one of software engineering’s oldest battles. No, I’m not talking about where to put curly braces, or whether to use tabs or spaces. I mean the eternal battle between nominal typing and structural typing. This article is inspired by a very vocal blogger who eloquently reminds us to … […] Please Avoid Functional … Continue reading Do You…
14 Dec 2016
Recently, at Devoxx, I’ve seen this beautiful slide in a talk by Kevlin Henney In his talk, he was displaying a variety of approaches to solve the FizzBuzz “problem”, including a couple of very elegant solutions in completely declarative approaches and languages. In this particular slide, Kevlin used a notation that is derived from maths. … Continue reading SQL, Streams,…
9 Dec 2016
The SQL language is very intuitive. Until it isn’t. Over the years, a lot of people have criticised the SQL language for a variety of reasons. For instance: IDEs cannot easily guess what auto completion options to offer, because as long as you don’t specify the FROM clause, there are no tables in scope (yet): … Continue reading A Beginner’s…
5 Dec 2016
As long as we allow ourselves to write string-based dynamic SQL embedded in other programming languages like Java, we will have a certain risk of being vulnerable to SQL injection. That’s a fact. Don’t believe it? Check out this website exposing all vulnerabilities on Stack Overflow for PHP questions: https://laurent22.github.io/so-injections In a previous blog post, … Continue reading Prevent SQL…
8 Nov 2016
Some of the biggest limitations when working with Oracle PL/SQL from Java is the lack of support for a variety of PL/SQL features through the JDBC interface. This lack of support is actually not limited to JDBC, but also extends to Oracle SQL. For instance, if you’re using the useful PL/SQL BOOLEAN type as such: … Continue reading Use jOOQ…
2 Nov 2016
I’m very happy to have another interesting blog post by Vlad Mihalcea on the jOOQ blog, this time about his Open Source library flexypool. Read his previous jOOQ Tuesdays post on Hibernate here. Vlad is a Hibernate developer advocate and he’s the author of the popular book High Performance Java Persistence, and he knows 1-2 … Continue reading Applying Queueing…
1 Nov 2016
I’ve recently discovered a rather significant performance issue on a productive Oracle 11g customer database. And I’m sure you have this issue too, which is why I’m documenting it here. This is a simplified representation of the setup at the customer site: ID PAYMENT_DATE TEXT ---------- ------------ ----------------------------------- 33803 21.05.16 DcTNBOrkQIgMtbietUWOsSFNMIqGLlDw... 29505 09.03.16 VIuPaOAQqzCMlFBYPQtvqUSbWYPDndJD... 10738 … Continue reading Why You…
31 Oct 2016
I’m frequently telling developers to put window functions almost everywhere, because they’re so awesome! One feature that I rarely see in the wild (even if it is extremely useful for reporting) is called “logical windowing” in Oracle, and it’s most useful when used with INTERVAL ranges. Let’s see what we may want to do. I … Continue reading A Little…
28 Oct 2016
In a recent blog post, I’ve advocated against the use of COUNT(*) in SQL, when a simple EXISTS() would suffice. This is important stuff. I keep tuning productive queries where a customer runs a COUNT(*) query like so: … where after they discard the exact count to only check for existence: It doesn’t matter if … Continue reading Don’t Even…
21 Oct 2016
Are you working with someone else’s schema and they haven’t declared nice names for all their constraints? Unfortunately, it is all too easy to create a table like this: Or like this: Sure, you get a little convenience when writing the table. But from now on, you’re stuck with weird, system generated names both for … Continue reading How to…
20 Oct 2016
Everyone who writes complex SQL (or dare I say, move business logic into the database?) wishes for parameterised views from time to time. This hypothetical syntax would be terrific: Or as user-defined functions if you insist (as available in SQL Server): As always on this blog, I’m using the useful Sakila database for examples. The … Continue reading Be Careful…
18 Oct 2016
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:…
7 Oct 2016
Adding the right index to speed up your queries is essential. But after a while, as your system grows, you may find yourself with tons of indexes, which all slow down writing to the database – as with each write to the table, the index needs to be updated as well in the same transaction. … Continue reading Does Your…
5 Oct 2016
In my SQL Masterclass, I frequently remind participants of the fact how important statistics are for a modern cost based optimiser. For instance, if you consider the fact that in an average E-Banking system’s bookings table, you will probably have a transaction amount histogram like the following: In other words, most of your transactions are … Continue reading Why You…
28 Sept 2016
Some people make architecture decisions purely based on the loudest consultant: no, we base all our important architectural decisions on # of tweets. It's a Twitter Oriented Architecture ;) — Gareth Western (@gareth) September 21, 2016 For most others, however, decisions are not as simple as this. For instance: When should we start evaluating NoSQL … Continue reading When to…
27 Sept 2016
A recent question on Stack Overflow about jOOQ caught my attention. The question essentially asked: Why do both of these loops work? And indeed, just like in PL/SQL, you can use any jOOQ ResultQuery as a Java 5 Iterable, because that’s what it is. An Iterable<R> where R extends Record. The semantics is simple. When … Continue reading A Hidden…
14 Sept 2016
A while ago, I blogged about the importance of avoiding unnecessary COUNT(*) queries:https://blog.jooq.org/sql-tip-of-the-day-be-wary-of-select-count … and how to replace them with equivalent EXISTS queries As I’m updating the SQL training to show also PostgreSQL performance characteristics in addition to Oracle, I really have to reiterate this topic. Please repeat after me: Thou shalt not use COUNT(*) … Continue reading Avoid Using…
29 Aug 2016
With Java 8 being mainstream now, people start using Streams for everything, even in cases where that’s a bit exaggerated (a.k.a. completely nuts, if you were expecting a hyperbole here). For instance, take mykong’s article here, showing how to collect a Map’s entry set stream into a list of keys and a list of values: … Continue reading Using jOOλ…
17 Aug 2016
Welcome to the jOOQ Tuesdays series. In this series, we’ll publish an article on the third Tuesday every other month (today, exceptionally on a Wednesday because of technical issues) 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 … Continue reading jOOQ Tuesdays:…
11 Aug 2016
This hilarious article with a click-bait title caught my attention, recently: View at Medium.com A hilarious (although not so true or serious) rant about the current state of JavaScript development in the node ecosystem. Dependency hell isn’t new Dependency hell is a term that made it into wikipedia. It defines it as such: Dependency hell … Continue reading All Libraries…
10 Aug 2016
Pagination is one of those things that almost everyone gets wrong for two reasons: Here’s why. What’s wrong with pagination? Most applications blindly produce pagination like this: This is how GMail implements pagination. With my current settings, it displays 100 E-Mails at a time and also shows how many E-Mails there are in total, namely … Continue reading Why Most…
8 Aug 2016
I’ve recently made an embarassing discovery: wha. I've never used while loops in PL/SQL. TIL :) — Lukas Eder (@lukaseder) July 26, 2016 Yes. In all of my professional work with PL/SQL (and that has been quite a bit, in the banking industry), I have never really used a WHILE loop – at least not … Continue reading Why I…
20 Jul 2016
We programmers keep cargo culting these wrong ideas. Recently, we said “NO” to Venn diagrams. Today we’re going to say no to surrogate keys. The surrogate keys vs. natural keys non-debate is one of the most overheated debates in data architecture, and I don’t get why everyone is so emotional. Both sides claim to hold … Continue reading Say NO…
19 Jul 2016
“Challenge accepted” said Tagir Valeev when I recently asked the readers of the jOOQ blog to show if the Java JIT (Just-In-Time compilation) can optimise away a for loop. Tagir is the author of StreamEx, very useful Java 8 Stream extension library that adds additional parallelism features on top of standard streams. He’s a speaker … Continue reading The Java…
15 Jul 2016
It seems that perfection is attained not when there is nothing more to add, but when there is nothing more to remove. – Antoine de Saint Exupéry in Terre des Hommes As SQL developers, we keep adding more and more indexes to our tables. Every time we run new queries that are potentially slow, a … Continue reading How to…
14 Jul 2016
A lot of people use SQL constraints mainly to enforce data integrity, and that’s already a very good thing. A UNIQUE constraint, for instance, makes sure that there is at most one instance of any possible value (or tuple, in the case of a composite constraint) in a table. For instance: Constraints are also good … Continue reading How Adding…
12 Jul 2016
A recent Tweet by Aaron Bertrand (whom you’ve certainly encountered on Stack Overflow) has triggered my interest Or the one that asks a bunch of questions about ANY / ALL syntax, which nobody has used since Celko was still in college? — Aaron Bertrand (@AaronBertrand) July 11, 2016 Indeed, few people I’ve met and who’ve … Continue reading Quantified Comparison…
7 Jul 2016
Catchy headline, yes. But check out this Stack Overflow question by user Mike: (I’m duplicating it here on the blog, as it might be deleted soon) It’s a pretty open ended question. I’ll be starting out a new project and am looking at different ORMs to integrate with database access. Do you have any favorites? … Continue reading “What Java…
5 Jul 2016
In recent times, there have been a couple of tremendously popular blog posts explaining JOINs using Venn Diagrams. After all, relational algebra and SQL are set oriented theories and languages, so it only makes sense to illustrate set operations like JOINs using Venn Diagrams. Right? Google seems to say so: Everyone uses Venn Diagrams to … Continue reading Say NO…
4 Jul 2016
A recent article about various ways to implement structural pattern matching in Java has triggered my interest: http://blog.higher-order.com/blog/2009/08/21/structural-pattern-matching-in-java The article mentions a Scala example where a tree data structure can be traversed very easily and neatly using Scala’s match keyword, along with using algebraic data types (more specifically, a sum type): Even if you’re not … Continue reading How Functional…
30 Jun 2016
When people start creating commercially licensed software (like we did, in 2013 with jOOQ), there is always the big looming question: What do I do about piracy? I’ve had numerous discussions with fellow entrepreneurs about this topic, and this fear is omnipresent. There has also been a recent discussion on reddit, titled “prevent sharing of … Continue reading With Commercial…
21 Jun 2016
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:…
27 May 2016
A common myth in SQL is the idea that correlated subqueries are evil and slow. For example, this query here: It “forces” the database engine to run a nested loop of the form (in pseudo code): So, for every actor, collect all the corresponding film_actors and count them. This will produce the number of films … Continue reading Correlated Subqueries…
12 May 2016
We’re excited to announce another very interesting guest post on the jOOQ Blog by John Mcclean from AOL. AOL is a global digital media and technology company, founded in 1985 and once known as America Online, AOL is now part of the Verizon Group. AOL focuses on four areas – video, mobile, ad technology and … Continue reading Cyclops-react Organises…
9 May 2016
Java 8 introduced JSR-308, which added new annotation capabilities to the Java language. Most importantly: Type annotations. It is now possible to design monsters like the below: The code displayed in that tweet really compiles. Every type can be annotated now, in order to enhance the type system in any custom way. Why, you may … Continue reading JSR-308 and…
25 Apr 2016
Listicles like these do work – not only do they attract attention, if the content is also valuable (and in this case it is, trust me), the article format can be extremely entertaining. This article will bring you 10 SQL tricks that many of you might not have thought were possible. The article is a … Continue reading 10 SQL…
21 Apr 2016
A very interesting question was posted to Stack Overflow and reddit just recently about Java generics. Consider the following method: While the unsafe cast seems a bit wonky, and you might guess there’s something wrong here, you can still go ahead and compile the following assignment in Java 8: This is obviously wrong, because Integer … Continue reading The Parameterless…
19 Apr 2016
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. We have the pleasure … Continue reading jOOQ Tuesdays:…
13 Apr 2016
In the context of a previous blog post about JUnit 5, Maaartinus, one of our readers, has brought up a very interesting idea: The only problem with try-catch is its verbosity, which is something I can live with (IMHO a lone catch would do better, the implicit try would apply to all preceding code in … Continue reading Would We…
12 Apr 2016
Security is important, especially on the data access layer. Most commercial databasese allow for fine-grained privilege control using database access grants. For instance, you would be restricting access from a user to a certain set of tables (or even better: views), via GRANT statements: With this fine-grained access control, write operations on certain database objects … Continue reading Using jOOQ’s…
11 Apr 2016
Few people know about this very very awesome feature of the Stack Exchange platform. The Stack Exchange Data Explorer To be found here: http://data.stackexchange.com As you may know, much of the Stack Exchange platform runs on SQL Server (interesting architecture details here: http://stackexchange.com/performance), and the team has had the courtesy of making a lot of … Continue reading Using SQL…
31 Mar 2016
This article is overdue. After the hype around the release of Kotlin 1.0 has settled, let’s have a serious look at some Kotlin language features that we should have in Java as well. In this article, I’m not going to wish for unicorns. But there are some low hanging fruit (as far as I naively … Continue reading 10 Features…
29 Mar 2016
An interesting question by Tagir Valeev on Stack Overflow has recently caught my attention. To keep things short (read the question for details), while the following code works: printing 1 2 3 4 5 The following, similar code won’t work: Causing a StackOverflowError. Sure, this kind of recursive iteration is not optimal. It wasn’t prior … Continue reading Watch Out…
24 Mar 2016
This Stack Overflow question has yet again nerd-sniped me [finding the] maximum element in the array that would result from performing all M operations Here’s the question by John that was looking for a Java solution: With an array of N elements which are initialized to 0. we are given a sequence of M operations … Continue reading Time for…
17 Mar 2016
Too many programmers think SQL is a bit of a beast. It is one of the few declarative languages out there, and as such, behaves in an entirely different way from imperative, object-oriented, or even functional languages (although, some say that SQL is also somewhat functional). As a SQL trainer (do visit our training, it’s … Continue reading 10 Easy…
15 Mar 2016
Here we go again. THAT TOPIC. But hang on. The approach discussed here (and in the Ceylon language) is not something you see every day. At the same time, it is very cunning. Nulls are baked into the language … or so it may seem. Indeed, in Ceylon, like in Kotlin (and possibly many other … Continue reading Ceylon Might…
10 Mar 2016
News could hardly get more exciting than this, for a programming language aficionado! There is now a JEP 286 for Local-Variable Type Inference with status “Candidate”. And a request for feedback by Brian Goetz, which I would love to invite you to participate in: http://mail.openjdk.java.net/pipermail/platform-jep-discuss/2016-March/000037.html Please do so, the survey remains open only from March … Continue reading Java 10’s…
9 Mar 2016
A lot of developers get the distinction between JOIN and SEMI-JOIN wrong. Let me explain… What are JOIN and SEMI-JOIN A little bit of relational algebra first. What is an (INNER) JOIN? An JOIN is nothing but a filtered cartesian product. And what is a cartesian product? Wikipedia explains this very nicely: for sets A … Continue reading SQL JOIN…
7 Mar 2016
Good decisions come from experience. Experience comes from making bad decisions. ― Mark Twain Today, let’s look at one piece of experience and how we can turn that into good decisions when implementing UI logic. Please, all UI developers read this. The bad decision When UI developers display tabular data, it is very common for … Continue reading UI Developers!…
1 Mar 2016
With jOOQ 3.7, we have finally added formal support for Java 8 features. This opened the door to a lot of nice improvements, such as: Creating result streams Calling statements asynchronously (jOOQ 3.8+) But obviously, we didn’t want to disappoint our paying customers who are stuck with Java 6 because of their using an older … Continue reading How to…
25 Feb 2016
If you’re programming in more advanced languages like Scala or Ceylon, or even JavaScript, “nested functions” or “local functions” are a very common idiom to you. For instance, you’ll write things like fibonacci functions as such: (Question from Stack Overflow by Aaron Yodaiken) The f() function contains a nested g() function, which is local to … Continue reading (Ab)using Java…
22 Feb 2016
This has caught me by surprise. After studying the Kotlin language to learn about how to best leverage this interesting new language for jOOQ, I stumbled upon this puzzler. What do you think the following program will print? fun main(args: Array) { (1..5).forEach { if (it == 3) return print(it) } print("done") } Well… You … Continue reading A Very…
18 Feb 2016
Have you been writing a lot of code following the Mute-Design-Pattern™ lately? E.g. There’s an easier way with Java 8! Just add this very useful tool to your Utilities or Helper class: Now you can wrap all your logic in this nice little wrapper: Done! Even better, in some cases, you can use method references
16 Feb 2016
Before I move on with the actual article, I’d like to give credit to Daniel Dietrich, author of the awesome vavr library, who has had the idea before me: @lukaseder try with a static method <T, T1 extends T, … Tn extends T> Seq<T> toSeq(T1 t1, …, Tn tn) { … } (from my mobile … Continue reading An Ingenious…
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:…
11 Feb 2016
Some APIs are set in stone. For instance, the JDK’s. Or public APIs, like the one between a database and a database client (e.g. JDBC). This makes designing such APIs rather difficult as a lot of thinking needs to be done prior to publishing an API. Which means that being defensive when designing the API … Continue reading Dear API…
9 Feb 2016
This is usually a tech blog, but every now and then, we make an exception when there’s something important to say. Today, I’m going to criticise a lot of our industry’s understanding of support. Who is this article for? It’s for every software engineer giving support to users and/or customers, and every manager who works … Continue reading The 5…
8 Feb 2016
One of the most awesome features of the Oracle database is Oracle AQ: Oracle Database Advanced Queuing. The AQ API implements a full fledged, transactional messaging system directly in the database. In a classic architecture where the database is at the center of your system, with multiple applications (some of which written in Java, others … Continue reading Using Oracle…
28 Jan 2016
Just now, we implemented a nice little feature in jOOQ’s code generator: https://github.com/jOOQ/jOOQ/issues/4974 It detects whenever the jOOQ code generator runs a slow query to reverse engineer schema meta information. Why? In our development and integration test environment, we don’t have huge schemas with all the different performance edge cases put in place. For instance, … Continue reading How to…
26 Jan 2016
Recently, we’ve published our article about the awesome window function support in jOOλ 0.9.9, which I believe is some of the best additions to the library that we’ve ever done. Today, we’ll look into an awesome application of window functions in a use-case that is inspired by this Stack Overflow question Sean Nguyen: How to … Continue reading How to…
20 Jan 2016
Sometimes, when we get pull requests for jOOQ or our other libraries, people change the code in our unit tests to be more “idiomatic JUnit”. In particular, this means that they tend to change this (admittedly not so pretty code): … into this, “better” and “cleaner” version: What have we gained? Nothing! Sure, we already … Continue reading Use JUnit’s…
18 Jan 2016
I’ve recently encountered a very interesting question on Stack Overflow by an unnamed user. The question was about generating a table of the following form in Oracle, using a table valued function: Description COUNT ------------------- TEST1 10 TEST2 15 TEST3 25 TEST4 50 The logic that should be implemented for the COUNT column is the … Continue reading Impress Your…
14 Jan 2016
Notice that the examples in this article may be outdated, as Typesafe’s Activator works differently now. The blog post will not be maintained to provide up-to-date Activator examples. We’re very happy to continue our a guest post series on the jOOQ blog by Manuel Bernhardt. In this blog series, Manuel will explain the motivation behind … Continue reading Reactive Database…
12 Jan 2016
Java has come a long way. A very long way. And it carries with it all the “junk” from early day design decisions. One thing that has been regretted time and again is the fact that every object (potentially) contains a monitor. This is hardly ever necessary and this flaw was corrected, finally, in Java … Continue reading If Java…