如何避免Spring Security中查询角色时产生过多SELECT语句？

问题现象 #

查看Hibernate执行日志，发现重复执行相同的角色查询SQL：

Hibernate: select a1_0.user_id,a1_1.id,a1_1.role from user_role a1_0 join roles a1_1 on a1_1.id=a1_0.role_id where a1_0.user_id=?
Hibernate: select a1_0.user_id,a1_1.id,a1_1.role from user_role a1_0 join roles a1_1 on a1_1.id=a1_0.role_id where a1_0.user_id=?
Hibernate: select a1_0.user_id,a1_1.id,a1_1.role from user_role a1_0 join roles a1_1 on a1_1.id=a1_0.role_id where a1_0.user_id=?
Hibernate: select a1_0.user_id,a1_1.id,a1_1.role from user_role a1_0 join roles a1_1 on a1_1.id=a1_0.role_id where a1_0.user_id=?
Hibernate: select a1_0.user_id,a1_1.id,a1_1.role from user_role a1_0 join roles a1_1 on a1_1.id=a1_0.role_id where a1_0.user_id=?

每个用户都会触发一次单独的角色查询，形成N+1查询问题，严重影响性能，需要优化为单次查询获取所有用户的角色数据。

问题分析 #

当前Spring Security使用JdbcUserDetailsManager实现用户认证，配置了两个SQL分别查询用户基础信息和用户权限：

@Configuration
@Import(WebMvcConfig.class)
@EnableWebSecurity
public class WebSecurityConfig {
    private final DataSource dataSource;

    private static final String USERS_BY_USERNAME_QUERY = """
            SELECT username, password, enabled
            FROM users where username = ?
            """;

    private static final String AUTHORITIES_BY_USERNAME_QUERY = """
            SELECT users.username, roles.role
            FROM user_role
            JOIN users ON user_role.user_id = users.id
            JOIN roles ON user_role.role_id = roles.id
            WHERE users.username = ?
            """;

    public WebSecurityConfig(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Bean
    public SecurityFilterChain filterChain(HttpSecurity http) throws Exception {
        http.authorizeHttpRequests()
                .requestMatchers("/user/**").hasAuthority("USER")
                .requestMatchers("/admin/**").hasAuthority("ADMIN")
                .anyRequest().authenticated()
                .and().formLogin()
                .successHandler(successHandler())
                .and().logout().logoutUrl("/logout").logoutSuccessUrl("/login");
        return http.build();
    }

    @Bean
    public AuthenticationProvider authenticationProvider() {
        JdbcUserDetailsManager userDetailsManager = new JdbcUserDetailsManager(dataSource);
        userDetailsManager.setUsersByUsernameQuery(USERS_BY_USERNAME_QUERY);
        userDetailsManager.setAuthoritiesByUsernameQuery(AUTHORITIES_BY_USERNAME_QUERY);

        DaoAuthenticationProvider authProvider = new DaoAuthenticationProvider();
        authProvider.setUserDetailsService(userDetailsManager);
        authProvider.setPasswordEncoder(passwordEncoder());
        return authProvider;
    }

    @Bean
    public PasswordEncoder passwordEncoder() {
        return new BCryptPasswordEncoder(10);
    }

    @Bean
    public AuthenticationSuccessHandler successHandler() {
        return (httpServletRequest, httpServletResponse, authentication) ->
                httpServletResponse.sendRedirect("/");
    }
}

同时，User实体通过@ManyToMany关联Role实体，配置为EAGER抓取，但仍未解决N+1问题。

实体类定义 #

User实体 #

@Entity
@Table(name = "users")
@Getter
@Setter
@EqualsAndHashCode
public class User implements UserDetails {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    @Column(nullable = false, unique = true)
    private String username;
    @Column(nullable = false)
    private String password;
    @Column(nullable = false)
    private String name;
    @Column(name = "last_name", nullable = false)
    private String lastName;
    @Column(nullable = false)
    private String department;
    @Column
    private int salary;
    @Column(nullable = false)
    private byte age;
    @Column
    private String email;
    @Column(name = "enabled", nullable = false)
    private byte enabledByte;
    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "user_role",
            joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id"))
    @EqualsAndHashCode.Exclude
    private Set<Role> authorities;
}

Role实体 #

@Entity
@Table(name = "roles")
@Getter
@Setter
@EqualsAndHashCode
public class Role implements GrantedAuthority {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    @Column(name = "role", nullable = false, unique = true)
    private String authority;
    @ManyToMany(mappedBy = "authorities")
    @EqualsAndHashCode.Exclude
    private Set<User> userList;
}

已尝试方案 #

1. 将关联关系的抓取类型设置为FetchType.EAGER，但仍出现大量重复的角色查询SQL。
2. 在Repository中使用原生SQL自定义查询，抛出LazyInitializationException异常，无论抓取类型是EAGER还是LAZY，结果一致：

public interface UserDao extends JpaRepository<User, Long> {
    @Query(value = """
SELECT users.id, users.username, users.password, users.name,
users.last_name, users.department, users.salary, users.age,
users.email, users.enabled, roles.role
FROM user_role
JOIN users ON user_role.user_id = users.id
JOIN roles ON user_role.role_id = roles.id
WHERE username != :username
""", nativeQuery = true)
    List<User> findAllExceptLoggedUser(String username);
}

异常信息：

org.hibernate.LazyInitializationException: failed to lazily initialize a collection of role: pp.spring_bootstrap.models.User.authorities: could not initialize proxy - no Session

解决方案 #

方案1：使用JPQL的JOIN FETCH一次性加载关联数据 #

修改Repository中的查询，使用JPQL的JOIN FETCH语法，一次性查询用户及其关联的角色，避免N+1查询：

public interface UserDao extends JpaRepository<User, Long> {
    @Query("SELECT u FROM User u JOIN FETCH u.authorities WHERE u.username != :username")
    List<User> findAllExceptLoggedUser(String username);
}

该查询会生成包含JOIN的SQL，一次性获取所有用户和对应的角色数据，彻底消除重复查询。

方案2：配置Hibernate批量抓取 #

如果无法修改查询语句，可以在User实体的authorities字段上添加@BatchSize注解，让Hibernate批量查询角色数据，减少SQL执行次数：

@ManyToMany(fetch = FetchType.EAGER)
@JoinTable(name = "user_role",
        joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"),
        inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id"))
@EqualsAndHashCode.Exclude
@BatchSize(size = 10) // 可根据实际调整批量大小
private Set<Role> authorities;

配置后，Hibernate会一次性查询多个用户的角色（比如一次查10个），将N+1查询优化为2次查询（一次查用户，一次查批量角色）。

关于原生查询的说明 #

原生SQL查询不会自动映射关联集合，即使查询了角色字段，Hibernate也不会将其填充到User的authorities集合中，因此会触发懒加载异常。推荐使用JPQL的JOIN FETCH来处理关联数据的查询。

内容的提问来源于stack exchange，提问作者Sergey Zolotarev