Quarkus FluentJdbc

This extension integrates FluentJdbc with Quarkus by making use of Agroal as the default connection pool.

Furthermore, it adds the following enhancements to it:

  • a RecordMapper class which can be used to map query results into Java Records,

  • a JsonObjectMapper which can be used to map queries into the JsonObject that comes with Quarkus,

  • a DynamicQuery class for writing conditional queries.

Installation

If you want to use this extension, you need to add the io.quarkiverse.fluentjdbc:quarkus-fluentjdbc extension first to your build file.

For instance, with Maven, add the following dependencies to your POM file:

<dependency>
    <groupId>io.quarkiverse.fluentjdbc</groupId>
    <artifactId>quarkus-fluentjdbc</artifactId>
    <version>1.0.0</version>
</dependency>

<dependency>
    <groupId>io.quarkus</groupId>
    <artifactId>quarkus-jdbc-postgresql</artifactId>
</dependency>

Usage

Define your datasource:

quarkus.datasource.db-kind=postgresql
%prod.quarkus.datasource.jdbc.url=jdbc:postgresql://localhost:5432/fruits
%prod.quarkus.datasource.username=sa
%prod.quarkus.datasource.password=sa

quarkus.datasource.devservices.port=5431

Now you can use FluentJdbc as follows:

import io.quarkiverse.fluentjdbc.runtime.RecordMapper;

@Path("/fruits")
public class FruitResource {

    static final RecordMapper fruitMapper = new RecordMapper(Fruit.class);

    @Inject
    FluentJdbc jdbc;

    @GET
    public List<Fruit> findAll(@RestQuery @DefaultValue("0") int start, @RestQuery @Min(1) @Max(200) @DefaultValue("50") int size) {
        return this.jdbc.query()
                .select("select * from fruit where id > ? order by id")
                .params(start)
                .maxRows(size)
                .listResult(fruitMapper);
    }
}

The RecordMapper maps ResultSets into Java Records.It will also automatically map the database column names into camel case in Java.

So if you have "external_id" in the database, this will be automatically mapped to "externalId".

You can also disable this functionality:

RecordMapper recordMapper = new RecordMapper(Fruit.class, false);

Define the Fruit DTO:

public record Fruit(
        Long id,
        UUID externalId,
        String name,
        String type,
        BigDecimal calories,
        BigDecimal carbohydrates,
        BigDecimal fiber,
        BigDecimal sugars,
        BigDecimal fat,
        BigDecimal protein
) {
}

You can also select only the columns that are defined in the Fruit record:

import io.quarkiverse.fluentjdbc.runtime.RecordMapper;

@Path("/fruits")
public class FruitResource {

    static final RecordMapper fruitMapper = new RecordMapper(Fruit.class);

    @Inject
    FluentJdbc jdbc;

    @GET
    public List<Fruit> findAll(@RestQuery @DefaultValue("0") int start, @RestQuery @Min(1) @Max(200) @DefaultValue("50") int size) {
        return this.jdbc.query()
                .select("select %s from fruit where id > ? order by id".formatted(fruitMapper.columnNames()))
                .params(start)
                .maxRows(size)
                .listResult(fruitMapper);
    }
}

If you don’t use Java Records, but POJO’s instead then you can use the ObjectMappers that comes with FluentJdbc:

import org.codejargon.fluentjdbc.api.mapper.ObjectMappers;

@Path("/fruits")
public class FruitResource {

    @Inject
    ObjectMappers objectMappers;

    @Inject
    FluentJdbc jdbc;

    @GET
    public List<MyFruit> findAll(@RestQuery @DefaultValue("0") int start, @RestQuery @Min(1) @Max(200) @DefaultValue("50") int size) {
        return this.jdbc.query()
                .select("select * from fruit where id > ? order by id")
                .params(start)
                .maxRows(size)
                .listResult(this.objectMappers.forClass(MyFruit.class));
    }
}

Note: you need to create a producer (@Produce) for the ObjectMappers.

Dynamic Queries

If you need to create dynamic queries where certain parameters can be empty, then you can use DynamicQuery as follows:

@GET
@Path("/search")
public List<Fruit> search(@BeanParam @Valid SearchCriteria criteria) {
       var queryResult = new DynamicQuery()
                .selectClauses(
                        "lower(name) = lower(:name)", (1)
                        "lower(type) = lower(:type)",
                        "calories", (2)
                        "carbohydrates" (2)
                )
                .paramsFromDto(criteria) (3)
                .build();

        return this.jdbc.query()
                .select("select * from fruit %s order by id".formatted(queryResult.query()))
                .params(queryResult.parameters())
                .listResult(fruitMapper);
}
1 clause with a named parameter,
2 calories and carbohydrates is a shorthand for calories = :calories, carbohydrates = :carbohydrates respectively.
3 the parameters from the DTO will be read and only the non-null values will be returned

Note that all clauses are optional. They are only evaluated when their corresponding parameters are not null.

You can also use it to create dynamic update statements:

    @PUT
    @Path("/{id}")
    public RestResponse<Void> update(@RestPath @Min(1) Long id, @Valid FruitPUT fruit) {
        var queryResult = new DynamicQuery()
                .updateClauses("name", "type", "calories", "carbohydrates", "fiber", "sugars", "fat", "protein") (1)
                .where("id") (1)
                .paramsFromDto(fruit, id) (2)
                .build();

        var count = this.jdbc.query()
                .update("update fruit %s".formatted(queryResult.query()))
                .params(queryResult.parameters())
                .run()
                .affectedRows();

        if (count > 0) {
            Log.infof("%d fruits updated", count);
        }

        return RestResponse.ok();
    }
1 these are all again shorthand notations
2 this will first evaluate the values from the DTO, then add the id to the end. So that the parameters returned will start from: name till id.

For more examples, see links below.

Extension Configuration Reference

Configuration property fixed at build time - All other configuration properties are overridable at runtime

Configuration property

Type

Default

quarkus.fluentjdbc.transaction-isolation

the default transaction isolation level.

Environment variable: QUARKUS_FLUENTJDBC_TRANSACTION_ISOLATION

none, read-committed, read-uncommitted, repeatable-read, serializable

quarkus.fluentjdbc.batch-size

The default batch size for batch queries. Default is 50.

Environment variable: QUARKUS_FLUENTJDBC_BATCH_SIZE

int

50

quarkus.fluentjdbc.fetch-size

The default fetch size for queries. Default is 20.

Environment variable: QUARKUS_FLUENTJDBC_FETCH_SIZE

int

20

Configuring SqlErrorHandler, ObjectMappers, ParamSetters & AfterQueryListener

The easiest way to configure these are by exposing them as CDI beans:

    @Produces
    @Singleton
    @Unremovable
    public SqlErrorHandler errorHandler() {
        return (err, query) -> {
            Log.errorf("Error occured while executing query: %s, state: %s, code: %s",
                    query.orElse("no query found"),
                    err.getSQLState(),
                    err.getErrorCode());

            if (err.getErrorCode() == 123) {
                return SqlErrorHandler.Action.RETRY;
            }
            throw err;
        };
    }

    @Produces
    @Singleton
    @Unremovable
    public AfterQueryListener queryListener() {
        return execution -> {
            if (execution.success()) {
                Log.debugf("Query took %s ms to execute: %s",
                        execution.executionTimeMs(),
                        execution.sql()
                );
            }
        };
    }

    @Produces
    @Singleton
    public ObjectMappers objectMappers() {
        return ObjectMappers.builder().build();
    }

    @Produces
    @Singleton
    @Unremovable
    public ParamSetter<UUID> uuidParamSetter() {
        return (uuid, prepStmt, i) -> prepStmt.setString(i, uuid.toString());
    }

    @Produces
    @Singleton
    @Unremovable
    public ParamSetter<MyOtherType> otherParamSetter() {
        return (other, prepStmt, i) -> prepStmt.setString(i, other.toString());
    }

More information: