16、mybatis动态sql 批量插入

1、EmployeeMapper

package com.mi.dao;

import com.mi.pojo.Employee;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Param;

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

public interface EmployeeMapper {

    public void insertEmps(@Param("emps") List<Employee> emps);

}

2、EmployeeMapper.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.mi.dao.EmployeeMapper">
    <!--id 为接口的方法名-->  
    <insert id="insertEmps">
        insert into employee (last_name,gender,d_id)
        values
        <foreach collection="emps" item ="emp" separator=",">
            (#{emp.lastName},#{emp.gender},#{emp.dept.id})
        </foreach>
    </insert>
</mapper>

3、Test

package com.mi.test;

import com.mi.dao.DeptMapper;
import com.mi.dao.EmployeeMapper;
import com.mi.pojo.Dept;
import com.mi.pojo.Employee;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class TestMybatis {

    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource  = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Test
    public void insertEmps() throws IOException {
        //1、获取SqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        //2、获取Sqlsesion对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            //3、获取接口的实现类对象
            //会为接口自动创建一个代理对象，代理对象去执行增删改查方法
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
            List<Employee> emps = new ArrayList<Employee>();
            emps.add(new Employee(null,"smith","1",new Dept(1)));
            emps.add(new Employee(null,"lucy","0",new Dept(1)));
            mapper.insertEmps(emps);
            sqlSession.commit();
        }finally {
            sqlSession.close();
        }
    }
}

4、以分号间隔执行每条语句（第二种方式）

  <insert id="insertEmps">
        <foreach collection="emps" item="emp" separator=";">
            insert into employee (last_name,gender,d_id)
            values(#{emp.lastName},#{emp.gender},#{emp.dept.id})
        </foreach>
    </insert>

5、Oracle下的批量插入（方式一）

    <!--Oracle下的批量插入-->
    <insert id="insertEmps">
        <foreach collection="emps" item="emp" open="begin" close="end;">
            insert into emp (empno,ename,deptno)
            values (emp_seq.nextval,#{emp.lastName},#{emp.dept.id});
        </foreach>
    </insert>

6、Oracle下的批量插入（方式二）

  <!--Oracle下的批量插入第二种方式-->
    <insert id="insertEmps">
        insert into emp (empno,ename,deptno)
            select emp_seq.nextval, last_name,d_id from (
                <foreach collection="emps" item="emp" separator="union">
                    select #{emp.lastName} last_name,#{emp.dept.id} d_id from dual
                </foreach>
            )
    </insert>