JOOQ SUM in Kotlin with custom types

Question

currently I have a converter to work with value objects in JOOQ with Kotlin.

My database has a table called transactions with a field type decimal(10, 2) called billing _amount and on my Kotlin code I have a simple ValueObject from DDD to wrap it up, called BillingAmount defined as following

data class BillingAmount(val value: BigDecimal)

As for my jooq custom converter I have the following code:

public final TableField<TransactionsRecord, BillingAmount> BILLING_AMOUNT =
        createField(
                DSL.name("billing_amount"),
                org.jooq.impl.SQLDataType.NUMERIC(10, 2).nullable(false),
                this,
                "",
                org.jooq.Converter.ofNullable(
                    java.math.BigDecimal.class,
                    okano.dev.jooqtesting.BillingAmount.class,
                    t -> new okano.dev.jooqtesting.BillingAmount(t),
                    u -> u.getValue()
                )
        );

On my repository, I'm just trying to retrieve a sum a billing amounts, but jooq is complaining that BillingAmount doesn't extend a Number class. I know that is a Java generic validation that is preventing my code from working, but there's any way around, except by extending the Number class, for solving this problem? I thought that the converter should be enough, but for sure I'm wrong about this.

Here's the simple query I'm trying to achieve:

// jooq is an instance of DSLContext
return jooq.select(sum(TABLE.BILLING_AMOUNT))
            .from(TABLE)
            .fetchSingle()

Any thoughts on this question? Thanks in advance.

Answer 1

I assume that problems you're experiencing is just due to Java's type system.

If so, you can simply coerce the field to a different type for a query, like so (admittedly, muddying the query defining code):

BillingAmount sum = jooq
  .select(sum(TABLE.BILLING_AMOUNT.coerce(BigDecimal.class)))
  .from(TABLE)
  .fetchSingle(TABLE.BILLING_AMOUNT);

It's not quite the same type of result as in original query, though, because it materializes BillingAmount directly, and not a Record<BillingAmount> (because after coercion the query return type would be Record<BigDecimal>.