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:
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!
You need to add ‘@Join’ on the repository. https://micronaut-projects.github.io/micronaut-data/latest/guide/#hibernateJoinQueries