【MyBatis框架】动态SQL

浅浅的花香味﹌ 2024-04-01 09:55 75阅读 0赞

## MyBatis之动态SQL ##

#### 目录 ####

*  MyBatis之动态SQL
 *   *   *  1. < if > 元素
         *  2. < where >
         *  3. < choose >,< when >,< otherwise >元素
         *  4. < trim >元素
         *  5. < set >元素
         *  6. < foreach >元素
         *   *  6.1 添加批量数据
             *  6.2 批量删除数据
         *  7. < SQL >元素
         *  8. 小结

开发人员在使用JDBC或者其他类似的框架进行数据库开发时，通常都要根据需求去手动拼装SQL，这是一个非常麻烦且痛苦的工作，而MyBatis提供的对SQL语句动态组装的功能，恰能很好的解决这一麻烦工作。

动态SQL是MyBatis的强大特性之一，其主要元素如下：

<table> 
 <thead> 
  <tr> 
   <th>元素</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>&lt; if &gt;</td> 
   <td>判断语句，用于条件单分支判断</td> 
  </tr> 
  <tr> 
   <td>&lt; choose &gt;</td> 
   <td>相当于Java中的switch语句，用于多分支判断</td> 
  </tr> 
  <tr> 
   <td>&lt; where &gt;,&lt; trim &gt;,&lt; set &gt;</td> 
   <td>辅助元素，用于处理一些SQL的拼装，特殊字符等问题</td> 
  </tr> 
  <tr> 
   <td>&lt; foreach &gt;</td> 
   <td>循环语句，常用于in语句等列举条件</td> 
  </tr> 
  <tr> 
   <td>&lt; bind &gt;</td> 
   <td>用于模糊查询</td> 
  </tr> 
 </tbody> 
</table>

实体类

public class Emp {
        
        private Integer empId;
        private String empName;
        private Integer age;
        private String gender;
     
     
        public Emp() {
        
        }
     
        public Emp(Integer empId, String empName, Integer age, String gender) {
        
            this.empId = empId;
            this.empName = empName;
            this.age = age;
            this.gender = gender;
        }
     
        public Integer getEmpId() {
        
            return empId;
        }
     
        public void setEmpId(Integer empId) {
        
            this.empId = empId;
        }
     
        public String getEmpName() {
        
            return empName;
        }
     
        public void setEmpName(String empName) {
        
            this.empName = empName;
        }
     
        public Integer getAge() {
        
            return age;
        }
     
        public void setAge(Integer age) {
        
            this.age = age;
        }
     
        public String getGender() {
        
            return gender;
        }
     
        public void setGender(String gender) {
        
            this.gender = gender;
        }
     
        @Override
        public String toString() {
        
            return "Emp{" +
                    "empId=" + empId +
                    ", empName='" + empName + '\'' +
                    ", age=" + age +
                    ", gender='" + gender + '\'' +
                    '}';
        }
    }

#### 1. < if > 元素 ####

> 在MyBatis中，< if > 是最常用的判断语句，它类似于Java中的if语句，主要用于实现某些简单的条件选择

<select id="getEmpByyCondition" resultType="com.atguigu.mybatis.pojo.Emp">
            select * from t_emp where 1=1
            <if test="empName!=null and empName!=''">
                and emp_name=#{empName}
            </if>
            <if test="age!=null and age!=''">
                and age=#{age}
            </if>
            <if test="gender!=null and gender!=''">
                and gender=#{gender}
            </if>
        </select>

测试方法：

public void test(){
        
            SqlSessionUtils sqlSessionUtils = new SqlSessionUtils();
            SqlSession sqlSession = sqlSessionUtils.getSqlSession();
            EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
            Emp emp = new Emp(null,"张三",null,null);
            List<Emp> list = mapper.getEmpByyCondition(emp);
            System.out.println(list);
        }

测试结果如下：

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

同时在为传递任何参数时，程序会将数据表中的所有数据查出

#### 2. < where > ####

如上述使用if语句查询我们注意到`select * from t_emp where 1=1`在语句中要添加`where 1=1`这是为了保证至少有条件成立，不至于程序报错，但是MyBatis的开发者设计了更好的方法，就是把< where >也作为元素，动态添加where，使用如下

<select id="getEmpByConditionTwo" resultType="Emp">
            select * from t_emp
            <where>
                <if test="empName != null and empName != ''">
                    emp_name = #{empName}
                </if>
                <if test="age != null and age != ''">
                    and age = #{age}
                </if>
                <if test="gender != null and gender != ''">
                    and gender = #{gender}
                </if>
            </where>
        </select>

这样就避免了那个设定

#### 3. < choose >,< when >,< otherwise >元素 ####

在使用< if >元素时，只要test属性中的表达式为true，就会执行元素中的条件语句，但是在实际应用中，有时只需要从多个选项中选择一个去执行。在这种场景下，使用< if > 元素进行处理是非常不合理的。如果使用的是Java语言，这种情况显然更适合switch语句来处理，那么MyBatis中有没有类似的语句呢？当然是有的。针对上面的情况，MyBatsi可以用< choose >,< when >,< otherwise >元素组合去实现上面的情况。

<select id="getEmpByChoose" resultType="Emp">
            select * from t_emp
            <where>
                <choose>
                    <when test="empName != null and empName != ''">
                        emp_name = #{empName}
                    </when>
                    <when test="age != null and age != ''">
                        age = #{age}
                    </when>
                    <when test="gender != null and gender != ''">
                        gender = #{gender}
                    </when>
                </choose>
            </where>
        </select>

测试语句：

public void testGetEmpByChoose(){
        
            SqlSession sqlSession = SqlSessionUtil.getSqlSession();
            DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
            Emp emp = new Emp(null, "张三", 21, "");
            List<Emp> list = mapper.getEmpByChoose(emp);
            list.forEach(System.out::println);
        }

结果如下：  
![在这里插入图片描述][ddf95ad413b44460917ba279150da014.png]

这是因为当条件满足其中一项，在这个案例中就是满足了名字，那么后面的就不会再去匹配啦，所以这里的查询是按照名字也就是张三来进行的。

#### 4. < trim >元素 ####

trim元素用于自定义拼接SQL语句，与where类似，具体我们可以对比来看

> < where >版

> < trim >版

<select id="getEmpByCondition" resultType="Emp">
            select <include refid="empColumns"></include> from t_emp
            <trim prefix="where" suffixOverrides="and">
                <if test="empName != null and empName != ''">
                    emp_name = #{empName} and
                </if>
                <if test="age != null and age != ''">
                    age = #{age} and
                </if>
                <if test="gender != null and gender != ''">
                    gender = #{gender}
                </if>
            </trim>
        </select>

trim用于去掉或添加标签中的内容

常用属性：

*  prefix：在trim标签中的内容的前面添加某些内容
 *  prefixOverrides：在trim标签中的内容的前面去掉某些内容
 *  suffix：在trim标签中的内容的后面添加某些内容
 *  suffixOverrides：在trim标签中的内容的后面去掉某些内容

#### 5. < set >元素 ####

在更新表中字段时，根据条件进行判断，从而实现部分更改，而不是更新所有字段，提高开发效率

<update id="updateColumns" parameterType="Emp">
            update t_emp 
            <set>
                <if test="empName != null and empName != ''">
                    emp_name = #{empName} and
                </if>
                <if test="age != null and age != ''">
                    age = #{age} and
                </if>
                <if test="gender != null and gender != ''">
                    gender = #{gender}
                </if>
            </set>
        </update>

其中的< if >元素用于判断相应的字段是否传入值，如果传入的字段非空，就将此字段进行动态SQL组转；并更新此字段，否则此字段不更新。

> 注意：在映射文件中使用 < set > 和 < if >元素组合纪念性update语句动态SQL组转时，如果< set >元素内包含的内容都为空，则会出现SQL语法错误。所以在使用< set > 元素进行字段信息更新时，要确保传入的更新字段都不能为空。

#### 6. < foreach >元素 ####

用于SQL语句执行批量操作

*  collection: 需做foreach(遍历)的对象，作为入参时，list、array对象时，collection属性值分别默认用"list"、"array"代替，Map对象没有默认的属性值。但是，在作为入参时可以使用@Param(“keyName”)注解来设置自定义collection属性值，设置keyName后，list、array会失效；
 *  item： 集合元素迭代时的别名称，该参数为必选项；
 *  index： 在list、array中，index为元素的序号索引。但是在Map中，index为遍历元素的key值，该参数为可选项；
 *  open： 遍历集合时的开始符号，通常与close=")"搭配使用。使用场景IN(),values()时，该参数为可选项；
 *  separator： 元素之间的分隔符，类比在IN()的时候，separator=“,”,最终所有遍历的元素将会以设定的（,）逗号符号隔开，该参数为可选项；
 *  close： 遍历集合时的结束符号，通常与open="("搭配使用，该参数为可选项；

##### 6.1 添加批量数据 #####

<insert id="insertEmps">
            insert into t_emp value
            <foreach collection="emps" item="emp" separator=",">
              (null,#{emp.empName},#{emp.age},#{emp.gender},null)
            </foreach>
        </insert>

测试方法：

public void test(){
        
            SqlSessionUtils sqlSessionUtils = new SqlSessionUtils();
            SqlSession sqlSession = sqlSessionUtils.getSqlSession();
            EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
            Emp emp1 = new Emp(null,"数据1",21,"女");
            Emp emp2 = new Emp(null,"数据2",24,"男");
            Emp emp3 = new Emp(null,"数据3",31,"女");
            List<Emp> emps = Arrays.asList(emp1, emp2, emp3);
            mapper.insertEmps(emps);
        }

执行结果：

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

##### 6.2 批量删除数据 #####

<delete id="deleteByEmpIds">
            delete from t_emp where emp_id in
            <foreach collection="empIds" item="empId" separator="," open="(" close=")">
            #{empId}
        	</foreach>
        </delete>

测试方法：

执行结果：

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

也可以这么拼接SQL语句

<delete id="deleteByEmpIds">
            delete from t_emp where
            <foreach collection="empIds" item="empId" separator="or" >
                emp_id=#{empId}
            </foreach>
        </delete>

#### 7. < SQL >元素 ####

> sql片段，可以记录一段公共sql片段，在使用的地方通过include标签进行引用

<sql id="empColumns"> emp_id,emp_name,age,gender</sql>
        <select id="selectAll" resultType="com.atguigu.mybatis.pojo.Emp">
            select <include refid="empColumns"></include> from t_emp
        </select>

测试方法：

执行结果：

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

#### 8. 小结 ####

动态SQL可以帮开发者灵巧的实现很多特殊条件的SQL语句，比如if元素是最常用的，同时要灵活使用sql语句来完成嵌套查询，要根据项目要求选取合适的元素来实现开发。

[3da7ab696d0c4e579e8e6568fbcbc3ab.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/dffa57dd3c254a2391c6ffb354404b09.png
[ddf95ad413b44460917ba279150da014.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/1dda56e39ed34cb1a166fb44229a31bd.png
[f5b65b06f2a74f07b0df888e744c279b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/a66b742b0dd44012ac20514022239a44.png
[f0404e0471e3484bacb2e2222404b7fe.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/e008f9522b6e42f48a9fdd53adabf489.png
[b7b8e6ca806d4a2b918d416e5f13ca36.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/60338f67118c44409418f04d3826ef35.png