登录sql抛出Java . SQL . sqlexception:Spring Security中的列索引超出范围，3 > 2[重复]

Question

下面的代码是使用Spring Boot Security的Spring BootLogin配置。第一

==application.properties

spring.datasource.driver-class-name = com.mysql.cj.jdbc.Driver
spring.datasource.url = jdbc:mysql://localhost/test?characterEncoding=utf8&serverTimezone=Asia/Seoul
spring.datasource.username = root
spring.datasource.password = password

spring.queries.users-query = SELECT username, password FROM blog_user WHERE username=?
spring.queries.roles-query = SELECT username, role FROM blog_user WHERE username=?

== Spring Security配置

@Value("${spring.queries.users-query}")
private String usersQuery;

@Value("${spring.queries.roles-query}")
private String rolesQuery;

@Override
protected void configure(AuthenticationManagerBuilder auth) throws Exception {
        // TODO Auto-generated method stub
        auth.jdbcAuthentication().usersByUsernameQuery(usersQuery).authoritiesByUsernameQuery(rolesQuery)
                .dataSource(dataSource).passwordEncoder(passwordEncoder());
}

@Override
protected void configure(HttpSecurity http) throws Exception {
        // TODO Auto-generated method stub
        http.csrf().disable()
            .authorizeRequests()
                .antMatchers("/" ,"/home*", "/js/**", "/css/**", "/icon/**", "/users/login", "/users/register").permitAll()
                .antMatchers("/users*", "/posts/**").hasAnyRole("USER")
                .anyRequest().authenticated()
                .and()
            .formLogin()
                .loginPage("/users/login").defaultSuccessUrl("/")
                .usernameParameter("username").passwordParameter("password")
                .and()
            .logout()
                .invalidateHttpSession(true)
                .clearAuthentication(true)
                .logoutSuccessUrl("/").and()
            .exceptionHandling().accessDeniedPage("/error/403");
}

但登录行抛出以下异常。

Caused by: java.sql.SQLException: Column Index out of range, 3 > 2. 
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.15.jar:8.0.15]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.15.jar:8.0.15]

我知道这个异常是从sql PreparedStatementCallback方法抛出的。但是恐怕我不知道< code > spring . queris . users-query 语句与Spring Security部分的关系，所以我可以修复这个preparedstatement参数。我是初学春安的。

Answer 1

问题是，SQL 查询在 3 个不同的列中搜索 3 个值，而在所选表中仅找到 2 列。来自 application.properties 文件的查询是唯一的查询？

我想这对你也有帮助。

也许您可以在这里打印整个stacktrac，并打印sql语句以及USERS和ROLES表的结构。