动态sql

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

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

环境搭建

CREATE TABLE `blog`(
`id` VARCHAR(50) NOT NULL COMMENT '博客id',
`title` VARCHAR(100) NOT NULL COMMENT '博客标题',
`author` VARCHAR(30) NOT NULL COMMENT '博客作者',
`create_time` DATETIME NOT NULL COMMENT '创建时间',
`views` INT(30) NOT NULL COMMENT '浏览量'
)ENGINE=INNODB DEFAULT CHARSET=utf8

创建一个基础工程
1、导包
2、编写配置文件
3、编写实体类
4、编写实体类对应的接口和xml文件

package com.linfeng.pojo;

import lombok.Data;

import java.util.Date;
@Data
public class Blog {
    private int id;
    private String titles;
    private String author;
    private Date creat_time;
    private int views;
}

if

<select id="queryBlogIF" parameterType="map" resultType="blog">
        select  * from mybatis.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="queryBlogChoose" parameterType="map" resultType="blog">
        select  * from mybatis.blog
<where>
    <choose>
        <when test="title != null">
            title = #{title}
        </when>
<when test="author">
    and author = #{author}
</when>
<otherwise>
    and Views = #{Views}
</otherwise>
    </choose>
</where>
    </select>

trim (where, set)

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

    </select>

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

foreach

//查询1-3号的博客
    List<Blog> queryBlogForeach(Map map);

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

@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);

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

sql标签

sql标签抽取公共部分

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

include引用

 <select id="queryBlogIF" parameterType="map" resultType="blog">
        select  * from mybatis.blog
        <where>
            <include refid="if-title-author"></include>
        </where>

    </select>

注意：

• 最好基于单表来定义SQL操作
• 不要存在where标签

== 动态SQL就是在拼接sql语句，我们只要保证sql的正确性，按照sql的格式，去排列组合就可以了==

建议：先在mysql中写出完整的sql，再对应的去修改成为我们的动态sql实现即可