Hi I am now using mybatis to construct dynamic sql in spring boot framework. and the database is mysql. I run it locally, the sql server is MYSQL 8.0.21. For mybatis, I am using(build.gradle)
compile('org.mybatis:mybatis:3.4.6')
compile('org.mybatis.spring.boot:mybatis-spring-boot-starter:1.3.2')
compile('mysql:mysql-connector-java:8.0.13')
When I was trying to do this in .xml file:
<update id="update" parameterType="java.util.List" keyProperty="id">
<foreach collection="list" item="answer" index="index" separator=";">
UPDATE Answer
SET RequestId=#{answer.requestId}, QuestionId=#{answer.questionId}, QuestionnaireId = #{answer.questionnaireId},
AnswerText = #{answer.content}, AnswerChoiceId = #{answer.choiceId}, CreatedTime = #{answer.createdTime},
LastUpdatedTime = #{answer.lastUpdatedTime}
WHERE AnswerId = #{answer.id}
</foreach>
</update>
it is works fine if I just pass one item like this（Json examples below just show you the fields, it doesn't mean I pass the values in this format, Actually, I convert the following examples as List in java):
[
{
"id": 2,
"requestId": 2,
"questionnaireId": 3,
"questionId": 5,
"content": "PROD",
"choiceId": 8,
"createdTime": "2020-08-04 20:50:41"
}
]
but it will throw error when I add two items:
[
{
"id": 2,
"requestId": 2,
"questionnaireId": 3,
"questionId": 5,
"content": "PROD",
"choiceId": 8,
"createdTime": "2020-08-04 20:50:41"
},
{
"id": 3,
"requestId": 2,
"questionnaireId": 3,
"questionId": 5,
"content": "LAB",
"choiceId": 7,
"createdTime": "2020-08-04 20:50:41"
}
]
The error saying:
"\n### Error updating database. Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE Answer\n SET RequestId=2, QuestionId=5, QuestionnaireId = 3,\n ' at line 7\n### The error may involve com.sample.demo.example.repository.mapper.AnswerMapper.update-Inline\n### The error occurred while setting parameters\n### SQL: UPDATE Answer SET RequestId=?, QuestionId=?, QuestionnaireId = ?, AnswerText = ?, AnswerChoiceId = ?, CreatedTime = ?, LastUpdatedTime = ? WHERE AnswerId = ? ; UPDATE Answer SET RequestId=?, QuestionId=?, QuestionnaireId = ?, AnswerText = ?, AnswerChoiceId = ?, CreatedTime = ?, LastUpdatedTime = ? WHERE AnswerId = ?\n### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE Answer\n SET RequestId=2, QuestionId=5, QuestionnaireId = 3,\n ' at line 7\n; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE Answer\n SET RequestId=2, QuestionId=5, QuestionnaireId = 3,\n ' at line 7",
It seems error when iterating through the list? Also I cannot see any error in my code, can anyone help?
Update I also tried to add a ";" at the end and also "BEGIN" and "END" . Still no luck
BEGIN
<foreach collection="list" item="answer" index="index" separator=";">
UPDATE Answer
SET RequestId=#{answer.requestId}, QuestionId=#{answer.questionId}, QuestionnaireId = #{answer.questionnaireId},
AnswerText = #{answer.content}, AnswerChoiceId = #{answer.choiceId}, CreatedTime = #{answer.createdTime},
LastUpdatedTime = #{answer.lastUpdatedTime}
WHERE AnswerId = #{answer.id}
</foreach>
;
END;
One last question is: I know this is not a batch operation. So do loop in java or using this foreach in mybatis. which is better? I was think using foreach maybe a litte bit faster?