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 hood.

I also tried to map the subtypes to their parent types (a city belongs to one county, a county belongs to one state, a state belongs to one country). This results in 4 tables, but makes it more difficult to retrieve all data at once from the application's point of view. If I'm not wrong, I would need 4 different requests instead of one.

How could I solve this problem? Is there a way to combine a simple table layout (with four tables, not seven) with easy to use entity classes (a parent type should know its children)? If not, how would you realize this?

I'm using JPA with Hibernate and PostgreSQL.

Answer 1

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.

Answer 2

You can achieve this pretty easily using JPQL:

SELECT DISTINCT country
FROM Country country
JOIN FETCH country.states states
JOIN FETCH states.counties counties
JOIN FETCH counties.cities cities
WHERE country.id = :countryId

Using fetchType = FetchType.EAGER on @OneToMany/@ManyToOne(believe that one is already EAGER by default) will achieve similar results.