动态SQL搭建

动态SQL搭建

**什么是动态SQL：**动态SQL就是根据不同的条件生成不同的SQL语句

我们之前写的 SQL 语句都比较简单，如果有比较复杂的业务，我们需要写复杂的 SQL 语句，往往需要拼接，而拼接 SQL ，稍微不注意，由于引号，空格等缺失可能都会导致错误。
那么怎么去解决这个问题呢？这就要使用 mybatis 动态SQL，通过 if, choose, when, otherwise, trim, where, set, foreach等标签，可组合成非常灵活的SQL语句，从而在提高 SQL 语句的准确性的同时，也大大提高了开发人员的效率。

搭建环境

CREATE TABLE `blog` (
  `id` int(11) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `author` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `views` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

创建一个基础工程

1.导依赖包

<!--导入依赖-->
    <dependencies>
    <!--mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.16</version>
        </dependency>
    <!--Mybatis依赖-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.3</version>
        </dependency>
     <!--Junit-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <!--在build中配置resources，来防止我们资源导出失败的问题-->
    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
        </resources>
    </build>

2.编写配置文件

resources下的db.properties和Mybatis-config.xml文件

3.编写实体类

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

4.编写实体类对应的Mapper接口和Mapper.xml文件

IF

 <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>

choose(when,otherwise)

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

trim(where,set)

where

    <select id="queryBlogIF" parameterType="Map" resultType="Blog">
        select * from blog
        <where>
            <if test="title != null">
                and title = #{title}
            </if>
            <if test="author !=null">
                and author = #{author}
            </if>
        </where>
    </select>

set

<update id="updateBlog"   parameterType="Map">
        update  blog
        <set>
            <if test="title !=null">
                title =#{title},
            </if>
            <if test="author !=null">
                author =#{author}
            </if>
            where id=#{id}
        </set>
    </update>

注意点：：if语句面的的最后一句不加逗号其他的都得加上逗号

SQL片段

有时候我们可能会将一部分的功能抽离出来，方便复用。

1.用SQL便签把公共的部分抽公共的部分

<sql id="if-title-author">
        <if test="title !=null">
            title =#{title},
        </if>
        <if test="author !=null">
            author =#{author}
        </if>
    </sql>

2.在需要使用的地方使用include便签引用即可

<update id="updateBlog"   parameterType="Map">
        update  blog
        <set>
            <include refid="if-title-author" />
            where id=#{id}
        </set>
    </update>

注意点：

• 最好基于一个标签来定义SQL片段
• 不要存在where标签

Foreach

<select id="queryBlogForeach" parameterType="Map" resultType="Blog">
      //  select * from blog where 1=1 and (id=1 or id=2 or id=3)
    
  <foreach item="id"  collection="ids"
           open="(" separator="or" close=")">
        #{id}
  </foreach>
</select>

1.第一步

<select id="queryBlogForeach" parameterType="Map" resultType="Blog">
        select * from blog
        <where>
            <foreach item="id"  collection="ids"
                     open="and (" separator="or" close=")">
                id=#{id}
            </foreach>
        </where>
    </select>

2.第二部

@Test
    public void queryBlogForeach() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        HashMap map = new HashMap();

        ArrayList<Integer> ids = new ArrayList<Integer>();
        ids.add(1);ids.add(2);ids.add(3);

        map.put("ids",ids);

        List<Blog> blogs = mapper.queryBlogForeach(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }