mybatis之动态SQL

简介

动态Sql就是根据不同的条件生成不同的sql语句。

动态Sql的好处

使用JDBC 或其它类似的框架，我们都应该能理解根据不同条件拼接 SQL 语句是很困难的，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号等等，但是利用动态Sql可以很好地解决这样的问题。

什么是动态Sql

动态Sql元素和JSTL或类似XML的文本处理器相似。在Mybatis之前的版本中，有很多元素需要花时间去了解。然而Mybatis3大大精简了元素种类，我们只需要学习原来元素的一半就可以了。Mybatis采用功能强大的基于OGNL的表达式来淘汰其它大部分元素。

需要学习的元素

• if
• choose (when, otherwise)
• trim (where, set)
• foreach

环境搭建

• 数据库表

• 导入架包 

<!--    导入依赖-->
    <dependencies>
        <!--        MySQL驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.29</version>
        </dependency>
        <!--        Mybatis-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.1</version>
        </dependency>
        <!--        junit-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.26</version>
        </dependency>
    </dependencies>
    <!--    在build中配置resource，防止资源导出失败的问题-->
    <build>
        <resources>
            <resource>
                <directory>src/main/resource</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>

•  编写核心配置文件

<settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <typeAliases>
        <package name="com.RXJ.pojo"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
<!--    接口绑定-->
    <mappers>
        <mapper class="com.RXJ.mapper.BlogMapper"/>
    </mappers>

• 编写实体类

@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private int views;
}

• 编写mapper接口及对应的mapper.xml文件

  • IF

    • mapper接口

    • public interface BlogMapper {
          //查询博客
          List<Blog> queryBlogIf(Map map);
      
          //插入博客
          int updateBlog(Map map);
      }

    • mapper.xml文件

    •     <select id="queryBlogIf" parameterType="map" resultType="blog">
              select * from blog where 1=1
              <if test="title != null">
                  and title=#{title}
              </if>
              <if test="author!=null">
                  and author=#{author}
              </if>
          </select>
      
          <update id="updateBlog" parameterType="map">
              update blog
              <set>
                  <if test="title!=null">
                      title=#{title},
                  </if>
                  <if test="author!=null">
                      author=#{author}
                  </if>
              </set>
              where id=#{id}
          </update>

    •   测试

    •     @Test
          public void queryBlogIf(){
              SqlSession sqlSession = MybatisUtils.getSqlSession();
              BlogMapper mapper = sqlSession.getMapper( BlogMapper.class );
      
              HashMap map = new HashMap();
      //        map.put( "title","努力学java" );
              map.put( "author","来一沓Java" );
      
              List<Blog> blogs = mapper.queryBlogIf( map );
              for(Blog blog:blogs){
                  System.out.println(blog);
              }
              sqlSession.close();
          }
      
          @Test
          public void updateBlog(){
              SqlSession sqlSession = MybatisUtils.getSqlSession();
              BlogMapper mapper = sqlSession.getMapper( BlogMapper.class );
      
              HashMap map = new HashMap();
              map.put( "title","努力学java1" );
              map.put( "author","来一沓Java" );
              map.put( "id","16c996067b8e40b2bdcc766102b2c9a6" );
      
              mapper.updateBlog( map );
              sqlSession.commit();
      
              sqlSession.close();
          }

        小结：动态Sql语句IF可以根据不同条件灵活选择sql语句。

• trim (where, set)：where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。set 元素用于动态更新语句的类似问题，set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）
  • mapper接口

  • List<Blog> queryBlogChoose(Map map);

  • mapper.xml文件

  •     <select id="queryBlogChoose" parameterType="map" resultType="blog">
            select * from blog
            <where>
                <if test="title!=null">
                    title=#{title}
                </if>
                <if test="author!=null">
                    and author=#{author}
                </if>
            </where>
        </select>

  • 测试

  •     @Test
        public void queryBlogChoose(){
            SqlSession sqlSession = MybatisUtils.getSqlSession();
            BlogMapper mapper = sqlSession.getMapper( BlogMapper.class );
    
            HashMap map = new HashMap();
    //        map.put( "title","努力学java" );
            map.put( "author","来一沓Java" );
    
            List<Blog> blogs = mapper.queryBlogIf( map );
            for(Blog blog:blogs){
                System.out.println(blog);
            }
            sqlSession.close();
        }

  •  choose (when, otherwise)

    • mapper接口

      • List<Blog> queryBlogWhereChoose(Map map);

    • mapper.xml文件

      •     <select id="queryBlogWhereChoose" parameterType="map" resultType="blog">
                select * from blog
                <where>
                    <choose>
                        <when test="title!=null">
                            title=#{title}
                        </when>
                        <when test="author!=null">
                            and author=#{author}
                        </when>
                        <otherwise>
                            and views=#{views}
                        </otherwise>
                    </choose>
                </where>
            </select>

    • 测试

      •     @Test
            public void queryBlogWhereChoose(){
                SqlSession sqlSession = MybatisUtils.getSqlSession();
                BlogMapper mapper = sqlSession.getMapper( BlogMapper.class );
        
                HashMap map = new HashMap();
        //        map.put( "title","努力学java" );
                map.put( "author","来一沓Java" );
                map.put( "views",9999 );
        
                List<Blog> blogs = mapper.queryBlogWhereChoose( map );
                for(Blog blog:blogs){
                    System.out.println(blog);
                }
                sqlSession.close();
            }

总结：所谓的动态SQL语句，本质上还是SQL语句，只是我们可以在SQL层面，去执行一个逻辑代码。