如何解决Hibernate与Spring Data JPA中的N+1查询问题？

我用Hibernate和Spring Data JPA从PostgreSQL数据库查询所有用户列表，开启Hibernate日志后发现执行了多条查询，而非预期的1条：

Hibernate执行日志：

Hibernate: select user0_.id as id1_1_, user0_.name as name2_1_ from user user0_
Hibernate: select roles0_.user_id as user_id1_2_0_, roles0_.roles_id as roles_id2_2_0_, role1_.id as id1_0_1_, role1_.name as name2_0_1_ from user_roles roles0_ inner join role role1_ on roles0_.roles_id=role1_.id where roles0_.user_id=?
Hibernate: select roles0_.user_id as user_id1_2_0_, roles0_.roles_id as roles_id2_2_0_, role1_.id as id1_0_1_, role1_.name as name2_0_1_ from user_roles roles0_ inner join role role1_ on roles0_.roles_id=role1_.id where roles0_.user_id=?
Hibernate: select roles0_.user_id as user_id1_2_0_, roles0_.roles_id as roles_id2_2_0_, role1_.id as id1_0_1_, role1_.name as name2_0_1_ from user_roles roles0_ inner join role role1_ on roles0_.roles_id=role1_.id where roles0_.user_id=?
Hibernate: select roles0_.user_id as user_id1_2_0_, roles0_.roles_id as roles_id2_2_0_, role1_.id as id1_0_1_, role1_.name as name2_0_1_ from user_roles roles0_ inner join role role1_ on roles0_.roles_id=role1_.id where roles0_.user_id=?

相关代码 #

User实体

@Entity
@Table(name = "t_users")
public class User {

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;
    private String name;

    @ManyToMany(fetch = FetchType.LAZY)                   
    private Set<Role> roles;
 }

Role实体

@Entity
@Table(name = "t_roles")
public class Role {

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

    private String name;
 }

UserRepository

public interface UserRepository extends CrudRepository<User, Long> {

    List<User> findAllBy();
}

解决方案 #

方法一：使用JPQL的Fetch Join #

在Repository中自定义查询，通过JOIN FETCH语法一次性加载用户及其关联的角色数据，避免逐条查询：

public interface UserRepository extends CrudRepository<User, Long> {

    @Query("SELECT DISTINCT u FROM User u JOIN FETCH u.roles")
    List<User> findAllBy();
}

添加DISTINCT是为了避免因多对多关联导致的用户数据重复，最终Hibernate会生成一条包含JOIN的SQL，一次性获取所有所需数据。

方法二：使用@EntityGraph注解 #

利用Spring Data JPA的@EntityGraph注解，指定要关联加载的属性，实现单查询加载：

public interface UserRepository extends CrudRepository<User, Long> {

    @EntityGraph(attributePaths = {"roles"})
    @Query("SELECT DISTINCT u FROM User u")
    List<User> findAllBy();
}

该注解会告知Hibernate在查询User时，同时加载其roles集合，同样生成单条JOIN查询，写法更简洁。

内容的提问来源于stack exchange，提问作者Alina Sagaidak