Mybatis学习（11）动态SQL

动态SQL

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

搭建环境

将数据库中的create_time转换为createTime驼峰命名法的设置

<setting name="mapUnderscoreToCamelCase" value="true"/>

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.编写实体类

@Data
public class Blog {

    private int id;
    private String title;
    private String author;
    private Date createTime;
    private int 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="queryBlogChoose" 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>

trim(where,set)

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

SQL片段

有时候，我们可能会将一些功能的部分抽取出来，方便复用！
1.使用SQL标签抽取公共部份

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

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

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

Foreach

    <select id="queryBlogForeach" parameterType="map" resultType="Blog">
        select * from 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);
        }

        sqlSession.close();
    }

动态SQL就是在拼接SQL语句，我们自己要保证SQL的正确性，按照SQL的格式，去排列组合就可以了
建议：现在Mysql中写出完整的SQL，再去对应的修改成为我们的动态SQL实现通用即可