I have 3 entities: User, Shelter and ShelterUsers
ShelterUsers is a join table for many to many relationship between Shelter and User with additional column. (design based on this suggestion)
I also have a @OneToMany relationship from User to Role which also uses a join table but without an additional field so there is no entity for that table
When I get the User from the database Roles are also attached with a join if I set the fetch to EAGER and they're loaded with additional query if I set fetch to LAZY (even without calling the user.getRoles() explicitly, which I also don't understand).
Problem is that ShelterUsers are not attached to user, no matter if I set it to EAGER or LAZY. After inspecting the sql that hibernate generates it seems like it generates the wrong sql:
First query:
SELECT user0_.id AS id1_7_0_,
user0_.created_at AS created_2_7_0_,
user0_.email AS email3_7_0_,
user0_.first_name AS first_na4_7_0_,
user0_.last_name AS last_nam5_7_0_,
user0_.password AS password6_7_0_,
user0_.updated_at AS updated_7_7_0_,
user0_.username AS username8_7_0_,
roles1_.user_id AS user_id1_8_1_,
role2_.id AS role_id2_8_1_,
role2_.id AS id1_4_2_,
role2_.name AS name2_4_2_
FROM users user0_
LEFT OUTER JOIN users_roles roles1_ ON user0_.id=roles1_.user_id
LEFT OUTER JOIN ROLES role2_ ON roles1_.role_id=role2_.id
WHERE user0_.id=?
Second query:
SELECT shelters0_.shelter_id AS shelter_3_6_0_,
shelters0_.id AS id1_6_0_,
shelters0_.id AS id1_6_1_,
shelters0_.shelter_id AS shelter_3_6_1_,
shelters0_.user_id AS user_id4_6_1_,
shelters0_.user_role AS user_rol2_6_1_,
user1_.id AS id1_7_2_,
user1_.created_at AS created_2_7_2_,
user1_.email AS email3_7_2_,
user1_.first_name AS first_na4_7_2_,
user1_.last_name AS last_nam5_7_2_,
user1_.password AS password6_7_2_,
user1_.updated_at AS updated_7_7_2_,
user1_.username AS username8_7_2_
FROM shelter_users shelters0_
LEFT OUTER JOIN users user1_ ON shelters0_.user_id=user1_.id
WHERE shelters0_.shelter_id=?
Where clause should be WHERE user1_.id = ?
Here is all relevant code:
User:
@Entity
@Data
@Table(name = "users")
public class User {
...
@OneToMany(fetch = FetchType.EAGER)
@JoinTable(name = "users_roles",
joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"),
inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id")
)
private Collection<Role> roles = new ArrayList<>();
@OneToMany(mappedBy = "shelter")
private Collection<ShelterUsers> shelters = new ArrayList<>();
...
}
Shelter:
@Entity
...
@Table(name = "shelters")
public class Shelter {
...
@OneToMany(mappedBy = "user")
private List<ShelterUsers> users = new ArrayList<>();
}
ShelterUsers:
@Entity
public class ShelterUsers {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@ManyToOne
@JoinColumn(name = "user_id", referencedColumnName = "id")
private User user;
@ManyToOne
@JoinColumn(name = "shelter_id", referencedColumnName = "id")
private Shelter shelter;
@Enumerated(EnumType.STRING)
@Column(name = "user_role")
private ShelterUserRole userRole;
}
Recap:
Roles get loaded with user no matter the fetch type.
ShelterUsers are not loaded
JSON example:
{
...
"roles": [
{
"id": 1,
"name": "ROLE_ADMIN"
}
],
"shelters": [],
...
}
What I would ideally want to achieve is this:
{
"roles": [
{
"id": 1,
"name": "ROLE_ADMIN"
}
],
"shelters": [
{
//Shelter entity (not ShelterUser)
},
]
}