執行
List<User> list=this.jdbcTemplate.query(usersByUsernameQuery, new String[]{username}, new RowMapper<User>(){
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
User usercache = new User();
String username = rs.getString("username");
String password = rs.getString("password");
boolean enabled = rs.getBoolean("enabled");
usercache.setEnabled(enabled);
usercache.setUsername(username);
usercache.setPassword(password);
return usercache;
}
});
出現異常信息:
09:20:25.419 [http-9090-1] DEBUG o.s.j.support.SQLErrorCodesFactory - Database product name cached for DataSource [com.mysql.jdbc.jdbc2.optional.MysqlDataSource@11bd80c]: name is 'MySQL'
09:20:25.419 [http-9090-1] DEBUG o.s.j.support.SQLErrorCodesFactory - SQL error codes for 'MySQL' found
09:20:25.419 [http-9090-1] DEBUG o.s.j.s.SQLErrorCodeSQLExceptionTranslator - Unable to translate SQLException with Error code '0', will now try the fallback translator
09:20:25.419 [http-9090-1] DEBUG o.s.j.s.SQLStateSQLExceptionTranslator - Extracted SQL state class 'S1' from value 'S1009'
09:20:25.419 [http-9090-1] DEBUG o.s.b.f.s.DefaultListableBeanFactory - Returning cached instance of singleton bean 'org.springframework.security.core.session.SessionRegistryImpl#0'
09:20:25.419 [http-9090-1] DEBUG o.s.s.w.a.UsernamePasswordAuthenticationFilter - Authentication request failed: org.springframework.security.authentication.AuthenticationServiceException: PreparedStatementCallback; SQL [select username,password,enabled from userTable where username = 'XXX]; Parameter index out of range (1 > number of parameters, which is 0).; nested exception is java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
09:20:25.419 [http-9090-1] DEBUG o.s.s.w.a.UsernamePasswordAuthenticationFilter - Updated SecurityContextHolder to contain null Authentication
查看
org.springframework.jdbc.core.JdbcTemplate源碼發現
<T> List<T> org.springframework.jdbc.core.JdbcTemplate.query(String sql, Object[] args, RowMapper<T> rowMapper) throws DataAccessException
Query given SQL to create a prepared statement from SQL and a list of arguments to bind to the query, mapping each row to a Java object via a RowMapper.
Specified by: query(...) in JdbcOperations
Parameters:
sql: SQL query to execute
args: arguments to bind to the query (leaving it to the PreparedStatement to guess the corresponding SQL type); may also contain SqlParameterValue objects which indicate not only the argument value but also the SQL type and optionally the scale
rowMapper: object that will map one object per row
Returns:the result List, containing mapped objects
Throws:DataAccessException - if the query fails
原因:SQL語句寫法錯誤. : 格式應為: ' select * from users where username =?';
重新編譯運行 報出異常:
09:16:30.543 [http-9090-1] DEBUG o.s.s.a.d.DaoAuthenticationProvider - Authentication failed: password does not match stored value
09:16:30.543 [http-9090-1] DEBUG o.s.b.f.s.DefaultListableBeanFactory - Returning cached instance of singleton bean 'org.springframework.security.core.session.SessionRegistryImpl#0'
09:16:30.543 [http-9090-1] DEBUG o.s.s.w.a.UsernamePasswordAuthenticationFilter - Authentication request failed: org.springframework.security.authentication.BadCredentialsException: Bad credentials
09:16:30.543 [http-9090-1] DEBUG o.s.s.w.a.UsernamePasswordAuthenticationFilter - Updated SecurityContextHolder to contain null Authentication
原因:密碼錯誤
解決方法:查看加密方式,已經加密方法.
Spring-security配置自定義加密方式.
<authentication-manager>
<authentication-provider user-service-ref="securityDetailsService">
<password-encoder ref="MD5Encoder" >
<!-- <salt-source user-property="username" ref="j_username"/>-->
</password-encoder>
</authentication-provider>
</authentication-manager>
<!--自定義加密器-->
<beans:bean id="MD5Encoder" class="com.sax.michael.annotations.cm.method.MD5Enconder" />
其中MD5Encoder.java 必須實現PasswordEncoder接口;可參考來源資料[ http://sarin.iteye.com/blog/830831]
public class MD5Encoder extends BasePasswordEncoder {
private static String key = "michael";
@Override
public String encodePassword(String rawPass, Object salt) {
salt = salt==null||"".equals("")?key:salt;
return Encryption.encryptPassword(mergePasswordAndSalt(rawPass, salt, true));
}
@Override
public boolean isPasswordValid(String encPass, String rawPass, Object salt) {
salt = salt==null||"".equals("")?key:salt;
boolean lable_e = encPass==null||"".equals(encPass)?false:true;
boolean lable_r = rawPass==null||"".equals(rawPass)?false:true;
if(lable_e&&lable_r){
String nowPass = Encryption.encryptPassword(mergePasswordAndSalt(rawPass, salt, false));
return Encryption.comparePassword(encPass,nowPass);
}
return false;
}
/**
* 加密
* @param sourcePass
* @param salt
* @return
*/
public static String encryptPasswordFirst(String password,Object salt){
salt = salt==null||"".equals("")?key:salt;
password =password==null||"".equals(password)?"":password+"{"+salt+"}" ;
if("".equals(password)){
throw new IllegalArgumentException("Cannot password a null or empty String");
}
return Encryption.encryptPassword(password);
}
}