• MyBatis 动态 SQL 实践教程

    一、MyBatis动态 sql 是什么

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

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

    1.Mybatis 动态 sql 是做什么的?

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

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

    图片

    3.动态 sql 的执行原理？

    原理为：使用 OGNL 从 sql 参数对象中计算表达式的值，根据表达式的值动态拼接 sql，以此来完成动态 sql 的功能。

    二、MyBatis标签

    1.if标签：条件判断

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

    1）不使用动态sql

    1. <select id="selectUserByUsernameAndSex"
    2.         resultType="user" parameterType="com.ys.po.User">
    3.     <!-- 这里和普通的sql 查询语句差不多，对于只有一个参数，后面的 #{id}表示占位符，里面          不一定要写id,
    4.          写啥都可以，但是不要空着，如果有多个参数则必须写pojo类里面的属性 -->
    5.     select * from user where username=#{username} and sex=#{sex}
    6. </select>

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

    <if test="判断条件">    SQL语句</if>

    2）使用动态sql

    上面的查询语句，我们可以发现，如果 #{username} 为空，那么查询结果也是空，如何解决这个问题呢？使用 if 来判断，可多个 if 语句同时使用。

    以下语句表示为可以按照网站名称（name）或者网址（url）进行模糊查询。如果您不输入名称或网址，则返回所有的网站记录。但是，如果你传递了任意一个参数，它就会返回与给定参数相匹配的记录。

    1. <select id="selectAllWebsite" resultMap="myResult">  
    2.     select id,name,url from website 
    3.     where 1=1    
    4.    <if test="name != null">        
    5.        AND name like #{name}   
    6.    </if>    
    7.    <if test="url!= null">        
    8.        AND url like #{url}    
    9.    </if>
    10. </select>

    2.where+if标签

    where、if同时使用可以进行查询、模糊查询

    注意，失败后，  关键字只会去掉库表字段赋值前面的and，不会去掉语句后面的and关键字，即注意， 只会去掉 语句中的最开始的and关键字。所以下面的形式是不可取的

    1. <select id="findQuery" resultType="Student">
    2.     <include refid="selectvp"/>
    3.     <where>
    4.         <if test="sacc != null">
    5.             sacc like concat('%' #{sacc} '%')
    6.         </if>
    7.         <if test="sname != null">
    8.             AND sname like concat('%' #{sname} '%')
    9.         </if>
    10.         <if test="sex != null">
    11.             AND sex=#{sex}
    12.         </if>
    13.         <if test="phone != null">
    14.             AND phone=#{phone}
    15.         </if>
    16.     </where>
    17. </select>

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

    3.set标签

    set可以用来修改

    1. <update id="upd">
    2.     update student
    3.     <set>
    4.         <if test="sname != null">sname=#{sname},</if>
    5.         <if test="spwd != null">spwd=#{spwd},</if>
    6.         <if test="sex != null">sex=#{sex},</if>
    7.         <if test="phone != null">phone=#{phone}</if>
    8.     sid=#{sid}
    9.     </set>
    10.     where sid=#{sid}
    11. </update>

    4.choose(when,otherwise) 语句

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

    1. <select id="selectUserByChoose" resultType="com.ys.po.User" parameterType="com.ys.po.User">
    2.       select * from user
    3.       <where>
    4.           <choose>
    5.               <when test="id !='' and id != null">
    6.                   id=#{id}
    7.               </when>
    8.               <when test="username !='' and username != null">
    9.                   and username=#{username}
    10.               </when>
    11.               <otherwise>
    12.                   and sex=#{sex}
    13.               </otherwise>
    14.           </choose>
    15.       </where>
    16.   </select>

    也就是说，这里我们有三个条件，id、username、sex，只能选择一个作为查询条件

    • 如果 id 不为空，那么查询语句为：select * from user where id=?

    • 如果 id 为空，那么看username 是否为空，如果不为空，那么语句为 select * from user where username=?;

    • 如果 username 为空，那么查询语句为 select * from user where sex=?

    5.trim

    trim标记是一个格式化的标记，可以完成set或者是where标记的功能

    ①、用 trim 改写上面第二点的 if+where 语句

    1. <select id="selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User">
    2.     select * from user
    3.     <!-- 
    4.         <if test="username != null">
    5.            username=#{username}
    6.         </if>
    7.          
    8.         <if test="username != null">
    9.            and sex=#{sex}
    10.         </if>
    11.     </where>  -->
    12.     <trim prefix="where" prefixOverrides="and | or">
    13.         <if test="username != null">
    14.            and username=#{username}
    15.         </if>
    16.         <if test="sex != null">
    17.            and sex=#{sex}
    18.         </if>
    19.     </trim>
    20. </select>

    • prefix：前缀

    • prefixoverride：去掉第一个and或者是or

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

    1. <!-- 根据 id 更新 user 表的数据 -->
    2. <update id="updateUserById" parameterType="com.ys.po.User">
    3.     update user u
    4.         <!-- 
    5.             <if test="username != null and username != ''">
    6.                 u.username = #{username},
    7.             </if>
    8.             <if test="sex != null and sex != ''">
    9.                 u.sex = #{sex}
    10.             </if>
    11.         </set> -->
    12.         <trim prefix="set" suffixOverrides=",">
    13.             <if test="username != null and username != ''">
    14.                 u.username = #{username},
    15.             </if>
    16.             <if test="sex != null and sex != ''">
    17.                 u.sex = #{sex},
    18.             </if>
    19.         </trim>
    20.      
    21.      where id=#{id}
    22. </update>

    • suffix：后缀

    • suffixoverride：去掉最后一个逗号（也可以是其他的标记，就像是上面前缀中的and一样）

    ③、trim+if同时使用可以添加

    1. <insert id="add">
    2.     insert  into student
    3.     <trim prefix="(" suffix=")" suffixOverrides=",">
    4.         <if test="sname != null">sname,</if>
    5.         <if test="spwd != null">spwd,</if>
    6.         <if test="sex != null">sex,</if>
    7.         <if test="phone != null">phone,</if>
    8.     </trim>
    9.  
    10.     <trim prefix="values (" suffix=")"  suffixOverrides=",">
    11.         <if test="sname != null">#{sname},</if>
    12.         <if test="spwd != null">#{spwd},</if>
    13.         <if test="sex != null">#{sex},</if>
    14.         <if test="phone != null">#{phone}</if>
    15.     </trim>
    16.  
    17. </insert>

    6.MyBatis foreach标签

    foreach是用来对集合的遍历，这个和Java中的功能很类似。通常处理SQL中的in语句。

    foreach 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符

    你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时，index 是当前迭代的序号，item 的值是本次迭代获取到的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。

    1. //批量查询
    2. <select id="findAll" resultType="Student" parameterType="Integer">
    3.     <include refid="selectvp"/> WHERE sid in
    4.     <foreach item="ids" collection="array"  open="(" separator="," close=")">
    5.         #{ids}
    6.     </foreach>
    7. </select>
    8. //批量删除
    9. <delete id="del"  parameterType="Integer">
    10.     delete  from  student  where  sid in
    11.     <foreach item="ids" collection="array"  open="(" separator="," close=")">
    12.         #{ids}
    13.     </foreach>
    14. </delete>
    整合案例

    xml

    1. <?xml version="1.0" encoding="UTF-8" ?>
    2. <!DOCTYPE mapper
    3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    5. <mapper namespace="com.yzx.mapper.StuMapper">
    6.     <sql id="selectvp">
    7.         select  *  from  student
    8.     </sql>
    9.     
    10.     <select id="find" resultType="Student">
    11.         <include refid="selectvp"/>
    12.     </select>
    13.  
    14.     <select id="findbyid"  resultType="student">
    15.         <include refid="selectvp"/>
    16.         WHERE 1=1
    17.         <if test="sid != null">
    18.             AND sid like #{sid}
    19.         </if>
    20.     </select>
    21.  
    22.         <select id="findQuery" resultType="Student">
    23.             <include refid="selectvp"/>
    24.             <where>
    25.                 <if test="sacc != null">
    26.                     sacc like concat('%' #{sacc} '%')
    27.                 </if>
    28.                 <if test="sname != null">
    29.                     AND sname like concat('%' #{sname} '%')
    30.                 </if>
    31.                 <if test="sex != null">
    32.                     AND sex=#{sex}
    33.                 </if>
    34.                 <if test="phone != null">
    35.                     AND phone=#{phone}
    36.                 </if>
    37.             </where>
    38.         </select>
    39.  
    40.     <update id="upd">
    41.         update student
    42.         <set>
    43.             <if test="sname != null">sname=#{sname},</if>
    44.             <if test="spwd != null">spwd=#{spwd},</if>
    45.             <if test="sex != null">sex=#{sex},</if>
    46.             <if test="phone != null">phone=#{phone}</if>
    47.         sid=#{sid}
    48.         </set>
    49.         where sid=#{sid}
    50.     </update>
    51.  
    52.     <insert id="add">
    53.         insert  into student
    54.         <trim prefix="(" suffix=")" suffixOverrides=",">
    55.             <if test="sname != null">sname,</if>
    56.             <if test="spwd != null">spwd,</if>
    57.             <if test="sex != null">sex,</if>
    58.             <if test="phone != null">phone,</if>
    59.         </trim>
    60.  
    61.         <trim prefix="values (" suffix=")"  suffixOverrides=",">
    62.             <if test="sname != null">#{sname},</if>
    63.             <if test="spwd != null">#{spwd},</if>
    64.             <if test="sex != null">#{sex},</if>
    65.             <if test="phone != null">#{phone}</if>
    66.         </trim>
    67.  
    68.     </insert>
    69.     <select id="findAll" resultType="Student" parameterType="Integer">
    70.         <include refid="selectvp"/> WHERE sid in
    71.         <foreach item="ids" collection="array"  open="(" separator="," close=")">
    72.             #{ids}
    73.         </foreach>
    74.     </select>
    75.  
    76.     <delete id="del"  parameterType="Integer">
    77.         delete  from  student  where  sid in
    78.         <foreach item="ids" collection="array"  open="(" separator="," close=")">
    79.             #{ids}
    80.         </foreach>
    81.     </delete>
    82.  
    83.  
    84.  
    85. </mapper>

    测试类:

    1. package com.yzx.test;
    2.  
    3. import com.yzx.entity.Student;
    4. import com.yzx.mapper.StuMapper;
    5. import org.apache.ibatis.io.Resources;
    6. import org.apache.ibatis.session.SqlSession;
    7. import org.apache.ibatis.session.SqlSessionFactory;
    8. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    9. import org.junit.After;
    10. import org.junit.Before;
    11. import org.junit.Test;
    12.  
    13. import java.io.IOException;
    14. import java.io.InputStream;
    15. import java.util.List;
    16.  
    17. public class StuTest {
    18.     SqlSession sqlSession=null;
    19.     InputStream is=null;
    20.  
    21.     @Before
    22.     public   void  before() throws IOException {
    23.         //1.读取核心配置文件
    24.         is= Resources.getResourceAsStream("sqlMapperConfig.xml");
    25.         //2.拿到工厂构建类
    26.         SqlSessionFactoryBuilder sqlSessionFactoryBuilder=new SqlSessionFactoryBuilder();
    27.         //3.拿到具体工厂
    28.         SqlSessionFactory build=sqlSessionFactoryBuilder.build(is);
    29.         //4.拿到session
    30.         sqlSession = build.openSession();
    31.     }
    32.  
    33.     @After
    34.     public  void  after(){
    35.         //7，提交事务
    36.         sqlSession.commit();
    37.         //8.关闭资源
    38.         sqlSession.close();
    39.         if(is!=null){
    40.             try {
    41.                 is.close();
    42.             } catch (IOException e) {
    43.                 e.printStackTrace();
    44.             }
    45.         };
    46.     }
    47.  
    48.     //查询所有
    49.     @Test
    50.     public  void  find(){
    51.         //5.获取具体的mapper接口
    52.         StuMapper mapper=sqlSession.getMapper(StuMapper.class);
    53.         //6.调用执行
    54.         List list=mapper.find();
    55.         list.forEach(a-> System.out.println(a));
    56.     }
    57.     //查询单个
    58.     @Test
    59.     public  void  findbyid(){
    60.  
    61.         StuMapper mapper=sqlSession.getMapper(StuMapper.class);
    62.         List list=mapper.findbyid(2);
    63.         list.forEach(a-> System.out.println(a));
    64.     }
    65.     //模糊查询
    66.     @Test
    67.     public  void  findQuery(){
    68.  
    69.         StuMapper mapper=sqlSession.getMapper(StuMapper.class);
    70.  
    71.         Student  stu=new Student();
    72.         stu.setSname("小");
    73.         stu.setSex("男");
    74.         List list=mapper.findQuery(stu);
    75.         list.forEach(a-> System.out.println(a));
    76.     }
    77.     //修改
    78.     @Test
    79.     public  void  upd(){
    80.  
    81.         StuMapper mapper=sqlSession.getMapper(StuMapper.class);
    82.  
    83.         Student  stu=new Student();
    84.         stu.setSid(3);
    85.         stu.setSname("小若");
    86.         stu.setSex("人妖");
    87.         int i=mapper.upd(stu);
    88.         System.out.println("修改了"+i+"条数据"+"  "+stu.toString());
    89.  
    90.     }
    91.     //添加
    92.     @Test
    93.     public  void  add(){
    94.  
    95.         StuMapper mapper=sqlSession.getMapper(StuMapper.class);
    96.  
    97.         Student  stu=new Student();
    98.         stu.setSname("小贺");
    99.         stu.setSex("男");
    100.         stu.setPhone("99999999");
    101.         int i=mapper.add(stu);
    102.         System.out.println("添加了"+i+"条数据"+"  "+stu.toString());
    103.  
    104.     }
    105.  
    106.     //批量操作
    107.     @Test
    108.     public  void  findAll(){
    109.  
    110.         StuMapper mapper=sqlSession.getMapper(StuMapper.class);
    111.         Integer[] i={1,2,3,4};
    112.         List list=mapper.findAll(i);
    113.         list.forEach(a-> System.out.println(a));
    114.     }
    115.     //批量操作
    116.  
    117.     //批量删除
    118.     @Test
    119.     public  void  del(){
    120.         StuMapper mapper=sqlSession.getMapper(StuMapper.class);
    121.         Integer[] i={1,2,3,4};
    122.         int i1=mapper.del(i);
    123.         System.out.println("删除了"+i1+"条数据");
    124.     }
    125. }

    7.sql

    在实际开发中会遇到许多相同的SQL，比如根据某个条件筛选，这个筛选很多地方都能用到，我们可以将其抽取出来成为一个公用的部分，这样修改也方便，一旦出现了错误，只需要改这一处便能处处生效了，此时就用到了这个标签了。

    当多种类型的查询语句的查询字段或者查询条件相同时，可以将其定义为常量，方便调用。为求