MyBatis-动态SQL的if、choose、when、otherwise、trim、where、set、foreach标签的使用

你的名字 2022-04-17 05:48 287阅读 0赞

**动态SQL是MyBatis最强大的特性之一。用于实现动态SQL的主要元素如下：**  
**1、if**  
**2、choose、when、otherwise**  
**3、trim、where、set**  
**4、foreach**

代码示例：

--------------------

## **1、if** ##

EmpMapper.xml配置

<select id="getEmpByIf" resultType="Emp" parameterType="Emp">
        select * from emp where 1 = 1
        <if test="job != null and job != ''">
            and job = #{job}
        </if>
        <if test="deptno != null ">
            and deptno = #{deptno}
        </if>
    </select>
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9

测试代码

public void getEmpByIf() {
            SqlSession sqlSession = null;
            Emp emp = new Emp();
            //工作为空字符串
            emp.setJob("");
            //部门编号为10
            emp.setDeptno(10);
            List<Emp> empList = new ArrayList<>();
            try {
                sqlSession = MyBatisUtil.createSqlSession();
                //EmpMapper接口中添加对应方法
                empList = sqlSession.getMapper(EmpMapper.class).getEmpByIf(emp);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                MyBatisUtil.closeSqlSession(sqlSession);
            }
            for (Emp emp1 : empList) {
                logger.debug(emp1.getEmpno() + "-->" + emp1.getEname() + "-->" + emp1.getDeptno());
            }
        }
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13
       14
       15
       16
       17
       18
       19
       20
       21

输出结果

![这里写图片描述][20160530230804834]

从结果可以看出因为job为空字符串所以条件没拼接,输出结果正确

--------------------

## **2、choose、when、otherwise** ##

类似于Java中的switch case default

EmpMapper.xml配置

<select id="getEmpByChoose" resultType="Emp" parameterType="Emp">
        select * from emp where 1 = 1
        <choose>
            <when test="job != null">
                and job = #{job}
            </when>
            <when test="deptno != null">
                and deptno = #{deptno}
            </when>
            <otherwise>
                and mgr = #{mgr}
            </otherwise>
        </choose>
    </select>
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13
       14

测试代码

public void getEmpByChoose() {
            SqlSession sqlSession = null;
            Emp emp = new Emp();
            //工作为CLERK
            emp.setJob("CLERK");
            //部门编号为10
            emp.setDeptno(10);
            //经理为7698
            emp.setMgr(7698);
            List<Emp> empList = new ArrayList<>();
            try {
                sqlSession = MyBatisUtil.createSqlSession();
                //EmpMapper接口添加对应方法
                empList = sqlSession.getMapper(EmpMapper.class).getEmpByChoose(emp);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                MyBatisUtil.closeSqlSession(sqlSession);
            }
            for (Emp emp1 : empList) {
                logger.debug(emp1.getEmpno() + "-->" + emp1.getEname() + "-->" + emp1.getDeptno());
            }
        }
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13
       14
       15
       16
       17
       18
       19
       20
       21
       22
       23

执行结果

![这里写图片描述][20160530232030308]

可以看出,只有一个条件生效,也就是只执行满足的条件when,没有满足的条件就执行otherwise,表示默认条件。

--------------------

## **3、where** ##

EmpMapper.xml配置

<select id="getEmpByWhere" resultType="Emp" parameterType="Emp">
        select * from emp
        <where>
            <if test="job != null and job != ''">
                and job = #{job}
            </if>
            <if test="deptno != null">
                and deptno = #{deptno}
            </if>
        </where>
    </select>
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11

测试代码

public void getEmpByWhere() {
            SqlSession sqlSession = null;
            Emp emp = new Emp();
            //工作为CLERK
            emp.setJob("CLERK");
            //部门编号为10
            emp.setDeptno(10);
            List<Emp> empList = new ArrayList<>();
            try {
                sqlSession = MyBatisUtil.createSqlSession();
                //EmpMapper接口中添加对应方法
                empList = sqlSession.getMapper(EmpMapper.class).getEmpByWhere(emp);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                MyBatisUtil.closeSqlSession(sqlSession);
            }
            for (Emp emp1 : empList) {
                logger.debug(emp1.getEmpno() + "-->" + emp1.getEname() + "-->" + emp1.getDeptno());
            }
        }
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13
       14
       15
       16
       17
       18
       19
       20
       21

执行结果

![这里写图片描述][20160530233118270]

**从配置的SQL语句及结果来看,where能在第一次满足添加条件时自动补全where这个单词,而且如果有and会替换掉,如果满足条件拼接的第二个语句没有and,会报错**

--------------------

## **4、set** ##

EmpMapper.xml配置

<update id="updateEmpBySet" parameterType="Emp">
        update emp
        <set>
            <if test="ename != null and ename != ''">
                ename = #{ename},
            </if>
            <if test="job != null and job != ''">
                job = #{job},
            </if>
        </set>
        where empno = #{empno}
    </update>
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12

测试代码

public void updateEmpBySet() {
            SqlSession sqlSession = null;
            //要更新的Emp对象
            Emp emp = new Emp();
            emp.setEmpno(2333);
            emp.setEname("new name");
            emp.setJob("new job");
    
            try {
                sqlSession = MyBatisUtil.createSqlSession();
                sqlSession.getMapper(EmpMapper.class).updateEmpBySet(emp);
                sqlSession.commit();
            } catch (Exception e) {
                e.printStackTrace();
                sqlSession.rollback();
            } finally {
                MyBatisUtil.closeSqlSession(sqlSession);
            }
        }
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13
       14
       15
       16
       17
       18
       19

执行结果

![这里写图片描述][20160530235745759]

**可以看出,set会在成功拼接的条件前加上SET单词且最后一个”,”号会被无视掉,但是有可能需要”,”的地方不能省略”,”否则异常。**

--------------------

## **5、trim** ##

EmpMapper.xml配置

胡乱写了一段代码用来测试(●ˇ∀ˇ●)

![这里写图片描述][20160531002836865]

直接上结果。。。

![这里写图片描述][20160531002940051]

**o(^▽^)o可以看到成功匹配掉了开头的$和末尾的\***

--------------------

## **6、foreach** ##

**foreach标签用于遍历生成单个或多个数据,根据传入的参数类型有俩种配置方式**

1、参数为数组

![这里写图片描述][20160531010217985]

EmpMapper接口添加方法

public List<Emp> getEmpByArray(String[] deptnos);
     
      
       1

测试代码

public void getEmpByArray() {
            SqlSession sqlSession = null;
            List<Emp> empList = new ArrayList<>();
            //参数为数组
            String[] deptnos = {
       "10", "20", "30"};
            try {
                sqlSession = MyBatisUtil.createSqlSession();
                empList = sqlSession.getMapper(EmpMapper.class).getEmpByArray(deptnos);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                MyBatisUtil.closeSqlSession(sqlSession);
            }
            for (Emp emp : empList) {
                logger.debug(emp.getEmpno() + "-->" + emp.getEname() + "-->" + emp.getDeptno());
            }
        }
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13
       14
       15
       16
       17

结果

![这里写图片描述][20160531010509942]

2、参数为List集合

![这里写图片描述][20160531010931819]

EmpMapper接口添加方法

public List<Emp> getEmpByList(List<String> deptnos);
     
      
       1

测试代码

public void getEmpByList() {
            SqlSession sqlSession = null;
            List<Emp> empList = new ArrayList<>();
            //参数为集合
            List<String> list = new ArrayList<>();
            list.add("10");
            try {
                sqlSession = MyBatisUtil.createSqlSession();
                empList = sqlSession.getMapper(EmpMapper.class).getEmpByList(list);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                MyBatisUtil.closeSqlSession(sqlSession);
            }
            for (Emp emp : empList) {
                logger.debug(emp.getEmpno() + "-->" + emp.getEname() + "-->" + emp.getDeptno());
            }
        }
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13
       14
       15
       16
       17
       18

结果

![这里写图片描述][20160531011049991]

[20160530230804834]: /images/20220417/d1dc1d289b3d4f06b0feee2ba556b5e8.png
[20160530232030308]: /images/20220417/ee627e45a75a4690bfef4443852249b0.png
[20160530233118270]: /images/20220417/95f026df514e499d884b59f472fb1db8.png
[20160530235745759]: /images/20220417/29f22772e0844d18a09e1ce20cfcba28.png
[20160531002836865]: /images/20220417/a9a1eb48fae44bc796d7092a6455e715.png
[20160531002940051]: /images/20220417/57d9387d627a48e7b87a08121d951041.png
[20160531010217985]: /images/20220417/3c907acaa7834e8d8fdcec6a48297533.png
[20160531010509942]: /images/20220417/2cbba882f4db4f2baa1d55159ec2c92b.png
[20160531010931819]: /images/20220417/7eb8805ab6b14c65949e546346ad08b4.png
[20160531011049991]: /images/20220417/813c463a1adf4515864ab279246d5cd9.png