kotlinjpajdbcmicronaut-data

Why are my joined tables not being queried?


I'm creating a simple database of tournaments and the matches in them. I have created 4 tables: Players, Matches, Tournaments and Tournament Rounds. Each tournament has multiple rounds, each round has multiple matches, and each match has exactly two players. Each tournament should also keep track of all the players registered in it. I have created an Entity for each of these tables, with two expectations:

  1. Saving a Tournament entity would also create/update the child players, rounds etc.
  2. Fetching a Tournament (via a repository) would also immediately fill it with all the child data

Expectation number one works, expectation number two doesn't.

I am using micronaut data JDBC. Here are my (simplified) definitions of the tables:

@MappedEntity(value = "player")
data class PlayerEntity(
    @field:Id @GeneratedValue val id: Int? = null,
)
@MappedEntity(value = "match")
data class MatchEntity(
    @field:Id @GeneratedValue val id: Int? = null,

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "player_a_id", nullable = false, columnDefinition = "int")
    val playerA: PlayerEntity,

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "player_b_id", nullable = false, columnDefinition = "int")
    val playerB: PlayerEntity,

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "tournament_round_id", nullable = false, columnDefinition = "int")
    val tournamentRound: TournamentRoundEntity,
)
@MappedEntity(value = "tournament_round")
data class TournamentRoundEntity(
    @field:Id @GeneratedValue val id: Int? = null,

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "tournament_id")
    val tournament: TournamentEntity,

    @OneToMany(mappedBy = "tournamentRound", orphanRemoval = true, cascade = [CascadeType.ALL], fetch = FetchType.LAZY)
    val matches: List<MatchEntity>,
)
@MappedEntity(value = "tournament")
data class TournamentEntity(
    @field:Id
    @GeneratedValue
    val id: Int? = null,

    @JoinTable(name = "tournament_player",
        joinColumns = [JoinColumn(name = "tournament_id", referencedColumnName = "id")],
        inverseJoinColumns = [JoinColumn(name = "player_id", referencedColumnName = "id")]
    )
    @ManyToMany(cascade = [CascadeType.ALL])
    val players: List<PlayerEntity>,

    @OneToMany(mappedBy = "tournament", orphanRemoval = true, cascade = [CascadeType.ALL])
    val tournamentRounds: List<TournamentRoundEntity>,
)

All my repositories look similar to this:

@JdbcRepository(dialect = Dialect.MYSQL)
@Transactional(Transactional.TxType.MANDATORY)
interface TournamentRepository : CrudRepository<TournamentEntity, Int>, JpaSpecificationExecutor<TournamentEntity>

Saving a tournament also correctly creates the appropriate records in the associated tables. However fetching a tournament (via tournamentRepository.findAll() or tournamentRepository.findById(id)) does not fetch anything from the associated tables. Using query logging, I can see that it doesn't even attempt to do so in any way:

// This is what happens when I call tournamentRepository.findAll()
DEBUG io.micronaut.data.query - Executing Query: SELECT tournament_entity_.`id` FROM `tournament` tournament_entity_

I have tried messing around with the annotations with no result. I will gladly appreciate any kind of help!


Solution

  • You need to add ‘@Join’ on the repository. https://micronaut-projects.github.io/micronaut-data/latest/guide/#hibernateJoinQueries