MyBatis_动态SQL详解

不念不忘少年蓝@ 2024-04-01 13:08 63阅读 0赞

## 1：MyBatis动态 sql 是？ ##

> 动态 SQL 是 MyBatis 的强大特性之一。在 JDBC 或其它类似的框架中，开发人员通常需要手动拼接 SQL 语句。根据不同的条件拼接 SQL 语句是一件极其痛苦的工作。例如，拼接时要确保添加了必要的空格，还要注意去掉列表最后一个列名的逗号。而动态 SQL 恰好解决了这一问题，可以根据场景动态的构建查询。

> 动态SQL：code that is executed dynamically。 它一般是根据用户输入或外部条件动态组合的SQL语句块。 动态SQL能灵活的发挥SQL强大的功能、方便的解决一些其它方法难以解决的问题。 相信使用过动态SQL的人都能体会到它带来的便利，然而动态SQL有时候在执行性能 (效率)上面不如静态SQL，而且使用不恰当，往往会在安全方面存在隐患 (SQL 注入式攻击)。

> Mybatis 动态 sql 是做什么的?  
> Mybatis 动态 sql 可以让我们在 Xml 映射文件内，以标签的形式编写动态 sql，完成逻辑判断和动态拼接 sql 的功能。

## 2.Mybatis 的 9 种 动 态 sql 标 签有哪些？ ##

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

## 3.动态 sql 的执行原理？ ##

> 原理为：使用 OGNL 从 sql 参数对象中计算表达式的值，根据表达式的值动态拼接 sql，以此来完成动态 sql 的功能。  
> OGNL是Object-Graph Navigation Language（对象图导航语言）的缩写，OGNL是一种功能强大的表达式语言（Expression Language，简称为EL），通过它简单一致的表达式语法，可以存取对象的任意属性，调用对象的方法，遍历整个对象的结构图，实现字段类型转化等功能

## 4 MyBatis标签 ##

> if标签：条件判断  
> MyBatis if 类似于 Java 中的 if 语句，是 MyBatis 中最常用的判断语句。使用 if 标签可以节省许多拼接 SQL 的工作，把精力集中在 XML 的维护上。

### 4.1、动态sql if ###

<select id="selectUserByUsernameAndSex"
            resultType="user" parameterType="com.ys.po.User">
        select * from user where username=#{
        username} and sex=#{
        sex}
    </select>

> if 语句使用方法简单，常常与 test 属性联合使用。语法如下。 SQL语句

> 使用动态sql if  
> 上面的查询语句，我们可以发现，如果 \#\{username\} 为空，那么查询结果也是空，如何解决这个问题呢？使用 if 来判断，可多个 if 语句同时使用。以下语句表示为可以按照网站名称（name）或者网址（url）进行模糊查询。如果您不输入名称或网址，则返回所有的网站记录。但是，如果你传递了任意一个参数，它就会返回与给定参数相匹配的记录。

<select id="selectAllWebsite" resultMap="myResult">  
        select id,name,url from website 
        where 1=1    
       <if test="name != null">        
           AND name like #{
        name}   
       </if>    
       <if test="url!= null">        
           AND url like #{
        url}    
       </if></select>

### 4.2、where+if标签 ###

> where、if同时使用可以进行查询、模糊查询  
> 注意，`<if>`失败后， `<where>` 关键字只会去掉库表字段赋值前面的and，不会去掉语句后面的and关键字，即注意，`<where>` 只会去掉`<if>` 语句中的最开始的and关键字

<select id="findQuery" resultType="Student">
                <include refid="selectvp"/>
                <where>
                    <if test="sacc != null">
                        sacc like concat('%' #{
        sacc} '%')
                    </if>
                    <if test="sname != null">
                        AND sname like concat('%' #{
        sname} '%')
                    </if>
                    <if test="sex != null">
                        AND sex=#{
        sex}
                    </if>
                    <if test="phone != null">
                        AND phone=#{
        phone}
                    </if>
                </where>
            </select>

> 这个“where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以AND 或OR 开头的，则它会剔除掉。

### 4.3、set标签 ###

> set可以用来修改

<update id="updateAuthorIfNecessary">
      update Author
        <set>
          <if test="username != null">username=#{
        username},</if>
          <if test="password != null">password=#{
        password},</if>
          <if test="email != null">email=#{
        email},</if>
          <if test="bio != null">bio=#{
        bio},</if>
        </set>
      where id=#{
        id}
    </update>

### 4.4、choose(when,otherwise) 语句 ###

> 有时候，我们不想用到所有的查询条件，只想选择其中的一个，查询条件有一个满足即可，使用 choose 标签可以解决此类问题，类似于 Java 的 switch 语句

<select id="selectUserByChoose" resultType="com.ys.po.User" parameterType="com.ys.po.User">
          select * from user
          <where>
              <choose>
                  <when test="id !='' and id != null">
                      id=#{
        id}
                  </when>
                  <when test="username !='' and username != null">
                      and username=#{
        username}
                  </when>
                  <otherwise>
                      and sex=#{
        sex}
                  </otherwise>
              </choose>
          </where>
      </select>

> 也就是说，这里我们有三个条件，id,username,sex，只能选择一个作为查询条件  
> 　　　　如果 id 不为空，那么查询语句为：select \* from user where id=?  
> 　　　　如果 id 为空，那么看username 是否为空，如果不为空，那么语句为 select \* from user where username=?;  
> 　　　　如果 username 为空，那么查询语句为 select \* from user where sex=?

### 4.5、trim ###

> trim标记是一个格式化的标记，可以完成set或者是where标记的功能  
> ①、用 trim 改写上面第二点的 if+where 语句

<select id="selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User">
            select * from user
            
            <trim prefix="where" prefixOverrides="and | or">
                <if test="username != null">
                   and username=#{
        username}
                </if>
                <if test="sex != null">
                   and sex=#{
        sex}
                </if>
            </trim>
        </select>

> prefix：前缀　　　　　　  
> prefixoverride：去掉第一个and或者是or

> ②、用 trim 改写上面第三点的 if+set 语句

<update id="updateUserById" parameterType="com.ys.po.User">
            update user u
                
                <trim prefix="set" suffixOverrides=",">
                    <if test="username != null and username != ''">
                        u.username = #{
        username},
                    </if>
                    <if test="sex != null and sex != ''">
                        u.sex = #{
        sex},
                    </if>
                </trim>
             where id=#{
        id}
        </update>

suffix：后缀　　  
suffixoverride：去掉最后一个逗号（也可以是其他的标记，就像是上面前缀中的and一样）  
trim+if同时使用可以添加

<insert id="add">
            insert  into student
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="sname != null">sname,</if>
                <if test="spwd != null">spwd,</if>
                <if test="sex != null">sex,</if>
                <if test="phone != null">phone,</if>
            </trim>
    
            <trim prefix="values (" suffix=")"  suffixOverrides=",">
                <if test="sname != null">#{
        sname},</if>
                <if test="spwd != null">#{
        spwd},</if>
                <if test="sex != null">#{
        sex},</if>
                <if test="phone != null">#{
        phone}</if>
            </trim>
    
        </insert>

### 4.7.MyBatis foreach标签 ###

> foreach是用来对集合的遍历，这个和Java中的功能很类似。通常处理SQL中的in语句。  
> foreach 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符  
> 你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时，index 是当前迭代的序号，item 的值是本次迭代获取到的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。

//批量查询

<select id="findAll" resultType="Student" parameterType="Integer">
        <include refid="selectvp"/> WHERE sid in
        <foreach item="ids" collection="array"  open="(" separator="," close=")">
            #{
        ids}
        </foreach>
    </select>
    //批量删除
    <delete id="del"  parameterType="Integer">
        delete  from  student  where  sid in
        <foreach item="ids" collection="array"  open="(" separator="," close=")">
            #{
        ids}
        </foreach>
    </delete>

### 4.8、bind ###

> bind 标签可以使用 OGNL 表达式创建一个变量井将其绑定到上下文中。在前面的例子中，  
> UserMapper.xml 有一个 selectByUser 方法，这个方法用到了 like 查询条件，部分代码如下 。

<if test=” userNarne != null and userNarne ! = ””>
    and user name like concat （ ’ 毛 ’， ＃｛ userNarne ｝，’ 毡 ’ ）
    </if>

> 使用 con cat 函数连接字符串，在 MySQL 中，这个函数支持多个参数，但在 Oracle 中只  
> 支持两个参数。由于不 同数据库之间的语法差异 ，如果更换数据库，有些 SQL 语句可能就需要  
> 重写。针对这种情况，可 以使用 bind 标签来避免由于更换数据库带来的一些麻烦。将上面的  
> 方法改为 bind 方式后，代码如下。

> bind 标签的两个属性都是必选项， name 为绑定到上下文的变量名， va l ue 为 OGNL 表  
> 达式。创建一个 bind 标签的变量后 ， 就可以在下面直接使用，使用 bind 拼接字符串不仅可  
> 以避免因更换数据库而修改 SQL，也能预防 SQL 注入。

## 5 整合案例 ##

xml

<?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.yzx.mapper.StuMapper">
        <sql id="selectvp">
            select  *  from  student
        </sql>
        
        <select id="find" resultType="Student">
            <include refid="selectvp"/>
        </select>
    
        <select id="findbyid"  resultType="student">
            <include refid="selectvp"/>
            WHERE 1=1
            <if test="sid != null">
                AND sid like #{
        sid}
            </if>
        </select>
    
         <select id="findQuery" resultType="Student">
                <include refid="selectvp"/>
                <where>
                    <if test="sacc != null">
                        sacc like concat('%' #{
        sacc} '%')
                    </if>
                    <if test="sname != null">
                        AND sname like concat('%' #{
        sname} '%')
                    </if>
                    <if test="sex != null">
                        AND sex=#{
        sex}
                    </if>
                    <if test="phone != null">
                        AND phone=#{
        phone}
                    </if>
                </where>
            </select>
    
        <update id="upd">
            update student
            <set>
                <if test="sname != null">sname=#{
        sname},</if>
                <if test="spwd != null">spwd=#{
        spwd},</if>
                <if test="sex != null">sex=#{
        sex},</if>
                <if test="phone != null">phone=#{
        phone},</if>
            </set>
            where sid=#{
        sid}
        </update>
    
        <insert id="add">
            insert  into student
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="sname != null">sname,</if>
                <if test="spwd != null">spwd,</if>
                <if test="sex != null">sex,</if>
                <if test="phone != null">phone,</if>
            </trim>
    
            <trim prefix="values (" suffix=")"  suffixOverrides=",">
                <if test="sname != null">#{
        sname},</if>
                <if test="spwd != null">#{
        spwd},</if>
                <if test="sex != null">#{
        sex},</if>
                <if test="phone != null">#{
        phone}</if>
            </trim>
    
        </insert>
        
        <select id="findAll" resultType="Student" parameterType="Integer">
            <include refid="selectvp"/> WHERE sid in
            <foreach item="ids" collection="array"  open="(" separator="," close=")">
                #{
        ids}
            </foreach>
        </select>
    
        <delete id="del"  parameterType="Integer">
            delete  from  student  where  sid in
            <foreach item="ids" collection="array"  open="(" separator="," close=")">
                #{
        ids}
            </foreach>
        </delete>
    
    </mapper>

package com.yzx.test;
    
    import com.yzx.entity.Student;
    import com.yzx.mapper.StuMapper;
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    import org.junit.After;
    import org.junit.Before;
    import org.junit.Test;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.List;
    
    public class StuTest {
        
        SqlSession sqlSession=null;
        InputStream is=null;
    
        @Before
        public   void  before() throws IOException {
        
            //1.读取核心配置文件
            is= Resources.getResourceAsStream("sqlMapperConfig.xml");
            //2.拿到工厂构建类
            SqlSessionFactoryBuilder sqlSessionFactoryBuilder=new SqlSessionFactoryBuilder();
            //3.拿到具体工厂
            SqlSessionFactory build=sqlSessionFactoryBuilder.build(is);
            //4.拿到session
            sqlSession = build.openSession();
        }
    
        @After
        public  void  after(){
        
            //7，提交事务
            sqlSession.commit();
            //8.关闭资源
            sqlSession.close();
            if(is!=null){
        
                try {
        
                    is.close();
                } catch (IOException e) {
        
                    e.printStackTrace();
                }
            };
        }
    
        //查询所有
        @Test
        public  void  find(){
        
            //5.获取具体的mapper接口
            StuMapper mapper=sqlSession.getMapper(StuMapper.class);
            //6.调用执行
            List<Student> list=mapper.find();
            list.forEach(a-> System.out.println(a));
        }
        //查询单个
        @Test
        public  void  findbyid(){
        
    
            StuMapper mapper=sqlSession.getMapper(StuMapper.class);
            List<Student> list=mapper.findbyid(2);
            list.forEach(a-> System.out.println(a));
        }
        //模糊查询
        @Test
        public  void  findQuery(){
        
    
            StuMapper mapper=sqlSession.getMapper(StuMapper.class);
    
            Student  stu=new Student();
            stu.setSname("小");
            stu.setSex("男");
            List<Student> list=mapper.findQuery(stu);
            list.forEach(a-> System.out.println(a));
        }
        //修改
        @Test
        public  void  upd(){
        
    
            StuMapper mapper=sqlSession.getMapper(StuMapper.class);
    
            Student  stu=new Student();
            stu.setSid(3);
            stu.setSname("小若");
            stu.setSex("人妖");
            int i=mapper.upd(stu);
            System.out.println("修改了"+i+"条数据"+"  "+stu.toString());
    
        }
        //添加
        @Test
        public  void  add(){
        
    
            StuMapper mapper=sqlSession.getMapper(StuMapper.class);
    
            Student  stu=new Student();
            stu.setSname("小贺");
            stu.setSex("男");
            stu.setPhone("99999999");
            int i=mapper.add(stu);
            System.out.println("添加了"+i+"条数据"+"  "+stu.toString());
    
        }
    
        //批量操作
        @Test
        public  void  findAll(){
        
    
            StuMapper mapper=sqlSession.getMapper(StuMapper.class);
            Integer[] i={
        1,2,3,4};
            List<Student> list=mapper.findAll(i);
            list.forEach(a-> System.out.println(a));
        }
        //批量操作
    
        //批量删除
        @Test
        public  void  del(){
        
            StuMapper mapper=sqlSession.getMapper(StuMapper.class);
            Integer[] i={
        1,2,3,4};
            int i1=mapper.del(i);
            System.out.println("删除了"+i1+"条数据");
        }
    }

## 6.sql ##

> 在实际开发中会遇到许多相同的SQL，比如根据某个条件筛选，这个筛选很多地方都能用到，我们可以将其抽取出来成为一个公用的部分，这样修改也方便，一旦出现了错误，只需要改这一处便能处处生效了，此时就用到了这个标签了。  
> 当多种类型的查询语句的查询字段或者查询条件相同时，可以将其定义为常量，方便调用。为求 结构清晰也可将 sql 语句分解。

<sql id="selectvp">
        select  *  from  student
    </sql>

## 7 include ##

> 这个标签和是天仙配，是共生的，include用于引用sql标签定义的常量。比如引用上面sql标签定义的常量  
> refid这个属性就是指定标签中的id值（唯一标识）

<select id="findbyid"  resultType="student">
        <include refid="selectvp"/>
        WHERE 1=1
        <if test="sid != null">
            AND sid like #{
        sid}
        </if>
    </select>

## 8、如何引用其他XML中的SQL片段 ##

> 比如你在com.xxx.dao.xxMapper这个Mapper的XML中定义了一个SQL片段如下：

<sql id="Base_Column_List"> ID,MAJOR,BIRTHDAY,AGE,NAME,HOBBY</sql>

此时我在com.xxx.dao.PatinetMapper中的XML文件中需要引用，如下：

[6ea7888f064a4694bf5437023fdc3e08.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/18ee79a5144f4d99ad150aafcaaac29e.png