CODEWITHSUNDEEP
javamybatisibatis
Vedavyas Sripathi
Vedavyas Sripathi

Reputation: 53

Update Operation not working properly in MYBATIS and getting error

Hi Iam trying to do update operation in MyBATIS,but iam getting SQL syntax error exception,iam not able to understand where iam doing wrong someone suggest me

Temp.java
public class Temp{
private int id;
private String name;
private List<Sect> sect;
//setters and getters
}

Sect.java
Public class Sect{
private int id;
private int sid;
private String sname;
private String priority;
//setters and getters
}

and my DB table structure for Sect is

sid     sname    priority  id

now i wanted to update multiple rows in Sect table at a time and my DAO query i wrote is:

<update id="updateSects" 
parameterType="com.****.****.****.Temp"> 
<foreach item="element" collection="sect" separator=",">   
Update sect set sid=#{element.sid},sname=#
{element.sname},priority=#{element.priority} WHERE 
id=#{element.id}
</foreach>;
</update>

but iam getting error as below: ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 sections set sname='trial3',sid='90',priority=1,u' at line 2

and in my Java Service i calls my dao.xml as:

Service.java
public void updateSects(Temp temp) throws exception

Can someone please tell me where iam getting error

Upvotes: 0

Views: 2155

Answers (1)

Jeff Butler
Jeff Butler

Reputation: 991

It appears you are trying to code a batch update. What you are doing here is not a batch update. With this you are building a single SQL statement - potentially a very large statement. Some databases support this type of syntax, some don't. It may work if you set separator=";".

It is much better to use MyBatis' built in support for batch operations. Take a look at this page for details: https://github.com/mybatis/mybatis-3/wiki/FAQ#how-do-i-code-a-batch-insert

Upvotes: 0

Related Questions