Mybatis之foreach批量操作、模糊查询和调用存储过程

100人浏览 2026-03-14 00:20:52

学习目标

1、动态sql之foreach

2、#{}和 ${}的区别？

3、模糊查询

4、调用存储过程

学习内容

1、foreach

应用场景：查询、批量数据操作（录入，删除，修改）；

简介：foreach元素的属性主要有 item，index，collection，open，separator，close。

item表示集合中每一个元素进行迭代时的别名，
index指定一个名字，用于表示在迭代过程中，每次迭代到的位置，
open表示该语句以什么开始，
separator表示在每次进行迭代之间以什么符号作为分隔符，
close表示以什么结束。

foreach的时候最关键的也是最容易出错的就是collection属性

如果传入的是单参数且参数类型是一个List的时候，collection属性值为list
如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array
如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，也可以传递单参数。item代表value,index代表key;

查询操作

方法1：传递list：进行查询

dao接口：

 List<Emp> find1(List list);

映射文件：

 <select id="find1" resultType="Emp" >
 select * from emp where empno in
 <foreach collection="list" open="(" separator="," close=")" item="id">
 #{id}
 </foreach>
 </select>

结果：

 select * from emp where empno in ( ? , ? , ? , ? )

方法2：传递数组：进行查询

 <select id="find1" resultType="Emp" >
 select * from emp where empno in
 <foreach collection="array" open="(" separator="," close=")" item="id">
 #{id}
 </foreach>
 </select>

批量录入

传递实体集合进行批量录入：

dao:

 int insert(List<Emp> list);

映射文件：

 <insert id="insert" >
 insert into emp(ename,job,sex,deptno,hiredate) values
 <foreach collection="list" item="item" separator=",">
 (#{item.ename},#{item.job},#{item.sex},#{item.dept.deptno},#{item.hiredate})
 </foreach>
 </insert>

测试:

 @Test
 public void test3(){
 
 List<Emp> emps=new ArrayList<>();
 emps.add(new Emp(0,"郭靖","帮主","男","2018-1-1",new Dept(1)));
 emps.add(new Emp(0,"黄蓉","帮主夫人","女","2018-5-1",new Dept(1)));
 emps.add(new Emp(0,"杨康","公子哥","男","2018-3-1",new Dept(2)));
 
 SqlSession session= SessionFactory.getSession();
 //接口绑定
 EmpDao dao= session.getMapper(EmpDao.class);
 int result= 0;
 try {
 result = dao.insert(emps);
 session.commit();
 } catch (Exception e) {
 e.printStackTrace();
 session.rollback();
 }
 System.out.println(result);
 }

批量更新：

注：在mysql的连接串上需要设置如下属性：

allowMultiQueries=true，表示允许批量操作

 url=jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true

方案一：

原理分析：模拟mysql中执行多条更新命令

dao接口：

 int update(List<Emp> list);

映射文件：

 <update id="update" parameterType="list">
 <foreach collection="list" separator=";" item="item" >
 update emp set ename=#{item.ename},job=#{item.job} where empno=#{item.empno}
 </foreach>
 </update>

测试：

 @Test
 public void test4(){
 
 List<Emp> emps=new ArrayList<>();
 emps.add(new Emp(1,"小明","aaa","m","2018-1-1",new Dept(1)));
 emps.add(new Emp(2,"小王","bbb","m","2018-5-1",new Dept(1)));
 emps.add(new Emp(3,"李四","ccc","f","2018-3-1",new Dept(2)));
 
 SqlSession session= SessionFactory.getSession();
 //接口绑定
 EmpDao dao= session.getMapper(EmpDao.class);
 int result= 0;
 try {
 result = dao.update(emps);
 session.commit();
 } catch (Exception e) {
 e.printStackTrace();
 session.rollback();
 }
 System.out.println(result);
 }

测试结果：

方案二

mysql中没有直接提供用于更新的语法，可以使用case...when语法来实现效果：

 UPDATE emp
 SET ename = CASE id 
 WHEN 1 THEN 'name1'
 WHEN 2 THEN 'name2'
 WHEN 3 THEN 'name3'
 END, 
 job = CASE id 
 WHEN 1 THEN 'job1'
 WHEN 2 THEN 'job2'
 WHEN 3 THEN 'job3'
 END
 WHERE id IN (1,2,3)

映射文件：

 <!--说明：
 prefix="ename =case" ：绑定前缀
 suffix="end,"：绑定后缀
 -->
 <update id="batchUpdate" parameterType="list">
 update emp
 <trim prefix="set" suffixOverrides=",">
 <trim prefix=" ename =case " suffix=" end,">
 <foreach collection="list" item="item" >
 <if test="item.ename!=null">
 when empno=#{item.empno} then #{item.ename}
 </if>
 </foreach>
 </trim>
 <trim prefix=" job =case " suffix=" end,">
 <foreach collection="list" item="item" >
 <if test="item.job!=null">
 when empno=#{item.empno} then #{item.job}
 </if>
 </foreach>
 </trim>
 </trim>
 <where>
 <foreach collection="list" separator="or " item="item">
 empno=#{item.empno}
 </foreach>
 </where>
 </update>

测试结果：

 ==> Preparing: update emp set ename =case when empno=? then ? when empno=? then ? when empno=? then ? end, job =case when empno=? then ? when empno=? then ? when empno=? then ? end WHERE empno=? or empno=? or empno=? 
 ==> Parameters: 1(Integer), 小明1(String), 2(Integer), 小王2(String), 3(Integer), 李四3(String), 1(Integer), aaa(String), 2(Integer), bbb(String), 3(Integer), ccc(String), 1(Integer), 2(Integer), 3(Integer)
 <== Updates: 3

批量删除

类似于录入的语法（略）；

3、#{}和${}的区别？

相同点：都可以作为参数在sql语句中使用；

不同点：

 #{}
 会对传入的数据进行转码处理，在预编译的时候当作?处理；避免sql注入。
 查询命令如下：
 select * from emp WHERE ename =? 
 ---------------------------------------------------------------
 ${}
 将数据以字符串的形式原封不动的传入sql命令中，一般在用到列名，表名的时候使用；
 查询命令如下： 
 select * from emp WHERE ename =一鸣 （错误）（需要加上引号）

正确用法示例：

 <select id="find" resultType="Emp" parameterType="Map">
 select * from emp
 <where>
 <if test="ename!=null and ename!=''">
 and ename =#{ename}
 </if>
 </where>
 order by ${hiredate}
 </select>

4、模糊查询

在mysql中进行模糊查询时的不同写法：

 <if test="ename!=null and ename!=''">
 and ename like "%"#{ename}"%"
 </if>
 
 <if test="ename!=null and ename!=''">
 and ename like '%${ename}%'
 </if>
 
 <if test="ename!=null and ename!=''">
 and ename like CONCAT('%','${ename}','%')
 </if>
 
 <if test="ename!=null and ename!=''">
 and ename like CONCAT('%',#{ename},'%')
 </if>

示例：根据员工的名字和入职日期的开始时间和结束时间进行查询：

 <select id="find" resultType="Emp" parameterType="Map">
 select * from emp
 <where>
 <if test="ename!=null and ename!=''">
 and ename like CONCAT('%',#{ename},'%')
 </if>
 <if test="start!=null and start!='' and end!=null and end!='' ">
 and hiredate BETWEEN #{start} and #{end}
 </if>
 </where>
 order by ${hiredate}
 </select>

测试：

 @Test
 public void test1(){
 //参数
 Map map=new HashMap<>();
 map.put("ename","一");
 map.put("start","2019-1-1");
 map.put("end","2019-12-31");
 map.put("hiredate","hiredate");
 
 SqlSession session= SessionFactory.getSession();
 //接口绑定
 EmpDao dao= session.getMapper(EmpDao.class);
 List<Emp> list=dao.find(map);
 for (Emp emp : list) {
 System.out.println(emp);
 }
 session.close();
 
 }

5、调用存储过程

mybatis中传递参数的格式：

 #{property,javaType=int,jdbcType=NUMERIC}

备注：JDBC 要求，如果一个列允许 null 值，并且会传递值 null 的参数，就必须要指定 JDBC Type

例如在oracle存储过程中输出参数是游标的情况：

 #{department, mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=departmentResultMap}

在mysql中创建存储过程：

 -- 模拟录入部门数据 并返回部门表总的行数
 create PROCEDURE sp_test1(in name1 VARCHAR(20),out num INTEGER)
 BEGIN
 insert into dept(dname) values(name1);
 select count(*) into num from dept;
 end;

映射文件：

 <!--<![CDATA[内容]]> ：会对数据进行转码处理-->
 <insert id="testProcedure" parameterType="map" statementType="CALLABLE">
 <![CDATA[
 call sp_test1(#{dname,mode=IN,jdbcType=VARCHAR},#{num,mode=OUT,jdbcType=INTEGER})
 ]]>
 </insert>

测试：

 @Test
 public void test5(){
 Map map=new HashMap();
 map.put("dname","科技部");
 //输出参数
 map.put("num",0);
 SqlSession session= SessionFactory.getSession();
 DeptDao dao= session.getMapper(DeptDao.class);
 try {
 dao.testProcedure(map);
 session.commit();
 } catch (Exception e) {
 e.printStackTrace();
 session.rollback();
 }
 //获取输出参数的值
 System.out.println(map.get("num"));
 
 }