How to correct bad sql grammar when passing data?-CodePudding

This is my JDBC file with a the following sql query:

private static final String UPDATE_QUESTION = "UPDATE Quiz SET type=?, questionIndex=?, choiceNum=?, question=?, choiceA=?, choiceB=?, choiceC=?, choiceD=?, correct=?, hint=? WHERE type=? AND questionIndex=?";

When I try and pass some data into the query above in JSON format:

{
"id": 84,
"type":"epidemics",
"questionIndex": 1,
"choiceNum":2,
"question":"updated question3",
"choiceA": "no3",
"choiceB":"yes2",
"choiceC":"no3",
"choiceD":"yes4",
"correct":"no3",
"hint":"second answer"
}

I am getting the following error message:

 "timestamp": "2022-11-26T11:52:16.431 00:00",
    "status": 500,
    "error": "Internal Server Error",
    "trace": "org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [UPDATE Quiz SET type=?, questionIndex=?, choiceNum=?, question=?, choiceA=?, choiceB=?, choiceC=?, choiceD=?, correct=?, hint=? WHERE (type=?) AND (questionIndex=?)]; nested exception is java.sql.SQLException: No value specified for parameter 12

Any ideas where I'm going wrong in the query?

CodePudding user response：

Note that you need to pass a value for each ? placeholder, even if the same column appears more than once in the prepared statement. So, you need to bind the value for questionIndex twice. Your Java code should look something like:

String UPDATE_QUESTION = "UPDATE Quiz SET type=?, questionIndex=?, choiceNum=?, question=?, choiceA=?, choiceB=?, choiceC=?, choiceD=?, correct=?, hint=? WHERE type=? AND questionIndex=?";
PreparedStatement ps = conn.prepareStatement(UPDATE_QUESTION);
ps.setString(1, type);
ps.setInt(2, questionIndex);  // first setter for questionIndex
ps.setInt(3, choiceNum);
ps.setString(4, question); 
ps.setString(5, choiceA);
ps.setString(6, choiceB);
ps.setString(7, choiceC);
ps.setString(8, choiceD);
ps.setString(9, correct);
ps.setString(10, hint);
ps.setString(11, type);
ps.setInt(12, questionIndex);  // second setter for questionIndex

int row = ps.executeUpdate();
// rows affected
System.out.println(row);