I'm using Jooq with Kotlin and i want to write a statement that fetches data from a query that uses couple of tables using join statement(example attached)

The problem I'm facing is that I want to map the result to my complex model which consist of one to many relationships and also many to many.

According to my knowledge I know i can use fetchgroups operation in Jooq to some how map the records but i still can't figure out how to get the result into my model.

my model:

data class MicroserviceDto(
  val microservice_id: Long = 1,
  val microservice_name: String? = "",
  val endpoint: String? = "",
  val mappings: String? = "",
  val solutionDefinitionMinimalDtoList: List<SolutionDefinitionDto> = emptyList(),
  val projectFileDtoList: List<ProjectFileDto> = emptyList()
)

data class SolutionDefinitionDto(
  val solution_definition_id: Long = 0L,
  val solution_definition_name: String = "",
  val solutionId: String = "",
  val solutionVersion: String = ""
)

data class ProjectFileDto(
  val project_file_id: Long = 1,
  val model: String = "",
  val relativePath: String = "",
  val fileContentDtoList: List<FileContentDto> = emptyList()
)

data class FileContentDto(
  val file_content_id: Long = 1,
  val content: ByteArray = ByteArray(0)
)

Link to my schema diagram Database Diagram visualization

Explanation of the diagram:

  1. Microservice has many to many relationship with SolutionDefinistion

  2. ProjectFile has one to many relationship with Microservice

  3. ProjectFile has one to many relationship with SolutionDefinition

  4. FileContent has one to many with ProjectFile

I've created a view to represent my desired query with all tables and the join statements between them.

Here is the View:

  CREATE OR REPLACE VIEW Microservice_Metadata_by_Microservice_Id AS
select
  # microservice
  M.id as `microservice_id`,
  M.name as `microservice_name`,
  M.mappings,
  M.endpoint,
  # solution definition
  SD.id as `solution_definition_id`,
  SD.name as `solution_definition_name`,
  SD.solutionId,
  SD.solutionVersion,
  # project file of microservice
  PF.id as `project_file_id`,
  PF.relativePath,
  PF.model,
  # file content data of project file
  FC.id as `file_content_id`,
  FC.content
from Microservice M
       # get project file
       left join Microservice_SolutionDefinition MSD
                 on MSD.microserviceId = M.id
       left join ProjectFile PF
                 on PF.microserviceId = M.id
  # get data content
       left JOIN FileContent FC
                 on PF.id = FC.projectFileId
  # get solutions of microservice
       left join SolutionDefinition SD
                 on SD.id = MSD.solutionDefinitionId;

How can I implement such a Jooq dsl query that map the ResultSet to my data model

0 Answers