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.
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:
Microservice has many to many relationship with SolutionDefinistion
ProjectFile has one to many relationship with Microservice
ProjectFile has one to many relationship with SolutionDefinition
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