准备
假设MySQL中存在这样一张表:
表名:tbl_employee,字段:id、name、gender、email
Oracle中存在这样一张表:
表名:tbl_employee,字段:id、name、gender、email,序列:seq_employee
有这样一个接口:
public interface EmployeeDao {
void addEmployees(@Param("emps") List<Employee> employees);
}
对应实体:
public class Employee {
private Integer id;
private String name;
private String gender;
private String email;
// getter and setter ...
}
MySQL批量插入
MySQL支持语法:INSERT INTO tbl_name (col1, col2...) VALUES (val1, val2...), (val3, val4...);
方式一(推荐):
<!--
databaseId:数据库标识,它的取值来自于mybatis-config.xml(主配置文件)
配置的databaseIdProvider,如:
<databaseIdProvider type="DB_VENDOR">
<property name="MySQL" value="mysql"/>
<property name="Oracle" value="oracle"/>
</databaseIdProvider>
相当于为不同的数据库厂商起别名,MyBatis会加载所有的语句,如果id相同,
没有databaseId的语句将被丢弃
collection:传入的集合参数
item:集合的每一项
separator:每次遍历结果的分隔符
-->
<insert id="addEmployees" databaseId="mysql">
INSERT INTO tbl_employee (name, gender, email) VALUES
<foreach collection="emps" item="emp" separator=",">
(#{emp.name}, #{emp.gender}, #{emp.email})
</foreach>
</insert>
方式二:
<!--
该方式需要数据库连接属性allowMultiQueries=true
这种分号分隔多条sql的可以用于其它批量操作(删除、修改)
-->
<insert id="addEmployees" databaseId="mysql">
<foreach collection="emps" item="emp" separator=";">
INSERT INTO tbl_employee (name, gender, email) VALUES
(#{emp.name}, #{emp.gender}, #{emp.email})
</foreach>
</insert>
Oracle批量插入
由于Oracle不支持类似VALUES的语法,批量的方式有如下两种:
方式一:多条INSERT语句放在BEGIN - END语句块里面(推荐)
<!--
语法:
BEGIN
INSERT INTO tbl_name (col1, col2...) VALUES (val1, val2...);
INSERT INTO tbl_name (col1, col2...) VALUES (val1, val2...);
INSERT INTO tbl_name (col1, col2...) VALUES (val1, val2...);
END;
注意:END后面需要分号,每条sql语句结束需要分号
-->
<insert id="addEmployees" databaseId="oracle">
<foreach collection="emps" item="emp" open="BEGIN" close="END;">
INSERT INTO tbl_employee (id, name, gender, email) VALUES
(seq_employee.nextval, #{emp.name}, #{emp.gender}, #{emp.email});
</foreach>
</insert>
方式二:利用中间表
<!--
语法:
INSERT INTO tbl_name (id, col1, col2)
SELECT seq_name.nextval, col1, col2 FROM (
SELECT val1 col1, val2 col2 FROM DUAL
UNION
SELECT val1 col1, val2 col2 FROM DUAL
UNION
SELECT val1 col1, val2 col2 FROM DUAL
...
)
注意:SELECT val1 col1, val2 col2 FROM DUAL中,col1、col2为字段别名
-->
<insert id="addEmployees" databaseId="oracle">
INSERT INTO tbl_employee (id, name, gender, email)
SELECT seq_employee.nextval, name, gender, email FROM (
<foreach collection="emps" item="emp" separator="UNION">
SELECT #{emp.name} name, #{emp.gender} gender, #{emp.email} email FROM DUAL
</foreach>
)
</insert>