lukaseder    A cool standard SQL: 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...

lukaseder    I’ve blogged about generic ways of getting top 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...

lukaseder    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...

lukaseder    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...

lukaseder    jOOQ . finally delivers on a set of features that will greatly simplify your queries further, after jOOQ . introduced implicit to one joins: What are these features Many ORMs e.g. JPA, Doctrine, jOOQ . and others support path joins they may have different names for this concept . A...

lukaseder    In MySQL, you cannot do this: The UPDATE statement will raise an error as follows: SQL Error HY : 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...

lukaseder    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...

lukaseder    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...

lukaseder    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...

lukaseder    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:

lukaseder    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...

lukaseder    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...

lukaseder    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 C: eclipse configuration dropins features ...

lukaseder    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 R DBC proxy for an existing...

lukaseder    jOOQ . 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...

lukaseder    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 ...

lukaseder    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...

lukaseder    One of MySQL ’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 ...

lukaseder    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...

lukaseder    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 . . , . . , and . ., a LoggingConnection is...

lukaseder    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...

lukaseder    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...

lukaseder    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...

lukaseder    Starting with jOOQ ., type safe implicit JOIN have been made available, and they’ve been enhanced to be supported also in DML statements in jOOQ . . 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...

lukaseder    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...

lukaseder    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,...

lukaseder    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...

lukaseder    Starting with jOOQ . and , there may be a compilation error with a message like this in your jOOQ generated code: ERROR DefaultCatalog.java: , cannot find symbol ERROR symbol: variable VERSION ERROR location: class org.jooq.Constants Typically, this error is mixed with...

lukaseder    Since jOOQ ., 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...

lukaseder    Starting with jOOQ ., 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...

lukaseder    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...

lukaseder    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...

lukaseder    The H 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 th, which is quite impressive, as this rank outperforms products like: CockroachDB Ignite Single Store previously MemSQL Interbase ...

lukaseder    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...

lukaseder    One of jOOQ . s coolest new features are client side computed columns. jOOQ . 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...

lukaseder    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...

lukaseder    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...

lukaseder    It’s been a while since jOOQ . 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...

lukaseder    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...

lukaseder    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...

lukaseder    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 call made to them by default. The Javadoc of the method says: Gives the JDBC driver a hint as to the number...

lukaseder    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...

lukaseder    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...

lukaseder    Since jOOQ . , we have an API that simplifies transactional logic on top of JDBC in jOOQ, and starting from jOOQ . and , an equivalent API will also be made available on top of R DBC, for reactive applications. As with everything jOOQ, transactions are implemented using explicit, API...

lukaseder    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...

lukaseder    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...

lukaseder    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 ...

lukaseder    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 What does the feature mean, specifically The awesome PostgreSQL...

lukaseder    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...

lukaseder    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...