MyBatis动态SQL--简化SQL语句的利器

曾经终败给现在 2021-08-31 01:32 574阅读 0赞

> `为什么要学MyBatis的动态SQL?`

> 在执行sql语句时,不免会有一些很常用但是写起来又很复杂的SQL语句,使用了动态SQL就能把这些sql语句`封装`,只需要写简短的代码就能`替换冗余`的sql语句,并且在修改这些重组的SQL语句时,因为进行了封装,就能只修改一份就可以,`减少出错的可能`

> 今天没有重点 ,`全是重点和干货`

### 文章目录 ###

*  动态SQL
 *   *  sql标签
     *  if标签
     *  where标签
     *  set标签
     *  trim标签
     *  foreach标签

# 动态SQL #

> MyBatis的映射文件中支持在基础SQL上添加一些逻辑操作,并动态拼接成完整的sql之后再执行,以达到SQL复用,简化编程的效果

## sql标签 ##

对两个查询方法中相同的sql语句用sql进行了封装

<table> 
 <thead> 
  <tr> 
   <th align="center">两个查询方法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center"><img src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20vbGVpeXVqaWEvY3Nkbi9yYXcvbWFzdGVyL2ltYWdlLTIwMjAwNjE3MDgzMTUwNjg0LnBuZw?x-oss-process=image/format,png" alt="image-20200617083150684"></td> 
  </tr> 
 </tbody> 
</table>

*  这两个方法都需要查询的是该课程的全部信息和属于该课程的学生的信息,只是查询的方式不同,一个是通过id,另一个通过name

<sql id="subject_all">
            select t_subjects.id,t_subjects.name,t_subjects.grade,t_students.id stu_id,t_students.name stu_name,t_students.sex
            from t_subjects join t_stu_sub
            on t_subjects.id = t_stu_sub.subject_id
            join t_students
            on t_students.id = t_stu_sub.student_id
        </sql>
    
        <select id="querySubjectById" resultMap="Subject_map">
            <include refid="subject_all"></include>
            where t_subjects.id=#{id}
        </select>
    
    
        <select id="querySubjectByName" resultMap="Subject_map">
            <include refid="subject_all"/>
            where t_subjects.name =#{name}
        </select>

*  通过标签能抽取到重复的sql语句,`简化代码书写,在业务逻辑发生改变时,修改代码时只需要修改sql标签中的sql语句就可以,不需要重复修改大量的sql语句`

## if标签 ##

另外对于查询方法而言,我们可能需要很多的查询方法,如果我们为每个方法都定义一个Dao的方法,那么我们的方法就会很多,`MyBatis允许我们把这些特别相似的方法合并为一个方法`

对于前面这两个方法,我们通过MyBatis来做合并方法

<table> 
 <thead> 
  <tr> 
   <th align="center">SubjectDao</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center"><img src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20vbGVpeXVqaWEvY3Nkbi9yYXcvbWFzdGVyL2ltYWdlLTIwMjAwNjE3MDkwMzI2NDA5LnBuZw?x-oss-process=image/format,png" alt="image-20200617090326409"></td> 
  </tr> 
 </tbody> 
</table>

**mapper映射文件**

<select id="querySubjectBySubject" resultMap="Subject_map">
            <include refid="subject_all"/>
            where
            <if test="id!=null">
                t_subjects.id=#{id}
            </if>
            <if test="name!=null">
                t_subjects.name = #{name}
            </if>
    
        </select>

*  需要注意的: if便签中的test属性中的id!=null就相当于我们的\#\{id\}!=null,也就是取类中的属性,只是在test的属性中我们不需要那么写.实际意义是相同的

**测试类代码**

**结果输出**

> 我们这个实例中使用的查询方法默认的是只有一个属性被赋值
> 
> 然后我们只查询那个被赋值的属性(另一个属性没有被赋值,为空)
> 
> 那么如果属性都不为空呢?

也就是查询这个属性`或者`通过另一个属性查询

//根据Subject类对象的属性查询该课程的全部信息和属于该课程的学生的信息
        List<Subject> querySubject(Subject subject);
        //不论提供的属性是否赋值,只要是赋值的属性都会根据查询 也就是关系中的or

*  由于得到的对象可能不是一个,这里需要定义为`集合`类型

<select id="querySubject" resultMap="Subject_map">
            <include refid="subject_all"/>
            where
            <if test="id!=null">
                t_subjects.id=#{id}
            </if>
            <if test="name!=null">
               or  t_subjects.name = #{name}
            </if>
        </select>

*  注意if中使用了or来控制mybatis的查询可以基于两个不同的属性

**测试类部分代码**

Subject subject3 = new Subject(1001,"JavaEE",null);
    
            List<Subject> subjects = mapper.querySubject(subject3);
            for (Subject subject4 :
                      subjects      ) { 
                System.out.println(subject4);
            }

不仅查询出了id值为1001的数据,也查询出了name值为JavaEE的属性

*  需要注意的是使用or关键字来实现了`或关系`的查询中,在文件映射中写的sql语句中关于对象的属性写在后面的属性可以为空
 *  其中`and`关键字实现的关联查询也可以用if标签实现

<table> 
 <thead> 
  <tr> 
   <th align="center">赋值注意事项</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center"><img src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20vbGVpeXVqaWEvY3Nkbi9yYXcvbWFzdGVyL2ltYWdlLTIwMjAwNjE3MDk0NTU3MzkzLnBuZw?x-oss-process=image/format,png" alt="image-20200617094557393"></td> 
  </tr> 
 </tbody> 
</table>

那么怎么解决这样的问题:

## where标签 ##

使用where标签,就能够解决or关键字异常注入的问题

> 1.  补充where关键字
> 2.  识别where子句中如果以or(and)开头 那么会去掉or(或者and)

<select id="querySubject" resultMap="Subject_map">
          <include refid="subject_all"/>
          <where>
              <if test="id!=null">
                  t_subjects.id=#{id}
              </if>
              <if test="name!=null">
                  or t_subjects.name = #{name}
              </if>
          </where>
         
      </select>

*  使用where标签而不是数据库中的where关键字

**使用了where标签之后我们将要赋值的第一个参数赋值为null,测试是否会报错**

System.out.println(">>>>>>>>>>>>>>>>>>>>");
            Subject subject3 = new Subject(null,"JavaEE",null);
    
            List<Subject> subjects = mapper.querySubject(subject3);
            for (Subject subject4 :
                      subjects      ) { 
                System.out.println(subject4);
            }

<table> 
 <thead> 
  <tr> 
   <th align="center">查询结果(没有报错,成功查询到数据)</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center"><img src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20vbGVpeXVqaWEvY3Nkbi9yYXcvbWFzdGVyL2ltYWdlLTIwMjAwNjE3MDk1MjU5MDIxLnBuZw?x-oss-process=image/format,png" alt="image-20200617095259021"></td> 
  </tr> 
 </tbody> 
</table>

## set标签 ##

> 1.  补充set
> 2.  自动将set子句的最后的逗号去除

如果不使用set标签的话:

> 如果我们的更新操作中.所有的属性列都不赋值为null(即所有的属列都进行赋值),那么不会出现问题,但是如果有属性列不进行赋值那么后面的逗号会注入到sql语句中造成语法错误

<table> 
 <thead> 
  <tr> 
   <th align="center">更新操作中不使用set标签造成的逗号的注入</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center"><img src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20vbGVpeXVqaWEvY3Nkbi9yYXcvbWFzdGVyL2ltYWdlLTIwMjAwNjE3MTA0MzAwMzQ3LnBuZw?x-oss-process=image/format,png" alt="image-20200617104300347"></td> 
  </tr> 
 </tbody> 
</table>

> 使用了标签,如果该列不想进行赋值更新,那么标签会自动的将逗号去除

## trim标签 ##

> trim标签可以替换掉where标签和set标签的功能

比如替换where

**原来的where标签实现的功能**

<select id="querySubject" resultMap="Subject_map">
            <include refid="subject_all"/>
            <where>
                <if test="id!=null">
                    t_subjects.id=#{id}
                </if>
                <if test="name!=null">
                    or t_subjects.name = #{name}
                </if>
            </where>
    
        </select>

**用trim标签替换where标签**

<select id="querySubject" resultMap="Subject_map">
            <include refid="subject_all"/>
            <trim prefix="where" perfixOverrides="and|or" <if test="id!=null">
                    t_subjects.id=#{id}
                </if>
                <if test="name!=null">
                    or t_subjects.name = #{name}
                </if>
    		</trim>
        </select>

**原来的set标签实现的功能**

<update id="querySubject" resultMap="Subject_map">
            <include refid="subject_all"/>
            <set>
                <if test="id!=null">
                    t_subjects.id=#{id}
                </if>
                <if test="name!=null">
                    or t_subjects.name = #{name}
                </if>
            </set>
    
        </update>

**用trim标签替换set标签**

<update id="querySubject" resultMap="Subject_map">
            <include refid="subject_all"/>
            <trim prefix="set"suffixOverrides=",">
                <if test="id!=null">
                    t_subjects.id=#{id}
                </if>
                <if test="name!=null">
                    or t_subjects.name = #{name}
                </if>
            </trim>
    
        </update>

## foreach标签 ##

> 标签的作用是支持一些`批量的操作`,批量的添加,批量的删除,以及一些批量的更新

<delect id="deleteSubjects" >
        DELETE FROM t_subjects
        WHERE id IN
        <foreach collection="list" open="(" separator=","close=")" item="id" index="i">
        </foreach>
    </delect>

<table> 
 <thead> 
  <tr> 
   <th align="center">参数</th> 
   <th align="center">描述</th> 
   <th align="center">取值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">collection</td> 
   <td align="center">容器类型</td> 
   <td align="center">list.array.map</td> 
  </tr> 
  <tr> 
   <td align="center">open</td> 
   <td align="center">起始符</td> 
   <td align="center">(</td> 
  </tr> 
  <tr> 
   <td align="center">close</td> 
   <td align="center">结束符</td> 
   <td align="center">)</td> 
  </tr> 
  <tr> 
   <td align="center">separator</td> 
   <td align="center">分隔符</td> 
   <td align="center">,</td> 
  </tr> 
  <tr> 
   <td align="center">index</td> 
   <td align="center">下标符</td> 
   <td align="center">从0开始,依次递增</td> 
  </tr> 
  <tr> 
   <td align="center">item</td> 
   <td align="center">当前项</td> 
   <td align="center">任意名称(循环中通过#{任意名称}表达式访问)</td> 
  </tr> 
 </tbody> 
</table>

<delect id="deleteSubjects" parameterType="java.util.List">
        DELETE FROM t_subjects
        WHERE t_subjects.id IN
        <foreach collection="list" open="(" separator="," close=")" item="id">
            #{id}
        </foreach>
    </delect>

测试类

System.out.println("<<<<<<<<<<<<<<<<<<");
            List<Integer> ids = Arrays.asList(1005, 1006);
            mapper.deleteSubjects(ids);
            MybatisUtil.commit();

<table> 
 <thead> 
  <tr> 
   <th align="center">批量删除结果</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center"><img src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20vbGVpeXVqaWEvY3Nkbi9yYXcvbWFzdGVyL2ltYWdlLTIwMjAwNjE3MTE0NjQ3NjIxLnBuZw?x-oss-process=image/format,png" alt="image-20200617114647621"></td> 
  </tr> 
 </tbody> 
</table>

> 我是雷雨佳,一个`普本科`的学生,主要专注于Java后端和大数据开发

> 如果这篇文章有帮助到你,希望你给我一个`大大的赞`  
> 如果有什么问题,希望你能`留言`和我一起`研究`,`学习靠自觉,分享靠自愿`

> 转载注明出处  
> [https://blog.csdn.net/qq\_40742223][https_blog.csdn.net_qq_40742223]

[https_blog.csdn.net_qq_40742223]: https://blog.csdn.net/qq_40742223