动态SQL

• https://mybatis.org/mybatis-3/zh/dynamic-sql.html

搭建环境

• sql

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;

• 导包
• 编写配置文件
• 编写实体类

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

• 编写对应的Mapper接口和Mapper.xml文件

If

• BlogMapper.java

public interface BlogMapper {
    List<Blog> getBlogs();

    List<Blog> queryBlogIf(Map map);
}

• BlogMapper.xml

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

    <resultMap id="myBlog" type="com.pojo.Blog">
        <result property="createTime" column="create_time"/>
    </resultMap>

    <select id="getBlogs" resultMap="myBlog">
        select *
        from blog
    </select>

    <select id="queryBlogIf" parameterType="map" resultMap="myBlog">
        select *
        from blog
        where 1=1
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="author!=null">
            and author = #{aotuor}
        </if>
    </select>

</mapper>

• test

@Test
public void test03(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    Map map=new HashMap();
    map.put("title", "java");
    List<Blog> blogs = mapper.queryBlogIf(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }
}

choose、when、otherwise

<select id="queryBlogChoose" parameterType="map" resultMap="myBlog">
    select *
    from blog
    <where>
        <choose>
            <when test="title != null">
                title = #{title}
            </when>
            <when test="author != null">
                and author= #{author}
            </when>
        </choose>
    </where>
</select>

trim、where、set

List<Blog> getBlogs();

List<Blog> queryBlogIf(Map map);

List<Blog> queryBlogChoose(Map map);

void updateBlog(Map map);

<select 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}
</select>

  @Test
  public void test05() {
      SqlSession sqlSession = MybatisUtils.getSqlSession();
      BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
      Map map = new HashMap();
      map.put("id","1");
      map.put("author", "baba");
      mapper.updateBlog(map);
  }

SQL片段

• sql，include

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

  <select id="updateBlog" parameterType="map">
      update blog
      <include refid="id-title-author"></include>
      where id = #{id}
  </select>

foreach

• xml

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

</select>

• test

@Test
public void test0() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    Map map = new HashMap();
    ArrayList<Integer> ids = new ArrayList<>();
    ids.add(1);
    ids.add(2);
    map.put("ids", ids);
    List<Blog> blogs = mapper.queryBlogForeach(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }
}