英文:
Spring Security query Invalid column index exception but working on Oracle
问题
我正在使用Spring Security 4，尽管在PL/SQL中可以正常工作，但似乎我键入的查询不起作用。
我想访问ROLETYPE表。这些表通过主键和外键链接在一起，如：UTILISATEURS与POSTES与ROLES与RHNOM（这是角色类型LIB1）。
以下是查询：
authorities-by-username-query="select LIB1 from RHNOM rh, UTILISATEURS u, POSTES p, ROLES r
where u.IDPOSTE = p.ID_POSTE and p.ID_ROLE = r.ID_ROLE and r.ID_TYP_ROLE = rh.IDNOM and u.LOGIN = ?" />
错误信息：
Caused by: org.springframework.jdbc.InvalidResultSetAccessException: PreparedStatementCallback; invalid ResultSet access for SQL [select LIB1 from RHNOM rh, UTILISATEURS u, POSTES p, ROLES r where u.IDPOSTE = p.ID_POSTE and p.ID_ROLE = r.ID_ROLE and r.ID_TYP_ROLE = rh.IDNOM and u.LOGIN = ?]; nested exception is java.sql.SQLException: Index de colonne non valide
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:235)
...
Caused by: java.sql.SQLException: Index de colonne non valide
at oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.java:1277)
...
如果需要进一步的帮助，请提供更多上下文信息。
英文:
I am using spring security 4, it appears that the query that I typed doesn't work even though it works properly in PL/SQL.
I want to access to ROLETYPE table. The tables are linked with primary & foreign keys like: UTILISATEURS have POSTES have ROLES have RHNOM (which is role type LIB1)
Here's the query :
authorities-by-username-query="select LIB1 from RHNOM rh, UTILISATEURS u, POSTES p, ROLES r
where u.IDPOSTE = p.ID_POSTE and p.ID_ROLE = r.ID_ROLE and r.ID_TYP_ROLE = rh.IDNOM and u.LOGIN = ?" />
error:
Caused by: org.springframework.jdbc.InvalidResultSetAccessException: PreparedStatementCallback; invalid ResultSet access for SQL [select LIB1 from RHNOM rh, UTILISATEURS u, POSTES p, ROLES r where u.IDPOSTE = p.ID_POSTE and p.ID_ROLE = r.ID_ROLE and r.ID_TYP_ROLE = rh.IDNOM and u.LOGIN = ?]; nested exception is java.sql.SQLException: Index de colonne non valide
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:235)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:660)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:695)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:727)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:737)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:787)
at org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl.loadUserAuthorities(JdbcDaoImpl.java:236)
at org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl.loadUserByUsername(JdbcDaoImpl.java:188)
at org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:114)
... 34 more
Caused by: java.sql.SQLException: Index de colonne non valide
at oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.java:1277)
at org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:213)
at org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:213)
at org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl$2.mapRow(JdbcDaoImpl.java:240)
at org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl$2.mapRow(JdbcDaoImpl.java:237)
at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:93)
at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:60)
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:708)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:644)
... 41 more
答案1
得分: 0
你需要匹配Spring Security所期望的所有列，对于authorities-by-username-query来说，这些列是username和authority，请参考<jdbc-user-service>以及检索的实现：
protected List<GrantedAuthority> loadUserAuthorities(String username) {
return getJdbcTemplate().query(this.authoritiesByUsernameQuery,
new String[] { username }, new RowMapper<GrantedAuthority>() {
@Override
public GrantedAuthority mapRow(ResultSet rs, int rowNum)
throws SQLException {
String roleName = JdbcDaoImpl.this.rolePrefix + rs.getString(2);
return new SimpleGrantedAuthority(roleName);
}
});
}
来自于Spring Security 4.2.18中的JdbcDaoImpl。
正如你所看到的，实际上你不需要第一列是username（因为它不会被检索），但是角色（authority）必须是第二列，因为它是通过索引检索的。
简而言之，你需要将查询更改为类似以下的内容：
select u.LOGIN, LIB1 from ...
英文:
You need to match all the columns that Spring Security expects, which for authorities-by-username-query is username, authority, see <jdbc-user-service> and the implementation of retrieval:
> java
> protected List<GrantedAuthority> loadUserAuthorities(String username) {
> return getJdbcTemplate().query(this.authoritiesByUsernameQuery,
> new String[] { username }, new RowMapper<GrantedAuthority>() {
> @Override
> public GrantedAuthority mapRow(ResultSet rs, int rowNum)
> throws SQLException {
> String roleName = JdbcDaoImpl.this.rolePrefix + rs.getString(2);
>
> return new SimpleGrantedAuthority(roleName);
> }
> });
> }
>
From JdbcDaoImpl in Spring Security 4.2.18.
As you can see you don't actually need the first column to be username (as it isn't retrieved), but the role (authority) must be the second column, as it is retrieved by index.
In short, you need to change your query to something like:
select u.LOGIN, LIB1 from ...
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论