MyBatis 动态sql

环境搭建、if语句、where、Set、choose语句

• 什么是动态sql：

动态SQL指的是根据不同的查询条件 , 生成不同的Sql语句。

if语句、where、Set、choose语句的特点

• if语句：如果符合if中test的条件，则条件成立。
• where语句：标签，智能的处理“where 和and”。
• choose语句：只选择其中的一个条件，查询条件有一个满足即可。
• Set语句：set的意义和where类似，set是为了智能去除“，”。

1. 新建数据库表：blog

use mybatis;

create table blog (
id varchar(50) not null comment '播客id',
title varchar(100) not null comment '播客标题', 
author varchar(50) not null comment '播客作者',
create_time datetime not null comment '创建时间',
view int not null comment '浏览量',
primary key(id)
)engine=innodb DEFAULT charset=utf8

2. 创建Mybatis基础工程
   各种路径和包的搭建
3. IDutil工具类
   自动生成String类型的id

public class IDUtils {
    public static String getId() {
        return UUID.randomUUID().toString().replaceAll("-","");
    }

}

4. 实体类编写 【注意set方法作用】

@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;//属性和字段名不一致
    private int view;
}

5. 编写Mapper接口及xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kuang.dao.BlogMapper">

    <!--环境搭建测试用-->
    <insert id="insertBlog" parameterType="Blog">
        insert into blog (id,title,author,create_time,view) values(#{id},#{title},#{author},#{createTime},#{view})
    </insert>

</mapper>

6. mybatis核心配置文件，下划线驼峰自动转换
   在核心配置文件中启用下划线与驼峰式命名规则的映射 mapUnderscoreToCamelCase

    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <setting name="mapUnderscoreToCamelCase" value="true" />
    </settings>

if语句、where、set和choose语句

1. 编写接口

public interface BlogMapper {

    //添加一条blog
    int insertBlog(Blog blog);

    //if条件判断查询
    List<Blog> selectBlogByIf(Map map);

    //choose语句查询blog
    List<Blog> selectBlogByChoose(Map map);

    //set语句更新blog
    int updateBlogBySet(Map map);
}

2. 编写sql语句

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kuang.dao.BlogMapper">

    <!--环境搭建测试用-->
    <insert id="insertBlog" parameterType="Blog">
        insert into blog (id,title,author,create_time,view) values(#{id},#{title},#{author},#{createTime},#{view})
    </insert>

    <!--if判断，查询blog-->
    <select id="selectBlogByIf" 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>

    <!--根据choose语句查询blog,只要有一个条件满足，就中断后续条件-->
    <select id="selectBlogByChoose" parameterType="Map" resultType="Blog">
        select * from blog
        <where>
            <choose>
                <when test="title!=null">
                    title=#{title}
                </when>
                <when test="author!=null">
                    author=#{author}
                </when>
                <otherwise>
                    and view=#{view}
                </otherwise>
            </choose>
        </where>
    </select>

    <!--根据set语句，更新blog-->
    <update id="updateBlogBySet" parameterType="Map">
        update blog
        <set>
            <if test="title!=null">
                title=#{title},
            </if>
            <if test="author!=null">
                author=#{author}
            </if>
        </set>
        where id = #{id}
    </update>

</mapper>

3. 编写测试

public class StMapperTest {

    @Test
    public void testInsertBLog() {

        SqlSession session = MyBatisUtils.getSqlSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        Blog blog = new Blog();
        blog.setId(IDUtils.getId());
        blog.setTitle("三峡大坝今年首次满发！");
        blog.setAuthor("大风吹");
        blog.setCreateTime(new Date());
        blog.setView(999);

        mapper.insertBlog(blog);

        blog.setId(IDUtils.getId());
        blog.setTitle("媒体批yyds！");
        blog.setAuthor("新华每日电讯");
        blog.setCreateTime(new Date());
        blog.setView(888);

        mapper.insertBlog(blog);

        blog.setId(IDUtils.getId());
        blog.setTitle("上海部分地区房租上涨15%");
        blog.setAuthor("上海打工人");
        blog.setCreateTime(new Date());
        blog.setView(777);

        mapper.insertBlog(blog);

        session.close();
    }

    //if判断，查询blog;以及choose语句
    @Test
    public void testSelectBlogByif() {
        SqlSession session = MyBatisUtils.getSqlSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        HashMap map = new HashMap();
        map.put("id","c7de6029f78f486c97fc4039173f5e29");
        map.put("title","阿福韩阅兵%");
        map.put("author","贝壳网");
        //map.put("view",3432);

        //if语句的测试
        //mapper.selectBlogByIf(map);
        //choose语句的测试
        //mapper.selectBlogByChoose(map);
        //set语句的测试
        mapper.updateBlogBySet(map);

        session.close();
    }

}