参数索引超出范围（1 > 参数数量，即 0）。嵌套异常是 java.sql.SQLException：
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17019249/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-11-01 00:44:05 来源:igfitidea点击:
Parameter index out of range (1 > number of parameters, which is 0).; nested exception is java.sql.SQLException:
提问by deepak kumar
I'm getting this error:
我收到此错误：
java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3813)
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3795)
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3840)
at com.mysql.jdbc.PreparedStatement.setInt(PreparedStatement.java:3784)
at com.mysql.jdbc.PreparedStatement.setObject(PreparedStatement.java:4052)
at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:351)
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:216)
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:144)
at org.springframework.jdbc.core.ArgPreparedStatementSetter.doSetValue(ArgPreparedStatementSetter.java:65)
at org.springframework.jdbc.core.ArgPreparedStatementSetter.setValues(ArgPreparedStatementSetter.java:46)
at org.springframework.jdbc.core.JdbcTemplate.doInPreparedStatement(JdbcTemplate.java:816)
at org.springframework.jdbc.core.JdbcTemplate.doInPreparedStatement(JdbcTemplate.java:1)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:812)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:868)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:876)
When i'm trying to execute this class:
当我尝试执行这个类时：
public List<CandidateDO> getCandidateList(final JobDO currentJob) {
List<CandidateDO> appliedCandidateList = new ArrayList<CandidateDO>();
CandidateDO candidate = new CandidateDO();
String query = "SET @sql = NULL;";
jdbcTemplate.execute(query);
query = "SELECT\n"
+ " GROUP_CONCAT(DISTINCT\n"
+ " CONCAT(\n"
+ " 'MAX(IF(`skillId` = ', `skillId`, ',levelId,NULL)) AS `',\n"
+ " `skillId`, '`'\n"
+ " )\n"
+ " ) INTO @sql\n"
+ "FROM tblCandidateToSkill,candidateToJob,tblCandidate\n"
+ "where tblCandidateToSkill.candidateId = candidateToJob.candidateId and tblCandidate.id = candidateToJob.candidateId;";
jdbcTemplate.execute(query);
query = "SET @sql = CONCAT('SELECT tblCandidateToSkill.candidateId,name,email,dob,phoneNumber,alternateNumber,addressLine, ', @sql, ' \n"
+ " FROM tblCandidateToSkill,candidateToJob,tblCandidate\n"
+ " where tblCandidateToSkill.candidateId = candidateToJob.candidateId and tblCandidate.id = candidateToJob.candidateId\n"
+ " and candidateToJob.jobId= ? \n"
+ " GROUP BY candidateId');";
jdbcTemplate.update(query, new Object[]{currentJob.getId()});
query = "PREPARE stmt FROM @sql;";
jdbcTemplate.execute(query);
query = "EXECUTE stmt;";
appliedCandidateList = jdbcTemplate.query(query, new RowMapper<CandidateDO>() {
@Override
public CandidateDO mapRow(ResultSet rs, int i) throws SQLException {
CandidateDO candidate = new CandidateDO();
candidate.setId(rs.getInt(1));
candidate.setName(rs.getString("name"));
candidate.setDob(rs.getDate("dob"));
candidate.setPhoneNumber(rs.getString("phoneNumber"));
candidate.setAlternateNumber(rs.getString("alternateNumber"));
candidate.setAddressLine(rs.getString("addressLine"));
for (Iterator<SkillDO> it = currentJob.getSkills().iterator(); it.hasNext();) {
SkillDO skill = it.next();
public List<CandidateDO> getCandidateList(final JobDO currentJob) {
List<CandidateDO> appliedCandidateList = new ArrayList<CandidateDO>();
CandidateDO candidate = new CandidateDO();
String query = "SET @sql = NULL;";
jdbcTemplate.execute(query);
query = "SELECT\n"
+ " GROUP_CONCAT(DISTINCT\n"
+ " CONCAT(\n"
+ " 'MAX(IF(`skillId` = ', `skillId`, ',levelId,NULL)) AS `',\n"
+ " `skillId`, '`'\n"
+ " )\n"
+ " ) INTO @sql\n"
+ "FROM tblCandidateToSkill,candidateToJob,tblCandidate\n"
+ "where tblCandidateToSkill.candidateId = candidateToJob.candidateId and tblCandidate.id = candidateToJob.candidateId;";
jdbcTemplate.execute(query);
query = "SET @sql = CONCAT('SELECT tblCandidateToSkill.candidateId,name,email,dob,phoneNumber,alternateNumber,addressLine, ', @sql, ' \n"
+ " FROM tblCandidateToSkill,candidateToJob,tblCandidate\n"
+ " where tblCandidateToSkill.candidateId = candidateToJob.candidateId and tblCandidate.id = candidateToJob.candidateId\n"
+ " and candidateToJob.jobId= ? \n"
+ " GROUP BY candidateId');";
jdbcTemplate.update(query, new Object[]{currentJob.getId()});
query = "PREPARE stmt FROM @sql;";
jdbcTemplate.execute(query);
query = "EXECUTE stmt;";
appliedCandidateList = jdbcTemplate.query(query, new RowMapper<CandidateDO>() {
@Override
public CandidateDO mapRow(ResultSet rs, int i) throws SQLException {
CandidateDO candidate = new CandidateDO();
candidate.setId(rs.getInt(1));
candidate.setName(rs.getString("name"));
candidate.setDob(rs.getDate("dob"));
candidate.setPhoneNumber(rs.getString("phoneNumber"));
candidate.setAlternateNumber(rs.getString("alternateNumber"));
candidate.setAddressLine(rs.getString("addressLine"));
for (Iterator<SkillDO> it = currentJob.getSkills().iterator(); it.hasNext();) {
SkillDO skill = it.next();
SkillLevelDO CandidateSkillLevel = new SkillLevelDO();
CandidateSkillLevel.setId(rs.getInt(String.valueOf(skill.getId())));
candidate.getSkillLevel().add(CandidateSkillLevel);
candidate.getSkills().add(skill);
}
return candidate;
}
});
query = "DEALLOCATE PREPARE stmt;";
jdbcTemplate.execute(query);
return appliedCandidateList;
} SkillLevelDO CandidateSkillLevel = new SkillLevelDO();
CandidateSkillLevel.setId(rs.getInt(String.valueOf(skill.getId())));
candidate.getSkillLevel().add(CandidateSkillLevel);
candidate.getSkills().add(skill);
}
return candidate;
}
});
query = "DEALLOCATE PREPARE stmt;";
jdbcTemplate.execute(query);
return appliedCandidateList;
}
回答by Jayamohan
Check out the reason for the error here.
在此处查看错误原因。
Looks like the following query is having an issue.
看起来以下查询有问题。
query = "SET @sql = CONCAT('SELECT tblCandidateToSkill.candidateId,name,email,dob,phoneNumber,alternateNumber,addressLine, ', @sql, ' \n"
+ " FROM tblCandidateToSkill,candidateToJob,tblCandidate\n"
+ " where tblCandidateToSkill.candidateId = candidateToJob.candidateId and tblCandidate.id = candidateToJob.candidateId\n"
+ " and candidateToJob.jobId= ? \n"
+ " GROUP BY candidateId');";
Your single quotes(') is suspicious. The (?) PLACEHOLDERinside the query is within the Single quotes which is just considered as STRINGrather than a (?) PLACEHOLDER.
您的单引号(') 是可疑的。查询中的 (?) PLACEHOLDER位于单引号内，它被视为STRING而不是 (?) PLACEHOLDER。
