0

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) 
      },  
    ]
}
Nermink
  • 1
  • 2
  • What exactly is your question? Please formulate it and update the original post. Thanks. – MWiesner Jan 06 '22 at 13:18
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Jan 07 '22 at 14:26

0 Answers0