基于MaBatis编写动态SQL语句

傷城~ 2023-10-03 13:59 16阅读 0赞

#### 目录 ####

*  前言
 *  一、动态SQL
 *   *  1. 介绍
     *  2. 在IDea，基于MaBatis完成动态SQL步骤
     *   *  2.1 创建实体类
         *   *  2.1.1 创建dto实体类
             *  2.1.2 编写实体类
         *  2.2 创建接口函数
         *   *  2.2.1 创建文件
             *  2.2.2 编写代码
         *  2.3 创建映射文件
         *   *  2.3.1 创建文件
             *  2.3.2 编写文件（动态SQL）
         *  2.4 测试即可
     *  3. 动态SQL的常用语法
     *   *  3.1 where 标签
         *  3.2 if 标签
         *  3.3 choose 标签
         *  3.4 set 标签
         *  3.5 foreach 标签

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

## 前言 ##

提示：具体项目创建、MyBatis配置等请看[https://blog.csdn.net/weixin\_45928161/article/details/119766861][https_blog.csdn.net_weixin_45928161_article_details_119766861]

## 一、动态SQL ##

### 1. 介绍 ###

根据传递的参数是否有值，动态的查询数据库的内容。就像找番的时候有个筛选：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTkyODE2MQ_size_16_color_FFFFFF_t_70]  
**动态SQL的查询语法涉及：**

*  If
 *  Choose
 *  When（类似C语言的case）
 *  Set
 *  Foreach

### 2. 在IDea，基于MaBatis完成动态SQL步骤 ###

**动态SQL与普通操作SQL的区别：**

*  动态SQL需要一个包含属性更多的实体类（所以要创建）
 *  涉及语法更复杂（但也只体现在编写.xml文件时）

#### 2.1 创建实体类 ####

由于动态查询，则传递的参数类型一般会涉及多个表格，普通的表格关联实体类是不满足使用条件，本质就是创建一个包含多个表格内容的结构体来做传递参数的数据类型，这种实体类统称为dto

##### 2.1.1 创建dto实体类 #####

在mapper同级目录中创建dto包，以及Dto java class 文件  
![在这里插入图片描述][2792762143514af89c840731f52bc878.png]

##### 2.1.2 编写实体类 #####

根据我们的需求定义该实体类，这里我们先继承了Emp实体类，再添加了两个其他属性

package com.upc.homework2.dto;
    
    import com.upc.homework2.po.Emp;
    
    public class Dto extends Emp {
        
        private  Double lowsal;
        private  Double higsal;
    
        public Double getLowsal() {
        
            return lowsal;
        }
    
        public void setLowsal(Double lowsal) {
        
            this.lowsal = lowsal;
        }
    
        public Double getHigsal() {
        
            return higsal;
        }
    
        public void setHigsal(Double higsal) {
        
            this.higsal = higsal;
        }
    }

#### 2.2 创建接口函数 ####

##### 2.2.1 创建文件 #####

![在这里插入图片描述][1ed517f8146c4f85a75343cd7bf6490b.png]

##### 2.2.2 编写代码 #####

package com.upc.homework2.mapper;
    
    import com.upc.homework2.dto.Dto;
    import com.upc.homework2.po.Emp;
    import org.apache.ibatis.annotations.Mapper;
    
    import java.util.List;
    
    @Mapper
    public interface MMapper {
        
        List<Emp> find(Dto dto);
    }

#### 2.3 创建映射文件 ####

##### 2.3.1 创建文件 #####

![在这里插入图片描述][d1f76b78a635405f804718d1f3517d12.png]

##### 2.3.2 编写文件（动态SQL） #####

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    
    <mapper namespace="com.upc.homework2.mapper.MMapper">
        <select id="find" resultType="com.upc.homework2.po.Emp"
                parameterType="com.upc.homework2.dto.Dto">
            select * from emp
            <where>
                <if test="ename != null and ename != ''">
                    and ename like '%${ename}%'
                </if>
                <if test="job != null and job != ''">
                    and job like '%${job}%'
                </if>
                <if test="lowsal != null and lowsal != ''">
                    and sal >= #{lowsal}
                </if>
                <if test="higsal != null and higsal != ''">
                    and sal <= #{higsal}
                </if>
                <if test="deptno != null and deptno != ''">
                    and deptno = #{deptno}
                </if>
            </where>
        </select>
    </mapper>

**代码解释：**  
整个结构其实很简单，就是如果传入了这个参数那么就用 and 添加上这个参数的条件，反之就跳过

<where>
    		
    			如果ename参数不为null，且不为空，则对ename进行模糊查询，反之跳过
                <if test="ename != null and ename != ''">
                    and ename like '%${ename}%'
                </if>
                
                如果job参数不为null，且不为空，则对job进行模糊查询，反之跳过
                <if test="job != null and job != ''">
                    and job like '%${job}%'
                </if>
                
                如果lowsal参数不为null，且不为空，则查询大于等于最低工资的数据
                <if test="lowsal != null and lowsal != ''">
                    and sal >= #{lowsal}
                </if>
    
                如果higsal参数不为null，且不为空，则查询小于等于最低工资的数据
                <if test="higsal != null and higsal != ''">
                    and sal <= #{higsal}
                </if>
    
                如果deptno参数不为null，且不为空，则查询与该参数相等的数据
                <if test="deptno != null and deptno != ''">
                    and deptno = #{deptno}
                </if>
                
            </where>

**知识点：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTkyODE2MQ_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][da95db59702f404da2af93105fe9f61a.png]  
**注意问题：**  
![在这里插入图片描述][85eef8dfc6c44ac18b6930141f830eaa.png]

#### 2.4 测试即可 ####

### 3. 动态SQL的常用语法 ###

使用MyBatis写SQL语句，会有特殊的标签帮助我们完成更复杂的SQL：set、choose、when、otherwise、if、foreach

#### 3.1 where 标签 ####

*  与SQL语句中的where关键字等效
 *  where 标签会自动消除标签里多余的拼接条件的 and ，但是缺少了and会报错，所以条件语句一般都带着and

**示例：**

<select id="find" resultType="com.upc.homework2.po.Emp" parameterType="com.upc.homework2.dto.Dto">
    
            select * from emp
            <where>
                <if test="ename != null and ename != ''">
                    and ename like '%${
        ename}%'
                </if>
                <if test="job != null and job != ''">
                    and job like '%${
        job}%'
                </if>
                <if test="lowsal != null and lowsal != ''">
                    and sal >= #{
        lowsal}
                </if>
                <if test="higsal != null and higsal != ''">
                    and sal <= #{
        higsal}
                </if>
                <if test="deptno != null and deptno != ''">
                    and deptno = #{
        deptno}
                </if>
            </where>
    
        </select>

从代码中可以发现，where标签就是SQL语句里的where，只是以标签的形式出现

#### 3.2 if 标签 ####

**基本格式**

<if test=" 属性名 != null and 属性名 != '' ">
    	列名 与 #{
        属性名} 的相关操作
    </if>

*  就是程序里的 if 判断语句，但是没有 else 语法
 *  其作用：判断属性是否传递参数，若传递参数则增加条件，反之跳过

**示例：**

**注意：**

*  if 标签是处理筛选查找（动态SQL）的核心之一，如果对应的属性赋值了，即传递参数了，则查找时增加与之相关的约束条件，反之跳过
 *  列名 与 \#\{属性名\} 的相关操作：要注意 if 标签之间在SQL语句中是如何拼接的，比如在where标签中 if 标签的内容是约束条件，则 if 标签中的SQL语句要有 and 拼接各个 if 标签的SQL；若 if 标签的内容是 set 语句 则连接各个 if 标签的是逗号  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTkyODE2MQ_size_16_color_FFFFFF_t_70 2]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTkyODE2MQ_size_16_color_FFFFFF_t_70 3]

*  if 标签 与 where 标签是相辅相成的

#### 3.3 choose 标签 ####

*  choose标签里有两个标签 when标签、otherwise标签
 *  choose就是C语言中的switch，when就是case，otherwise就是default

<select id="findAll" parameterType="com.upc.homework2.dto.Dto" resultType="com.upc.homework2.po.Emp">
            select * from emp
            <where>
            
                <choose>
                    <when test="empno != null and empno !=''">
                        and empno = #{
        empno}
                    </when>
                    <when test="sal != null and sal != ''">
                        and sal = #{
        sal}
                    </when>
                    <when test="deptno != null and deptno != ''">
                        and deptno = ${
        deptno}
                    </when>
                    <otherwise>
                        and empno = #{
        empno}
                    </otherwise>
                </choose>
                
            </where>
        </select>

choose的when都不满足时，会执行otherwise，大多数情况下是不用otherwise的，尤其对于大型的项目修改、增加when语句的操作很常见，otherwise对于维护而言是个累赘

#### 3.4 set 标签 ####

*  Set 标签用于修改操作，就是SQL里的set关键语法

<update id="update" parameterType="com.upc.homework2.dto.Dto">
            update emp
            <set>
            
                <choose>
                    <when test="sal != null and sal !=''">
                        sal=#{
        sal}
                    </when>
                    <otherwise>
                        <if test="ename !=null  and ename !=''">
                            ename=#{
        ename},
                        </if>
                        <if test="deptno != null and deptno !=''">
                            deptno=#{
        deptno},
                        </if>
                        <if test="comm != null and comm != ''">
                            comm=#{
        comm},
                        </if>
                    </otherwise>
                </choose>
                
            </set>
            <where>
                empno=#{
        empno}
            </where>
        </update>

#### 3.5 foreach 标签 ####

**存在的意义：**  
SQL语句里的 in 语法后面是 ( ) ，在SQL里我们删除主键为3、5、7的语句如下：

Delete from emp where emp.empno in (3,5,7)

我们如何用Java语句操作进行删除？尤其对于批量删除，对我们而言Java传递参数时传入个数组，数组里是要删除的主键，这个构思是好的，但是Java的数组的形式是 \[.,.,.,.\]，也不是圆括号不符合SQL语句，如下写法是不符合语法规范的

Delete from emp where emp.empno in #{Java_array}

所以我们要把数组里的数据由 \[.,.,.,.\]形式转换为 (.,.,.,.) 的形式，于是foreach应运而生，for是for循环，each是每个，顾名思义，foreach就是遍历每一个元素的函数。

语法格式如下

<foreach collection="array" item="array" open="(" close=")" separator=",">
    	#{
        array}
    </foreach>

**语法解析：**

*  index：表示遍历数组的下标（可能是开始下标）
 *  collection=“array”：表示遍历数据类型是线性数组
 *  item=“array”：item是指遍历属性的名，表示获取数据是\#\{array\}里的array数组（item=“ ”要与\#\{ \}的名字一致）
 *  open=“(” ：遍历生成的结果以 ’ ( ’ 开头
 *  close=“)” ：遍历生成的结果以 ’ ) ’ 结尾
 *  separator=“,”：遍历的各个元素的间隔用逗号隔开

**示例：**

<delete id="delete" parameterType="int">
            delete from emp
            <where>
                empno in 
                <foreach collection="array" item="no" open="(" close=")" separator=",">
                    #{
        no}
                </foreach>
            </where>
        </delete>

[https_blog.csdn.net_weixin_45928161_article_details_119766861]: https://blog.csdn.net/weixin_45928161/article/details/119766861
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTkyODE2MQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/33cf9aef76704d09af1c4064378b4abb.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTkyODE2MQ==,size_16,color_FFFFFF,t_70
[2792762143514af89c840731f52bc878.png]: https://img-blog.csdnimg.cn/2792762143514af89c840731f52bc878.png
[1ed517f8146c4f85a75343cd7bf6490b.png]: https://img-blog.csdnimg.cn/1ed517f8146c4f85a75343cd7bf6490b.png
[d1f76b78a635405f804718d1f3517d12.png]: https://img-blog.csdnimg.cn/d1f76b78a635405f804718d1f3517d12.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTkyODE2MQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/72384515664648af97c0ab5dd6476cc6.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTkyODE2MQ==,size_16,color_FFFFFF,t_70
[da95db59702f404da2af93105fe9f61a.png]: https://img-blog.csdnimg.cn/da95db59702f404da2af93105fe9f61a.png
[85eef8dfc6c44ac18b6930141f830eaa.png]: https://img-blog.csdnimg.cn/85eef8dfc6c44ac18b6930141f830eaa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTkyODE2MQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/70030e2b5b5c4ed4adc12a482251bd93.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTkyODE2MQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTkyODE2MQ_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/43b2404f5f4f4f5880c4a3d828964083.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTkyODE2MQ==,size_16,color_FFFFFF,t_70