mybatis中的<foreach>标签实现批量添加

mybatis中的标签实现批量添加

---

需求

• 使用Map集合，向emp表中批量添加数据。

表展示

• 字段展示
  

---

第一步：创建dao层接口

package com.aaa.mb.dao;

import java.util.List;
import java.util.Map;

/**
 * className:EmpDao
 * discription:
 * author:zz
 * createTime:2018-11-14 10:47
 */
public interface EmpDao {
    /**
     * 批量添加员工信息(mybatis动态SQL  foreach用法)
     * @param mapList
     * @return
     */
    int batchAdd(List<Map> mapList);
}

第二步：编写Mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.aaa.mb.dao.EmpDao">
    <!--批量添加员工信息(mybatis动态SQL  foreach用法)
       拼接后的语句
        insert into emp(empno,ename,job,sal,hiredate,deptno)
        select seq_emp_id.nextval,a.* from (
        select 'zs','clerk',1000,sysdate,10 from dual union all
        select 'zs','clerk',1000,sysdate,10 from dual union all
        select 'zs','clerk',1000,sysdate,10 from dual
        ) a
    -->
    <insert id="batchAdd" >
          insert into emp(empno,ename,job,sal,hiredate,deptno)
          select seq_emp_id.nextval,a.* from (
              <foreach collection="list"  separator="union all" item="empMap">
                  select #{empMap.ename},#{empMap.job}
                        ,#{empMap.sal},to_date(#{empMap.hiredate},'yyyy-mm-dd'),10 from dual
              </foreach>
          ) a
    </insert>
</mapper>

第三步:编写测试类

    @Test
    public void testBatchAdd(){
        SqlSession sqlSession = null;
        try {
            sqlSession = SqlSessionFactoryUtil.getSession();
            EmpDao empDao = sqlSession.getMapper(EmpDao.class);
            List<Map> paramList  = new ArrayList();
            Map map  = new HashMap();
            map.put("ename","zhangsan1");
            map.put("job","clerk");
            map.put("sal","10000");
            map.put("hiredate","2018-11-15");
            paramList.add(map);

            Map map1  = new HashMap();
            map1.put("ename","zhangsan2");
            map1.put("job","clerk");
            map1.put("sal","8000");
            map1.put("hiredate","2018-11-14");
            paramList.add(map1);

            Map map2  = new HashMap();
            map2.put("ename","zhangsan3");
            map2.put("job","clerk");
            map2.put("sal","9000");
            map2.put("hiredate","2018-11-13");
            paramList.add(map2);

            int i = empDao.batchAdd(paramList);
            if(i>0){
                System.out.println("添加成功！");
                sqlSession.commit();
            }else {
                System.out.println("添加失败！");
                sqlSession.rollback();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(sqlSession!=null){
                sqlSession.close();
            }
        }
    }