在 Spring 引导中进行 JDBC 身份验证时，我是否必须使用缺省'users'表？

Question

我正在尝试在Spring Boot中使用JDBC身份验证。我正在尝试使用与默认"用户"不同的表。但是用这种方法

@Override
protected void configure(AuthenticationManagerBuilder auth) throws Exception {
auth.jdbcAuthentication().dataSource(dataSource)
.usersByUsernameQuery("SELECT email as USERNAME, PASSWORD as password FROM [user_account] WHERE email=?");
}

使用给定的表架构： [![f][1]][1]

我收到以下错误：

17:34:29.800 [http-nio-8081-exec-1] INFO  o.s.j.support.SQLErrorCodesFactory - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]
17:34:29.806 [http-nio-8081-exec-1] WARN  o.s.s.o.p.endpoint.TokenEndpoint - Handling error: [![enter image description here][2]][2]InternalAuthenticationServiceException, PreparedStatementCallback; SQL [SELECT email as USERNAME, PASSWORD as password FROM [user_account] WHERE email=?]; The index 3 is out of range.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The index 3 is out of range.

如何使用自己的用户数据表进行 JDBC 身份验证？

Answer 1

您可以通过附加启用作为第三个参数来更新查询以选择

SELECT email as USERNAME, PASSWORD as password, enabled FROM [user_account] WHERE email=?