jOOQ 3.16 fetch one to one

user_7801732 0
Viewed 73

I want to fetch a DTO as follows:

data class CartItemDto(
    val id: UUID
    val product: ProductDTO 
)

The only way I can currently achieve is is by doing two separate queries and then combining those two results into the data class above, but I want to do everything in one jOOQ query. Multiset is not an option and whenever we use jsonObject in a custom field, it won't work.

The only examples I can find on the internet are for DTO's with lists:

data class CartItemDto(
    val id: UUID
    val product: List<ProductDto> 
)

Extra information

ProductDto:

data class ProductDto(
    val id: UUID
    val title: String
)

Current query:

databaseRepository.getConnection()
                .select(CART_ITEM.CART_ID,
                        row(
                            PRODUCT.ID,
                        ).mapping(::ProductDto))
                .from(CART_ITEM)
                .join(PRODUCT)
                .on(PRODUCT.ID.eq(CART_ITEM.PRODUCT_ID))
                .fetch(Records.mapping(::CartItemDto))

But this will only work if I make the data class fields nullable.

jooq kotlin postgresql
1 Answers

The jOOQ way to map to that data class is to use nested records, e.g.

ctx.select(PRODUCT.ID, row(PRODUCT.A, PRODUCT.B, ...).mapping(::ProductDTO))
   .from(PRODUCT)
   .fetch(Recods.mapping(::DTO)) // Your data class, whatever it's called

This is the type safe and recommended way. Starting from jOOQ 3.17, you can even project the entire T table as a nested record, e.g.

ctx.select(PRODUCT.ID, PRODUCT.mapping(::ProductDTO))
   .from(PRODUCT)
   .fetch(Recods.mapping(::DTO)) // Your data class, whatever it's called

Alternatively, you can use the classic approach using reflection and nested column aliases:

ctx.select(
        PRODUCT.ID,
        PRODUCT.A.as("product.a"),
        PRODUCT.B.as("product.b"))
   .from(PRODUCT)
   .fetchInto(DTO::class.java) // Your data class again

But I recommend using the type safe approach instead, which also works well along with MULTISET.

user_5217990
Related