mybatis 遍历Map

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
复制代码

转载于:https://juejin.im/post/5b796d1fe51d4538807126ed