Mybatis之动态SQL

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

package com.lei.pojo;

import java.util.Date;

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

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public int getViews() {
        return views;
    }

    public void setViews(int views) {
        this.views = views;
    }

    @Override
    public String toString() {
        return "blog{" +
                "id=" + id +
                ", title='" + title + '\'' +
                ", author='" + author + '\'' +
                ", createTime=" + createTime +
                ", views=" + views +
                '}';
    }
}

package com.lei.utils;

import java.util.UUID;

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


}

package com.lei.dao;

import com.lei.pojo.Blog;

import java.util.List;
import java.util.Map;

public interface BlogMapper {
    int addBlog(Blog blog);
    List<Blog> findBlogIF(Map map);
}

 @Test
    public void addBlog() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Blog blog = new Blog();
        blog.setId(IdUtils.getId());
        blog.setTitle("Mybatis1");
        blog.setAuthor("王五");
        blog.setCreateTime(new Date());
        blog.setViews (9999);
        mapper.addBlog(blog);
        Blog blog1 = new Blog();
        blog1.setId(IdUtils.getId());
        blog1.setTitle( "mybatis2");
        mapper .addBlog(blog1);
        Blog blog2 = new Blog();
        blog2.setId(IdUtils.getId());
        blog2.setTitle( "mybatis3");
        mapper.addBlog(blog2);
        Blog blog3 = new Blog();
        blog3.setId (IdUtils.getId());
        blog3.setTitle("mybatis4");
        mapper.addBlog(blog3);

        sqlSession.close();

    }

if

<?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.lei.dao.BlogMapper">
    <insert id="addBlog" parameterType="com.lei.pojo.Blog">
        insert into blog values(#{id},#{title},#{author},#{createTime},#{views})
    </insert>

    <select id="findBlogIF" parameterType="Map" resultType="com.lei.pojo.Blog">
        select * from blog where 1=1
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="author != null">
        and author = #{author}
        </if>
    </select>

</mapper>

where

但上面1=1这样的SQL是不规范的，要想进行多个条件的筛选然后拼装SQL，都需要在前面加一个and，但现在我们有了where标签，可以帮我们自动去掉字句开头的and。

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

    </select>

他会自动把第一个and去掉。

where,choose,when

  <select id="findBlogChoose" parameterType="Map" resultType="com.lei.pojo.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>

 @Test
    public void findBlogIF() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map map = new HashMap();
        map.put("title","mybatis3");
        map.put("author","王五");
        map.put("views",9999);
        List<Blog> blogIF = mapper.findBlogIF(map);
        for (Blog blog:blogIF) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

如果都不满足就会走otherwise这个。

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>

package com.lei.dao;

import com.lei.pojo.Blog;

import java.util.List;
import java.util.Map;

public interface BlogMapper {
    int addBlog(Blog blog);
    List<Blog> findBlogIF(Map map);
    List<Blog> findBlogChoose(Map map);
    int updateBlog(Map map);
}

   @Test
    public void updateBlog() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map map = new HashMap();
        map.put("title","mybatis311");
        map.put("author","王五11");
        map.put("id", "a6f0037b230044fbabacc788387cf2e8");
        mapper.updateBlog(map);
        sqlSession.close();
    }

foreach

package com.lei.dao;

import com.lei.pojo.Blog;

import java.util.List;
import java.util.Map;

public interface BlogMapper {
    int addBlog(Blog blog);
    List<Blog> findBlogIF(Map map);
    List<Blog> findBlogChoose(Map map);
    int updateBlog(Map map);
    //查询1,2,3号博客
    List<Blog> findBlogForeach(Map map);
}

<!--    select * from blog where 1=1 and (id=1 or id = 2 or id=3)-->
<!--    我们可以传递一个map，这个map里面可以存在一个集合-->
    <select id="findBlogForeach" parameterType="Map" resultType="com.lei.pojo.Blog">
        select * from blog
        <where>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>

    @Test
    public void findBlogForeach() {
        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> blogForeach = mapper.findBlogForeach(map);
        for (Blog blog:blogForeach) {
            System.out.println(blog);
        }
        sqlSession.close();
    }