How to Integration Test Stored Procedures with jOOQ

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: public static int add(int a, int b) { return a + b; … Continue reading How to Integration Test Stored Procedures with jOOQ

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

Why You Should Use jOOQ With Code Generation

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 Should Use jOOQ With Code Generation

Vendor Agnostic, Dynamic Procedural Logic with jOOQ

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, Dynamic Procedural Logic with jOOQ

MySQL’s allowMultiQueries flag with JDBC and jOOQ

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: try (Statement s = connection.createStatement()) { try { s.execute("create table t (i int);"); // This doesn't work, by default: s.executeUpdate(""" insert into t values (1); insert … Continue reading MySQL’s allowMultiQueries flag with JDBC and jOOQ

Translating Stored Procedures Between Dialects

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: -- Some dialect that supports this create table if not exists t (i varchar(10)); -- Db2 begin declare continue handler for … Continue reading Translating Stored Procedures Between Dialects

jOOQ Internals: Pushing up SQL fragments

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: Pushing up SQL fragments

The Cost of JDBC Server Roundtrips

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 of JDBC Server Roundtrips

jOOQ 3.10 Supports SQL Server’s Table Valued Parameters

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: CREATE TYPE numbers AS TABLE (i … Continue reading jOOQ 3.10 Supports SQL Server’s Table Valued Parameters

Using Stored Procedures With JPA, JDBC… Meh, Just Use jOOQ

The current edition of the Java magazine has an article about Big Data Best Practices for JDBC and JPA by Josh Juneau: http://www.javamagazine.mozaicreader.com/MayJune2016 The article shows how to use a stored procedure with JDBC (notice how resources aren't closed, unfortunately. This is commonly forgotten, even in Java Magazine articles) // Using JDBC to call upon … Continue reading Using Stored Procedures With JPA, JDBC… Meh, Just Use jOOQ