How to do Hibernate ORM mapping for a 2D Array

Question

I am new to ORM and got stuck on the following issue (simplified for the discussion here):

I am modelling a tournament, with competitors and disciplines. Both have their own entity class. Competitors compete in each discipline exactly once, and receive a score. As long as a competitor has not yet competed in a given discipline, there is no score.

Data Model:

A straightforward DB design would be a scores table with foreign keys to both the competitors table and the disciplines table. That is, I would set up two one-to-many relationships, plus integrity constraints on the foreign keys - I cannot delete a competitor or a discipline as long as there are scores that reference either one.

But how do I map this 2D Array (competitors/disciplines) onto my classes? I am using Java and Hibernate. My current solution is to put a collection of scores into the Competitor entity class, and similarly for the Disciplines class. This creates a bidirectional relationship with a join table for each of the two entity classes. Is this the recommended way to do the mapping?

It does map the relationship form the perspective of each domain class, but it misses out on the 2D array structure. I want to output the entire array - on a UI, for example - with rows for competitors, columns for disciplines, and the scores in the corresponding table cell. Building such an output from the entity classes as just described is tedious and requires (a) to iterate through the competitor collection and then (b) look up the corresponding discipline - or the other way around.

Ideally, I would like to have a hash map with two keys, like the Guava Table, or a nested hash map. I suppose that there is no native Hibernate mapping for this kind of advanced collection. But maybe there is a best practice how to implement it using custom queries?

Answer 1

But how do I map this 2D Array (competitors/disciplines) onto my classes? I am using Java and Hibernate. My current solution is to put a collection of scores into the Competitor entity class, and similarly for the Disciplines class. This creates a bidirectional relationship with a join table for each of the two entity classes. Is this the recommended way to do the mapping?

IIRC, an implicit join table doesn't allow to add the score. Even if it did, I would't like it as the scores are actually the main information. So I'd go for an explicit table.

class Score {
    @ManyToOne(optional=false)
    Competitor competitor;
    @ManyToOne(optional=false)
    Discipline discipline;
}

This should provide everything you need. You may also want a Set<Score> or even Map<Discipline, Score> in the class Competitor (and vice versa in the other class), but you may not need it. The mapping would probably use @ManyToMany(mappedBy="competitor") and @MapKey... I haven't used it for long as I found out I don't really need it.

Ideally, I would like to have a hash map with two keys, like the Guava Table, or a nested hash map.

Using the default @ManyToOne(fetch=EAGER), the needed competitors and disciplines get fetched automatically using a JOIN. I'm afraid, a List is all you can get, but iterating it once and filling a Guava Table is trivial:

list.stream()
.forEach(score -> table.put(score.competitor, score.discipline, score));

Just don't forget that the entities are mutable but mustn't be mutated when used as keys. Obviously, you should only fetch the entities you need rather than filtering the Table. However, once you have the Table, you can use all its operations at will; Hibernate won't help you here anymore, but you don't need it (and you don't want to hit the DB again).