An Efficient Way to Check for Existence of Multiple Values in SQL
JOOQ
by lukaseder
1w ago
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 ..read more
Visit website
A Hidden Benefit of Implicit Joins: Join Elimination
JOOQ
by lukaseder
1M ago
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 ..read more
Visit website
JOOQ 3.19’s new Explicit and Implicit to-many path joins
JOOQ
by lukaseder
1M ago
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 ..read more
Visit website
Workaround for MySQL’s “can’t specify target table for update in FROM clause” Error
JOOQ
by lukaseder
1M ago
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 ..read more
Visit website
JOOQ 3.19.0 Released with DuckDB, Trino, Oracle 23c support, join path improvements, an official gradle plugin, commercial maven repositories, policies, UDT paths, trigger meta data, hierarchies, and much more
JOOQ
by lukaseder
1M ago
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 ..read more
Visit website
Maven Coordinates of the most popular JDBC Drivers
JOOQ
by lukaseder
1M ago
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 ..read more
Visit website
To DAO or not to DAO
JOOQ
by lukaseder
1M ago
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 ..read more
Visit website
JDBC Connection URLs of the Most Popular RDBMS
JOOQ
by lukaseder
3M ago
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 ..read more
Visit website
How to Pass a Table Valued Parameter to a T-SQL Function with jOOQ
JOOQ
by lukaseder
10M ago
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 ..read more
Visit website
How to Turn a List of Flat Elements into a Hierarchy in Java, SQL, or jOOQ
JOOQ
by lukaseder
1y ago
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 ..read more
Visit website

Follow JOOQ on FeedSpot

Continue with Google
Continue with Apple
OR