SpringData N+1 solution with @NamedEntityGraph

In the development of Java applications, optimizing database queries plays a crucial role, especially when working with JPA. The @NamedEntityGraph annotation from the JPA 2.1 specification offers a solution for managing entity selections and their associations, allowing necessary data to be loaded in a single query and thereby avoiding the N+1 query problem.

Hi, this is Paul, and welcome to this article, we explore how @NamedEntityGraph work and how we can solve the N+1 issue with it.

Let’s consider an example. We work with three main entities: User, Role, and Group. These entities represent typical components of an access management system, where:

• User describes a system user with all their attributes and connections.
• Role represents a role or permissions that can be assigned to a user. One user can have many roles, meaning the type of relationship is ManyToMany.
• Group defines a group to which a user can belong. One user can belong to many groups, meaning the type of relationship is ManyToMany.

This is roughly what the mapping through a junction table would look like:

@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "users_roles",
        joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"),
        inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id"))
@ToString.Exclude
private Set<Role> roles;

Depending on the context, we may need to load a user with their roles, groups, or both together. This is where @NamedEntityGraph comes in handy. For example, when querying a user by ID, we need to join roles, when querying by username, join only roles, and when querying a list with pagination, join both roles and groups simultaneously.

How to ensure such variability?

Here is an example of a repository.

@Repository
@Transactional
public interface UserRepository extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> {

    @NotNull
    @EntityGraph(value = "user[roles]")
    Optional<User> findById(@NotNull Long userId);

    @EntityGraph(value = "user[groups]")
    User findByUsername(@NonNull String username);

    @NotNull
    @Override
    @EntityGraph(value = "user[roles][groups]")
    Page<User> findAll(@Nullable Specification<User> spec, @NotNull Pageable pageable);

Here’s what the DB schema will look like:

CREATE TABLE IF NOT EXISTS users
(
    id                      BIGSERIAL PRIMARY KEY,
    username                VARCHAR(255) UNIQUE NOT NULL,
);

CREATE TABLE IF NOT EXISTS roles
(
    id          BIGINT PRIMARY KEY,
    title       VARCHAR(255) UNIQUE NOT NULL,
    system_name VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS users_roles
(
    user_id BIGINT NOT NULL REFERENCES users (id),
    role_id BIGINT NOT NULL REFERENCES roles (id)
);

CREATE UNIQUE INDEX IF NOT EXISTS ui_user_role ON users_roles (user_id, role_id);

CREATE TABLE IF NOT EXISTS groups
(
    id         BIGSERIAL PRIMARY KEY,
    title      VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP           NOT NULL
);

CREATE TABLE IF NOT EXISTS users_groups
(
    user_id  BIGINT REFERENCES users (id),
    group_id BIGINT REFERENCES groups (id)
);

CREATE UNIQUE INDEX IF NOT EXISTS ui_user_group ON users_roles (user_id, group_id);

Java Entity

@Entity
@Getter
@Setter
@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "users")
@NamedEntityGraphs({
        @NamedEntityGraph(
                name = "user[roles]",
                attributeNodes = {
                        @NamedAttributeNode("roles")
                }),
        @NamedEntityGraph(
                name = "user[groups]",
                attributeNodes = {
                        @NamedAttributeNode("groups"),
                }),
        @NamedEntityGraph(
                name = "user[roles][groups]",
                attributeNodes = {
                        @NamedAttributeNode("roles"),
                        @NamedAttributeNode("groups")
                })
})
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

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

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "users_roles",
            joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id"))
    @ToString.Exclude
    private Set<Role> roles;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "users_groups",
            joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "group_id", referencedColumnName = "id"))
    @ToString.Exclude
    private Set<Group> groups;

    // equals and hashCode
}

@Getter
@Setter
@Entity
@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "roles")
public class Role {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

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

    @ManyToMany(mappedBy = "roles", fetch = FetchType.LAZY)
    @ToString.Exclude
    private List<User> users;

    // equals and hashCode
}

@Entity
@Getter
@Setter
@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "groups")
public class Group {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

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

    @ManyToMany(mappedBy = "groups", fetch = FetchType.LAZY)
    @ToString.Exclude
    private Set<User> users;

    // equals and hashCode
}

The greatest interest for us is this plot:

@NamedEntityGraphs({
        @NamedEntityGraph(
                name = "user[roles]",
                attributeNodes = {
                        @NamedAttributeNode("roles")
                }),
        @NamedEntityGraph(
                name = "user[groups]",
                attributeNodes = {
                        @NamedAttributeNode("groups"),
                }),
        @NamedEntityGraph(
                name = "user[roles][groups]",
                attributeNodes = {
                        @NamedAttributeNode("roles"),
                        @NamedAttributeNode("groups")
                })
})
public class User ...

@NamedAttributeNode(“roles”) the “roles” here is the name of the field.

private Set<Role> roles;

Here we define which names for joining which related entities we will use, thus name = “user[groups]” is the key for joining the roles table for use in the repository. Each element of attributeNodes the array turns into an SQL JOIN operation. To apply just add @EntityGraph(value = “user[roles]”) the above repository method:

@EntityGraph(value = "user[roles]")
Optional<User> findById(@NotNull Long userId);

And so on for the other named graphs. It should be noted that there is no strict convention for naming, and you can name your graph whatever you want or come up with your approach, although user[roles] or user.roles is most often used.

As a result, you get a join instead of additional queries. This is useful for searching for individual composite objects, but it has the greatest impact on list queries, as the N+1 problem is particularly unpleasant there. We are not analyzing generated SQL queries here because it’s just a typical JOIN operation but in real life, you have to check this using configuration:

spring:
  jpa:
    properties:
      hibernate:
        format_sql: 'true'
        show_sql: 'true'

It helps you make sure you're Entity Graph works correctly.

Conclusion

Thus, @NamedEntityGraph and @EntityGraph offer flexible and powerful tools for managing the loading of related data in JPA, allowing you to significantly improve the efficiency of queries and the overall performance of the application.

Thank you for reading until the end. Before you go:

• Please consider clapping and following the writer! 👏
• Follow us on Twitter(X), LinkedIn