foreach 遍历map 进行insert
####测试代码
@Autowired
private UserMapper userMapper;
@RequestMapping("/test")
public void test(){
Map<String,String> map=new HashMap<>();
map.put("tom","20");
map.put("tom2","222");
userMapper.test(map);
}
复制代码
mybatis xml
<insert id="test">
insert into mmall_user (username,password) values
<foreach collection="map" index="key" item="value" open="" separator="," close="">
(#{key,jdbcType=VARCHAR},#{value,jdbcType=VARCHAR})
</foreach>
</insert>
复制代码
输出
2018-08-19 20:58:44.391 DEBUG 7972 --- [nio-8080-exec-5] com.llt.mmall.Dao.UserMapper.test : ==> Preparing: insert into mmall_user (username,password) values (?,?) , (?,?)
2018-08-19 20:58:44.392 DEBUG 7972 --- [nio-8080-exec-5] com.llt.mmall.Dao.UserMapper.test : ==> Parameters: tom(String), 20(String), tom2(String), 222(String)
复制代码
如果使用这种写法是会报错的
<insert id="test2">
<foreach collection="map" index="key" item="value" open="" separator="" close="">
insert into mmall_user (username,password) values (#{key,jdbcType=VARCHAR},#{value,jdbcType=VARCHAR});
</foreach>
</insert>
复制代码
错误提示
### 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 'insert into mmall_user (username,password) values ('tom2','222')' at line 3
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: insert into mmall_user (username,password) values (?,?); insert into mmall_user (username,password) values (?,?);
### 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 'insert into mmall_user (username,password) values ('tom2','222')' at line 3
; bad SQL grammar []; nested exception is 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 'insert into mmall_user (username,password) values ('tom2','222')' at line 3] with root cause
复制代码
可能是先因为是foreach循环,生成了两个预编译的SQL语句,这两个语句进系统的时候报的错。而采用第一种写法,只有一个预编译的SQL进系统,没有产生错误。
可以测试,下方情况也是不会报错的
测试代码
@RequestMapping("/test2")
public void test2(){
Map<String,String> map=new HashMap<>();
//map.put("tom","20");
map.put("tom23","222");
userMapper.test2(map);
}
复制代码
xml
<insert id="test2">
<foreach collection="map" index="key" item="value" open="" separator="" close="">
insert into mmall_user (username,password) values (#{key,jdbcType=VARCHAR},#{value,jdbcType=VARCHAR});
</foreach>
</insert>
复制代码
输出
2018-08-19 21:25:33.562 DEBUG 5340 --- [nio-8080-exec-1] com.llt.mmall.Dao.UserMapper.test2 : ==> Preparing: insert into mmall_user (username,password) values (?,?);
2018-08-19 21:25:33.582 DEBUG 5340 --- [nio-8080-exec-1] com.llt.mmall.Dao.UserMapper.test2 : ==> Parameters: tom23(String), 222(String)
2018-08-19 21:25:33.583 DEBUG 5340 --- [nio-8080-exec-1] com.llt.mmall.Dao.UserMapper.test2 : <== Updates: 1
复制代码
因此,应该是生成多条含有占位符的预编译语言进系统产生的问题。在采用foreach遍历的时候,不要讲整条语句进行foreach循环!
在mybatis中访问对象的成员变量
public class PP {
private String name;
private Integer id;
constructor,getter,setter~
}
复制代码
@RequestMapping("/test3")
public void test3(){
List<PP> list=new ArrayList<>();
list.add(new PP("tom",1));
list.add(new PP("tom2",2));
List<User> users=userMapper.test3(list);
users.forEach(user -> {
System.out.println(user.getUsername()+" "+user.getRole());
});
}
复制代码
<select id="test3" resultType="com.llt.mmall.Pojo.User">
select * from mmall_user
<where>
id in
<if test="list != null and list.size()>0">
<foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
#{item.id}// 在这里访问list中item(实际上是PP类)的成员变量id
</foreach>
</if>
</where>
</select>
复制代码
2018-08-21 23:12:27.927 DEBUG 2988 --- [nio-8080-exec-1] com.llt.mmall.Dao.UserMapper.test3 : ==> Preparing: select * from mmall_user WHERE id in ( ? , ? )
2018-08-21 23:12:27.927 DEBUG 2988 --- [nio-8080-exec-1] com.llt.mmall.Dao.UserMapper.test3 : ==> Parameters: 1(Integer), 2(Integer)
2018-08-21 23:12:28.095 DEBUG 2988 --- [nio-8080-exec-1] com.llt.mmall.Dao.UserMapper.test3 : <== Total: 2
admin 1
geely 0
复制代码