Spring Security - 无效的列索引异常
我想使用spring security.But我得到了SQLException.I仍然没有找到任何解决方案。Spring Security - 无效的列索引异常
我的表结构:
APP_USER(ID_APP_USER,NAME,PASSWORD)
APP_ROLE(ID_ROLE,NAME)
APP_USER_ROLE(ID_USER_ROLE,ID_USER,ID_ROLE)
我弹簧配置文件:
<!-- enable use-expressions -->
<http auto-config="true" use-expressions="true">
<intercept-url pattern="/admin**" access="hasRole('ROLE_ADMIN')" />
<!-- access denied page -->
<access-denied-handler error-page="/403" />
<form-login
login-page="/login"
default-target-url="/index"
authentication-failure-url="/login?error"
username-parameter="username"
password-parameter="password" />
<logout logout-success-url="/login?logout" />
<!-- enable csrf protection -->
<csrf/>
</http>
<!-- Select users and user_roles from database -->
<authentication-manager>
<authentication-provider>
<jdbc-user-service data-source-ref="dataSource"
users-by-username-query=
"select name as username,password from app_user where name=?"
authorities-by-username-query=
"SELECT app_user.name as username, app_role.name as role
FROM app_user
INNER JOIN app_user_role ON app_user.id_app_user = app_user_role.id_user
INNER JOIN app_role ON app_user_role.id_role = app_role.id_role
WHERE app_user.name = ? " />
</authentication-provider>
</authentication-manager>
而且我收到以下错误:
20:36:55.281 [HTTP-NIO-8089-EXEC-10] DEBUG osjsSQLErrorCodeSQLExceptionTranslator - 翻译的SQLException用SQL状态 '99999',错误代码'17003',消息[列索引无效];对于任务[PreparedStatementCallback] 20:36:55.282 [http-nio-8089-exec-10],SQL是[选择名称作为用户名,密码来自app_user,其中name =?] DEBUG osswaUsernamePasswordAuthenticationFilter - 认证请求失败:org.springframework。 security.authentication.AuthenticationServiceException:PreparedStatementCallback;对于SQL无效的ResultSet访问权限[选择名称作为用户名,来自app_user的密码where name =?];嵌套的异常是java.sql.SQLException:列索引无效 20:36:55.282 [http-nio-8089-exec-10] DEBUG osswaUsernamePasswordAuthenticationFilter - 更新SecurityContextHolder以包含空认证 20:36:55.282 [http-nio- 8089-exec-10] DEBUG osswaUsernamePasswordAuthenticationFilter - 委托身份验证失败处理程序org.springframework.se[email protected]2f483b1e 20:36:55.282 [http-nio-8089-exec-10] DEBUG osswaSimpleUrlAuthenticationFailureHandler - 重定向到/登录错误 20:36:55.282 [HTTP-NIO-8089-EXEC-10] DEBUG ossweb.DefaultRedirectStrategy - 重定向到 '?/ FNDWEB /登录错误'
什么错了吗?请帮助我:/提前致谢。
最后我找到了解决办法。我们需要在我们的查询中添加“,1作为启用”。
<authentication-provider>
<jdbc-user-service data-source-ref="dataSource"
users-by-username-query=
"select name as username,password,1 as enabled from app_user where name=?"
authorities-by-username-query=
"SELECT app_user.name as username, app_role.name as role
FROM app_user
INNER JOIN app_user_role ON app_user.id_app_user = app_user_role.id_user
INNER JOIN app_role ON app_user_role.id_role = app_role.id_role
WHERE app_user.name = ? " />
</authentication-provider>