Implementing hierarchical data structures with JPA (fixed depth)

Question

I have a hierarchical data structure with a fixed depth of 4. For a better understanding, let's assume the following (just an example): - The "root" level is called countries - Each country contains an arbitrary amount of states - Each state countains an arbitrary amount of counties - Each county contains an arbitrary amount of cities So there are always 1-N relationships between the levels. A very important usecase (given the id of a country) is to load the whole "content" of a country at once with the smallest possible impact on the performance of the database. In a first naive approach, I created 4 entitiy classes in Java where the entity "Country" contains a list of the type "State", the entity "State" contains a list of the type "County" and so on... But what JPA creates afterwards are of course not 4 tables, but 7 (4 for the entities + 3 for the connection between the levels due to 1-N). I don't know if this is a good solution since there is a lot of joining going on under the h…

Accepted Answer

You can avoid the 3 extra mapping tables by using the `@JoinColumn` annotation rather than the `@JoinTable` annotation that I suspect you are using.

So for example:

COUNTRY

```
@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER, mappedBy="country")
private List<State> stateList;
```

STATE

```
@ManyToOne
@JoinColumn(name="country_id")
private Country country
```

The database tables would be as follows:

Country

```
country_id => primary key
```

State

```
state_id => primary key
country_id => foreign key
```

This way the mapping tables between all the 4 entities can be avoided.

Implementing hierarchical data structures with JPA (fixed depth)

Solution

Problem

Related problems