7. MyBatis批量数据处理

常用批量操作

7.1 批量新增数据

7.1.1 映射文件定义SQL

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//[mybatis.org//DTD](http://mybatis.org//DTD) Mapper 3.0//EN" "[http://mybatis.org/dtd/mybatis-3-mapper.dtd](http://mybatis.org/dtd/mybatis-3-mapper.dtd)">
<mapper namespace="cn.it.entity.DeptMapper">
    <!--resultMap用于查询，可以把查询后字段值封装到对应类的属性， type指定的是对应的实体类 -->
    <resultMap type="Dept" id="deptResultMap">
    <!-- id用来配置表的主键与类的属性的映射关系 ,column指定的是表的字段名； property指定的是类的属性名-->
    <id column="dept_id" property="deptId"/>
    <!-- result用来配置 普通字段与类的属性的映射关系 ,column指定的是表的字段名； property指定的是类的属性名-->
    <result column="dept_name" property="deptName"/>
    <result column="dept_address" property="deptAddress"/>
    </resultMap>
    <insert id="insertDeptList" >
         <!-- insert into dept(dept_name,dept_address) values('市场部','广州'),('行政部','广州'); -->
         insert into dept(dept_name,dept_address) values
         <foreach collection="list" item="dept" separator="," >
         (#{dept.deptName},#{dept.deptAddress})
         </foreach>
    </insert>
</mapper>

7.1.2 实现类DeptDaoImpl.java

package com.demo.dao.impl;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import com.demo.entity.Dept;
import com.demo.util.MyBatisUtil;
public class DeptDaoImpl {
    SqlSession session;
    //批量添加部门信息
    public int insertDeptList(List<Dept> depts){
         int i = 0;
         try {
             session = MyBatisUtil.getSession();
             i = session.insert("com.demo.entity.DeptMapper.insertDeptList", depts);
             session.commit();
         } catch (Exception e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
             session.rollback();
         }finally{
             try {
                 MyBatisUtil.closeSession();
             } catch (Exception e) {
                 // TODO Auto-generated catch block
                 e.printStackTrace();
             }
         }
         return i;
    }
}

7.1.3 测试类TestDeptDaoImpl.java

package com.demo.test;
import java.util.ArrayList;
import java.util.List;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import com.demo.dao.impl.DeptDaoImpl;
import com.demo.entity.Dept;
public class TestDeptDaoImpl {
    private static DeptDaoImpl deptDaoImpl;
    @BeforeClass
    public static void setUpBeforeClass() throws Exception {
         deptDaoImpl = new DeptDaoImpl();
    }

    @AfterClass
    public static void tearDownAfterClass() throws Exception {
         deptDaoImpl = null;
    }

    // 批量添加部门信息
    @Test
    public void testInsertDeptList() {
         List<Dept> depts = new ArrayList<Dept>();
         for (int i = 0; i < 5; i++) {
             Dept dept = new Dept();
             dept.setDeptName("deptName"+i);
             dept.setDeptAddress("deptAddress"+i);
             depts.add(dept);
         }
          System.out.println("受影响的行数："+deptDaoImpl.insertDeptList(depts));
    }
 }

4、测试效果：

image.png image.png

---

7.2 批量删除部门

7.2.1 映射文件定义SQL

<!-- 批量删除部门 -->
    <delete id="deleteDeptList" >
         delete from dept where dept_id in(
             <foreach collection="array" item="deptId" separator=",">
                 #{deptId}
             </foreach>
         )
    </delete>

7.2.2 编写批量删除部门的方法

//根据部门编号批量删除部门信息
    public int deleteDeptList(Integer[] deptIds){
         int i = 0;
         try {
             session = MyBatisUtil.getSession();
             i = session.delete("com.demo.entity.DeptMapper.deleteDeptList", deptIds);
             session.commit();
         } catch (Exception e) {
             e.printStackTrace();
             session.rollback();
         }finally{
             try {
                 MyBatisUtil.closeSession();
             } catch (Exception e) {
                 // TODO Auto-generated catch block
                 e.printStackTrace();
             }
         }
         return i;
    }

7.2.3 编写测试代码

    // 批量删除部门信息
    @Test
    public void testDeleteDeptList() {
         Integer [] deptIds = {5,7,8};
         System.out.println("受影响的行数："+deptDaoImpl.deleteDeptList(deptIds));
    }

7.2.4 测试效果

image.png image.png

---

7.3 批量更新数据

7.3.1 修改db.properties文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
name=root
password=

7.3.2 映射文件定义SQL

    <!-- 批量修改部门信息 -->
    <update id="updateDeptList" >
         <foreach collection="list" item="dept" separator=";">
             update dept set dept_name = #{dept.deptName},dept_address = #{dept.deptAddress}
             where dept_id = #{dept.deptId}
         </foreach>
    </update>

7.3.3 编写批量删除部门的方法

    //批量修改部门信息
         public int updateDeptList(List<Dept> depts){
             int i = 0;
             try {
                 session = MyBatisUtil.getSession();
                 i = session.update("com.demo.entity.DeptMapper.updateDeptList", depts);
                 session.commit();
             } catch (Exception e) {
                 e.printStackTrace();
                 session.rollback();
             }finally{
                 try {
                      MyBatisUtil.closeSession();
                 } catch (Exception e) {
                      // TODO Auto-generated catch block
                      e.printStackTrace();
                 }
             }
             return i;
         }

7.3.4 编写测试代码

    // 批量修改部门信息
    @Test
    public void testUpdateDeptList() {
         List<Dept> depts = new ArrayList<Dept>();
         for (int i = 1; i < 5; i++) {
             Dept dept = new Dept();
             dept.setDeptId(i);
             dept.setDeptName("deptNameaaa"+i);
             dept.setDeptAddress("deptAddressaaa"+i);
             depts.add(dept);
         }
          System.out.println("受影响的行数："+deptDaoImpl.updateDeptList(depts));
    }

7.3.5 测试效果

image.png image.png