Question

DB2用mybatis批量update的问题

这是Test.java里测试用的代码
ApplicationContext ac = new ClassPathXmlApplicationContext("spring-mybatis.xml");
JobNodeLnkMapper mapper = ac.getBean("jobNodeLnkMapper", JobNodeLnkMapper.class);
JobNodeLnk[] jobNodeLnks = new JobNodeLnk[5];
JobNodeLnk lnk = null;
for (int i = 0; i < 5; i++) {
lnk = new JobNodeLnkImpl(Long.parseLong(String.valueOf(62+i)),Long.parseLong(String.valueOf((i+5))),Long.parseLong(String.valueOf((i+5))));
jobNodeLnks[i] = lnk;
}
for (int i = 0; i < 5; i++) {
System.out.println(jobNodeLnks[i].toString());
}
System.out.println(mapper.updateJobNodeLnks(jobNodeLnks));
这是Mapper.xml里的语句
<update id="updateJobNodeLnks" parameterType="dayu.dmc.bean.impl.JobNodeLnkImpl">
<foreach collection="array" item="item">
update ysbase_dmc_jobNodeLnk set nodeid=#{item.nodeId} where id=#{item.id};
</foreach>
</update>
### The error occurred while setting parameters

### SQL: update ysbase_dmc_jobNodeLnk set nodeid=? where id=?;update ysbase_dmc_jobNodeLnk set nodeid=? where id=?;update ysbase_dmc_jobNodeLnk set nodeid=? where id=?;update ysbase_dmc_jobNodeLnk set nodeid=? where id=?;update ysbase_dmc_jobNodeLnk set nodeid=? where id=?
### Cause: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=update YSBASE_DMC_JOBNODELNK set NODEID=? w;BEGIN-OF-STATEMENT;<space>, DRIVER=4.16.53
; bad SQL grammar []; nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=update YSBASE_DMC_JOBNODELNK set NODEID=? w;BEGIN-OF-STATEMENT;<space>, DRIVER=4.16.53

Answer 1

MySQL没有提供直接的方法来实现批量，但可以使用case when语法来实现这个功能。
UPDATE course
SET name = CASE id
WHEN 1 THEN 'name1'
WHEN 2 THEN 'name2'
WHEN 3 THEN 'name3'
END,
title = CASE id
WHEN 1 THEN 'New Title 1'
WHEN 2 THEN 'New Title 2'
WHEN 3 THEN 'New Title 3'
END
WHERE id IN (1,2,3)