mybatisSQL语句的问题

liudachu 2020-06-25 12:51:49
想要实现一次修改多个图书的信息,通过List<Map<String,Object>> list集合传递

请问问题出在SQL语句上还是,什么,给点建议

mapper.xml中的sql语句
   <update id="updateBooks" parameterType="list">
<foreach collection="list" index="index" item="item" separator=";">
update books
<set>
<if test="item.title!=null">
title=#{item.title},
</if>
<if test="item.name!=null">
name=#{item.name},
</if>
<if test="item.publisher!=null">
publisher=#{item.publisher},
</if>
<if test="item.time!=null">
time=#{item.time},
</if>
<if test="item.price!=null">
price=#{item.price}
</if>
</set>
where id=#{item.id}
</foreach>
</update>


测试代码
 @Test
public void test3(){
BookService service=new BookService();

Map<String, Object> map=new HashMap<String, Object>();
map.put("id",12);
map.put("name","张三");
Map<String, Object> map2=new HashMap<String, Object>();
map2.put("id",13);
map2.put("name","张四");
Map<String, Object> map3=new HashMap<String, Object>();
map3.put("id",14);
map3.put("name","张五");

List<Map<String, Object>> list=new ArrayList<Map<String, Object>>();
list.add(map);
list.add(map2);
list.add(map3);
service.updateBooks(list);

}


出现的异常

2020-06-25 12:42:40,070 198 [ main] DEBUG .apache.ibatis.io.ResolverUtil - Checking to see if class com.chif.domain.Book matches criteria [is assignable to Object]
2020-06-25 12:42:40,088 216 [ main] DEBUG ache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
2020-06-25 12:42:40,118 246 [ main] DEBUG source.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
2020-06-25 12:42:40,118 246 [ main] DEBUG source.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
2020-06-25 12:42:40,118 246 [ main] DEBUG source.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
2020-06-25 12:42:40,118 246 [ main] DEBUG source.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
2020-06-25 12:42:40,392 520 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Opening JDBC Connection
Thu Jun 25 12:42:40 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
2020-06-25 12:42:41,499 1627 [ main] DEBUG source.pooled.PooledDataSource - Created connection 771775563.
2020-06-25 12:42:41,501 1629 [ main] DEBUG hif.dao.BookMapper.updateBooks - ==> Preparing: update books SET name=? where id=? update books SET name=? where id=? update books SET name=? where id=?
2020-06-25 12:42:41,531 1659 [ main] DEBUG hif.dao.BookMapper.updateBooks - ==> Parameters: 张三(String), 12(Integer), 张四(String), 13(Integer), 张五(String), 14(Integer)

org.apache.ibatis.exceptions.PersistenceException:
### Error updating database. 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 books
SET name='张四'
where id=13

' at line 5
### The error may involve com.chif.dao.BookMapper.updateBooks-Inline
### The error occurred while setting parameters
### SQL: update books SET name=? where id=? update books SET name=? where id=? update books SET name=? where id=?
### 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 books
SET name='张四'
where id=13

' at line 5

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:200)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy3.updateBooks(Unknown Source)
at com.chif.BookService.updateBooks(BookService.java:65)
at test.test3(test.java:42)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at org.junit.runners.model.Fra
...全文
135 2 打赏 收藏 转发到动态 举报
写回复
用AI写文章
2 条回复
切换为时间正序
请发表友善的回复…
发表回复
liudachu 2020-06-25
  • 打赏
  • 举报
回复
什么意思大佬
卖水果的net 2020-06-25
  • 打赏
  • 举报
回复
        <foreach collection="list" index="index" item="item" separator=";"> 这个 update 循环,放在 java 中,多次调用 mapper 中的方法 。 insert 和 delete 通过动态拼接的形式,可以直接在 mapper 中处理多行数据。

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧