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
Tag: JDBC
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
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
The Many Ways to Return Data From SQL DML
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: CREATE TABLE actor ( … Continue reading The Many Ways to Return Data From SQL DML
The Best Way to Call Stored Procedures from Java: With jOOQ
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 Way to Call Stored Procedures from Java: With jOOQ
The Many Different Ways to Fetch Data in jOOQ
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: Result<Record1<String>> result = ctx.select(BOOK.TITLE) .from(BOOK) .fetch(); for (Record1<String> record : result) { // ... } It fetches the entire result set into memory … Continue reading The Many Different Ways to Fetch Data in jOOQ
Setting the JDBC Statement.setFetchSize() to 1 for Single Row Queries
An interesting hint by Vladimir Sitnikov has made me think about a new benchmark for jOOQ: https://twitter.com/lukaseder/status/1407662449331949568 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 … Continue reading Setting the JDBC Statement.setFetchSize() to 1 for Single Row Queries
Nested Transactions in jOOQ
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 in jOOQ
Using jOOQ’s DiagnosticsConnection to detect N+1 Queries
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 runN 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 allow … Continue reading Using jOOQ’s DiagnosticsConnection to detect N+1 Queries
Ad-hoc Data Type Conversion with jOOQ 3.15
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 allow … Continue reading Ad-hoc Data Type Conversion with jOOQ 3.15