mybatis的批量update
方法有三种：1.通过java代码batch方式，xml文件只需一条update语句。java代码繁琐
2.xml使用foreach，“;”分割多条update语句，要求：jdbc的url需加上allowMultiQueries=true。速度慢
3.xml使用foreach进行代码拼接，用了case...when...then...end，oracle好像不支持这种写法，mysql和sqlserver是可以的
xml文件：
<mapper namespace="com.dao.EmployeeDao">
<!--可以通过改变实体类的字段来验证映射-->
<update id="updateFirstWay">
update employee set employee_name = #{employeeName},
employee_gender = #{employeeGender},
employee_salary = #{employeeSalary},
dept_id = #{deptId}
where employee_id = #{employeeId}
</update>
<update id="updateSecondWay">
<foreach collection="list" item="emp" separator=";">
update employee set employee_name = #{emp.employeeName},
employee_gender = #{emp.employeeGender},
employee_salary = #{emp.employeeSalary},
dept_id = #{emp.deptId}
where employee_id = #{emp.employeeId}
</foreach>
</update>
<update id="updateThirdWay">
update employee set
employee_name= case employee_id
<foreach collection="list" item="emp">
when #{emp.employeeId} then #{emp.employeeName}
</foreach>
end, employee_gender= case employee_id
<foreach collection="list" item="emp">
when #{emp.employeeId} then #{emp.employeeGender}
</foreach>
end, employee_salary= case employee_id
<foreach collection="list" item="emp">
when #{emp.employeeId} then #{emp.employeeSalary}
</foreach>
end,dept_id= case employee_id
<foreach collection="list" item="emp">
when #{emp.employeeId} then #{emp.deptId}
</foreach>
end
where
<foreach collection="list" item="emp" separator="or">
employee_id = #{emp.employeeId}
</foreach>
</update>
</mapper>
实体类：
package com.entity;
import java.math.BigDecimal;
public class Employee {
private Integer employeeId;
private String employeeName;
private int employeeGender;
private BigDecimal employeeSalary;
private Integer deptId;
public Employee() {
}
public Employee(Integer employeeId, String employeeName, int employeeGender, BigDecimal employeeSalary, Integer deptId) {
this.employeeId = employeeId;
this.employeeName = employeeName;
this.employeeGender = employeeGender;
this.employeeSalary = employeeSalary;
this.deptId = deptId;
}
@Override
public String toString() {
return "Employee{" +
"employeeId=" + employeeId +
", employeeName='" + employeeName + '\'' +
", employeeGender=" + employeeGender +
", employeeSalary=" + employeeSalary +
", deptId='" + deptId + '\'' +
'}';
}
public Integer getEmployeeId() {
return employeeId;
}
public void setEmployeeId(Integer employeeId) {
this.employeeId = employeeId;
}
public String getEmployeeName() {
return employeeName;
}
public void setEmployeeName(String employeeName) {
this.employeeName = employeeName;
}
public int getEmployeeGender() {
return employeeGender;
}
public void setEmployeeGender(int employeeGender) {
this.employeeGender = employeeGender;
}
public BigDecimal getEmployeeSalary() {
return employeeSalary;
}
public void setEmployeeSalary(BigDecimal employeeSalary) {
this.employeeSalary = employeeSalary;
}
public Integer getDeptId() {
return deptId;
}
public void setDeptId(Integer deptId) {
this.deptId = deptId;
}
}
EmployeeDao.class:
void updateFirstWay(Employee employee);
int updateSecondWay(List<Employee> employees);
int updateThirdWay(List<Employee> employees);
test代码：
@Test
public void testUpdateFirstWay() {
List<Employee> employees = new ArrayList<>();
employees.add(new Employee(10,"傲慢", 1, new BigDecimal(9999), 1));
employees.add(new Employee(11,"色欲", 0, new BigDecimal(8888), 2));
employees.add(new Employee(12,"怠惰", 1, new BigDecimal(7777), 3));
employees.add(new Employee(13,"贪婪", 1, new BigDecimal(6666), 4));
employees.add(new Employee(14,"愤怒", 1, new BigDecimal(5555), 5));
SqlSessionFactory sqlSessionFactory = SqlSessionFactoryUtil.getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
EmployeeDao mapper = sqlSession.getMapper(EmployeeDao.class);
try {
for (int i = 0; i < employees.size(); i ++) {
mapper.updateFirstWay(employees.get(i));
if (i % 2 == 0 || i == employees.size() - 1) {
sqlSession.commit();
}
}
} catch (Exception e) {
sqlSession.rollback();
} finally {
sqlSession.close();
}
}
@Test
public void testUpdateSecondWay() {
List<Employee> employees = new ArrayList<>();
employees.add(new Employee(15,"傲慢", 1, new BigDecimal(9999), 1));
employees.add(new Employee(16,"色欲", 1, new BigDecimal(8888), 2));
employees.add(new Employee(17,"怠惰", 1, new BigDecimal(7777), 3));
employees.add(new Employee(18,"贪婪", 0, new BigDecimal(6666), 4));
employees.add(new Employee(19,"愤怒", 1, new BigDecimal(5555), 5));
EmployeeDao employeeDao = MapperFactory.generateMapper(EmployeeDao.class);
employeeDao.updateSecondWay(employees);
}
@Test
public void testUpdateThirdWay() {
List<Employee> employees = new ArrayList<>();
employees.add(new Employee(1,"傲慢", 1, new BigDecimal(9999), 1));
employees.add(new Employee(2,"色欲", 1, new BigDecimal(8888), 2));
EmployeeDao employeeDao = MapperFactory.generateMapper(EmployeeDao.class);
employeeDao.updateThirdWay(employees);
}
总结：#{}里面写的是实体类的字段，已经测试过了。
最后：根据实际要求，每个字段是要判断是否为空的，例
<update id="updateFirstWay">
update employee set
<if test="b != null and b != ''">
employee_name = #{b},
</if>
<if test="c != null and c != ''">
employee_gender = #{c},
</if>
<if test="d != null and d != ''">
employee_salary = #{d},
</if>
<if test="e != null and e !=''">
dept_id = #{e}
</if>
where employee_id = #{a}
</update>
因为当只修改部分数据库的列的值，而不是全部列的时候，就需要将不要修改的字段跳过。上面的代码有漏洞，当所有列都不修改的时候会出错，修改代码的话以后再说，累了
有个bug:数据库的binary类型，实体类写的Integer，但是写在ongl里面判断x != null and x != ''，如果是0的话，那么这句话会跳过，false
