mysql批量插入与oracle批量插入不同
一、搭建数据库
tbl_employee表【d_id 是外键 对应 tbl_dept表】
id last_name gender email d_id
1 mike 0 [email protected] 1
2 book 0 [email protected] 2
3 tom 1 [email protected] 2
4 jerry 1 [email protected] 1
tbl_dept表：
id dept_name
1 开发部
2 测试部
二、编辑javabean
package com.mybatis.bean;
public class Employee {
private Integer id;
private String lastName;
private String email;
private String gender;
private Department department;
public Department getDepartment() {
return department;
}
public void setDepartment(Department department) {
this.department = department;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
@Override
public String toString() {
return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]";
}
/***** 关键是 这个地方啊 出了 问题 ****/
public Employee() {
super();
}
/*****/
public Employee(Integer id,String lastName, String email, String gender){
this.id =id;
this.lastName =lastName;
this.email =email;
this.gender =gender;
}
public Employee(Integer id,String lastName, String email, String gender,Department dep){
this.id =id;
this.lastName =lastName;
this.email =email;
this.gender =gender;
this.department =dep;
}
// 历史教训 ，一开始 没有添加 无参构造函数，而添加了下面这个函数
// public Employee(Integer id,String lastName, String email, String gender,int temp){
// this.id =id;
// this.lastName =lastName;
// this.email =email;
// this.gender =gender;
//
// System.out.println(" id:"+id+" lastName:"+lastName+" email:"+email+" gender:"+gender+" temp:"+temp);
// }
}
package com.mybatis.bean;
import java.util.List;
public class Department {
private Integer id;
private String departmentName;
private List<Employee> employees;
public Department() {
}
public Department(int id){
this.id = id;
}
public List<Employee> getEmployees() {
return employees;
}
public void setEmployees(List<Employee> employees) {
this.employees = employees;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getDepartmentName() {
return departmentName;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
@Override
public String toString() {
return "Department [id=" + id + ", departmentName=" + departmentName + "]";
}
}
三、编辑junit测试
@Test
public void testBatchSave() throws IOException{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try{
EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
List<Employee> emps = new ArrayList<>();
emps.add(new Employee(null, "smith0x1", "[email protected]", "1" , new Department(1) ));
emps.add(new Employee(null, "allen0x1", "[email protected]", "0" , new Department(1) ));
mapper.addEmps(emps);
openSession.commit();
}finally{
openSession.close();
}
}
四、编辑SQl映射文件
1、mysql 版
<!--MySQL下批量保存：可以foreach遍历 mysql支持values(),(),()语法-->
<!-- 批量保存 -->
<!--public void addEmps(@Param("emps")List<Employee> emps); -->
<!--MySQL下批量保存：可以foreach遍历 mysql支持values(),(),()语法-->
<insert id="addEmps">
insert into tbl_employee(last_name,email,gender,d_id)
values
<foreach collection="emps" item="emp" separator=",">
(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.department.id})
</foreach>
</insert><!-- -->
<!-- 这种方式需要数据库连接属性allowMultiQueries=true；
这种分号分隔多个sql可以用于其他的批量操作（删除，修改） -->
<!-- <insert id="addEmps">
<foreach collection="emps" item="emp" separator=";">
insert into tbl_employee(last_name,email,gender,d_id)
values(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.department.id})
</foreach>
</insert> -->
mysql补充：在mysql配置时，我们可能需要配置 allowMultiQueries=true
jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true
若一个sql中通过分号分割(或包含)了多个独立sql的话,如:
select 'hello';select 'world'
就会报如下错：
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 'select 'world'' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
当若显式设置allowMultiQueries为true的话,就可以正常执行不会报错.如下所示:
String url = "jdbc:mysql://localhost:3306?allowMultiQueries=true";
官方文档解释:
allowMultiQueries
Allow the use of ';' to delimit multiple queries during one statement (true/false), defaults to 'false', and does not affect the addBatch() and executeBatch() methods, which instead rely on rewriteBatchStatements.
Default: false
Since version: 3.1.1
2、oracle版
<!-- Oracle数据库批量保存：
Oracle不支持values(),(),()
Oracle支持的批量方式
1、多个insert放在begin - end里面
begin
insert into employees(employee_id,last_name,email)
values(employees_seq.nextval,'test_001','[email protected]');
insert into employees(employee_id,last_name,email)
values(employees_seq.nextval,'test_002','[email protected]');
end;
2、利用中间表：
insert into employees(employee_id,last_name,email)
select employees_seq.nextval,lastName,email from(
select 'test_a_01' lastName,'test_a_e01' email from dual
union
select 'test_a_02' lastName,'test_a_e02' email from dual
union
select 'test_a_03' lastName,'test_a_e03' email from dual
)
-->
实际写法：
<insert id="addEmps" databaseId="oracle">
<!-- oracle第一种批量方式 -->
<!-- <foreach collection="emps" item="emp" open="begin" close="end;">
insert into employees(employee_id,last_name,email)
values(employees_seq.nextval,#{emp.lastName},#{emp.email});
</foreach> -->
<!-- oracle第二种批量方式 -->
insert into employees(
<!-- 引用外部定义的sql -->
<include refid="insertColumn">
<property name="testColomn" value="abc"/>
</include>
)
<foreach collection="emps" item="emp" separator="union"
open="select employees_seq.nextval,lastName,email from("
close=")">
select #{emp.lastName} lastName,#{emp.email} email from dual
</foreach>
</insert>
特别提醒一下：下面的引用其实就是 待插入的 各个字段啦。
<!-- 引用外部定义的sql -->
<include refid="insertColumn">
<property name="testColomn" value="abc"/>
</include>
用法说明：不是只能用在批量插入中，也可以用在其他数据操作中，因为只是对SQL片段的抽取。
<!--
抽取可重用的sql片段。方便后面引用
1、sql抽取：经常将要查询的列名，或者插入用的列名抽取出来方便引用
2、include来引用已经抽取的sql：
3、include还可以自定义一些property，sql标签内部就能使用自定义的属性
include-property：取值的正确方式${prop},
#{不能使用这种方式}
4、employee_id,last_name,email,${prop} 这个oracle插入 其实等价于【当然数据库有没有相应字段是另一回事，在这里只是用来演示】 employee_id,last_name,email,abc
5、_databaseId 是 mybatis 内置参数，和在 全局配置文件中，标记的数据库 名是 一样的
-->
<sql id="insertColumn">
<if test="_databaseId=='oracle'">
employee_id,last_name,email,${prop}
</if>
<if test="_databaseId=='mysql'">
last_name,email,gender,d_id
</if>
</sql>