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

EmployeeMapperDynamicSql.java

packagecom.gong.mybatis.mapper;importjava.util.List;importjava.util.Map;importorg.apache.ibatis.annotations.MapKey;importorg.apache.ibatis.annotations.Param;importcom.gong.mybatis.bean.Employee;public interfaceEmployeeMapperDynamicSql {public ListgetEmpByConditionIf(Employee employee);public void addEmp(@Param("emps") Listemployee);

}

EmployeeMapperDynamicSql.xml

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

select * from tbl_employee

id=#{id}

last_name like #{lastName}

email=#{email}

insert into tbl_employee(last_name,gender,email,d_id)

values(#{emp.lastName},#{emp.gender},#{emp.email},#{emp.dept.id})

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

进行测试：

packagecom.gong.mybatis.test;importjava.io.IOException;importjava.io.InputStream;importjava.util.ArrayList;importjava.util.Arrays;importjava.util.HashMap;importjava.util.List;importjava.util.Map;importorg.apache.ibatis.io.Resources;importorg.apache.ibatis.session.SqlSession;importorg.apache.ibatis.session.SqlSessionFactory;importorg.apache.ibatis.session.SqlSessionFactoryBuilder;importorg.junit.Test;importcom.gong.mybatis.bean.Department;importcom.gong.mybatis.bean.Employee;importcom.gong.mybatis.mapper.EmployeeMapperDynamicSql;public classTestMybatis3 {public SqlSessionFactory getSqlSessionFactory() throwsIOException {

String resource= "mybatis-config.xml";

InputStream is=Resources.getResourceAsStream(resource);return newSqlSessionFactoryBuilder().build(is);

}

@Testpublic void test() throwsIOException {

SqlSessionFactory sqlSessionFactory=getSqlSessionFactory();

SqlSession openSession=sqlSessionFactory.openSession();try{

EmployeeMapperDynamicSql mapper= openSession.getMapper(EmployeeMapperDynamicSql.class);

List emps = new ArrayList<>();

Department d1= new Department(1);

Employee e1= new Employee(null,"manwang","1","manwang@qq.com",d1);

Department d2= new Department(2);

Employee e2= new Employee(null,"timo","0","timo@qq.com",d2);

emps.add(e1);

emps.add(e2);

mapper.addEmp(emps);

List es = mapper.getEmpByConditionIf(newEmployee());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,gender,email,d_id) values (?,?,?,?) , (?,?,?,?) (BaseJdbcLogger.java:145)

DEBUG 01-21 21:16:38,261 ==> Parameters: manwang(String), 1(String), manwang@qq.com(String), 1(Integer), timo(String), 0(String), timo@qq.com(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,gender=1,email=dema@qq.com,dept=null]Employee [id=2,lastName=jack,gender=1,email=675544321@qq.com,dept=null]Employee [id=3,lastName=小红,gender=0,email=xiaohong@qq.com,dept=null]Employee [id=4,lastName=小明,gender=0,email=xiaoming@qq.com,dept=null]Employee [id=5,lastName=manwang,gender=1,email=manwang@qq.com,dept=null]Employee [id=6,lastName=timo,gender=0,email=timo@qq.com,dept=null]

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

jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true

然后在xml文件中：

insert into tbl_employee(last_name,gender,email,d_id)

values

(#{emp.lastName},#{emp.gender},#{emp.email},#{emp.dept.id})

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

结果：

DEBUG 01-21 21:34:32,747 ==> Preparing: insert into tbl_employee(last_name,gender,email,d_id) values (?,?,?,?) ; insert into tbl_employee(last_name,gender,email,d_id) values (?,?,?,?) (BaseJdbcLogger.java:145)

DEBUG 01-21 21:34:32,823 ==> Parameters: manwang(String), 1(String), manwang@qq.com(String), 1(Integer), timo(String), 0(String), timo@qq.com(String), 2(Integer) (BaseJdbcLogger.java:145)

DEBUG 01-21 21:34:32,889<== Updates: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=1,lastName=dema,gender=1,email=dema@qq.com,dept=null]Employee [id=2,lastName=jack,gender=1,email=675544321@qq.com,dept=null]Employee [id=3,lastName=小红,gender=0,email=xiaohong@qq.com,dept=null]Employee [id=4,lastName=小明,gender=0,email=xiaoming@qq.com,dept=null]Employee [id=5,lastName=manwang,gender=1,email=manwang@qq.com,dept=null]Employee [id=6,lastName=timo,gender=0,email=timo@qq.com,dept=null]Employee [id=7,lastName=manwang,gender=1,email=manwang@qq.com,dept=null]Employee [id=8,lastName=timo,gender=0,email=timo@qq.com,dept=null]