今天想了半天的问题
怎么使用mybatis动态sql进行批量修改,经过不懈努力还是干了它;
首先是分隔符的问题,怎么让每一条sql语句都加上分号呢?
经过查资料还是知道了JDBC有个功能 allowMultiQueries=true参数
能使sql语句用分号作为分隔符。
该问题解决了,还有一个问题,怎样使参数用逗号分隔,深思熟虑,想起来还有一个trim标签的suffixOverrides属性可以用。
<update id="updateEmps">
<foreach collection="emps" item="emp" separator=";">
<!-- insert into tbl_employee(last_name,email,gender,d_id)
values(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id}) -->
update tbl_employee
<trim suffixOverrides=",">
<set>
<if test="emp.lastName!=null">last_name = #{emp.lastName},</if>
<if test="emp.email!=null">email = #{emp.email},</if>
<if test="emp.gender!=null">gender = #{emp.gender},</if>
<if test="emp.dept.id!=null">d_id = #{emp.dept.id},</if>
</set>
</trim>
where id =#{emp.id}
</foreach>
</update>
@Test
public void testBatchsave() throws IOException{
SqlSessionFactory sessionFactory = getSqlSessionFactory();
SqlSession openSession = sessionFactory.openSession();
try {
EmployeeMapperDynamicSQL Mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
List<Employee> emp = new ArrayList<Employee>();
emp.add(new Employee(11, "liuxing", null, null,new Department(1)));
emp.add(new Employee(10, null, "163@.com",null,new Department(2)));
Mapper.updateEmp(emp);
openSession.commit();
} finally {
openSession.close();
}
}
看到成功,一身轻松。