JPA Hibernate Lazy Fetch Attributes with one Query

Question

Consider following entities:

UserEntity:

@Entity(name = "user")
public class UserEntity {

    @Id
    @Column(name = "id", columnDefinition = "serial")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

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

    @OneToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "user_role", joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id"))
    private List<RoleEntity> roles = new ArrayList<>();

}

RoleEntity:

@Entity(name = "role")
@NamedEntityGraph(
        name = "role.entity_graph",
        attributeNodes = {
                @NamedAttributeNode(value = "permissions")
        })
public class RoleEntity {

    @Id
    @Column(name = "id", columnDefinition = "serial")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

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


    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "role_permission", joinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "permission_id", referencedColumnName = "id"))
    private List<PermissionEntity> permissions = new ArrayList<>();

}

PermissionEntity:

@Entity(name = "permission")
public class PermissionEntity {

    @Id
    @Column(name = "id", columnDefinition = "serial")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

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


}

roles of the UserEntity should be fetched lazy. Problem is when the roles are used (getter is called), hibernate fetches them with a query for each role and a query for each permission of each role, resulting in the n+1 issue.

Thus my question is: How can I lazy fetch the user roles with one query? Can I somehow utilize RoleEntity's EntityGraph?

Note: I already tried using @Fetch:

@Fetch(FetchMode.JOIN)
@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "role_permission", joinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "permission_id", referencedColumnName = "id"))
private List<PermissionEntity> permissions = new ArrayList<>();

This did not work. However together with @BatchSize it works as excpeted.

@Fetch(FetchMode.JOIN)
@BatchSize(size = 1000)
@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "role_permission", joinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "permission_id", referencedColumnName = "id"))
private List<PermissionEntity> permissions = new ArrayList<>();

I'm not happy with this solution though. Why do I need to add @BatchSize?

score 0 · Answer 1 · answered Jun 02 '21 at 23:35

I think using Subgraphs might help you. Take a look at a description from Thorben Janssen on his Website: Entitygraph with multiple Subgraphs, quote:

@NamedEntityGraph(
    name = "graph.AuthorBooksPublisherEmployee", 
    attributeNodes = @NamedAttributeNode(value = "books", subgraph = "subgraph.book"), 
    subgraphs = {
        @NamedSubgraph(name = "subgraph.book", 
                       attributeNodes = @NamedAttributeNode(value = "publisher", subgraph = "subgraph.publisher")),
        @NamedSubgraph(name = "subgraph.publisher", 
                       attributeNodes = @NamedAttributeNode(value = "employees")) })

JPA Hibernate Lazy Fetch Attributes with one Query

1 Answers1