Spring + Hibernate: How do I efficiently chain two link tables and include resulting data in single entity? (user-role-right)
<p><strong>Short version</strong></p>

<p>I have a basic setup where a user table is linked to a role table and a role table is linked to a right. These are both Many-to-Many relations. The roles are a dynamic entity and not of interest for the application (only for visual aspects). When I fetch a user I want to return the data in the user table including a list of the names of all rights.</p>

<p>To clarify, this is what I want the solution to do:</p>

<p><a href="https://i.stack.imgur.com/g5PyY.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/g5PyY.png" alt="solution overview"></a></p>

<p>I managed to get the rights in my user object and return them, but it's inefficient due to the extra query calls hibernate makes after the original query was called.</p>

<p><strong>Detailed version</strong></p>

<p>Let me first give you some information on how to entities are linked and what the code looks like. My (simplified) database table structure looks like this:</p>

<p><a href="https://i.stack.imgur.com/2sbTS.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/2sbTS.png" alt="Database diagram"></a></p>

<p><em>User.java</em></p>

<pre><code>@Entity
@Table(name = "user")
public class User {

  @Id
  @Column(name = "user_id", columnDefinition = "user_id")
  private Long userId;

  @Transient
  private List&lt;String&gt; rights

  @ManyToMany
  @JoinTable(
    name = "user_role",
    joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "user_id"),
    inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "role_id"))
  @JsonIgnore
  private List&lt;Role&gt; roles;

  //Getters and setters
}
</code></pre>

<p><em>Role.java</em></p>

<pre><code>@Entity
@Table(name = "role")
public class Role {

  @Id
  @Column(name = "role_id", columnDefinition = "role_id")
  private Long roleId;

  @ManyToMany
  @JoinTable(
    name = "user_role",
    joinColumns = @JoinColumn(name = "role_id", referencedColumnName = "role_id"),
    inverseJoinColumns = @JoinColumn(name = "user_id", referencedColumnName = "user_id"))
  @JsonIgnore
  private List&lt;Employee&gt; employees;

  @ManyToMany
  @JoinTable(
    name = "role_right",
    joinColumns = @JoinColumn(name = "role_id", referencedColumnName = "role_id"),
    inverseJoinColumns = @JoinColumn(name = "right_id", referencedColumnName = "right_id"))
  @JsonIgnore
  private List&lt;Right&gt; rights;


  //Getters and setters
}
</code></pre>

<p><em>Right.java</em></p>

<pre><code>@Entity
@Table(name = "right")
public class Right {

  @Id
  @Column(name = "right_id", columnDefinition = "right_id")
  private Long rightId;

  @Column(name = "name", columnDefinition = "name")
  private String name;

  @ManyToMany
  @JoinTable(
    name = "role_right",
    joinColumns = @JoinColumn(name = "right_id", referencedColumnName = "right_id"),
    inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "role_id"))
  @JsonIgnore
  private List&lt;Role&gt; roles;

  //Getters and setters
}
</code></pre>

<p>It's important to know that I use the Java Specifications API to join the tables:</p>

<pre><code>return (root, query, cb) -&gt; {
  query.distinct(true);
  Join rolesJoin = root.join("roles", JoinType.LEFT);
  Join rightsJoin = rolesJoin.join("rights", JoinType.LEFT);
  return cb.conjunction();
};
</code></pre>

<p>This creates the correct query:</p>

<pre><code>select &lt;columns go here&gt; 
from employee user0_
left outer join user_role roles1_ on user0_.user_id=roles1_.user_id
left outer join role role2_ on roles1_.role_id=role2_.role_id
left outer join role_right rights3_ on role2_.role_id=rights3_.role_id
left outer join right right4_ on rights3_.right_id=right4_.right_id
</code></pre>

<p>Everything looked to good to me till now. But when I tried to fetch the names of all roles, there where more than two queries (count for page and the original one) being executed</p>

<pre><code>//The original code uses lambda for this
for(Role role : user.getRoles()){
  for(Right right: role.getRights()){
    user.addRight(right.getName());
  }
}
</code></pre>

<p>The extra query looks like:</p>

<pre><code>select &lt;column stuff&gt; 
from role_right rights0_
inner join right right1_ on rights0_.right_id=right1_.right_id
where rights0_.role_id=?
</code></pre>

<p>This makes the call very inefficient to me. In this case it's a single user, but with multiple users it adds up.</p>

<p>Is there a way to have a single query put the names of all rights in the user entity, without adding extra query executions?</p>

<p>Things I tried so far:</p>

<ul>
<li><p>Using <code>@SecondaryTable</code> to directly define column from the Right table in my User entity. I could not get to first link the Role to the User and then use fields from the Role table to link the Right table. So in the end I would have to @SecondaryTable annotation on top of my User object and define columns of the Right object below.</p></li>
<li><p>Using <code>@Formula</code> in the User entity to insert a native call into the query. This did also not work as the annotation did not understand how to map everything into a list of rights.</p></li>
</ul>

<p>There might be other options here, or I did something horribly wrong with implementing the ones above. But for now I don't which way to go in finding a solution for my problem. If someone could tell me, that would be great.</p>

<p>Thanks in advance,<br>
Robin</p>