Java's package private visibility is an underrated feature. When you omit any visibility modifier in Java, then the default (for most objects) is package private, i.e. the object is visible only to types in the same package: class YouDontSeeMe {} class YouDontSeeMeEither {} In fact, a compilation unit (the .java file) can contain multiple such … Continue reading How to Generate Package Private Code with jOOQ’s Code Generator
Tag: jooq
How to Pass a Table Valued Parameter to a T-SQL Function with jOOQ
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: CREATE TYPE u_number_table AS TABLE (column_value INTEGER); CREATE FUNCTION f_cross_multiply ( @numbers u_number_table READONLY ) RETURNS @result TABLE ( i1 INTEGER, i2 … Continue reading How to Pass a Table Valued Parameter to a T-SQL Function with jOOQ
How to use jOOQ’s Converters with UNION Operations
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: // Without the converter, assuming BOOK.ID … Continue reading How to use jOOQ’s Converters with UNION Operations
How to Write a Derived Table in jOOQ
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: SELECT nested.* FROM ( SELECT AUTHOR_ID, count(*) books FROM BOOK GROUP BY AUTHOR_ID ) nested ORDER BY nested.books DESC In jOOQ: // Declare the … Continue reading How to Write a Derived Table in jOOQ
Why You Should Execute jOOQ Queries With jOOQ
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: JPA JDBC / … Continue reading Why You Should Execute jOOQ Queries With jOOQ
jOOQ’s R2DBC LoggingConnection to log all SQL statements
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 LoggingConnection to log all SQL statements
When to Use jOOQ and When to Use Native SQL
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: ctx.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count()) .from(AUTHOR) .join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID)) .groupBy(AUTHOR.ID, AUTHOR.FIRST_NAME, … Continue reading When to Use jOOQ and When to Use Native SQL
Calling Procedures with Default Parameters using JDBC or jOOQ
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: CREATE OR REPLACE PROCEDURE p ( p_i1 IN number, p_o1 OUT number, p_i2 IN varchar2, p_o2 OUT varchar2 ) IS BEGIN p_o1 := p_i1; p_o2 := p_i2; … Continue reading Calling Procedures with Default Parameters using JDBC or jOOQ
Using jOOQ’s Implicit Join From Within the JOIN .. ON Clause
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 Implicit Join From Within the JOIN .. ON Clause
A Brief Overview over the Most Common jOOQ Types
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 Overview over the Most Common jOOQ Types