mybatis动态sql之使用foreach进行批量插入的两种方式

概述EmployeeMapperDynamicSql.java package com.gong.mybatis.mapper; import java.util.List; import java.ut

EmployeeMapperDynamicsql.java

package com.gong.mybatis.mapper;import java.util.List; java.util.Map; org.apache.ibatis.annotations.MapKey; org.apache.ibatis.annotations.Param; com.gong.mybatis.bean.Employee;public interface EmployeeMapperDynamicsql {    public List<Employee> getEmpByConditionIf(Employee employee);        voID addEmp(@Param("emps") List<Employee> employee);}

EmployeeMapperDynamicsql.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.gong.mybatis.mapper.EmployeeMapperDynamicsql">    <!-- 查询，要查那个就带上那个条件 -->    select ID="getEmpByConditionIf" resultType="com.gong.mybatis.bean.Employee">        select * from tbl_employee         where>            choose>                when test="ID!=null"                    ID=#{ID}                </when="lastname!=null"                    last_name like #{lastname}                ="email!=null"                    email=#{email}                otherwise>        select>        insert ="addEmp"        insert into tbl_employee(last_name,gender,email,d_ID)        values        foreach collection="emps" item="emp" separator=","            (#{emp.lastname},#{emp.gender},#{emp.email},#{emp.dept.ID})        foreachinsert>mapper>

第一种方式如上所示：insert into tbl_employee(last_name,d_ID) values (?,?,?),(?,?)

进行测试：

 com.gong.mybatis.test; java.io.IOException; java.io.inputStream; java.util.ArrayList; java.util.Arrays; java.util.HashMap; org.apache.ibatis.io.Resources; org.apache.ibatis.session.sqlSession; org.apache.ibatis.session.sqlSessionFactory; org.apache.ibatis.session.sqlSessionFactoryBuilder; org.junit.Test; com.gong.mybatis.bean.Department; com.gong.mybatis.bean.Employee; com.gong.mybatis.mapper.EmployeeMapperDynamicsql;class TestMybatis3 {        public sqlSessionFactory getsqlSessionFactory() throws IOException {        String resource = "mybatis-config.xml";        inputStream is = Resources.getResourceAsstream(resource);        return new sqlSessionFactoryBuilder().build(is);    }    @Test    voID test()  IOException {        sqlSessionFactory sqlSessionFactory = getsqlSessionFactory();        sqlSession openSession = sqlSessionFactory.openSession();        try {            EmployeeMapperDynamicsql mapper = openSession.getMapper(EmployeeMapperDynamicsql.);            List<Employee> emps = new ArrayList<>();            Department d1 = new Department(1);            Employee e1 = new Employee(null,"manwang","1","[email protected]",d1);            Department d2 = new Department(2);            Employee e2 =  Employee());            for(Employee e:es) {                System.out.println(e);            }            openSession.commit();        } finally {            openSession.close();        }            }    }

输出结果：

DEBUG 01-21 21:16:38,183 ==>  Preparing: insert into tbl_employee(last_name,?)   (BaseJdbcLogger.java:145) DEBUG 01-21 21:16:38,261 ==> Parameters: manwang(String),1(String),[email protected](String),1(Integer),timo(String),0(String),[email protected](String),2(Integer)  (BaseJdbcLogger.java:145) DEBUG 01-21 21:16:38,374 ==    Updates: 2  (BaseJdbcLogger.java:145) DEBUG 01-21 21:16:38,385 ==>  Preparing: select * from tbl_employee   (BaseJdbcLogger.java:145) DEBUG 01-21 21:16:38,386 ==> Parameters:   (BaseJdbcLogger.java:145) DEBUG 01-21 21:16:38,430 <==      Total: 6  (BaseJdbcLogger.java:145) Employee [ID=1,lastname=dema,1)">genderemail[email protected],1)">dept=null]Employee [ID=2,1)">=jack,1)">[email protected],1)">=3,1)">=小红,1)">=0,1)">[email protected],1)">=4,1)">=小明,1)">[email protected],1)">=5,1)">=manwang,1)">[email protected],1)">=6,1)">=timo,1)">[email protected],1)">=null]

第二种方式需要在配置连接数据源时使用：

jdbc.url=jdbc:MysqL://localhost:3306/mybatis?allowMultiQuerIEs=true

然后在xml文件中：

    =";"            insert into tbl_employee(last_name,d_ID)            values            (#{emp.lastname},1)">> 

这种方式的sql形如：insert into tbl_employee(last_name,?) ; insert into tbl_employee(last_name,?)

结果：

DEBUG 01-21 21:34:32,747 ==>  Preparing: insert into tbl_employee(last_name,?)   (BaseJdbcLogger.java:145) DEBUG 01-21 21:34:32,823 ==> Parameters: manwang(String),2(Integer)  (BaseJdbcLogger.java:145) DEBUG 01-21 21:34:32,889 1  (BaseJdbcLogger.java:145) DEBUG 01-21 21:34:32,900 Preparing: select * from tbl_employee   (BaseJdbcLogger.java:145) DEBUG 01-21 21:34:32,902 Parameters:   (BaseJdbcLogger.java:145) DEBUG 01-21 21:34:32,966 <Total: 8  (BaseJdbcLogger.java:145) Employee [ID=7,1)">=8,1)">=null]

总结

以上是内存溢出为你收集整理的mybatis动态sql之使用foreach进行批量插入的两种方式全部内容，希望文章能够帮你解决mybatis动态sql之使用foreach进行批量插入的两种方式所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。