0

I currently have a named native query set up in CrudRepository where I'm joinnig few tables and I need to map that query result into a Dto.

select
event_id, replaced_by_match_id, scheduled, start_time_tbd, status, away_team_competitor_id, home_team_competitor_id, round_round_id, season_season_id, tournament_tournament_id, venue_venue_id,
competitorHome.competitor_id as home_competitor_competitor_id, competitorHome.abbreviation as home_competitor_competitor_abbreviation, competitorHome.country_code as home_competitor_ccountry_code, competitorHome.ioc_code as home_competitor_ioc_code, competitorHome.rotation_number as home_competitor_rotation_number, competitorHome.virtual as home_competitor_virtual,
competitorAway.competitor_id as away_competitor_competitor_id, competitorAway.abbreviation as away_competitor_competitor_abbreviation, competitorAway.country_code as away_competitor_ccountry_code, competitorAway.ioc_code as away_competitor_ioc_code, competitorAway.rotation_number as away_competitor_rotation_number, competitorAway.virtual as away_competitor_virtual,
homeTeamTranslation.competitor_competitor_id as home_team_translation_competitor_competitor_id, homeTeamTranslation.language_language_id as home_team_translation_language_language_id, homeTeamTranslation.competitor_name as home_team_translation_competitor_name, homeTeamTranslation.competitor_country as home_team_competitor_country,
awayTeamTranslation.competitor_competitor_id as away_team_translation_competitor_competitor_id, awayTeamTranslation.language_language_id as away_team_translation_language_language_id, awayTeamTranslation.competitor_name as away_team_translation_competitor_name, awayTeamTranslation.competitor_country as away_team_competitor_country
from "event" as e
left join competitor as competitorAway on competitorAway.competitor_id = e.away_team_competitor_id
left join competitor as competitorHome  on competitorHome.competitor_id  = e.home_team_competitor_id
left join competitor_translation as homeTeamTranslation on competitorHome.competitor_id = homeTeamTranslation.competitor_competitor_id
left join competitor_translation as awayTeamTranslation on competitorAway.competitor_id = awayTeamTranslation.competitor_competitor_id
where awayTeamTranslation.language_language_id  = 'en' and homeTeamTranslation.language_language_id = 'en'

I'm trying to use @SqlResultSetMapping annotation to map result into Dto classes but unsuccessfully.

I've set up mapping this way

@SqlResultSetMapping(
    name = "mapLocalizedEvent",
    classes = [ConstructorResult(
            targetClass = TranslatedLocalEvent::class,
            columns = arrayOf(
                    ColumnResult(name = "event_id"),
                    ColumnResult(name = "scheduled"),
                    ColumnResult(name = "start_time_tbd"),
                    ColumnResult(name = "status"),
                    ColumnResult(name = "replaced_by_match_id")
            )
    )]
)

and it is working fine where all of the ColumnResult used are simple types String or Boolean. It maps to object TranslatedLocalEvent looking like this

class TranslatedLocalEvent(
    val eventId: String? = null,
    val scheduled: String? = null,
    val startTimeTbd: Boolean? = null,
    val status: String? = null,
    val replacedByMatchId: String? = null
)

Is there a way I can use this approach to map a complex object? TranslatedLocalEvent object needs to contain TranslatedLocalCompetitor object built from parts of columns query returnes

class TranslatedLocalEvent(
    val eventId: String? = null,
    val scheduled: String? = null,
    val startTimeTbd: Boolean? = null,
    val status: String? = null,
    val replacedByMatchId: String? = null,
    val homeTeam: TranslatedLocalCompetitor? = null
)


public class TranslatedLocalCompetitor(
    val competitorId: String? = null
    val competitorName: String? = null
    val competitorCountry: String? = null
)
lmiskovic
  • 109
  • 1
  • 10
  • https://thoughts-on-java.org/result-set-mapping-complex-mappings/ – Robert Niestroj Jan 16 '20 at 09:58
  • 1
    In that article EntityResult annotation is used to map query response to object with type of class anotated with Entity annotation ("Book" in article). I need to use ConstructorResult annotation as want to map into Dto (non entity) class. – lmiskovic Jan 16 '20 at 11:14

1 Answers1

0

The easiest way i see is in your TranslatedLocalEvent constructor accept all columns and in the contstructor create and assign the TranslatedLocalCompetitor object.

Robert Niestroj
  • 15,299
  • 14
  • 76
  • 119
  • I agree. For more complex classes (ones with more properties) code will look a bit messier but currenty there is no any way (I'm familiar with) for SpringBoot to take care of this. EDIT: Only thing I would like to mention is it is required to take care of constructor parameter order, and for Kotlin take care of what will be constructor property and what will be a constructor parameter. – lmiskovic Jan 16 '20 at 16:01