MySql+Mybatis+Druid优化之MyBatis批量删除、更新业务需求：⼀次更新/删除多条数据，通常有两种⽅式：
（1）在业务代码中循环遍历，逐条删除，业务清晰；
（2）在sql语句中，循环删除，只操作⼀次数据库，这个分为两种⽅式：
⽅式A：通过循环⼀个id列表，循环删除数据；
⽅式B：条件为多个字段，为了更新多条记录为多个字段的不同值；
补充：这⾥的删除采⽤软删除，使⽤⼀个字段进⾏标记，所以，所有的删除实际就是更新。
正⽂：
（1）业务中循环删除
Mapper
<deleteid="deleteSingleProject"parameterType="java.lang.String">
updatet_single_project_scoresetis_delete=1,update_time=now(),operator=#{operator,jdbcType=VARCHAR}
wherestudent_id=#{studentId,jdbcType=VARCHAR}
andschool_year=#{schoolYear,jdbcType=VARCHAR}
andis_delete=0
</delete>
service业务⽅法
deleteSPByList接⼝
/**
* 批量删除-在业务层实现批量删除-徐玲博-2017-12-11 16:25:19
* @param singleProjectScoreEntityList 单项⽬成绩list
* @param operator 操作⼈
* @return 受影响⾏
*/
int deleteSPByList(List<SingleProjectScoreEntity> singleProjectScoreEntityList, String operator);
deleteSPByList实现
*批量删除-在业务层实现批量删除-徐玲博-2017-12-11 16:25:19
*
*@paramsingleProjectScoreEntityList单项⽬成绩list
*@paramoperator操作⼈
*@return受影响⾏
*/
@Override
Public int deleteSPByList(List<SingleProjectScoreEntity>singleProjectScoreEntityList,Stringoperator){ intresult=0;
for(SingleProjectScoreEntitysingleProjectScoreEntity:singleProjectScoreEntityList){
StudentId();
SchoolYear();
result=deleteSingleProject(studentId,schoolYear,operator);
result+=result;//删除数据的条数
}
returnresult;
}
调⽤的deleteSingleProject⽅法
/**
* 根据学⽣id及学年删除项⽬成绩-徐玲博-2017-12-11 16:35:52
批量更新sql语句*
* @param studentId 学⽣id
* @param schoolYear 学年
* @param operator 操作⼈
* @return 受影响⾏
*/
@Override
@Transactional(rollbackFor = Exception.class)
public int deleteSingleProject(String studentId, String schoolYear, String operator) {
return singleProjectScoreDao.deleteSingleProject(studentId, schoolYear, operator);
}
单元测试：
* 批量删除-项⽬成绩-徐玲博-2017-12-11 16:27:11
*/
@Test
public void deleteSingleProjectList() {
List<SingleProjectScoreEntity> singleProjectScoreEntityList = new ArrayList<>();
SingleProjectScoreEntity singleProjectScoreEntity = new SingleProjectScoreEntity();
singleProjectScoreEntity.setStudentId("BrRdBHWG1w8Vv1ZPK3qS3n");
singleProjectScoreEntity.setSchoolYear("1");
SingleProjectScoreEntity singleProjectScoreEntity1 = new SingleProjectScoreEntity();
singleProjectScoreEntity1.setStudentId("1bDN62uHbiPMS9fUPpFHS");
singleProjectScoreEntity1.setSchoolYear("1");
singleProjectScoreEntityList.add(singleProjectScoreEntity);
singleProjectScoreEntityList.add(singleProjectScoreEntity1);
String operator = "xlb";
if (singleProjectFacade.deleteSingleProjectList(singleProjectScoreEntityList, operator) > 0) {
assert true : "正确";
} else {
assert false : "失败";
}
}
每循环⼀次list就会建⽴和断开与数据库的联系
#-----2017-12-11 16:33:05:408 | took 19ms | statement | connection 0
update t_single_project_score set is_delete = 1 , update_time=now() , operator = 'xlb'
where student_id = 'BrRdBHWG1w8Vv1ZPK3qS3n'
and school_year='1'
and is_delete = 0;
#-----2017-12-11 16:33:05:442 | took 17ms | statement | connection 1
update t_single_project_score set is_delete = 1 , update_time=now() , operator = 'xlb'
where student_id = '1bDN62uHbiPMS9fUPpFHS'
and school_year='1'
and is_delete = 0;
结果：is_delete更新成1，删除成功
优点：数据量⼩，业务逻辑清晰，实现的简单，如果有逆向⼯程例⼦，⾃⼰不⽤写sql语句。
（2）在sql语句中，循环删除
借助mybatis的<foreach>语法来拼凑成了批量更新的sql。
⽅式A：通过循环⼀个id列表，循环删除数据
mybatis
<!--l-根据list（包含id）删除-->
<delete id="deleteByIds">
update t_single_project_score set is_delete = 1 , update_time=now() , operator = #{operator,jdbcType=VARCHAR}
where id in (
<foreach collection="list" item="id" separator=",">
#{id}
</foreach>
) and is_delete = 0
</delete>
service层没有写过多逻辑，只承担了数据传递的责任，此处省略
单元测试
/**
* 【测试】-根据ids删除项⽬-徐玲博-2017-12-11 15:06:29
*/
@Test
public void delteByIds() {
List<String> ids = new ArrayList<>();
ids.add("17ZZjzWjbqTQWcMDn9Q2Jp");
ids.add("2BxDmxDFbe1iG5DdWAeRxB");
String operator = "徐玲博-测试";
if (singleProjectFacade.delteteByIds(ids, operator) > 0) {
assert true : "成功";
} else {
assert false : "失败";
}
}
编译得到sql语句：
update t_single_project_score set is_delete = 1 , update_time=now() , operator = '徐玲博-测试'
where id in (
'17ZZjzWjbqTQWcMDn9Q2Jp'
,
'2BxDmxDFbe1iG5DdWAeRxB'
) and is_delete = 0;
查看数据库结果：
优点：相对于在业务层处理，可以通过⼀个sql就搞定，如果数据量⾮常⼤的时候，不会频繁建⽴、断开与数据库的联系，这样还是能够看到性能上的挺⼤提⾼的。
⽅式B：条件为多个字段，为了更新多条记录为多个字段的不同值
在where条件中，有两个以上的条件，就不能再⽤简单循环id的⽅式了，以下有第⼆种⽅式的解决⽅案：mysql⽅式：
<delete id="deleteSingleProjectByIds" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
UPDATE
t_single_project_score
<set>
update_time=now() ,
is_delete = 1 ,
operator =#{operator,jdbcType=VARCHAR}
</set>
WHERE
<if test="item.studentId!=null">
student_id IN (#{item.studentId,jdbcType=VARCHAR})
</if>
<if test="item.schoolYear!=null">
AND school_year IN(
#{item.schoolYear,jdbcType=VARCHAR}
)
</if>
AND is_delete = 0
</foreach>
</delete>
oracle⽅式(没有⽤到oracle，此处没有做测试，仅供记录、参考):
<update id="batchUpdate" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="begin" close="end;" separator=";">
update test
<set>
test=${st}+1
</set>
where id = ${item.id}
</foreach>
</update>
foreach中各项解释
list：参数是list类型
item：list中的项
index
open
close
separator：以 ‘，’ ‘；’ 隔开
Test 单元测试
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论