Quarkus and using jOOQ

This post is to try and read a simple DTO or Record object from a database using jOOQ within a Quarkus environment. The Quarkus environment is set up in a previous post: Quarkus Hibernate Stateless Session

jOOQ https://www.jooq.org/ is a very versatile library to make your sql programming life easy. You should watch “JOOQ, Joy of SQL by Kevin Davin” talk from Devoxx 2022 to get a good impression.

Adding jOOQ to your Quarkus project

In this post I just want to get jOOQ to work to perform some simple queries. Adding jOOQ to a Quarkus project is well documented at https://github.com/quarkiverse/quarkus-jooq

WARNING: I could not get it to work, unless I use an old Quarkus version.

What I did:

Add the driver setting to your application.properties:

# jOOQ specific setting
quarkus.jooq.dialect=H2

next to the other Quarkus database connection settings.

Add the maven dependency to your pom.xml:

<!-- jOOQ https://github.com/quarkiverse/quarkus-jooq -->    
<dependency>
    <groupId>io.quarkiverse.jooq</groupId>
    <artifactId>quarkus-jooq</artifactId>
    <version>0.3.0</version>
</dependency>

Now you can start programming using jOOQ:

import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.Result;

import jakarta.enterprise.context.ApplicationScoped;
import jakarta.inject.Inject;
import jakarta.transaction.Transactional;
import nl.escay.entity.PersonRecord;

@ApplicationScoped
public class JooqService {

    @Inject
    DSLContext dsl;
    
    @Transactional
    public void createPerson(Long id, String firstName, String lastName) {
        // Not using jOOQ generated Tables and Columns at this moment.
        // Try out the plain SQL api of jOOQ instead:
        // https://www.jooq.org/doc/latest/manual/sql-building/plain-sql/

        // TODO: Implement this method, but first implement the simple select.
        // @Transactional should work.
    }
    
    public List<PersonRecord> getPersonsAsRecord() {
        List<PersonRecord> personRecords = null;

        // Make plain SQL call using jOOQ
        Result<Record> fetchResult = dsl.fetch("select id, firstName, lastName from Person");
        
        // Convert Result (which implements java.util.List) to Record using stream api
        personRecords = fetchResult.stream()
                .map(tuple -> new PersonRecord(
                        (Integer) tuple.get(0), 
                        (String) tuple.get(1), 
                        (String) tuple.get(2)))
                .collect(Collectors.toList());
        
        System.out.println(Arrays.toString(personRecords.toArray()));
        return personRecords;
    }
}

Running the code however fails with:

Caused by: io.quarkus.builder.BuildException: Build failure: Build failed due to errors
	[error]: Build step io.quarkiverse.jooq.deployment.JooqProcessor#build threw an exception: java.lang.NoClassDefFoundError: javax/enterprise/context/ApplicationScoped
	at io.quarkiverse.jooq.deployment.JooqProcessor.createDslContextProducerBean(JooqProcessor.java:117)
	at io.quarkiverse.jooq.deployment.JooqProcessor.build(JooqProcessor.java:83)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
	at java.base/java.lang.reflect.Method.invoke(Method.java:578)
	at io.quarkus.deployment.ExtensionLoader$3.execute(ExtensionLoader.java:909)
	at io.quarkus.builder.BuildContext.run(BuildContext.java:281)
	at org.jboss.threads.ContextHandler$1.runWith(ContextHandler.java:18)
	at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2449)
	at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1478)
	at java.base/java.lang.Thread.run(Thread.java:1589)
	at org.jboss.threads.JBossThread.run(JBossThread.java:501)

Looking at issues at quarkus-jooq shows for example issue: https://github.com/quarkiverse/quarkus-jooq/issues/89 which states the quarkus-jooq integration is still using an old jOOQ 3.15.5 dependency. And the ‘quarkus-jooq’ plugin itself is also still using javax packages.

At this moment I do not want to spend time on a workaround by forking the quarkus-jooq code. It seems the lead developer of the quarkus-jooq plugin is not active anymore.

Workaround for quarkus-jooq javax

A workaround might be to use the latest jOOQ dependency and not use the quarkus-jooq integration.

  • Disadvantage: @Inject DSLContext is not available.
  • Disadvantage: Quarkus Native image creation is probably not possible.

Replace the ‘quarkus-jooq’ dependency with a direct jOOQ dependency:

    <!-- jOOQ directly, without quarkus integration -->    
    <dependency>
        <groupId>org.jooq</groupId>
        <artifactId>jooq</artifactId>
        <version>3.17.6</version>
    </dependency>

Replace the @Inject and use the AgroalDataSource instead of the DSLContext

import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.Result;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;

import io.agroal.api.AgroalDataSource;
import jakarta.enterprise.context.ApplicationScoped;
import jakarta.inject.Inject;

@ApplicationScoped
public class JooqService {

    @Inject
    AgroalDataSource defaultDataSource;
    
    public List<PersonRecord> getPersonsAsRecord() throws SQLException {
        Connection connection = defaultDataSource.getConnection();
        DSLContext dsl = DSL.using(connection, SQLDialect.H2);
        
        List<PersonRecord> personRecords = null;

        // Make plain SQL call using jOOQ
        Result<Record> fetchResult = dsl.fetch("select id, firstName, lastName from Person");
        
        // Convert Result (which implements java.util.List) to Record using stream api
        personRecords = fetchResult.stream()
                .map(tuple -> new PersonRecord(
                        (Integer) tuple.get(0), 
                        (String) tuple.get(1), 
                        (String) tuple.get(2)))
                .collect(Collectors.toList());
        
        System.out.println(Arrays.toString(personRecords.toArray()));
        return personRecords;
    }
}

And you can query data from the Quarkus provided connection using jOOQ libraries.

Inserting of data without jOOQ code generation is also possible using the jOOQ dsl. And the @Transaction annotation works. Example:

import static org.jooq.impl.DSL.table;

import java.sql.Connection;
import java.sql.SQLException;

import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.Result;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;

import io.agroal.api.AgroalDataSource;
import jakarta.enterprise.context.ApplicationScoped;
import jakarta.inject.Inject;
import jakarta.transaction.Transactional;

@ApplicationScoped
public class JooqService {

    @Inject
    AgroalDataSource defaultDataSource;

    @Transactional
    public void createPerson(Long id, String firstName, String lastName) throws SQLException {
        // Not using jOOQ generated Tables and Columns at this moment.
        // Try out the plain SQL api of jOOQ instead:
        // https://www.jooq.org/doc/latest/manual/sql-building/plain-sql/
        // https://www.jooq.org/doc/latest/manual/getting-started/use-cases/jooq-as-a-sql-builder-without-codegeneration/

        try (Connection connection = defaultDataSource.getConnection();) {
            DSLContext dsl = DSL.using(connection, SQLDialect.H2);
            
            dsl.insertInto(table("person"))
                .values(id, firstName, lastName)
                .execute();
        } catch (SQLException e) {
            throw e;
        }
    }
}

Wrapup

Github code for this example:

Things learned:

  • Using Sql without jOOQ generated code is possible.
  • Using jOOQ dependency without quarkus-jooq plugin is possible.
  • @Transactional works

Not tested / covered:

  • Complex jOOQ usage in combination with Quakrus.
  • jOOQ code generation, which is suggested by jOOQ is the ideal partner for jOOQ usage.
  • Correct resource closing, not sure if jOOQ requires some resources to be cleaned up.