jOOQ vs JdbcTemplate

A quick comparison between using jOOQ and Spring's JdbcTemplate to interact with a database.

Introduction

Not so long ago, I would have reached to Spring's JdbcTemplate to query databases.
It's 'richer' than the JDBC API and it would have been my preferred choice, for 'rapid interaction' with a database.
Today we'll see that jOOQ is an excellent alternative.
We are going to use a similar setup than in the previous article.

Overview

This is a gradle project, with the jOOQ gradle plugin, using Java 17.
From the previous project, we have removed Flyway.
We don't need it here as we'll be using an existing database.
We are using a PostgreSQL database from ElephantSQL, an excellent PostgreSQL as a Service provider, with a free offering to get started.
Therefore we have replaced the H2 database dependencies with those needed for PostgreSQL.

Directory structure

The directory structure is as follow:

Dependencies

We'll be using the JdbcTemplate, as well as Spring Boot auto-configuration and dependency injection features.
Therefore, we've added - as dependencies - 'spring-boot-starter-jdbc' and 'spring-boot-starter-test'.

The build.gradle file looks as follow:

plugins {
    id 'org.springframework.boot' version '3.2.1'
    id("application")
    id ("nu.studer.jooq") version("9.0")
}

version = "0.1"
group = "net.sammy"

repositories {
    mavenCentral()
}

dependencies {
    jooqGenerator("org.postgresql:postgresql:42.7.1")
    implementation("org.postgresql:postgresql:42.7.1")
    implementation("org.springframework.boot:spring-boot-starter-jdbc:3.2.1")
    testImplementation("org.springframework.boot:spring-boot-starter-test:3.2.1")
}

java {
    sourceCompatibility = JavaVersion.toVersion("17")
    targetCompatibility = JavaVersion.toVersion("17")
}

test {
    useJUnitPlatform()
}

jooq {
    configurations {
        main {
            generationTool {
                jdbc {
                    driver = 'org.postgresql.Driver'
                    url = 'jdbc:postgresql://<database_host>/<database_name>'
                    user = 'your_username'
                    password = 'your_password'
                }
                generator {
                    name = 'org.jooq.codegen.DefaultGenerator'
                    database {
                        name = 'org.jooq.meta.postgres.PostgresDatabase'
                        inputSchema = 'public'
                    }
                    generate {
                        deprecated = false
                        records = true
                        immutablePojos = true
                        fluentSetters = true
                    }
                    target {
                        packageName = 'net.sammy'
                        directory = 'build/generated-src/jooq/main'
                    }
                }
            }
        }
    }
}

tasks.named('generateJooq').configure { allInputsDeclared = true }

The database connection string, in the build.gradle file, is for the jOOQ code generation tool; not for the application.
For the application (which is a Spring Boot application), we put the database URL in the file application.yml.

# application.yml
spring:
  datasource:
    url: jdbc:postgresql://<database_host>/<database_name>
    username: 'your_username'
    password: 'your_password'

Note

Here we've added database details in the files build.gradle and application.yml, however, in a more realistic scenario, database details could be made available via environment variables.
For this article, it is more convenient to leave them in the files, otherwise, to run gradle tasks (like gradle generateJooq), run the jar file, or run the tests in the IDE, environment variables would be needed:

# application.yml
spring:
  datasource:
    url: 
    username: 
    password:

// build.gradle
...
            generationTool {
                jdbc {
                    driver = 'org.postgresql.Driver'
                    url = System.getenv("SPRING_DATASOURCE_URL")
                    user = System.getenv("SPRING_DATASOURCE_USERNAME")
                    password = System.getenv("SPRING_DATASOURCE_PASSWORD")
                }
...

Using JdbcTemplate

We leverage Spring Boot auto configuration; meaning Spring Boot will automatically create a Datasource and JdbcTemplate objects / beans.
The file Application.java is as follow:

package net.sammy;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

We can then 'autowire' spring beans in our code.
The file below is JdbcTemplatePostgresTest.java .

package net.sammy;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.Map;

@SpringBootTest
public class JdbcTemplatePostgresTest {
    @Autowired
    JdbcTemplate jdbcTemplate;

    @Test
    void testSelectActors() {
        var sql = "SELECT * FROM actor LIMIT 100";
        var list = jdbcTemplate.query(sql,
                (rs, n) -> Map.of(rs.getInt("actor_id"), rs.getString("first_name")));

        System.out.println(list);
    }
}

With JdbcTemplate the SQL query is a string and we need a 'row mapper', which here is a lambda expression.

Using jOOQ

Spring Boot helps us again by auto-configuring jOOQ's DSLContext, which we have 'autowired'.
Alternatively, you could 'autowire' a DataSource and use it to create a DSLContext.

Spoiler, you'll notice something unusual: the class cast.
The file below is JooqPostgresTest.java:

package net.sammy;

import org.jooq.DSLContext;
import org.jooq.Record1;
import org.jooq.Result;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import static net.sammy.tables.Actor.ACTOR;
import static net.sammy.tables.Film.FILM;
import static net.sammy.tables.FilmActor.FILM_ACTOR;

@SpringBootTest
public class JooqPostgresTest {
    @Autowired
    DSLContext context;

    @Test
    void testFetchUsingJooq() {
        Result<Record1<String>> result = context
            .select(FILM.TITLE).from(FILM)
            .leftJoin(FILM_ACTOR)
            .on(FILM.FILM_ID.eq(FILM_ACTOR.FILM_ID.cast(Integer.class)))
            .join(ACTOR).on(FILM_ACTOR.ACTOR_ID.eq(ACTOR.ACTOR_ID.cast(Short.class)))
            .where(ACTOR.FIRST_NAME.like("Penelope"))
            .fetch();

        System.out.println("** Results found **");
        System.out.println(result.map(Record1::component1));
    }
}

With jOOQ, the SQL query is type safe.
There is also a lot of methods available on the Result class to further transform the results.

Nonetheless, the 'elephant in the room' are the class casts.
I did not expect having issues with data types and I've left the code the way it is because I think that's something to be aware of.
I'm not sure doing a cast is what should be done, however I couldn't come up with anything else in order to compile the code (don't use that in production :).
jOOQ chose the data types during code generation, I didn't realise at first however, some of the generated IDs did not have the same types.
I think data types can be adjusted in the generateJooq task, however that will be for a future article.
For convenience I've put more details in the code snippets below, however the real generated code(s) are not in the same files.
Also, semi-colons are omitted (that stresses it's just for illustration).
Notice that some IDs are Integer and others Short.

 net.sammy.tables.Film
public final TableField<FilmRecord, Integer> FILM_ID
    = createField(DSL.name("film_id"), SQLDataType.INTEGER.nullable(false).identity(true), this, "")
...
 net.sammy.tables.FilmActor
public final TableField<FilmActorRecord, Short> FILM_ID
    = createField(DSL.name("film_id"), SQLDataType.SMALLINT.nullable(false), this, "")
...
 net.sammy.tables.Actor
public final TableField<ActorRecord, Integer> ACTOR_ID
    = createField(DSL.name("actor_id"), SQLDataType.INTEGER.nullable(false).identity(true), this, "")

I don't have privileges to see the database schema, therefore I didn't know what were the types of the fields.
Also, the data is sample data I didn't own.
Again, I found interesting that, at first, I had compilation errors when trying to do the SQL joins.
Therefore I've left the code as is, in case readers are faced with similar issues.
I still need to read jOOQ's documentation to find out what's the recommended way to handle (or prevent) those cases.

Conclusion

In this article we've used Spring Boot with JdbcTemplate and jOOQ, to query a database.
The query with JdbcTemplate is a bit simpler however it is enough to see that one has to use plain strings, whereas jOOQ queries are type safe and the IDE can assist.
Also when processing query results, jOOQ has again, I think, an advantage.

I hope to revisit this article, or make a follow up.
I initially intended to use a setup with 2 databases, however, I came across some issues with the 2-database setup.
Also having to use class cast, to compile the SQL joins, was unexpected.

I don't add a link to a code repository this time around, as I hope to change a few things in a future article.