myBatis动态sql if when choose set

动态sql if when choose set

本质就是在xml进行sql语句的拼接

在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">
<!--namespace绑定接口，ID绑定方法-->
<mapper namespace="dao.BlogMapper">
    <insert id="addBlog" parameterType="pojo.Blog">
        insert into mybatis.blog(id, title, author, create_time, views) value (#{id},#{title}, #{author}, #{createTime}, #{views});
    </insert>

    <!--某列的if模糊查询，全部if都会判断-->
    <select id="QueryBlog" resultType="pojo.Blog" parameterType="map">
        select * from myBatis.blog
        <where>
            <if test="title != null">
                and title =#{title}
            </if>
            <if test="author != null">
                and author=#{author}
            </if>
        </where>
    </select>

    <!--某列的choose模糊查询，当顺序的第一个条件符合就执行一个，后面的失效-->
    <select id="QueryChooseBlog" parameterType="map" resultType="pojo.Blog">
        select * from myBatis.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>

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

    </update>

</mapper>

接口

package dao;

import pojo.Blog;

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

public interface BlogMapper {
    int addBlog(Blog blog);

//if查询，全部if判断
    List<Blog> QueryBlog(Map map);


    //when、choose查询，只选择choose条件的其中一个，其他无效
    List<Blog> QueryChooseBlog(Map map);


    //set 用于update语句 update table set
    int updateSetBlog(Map map);

测试

import dao.BlogMapper;
import org.apache.ibatis.session.SqlSession;
import pojo.Blog;
import pojo.IDtiles;
import utils.myBatisUtils;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Test {
    @org.junit.Test
    public void addBlog() {
        //添加用户
        SqlSession sqlSession = myBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        Blog blog = new Blog();
        blog.setId(IDtiles.getBlogID());
        blog.setAuthor("作者1");
        blog.setTitle("标题1");
        blog.setViews(99);
        blog.setCreateTime(new Date());
        mapper.addBlog(blog);


        blog.setId(IDtiles.getBlogID());
        blog.setTitle("标题2");
        mapper.addBlog(blog);


        blog.setId(IDtiles.getBlogID());
        blog.setTitle("标题3");
        mapper.addBlog(blog);

        blog.setId(IDtiles.getBlogID());
        blog.setTitle("标题4");
        mapper.addBlog(blog);

        sqlSession.commit();
        sqlSession.close();
    }

    @org.junit.Test
    public void QueryBlog() {
        //if某字段查询
        SqlSession sqlSession = myBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        Map<String, Object> map = new HashMap<>();
        map.put("title","标题1");
        map.put("author","作者1");

        List<Blog> blogs = mapper.QueryBlog(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }

    @org.junit.Test
    public void QueryChooseBlog() {
        //choose  when某字段查询
        SqlSession sqlSession = myBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        Map<String, Object> map = new HashMap<>();
       //只选择一个条件查询，顺序以xml文件为准
        map.put("title","标题1");
        map.put("author","作者1");

        List<Blog> blogs = mapper.QueryChooseBlog(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }


    @org.junit.Test
    public void updateSetBlog() {
        //choose  when某字段查询
        SqlSession sqlSession = myBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        Map<String, Object> map = new HashMap<>();
        //只选择一个条件查询，顺序以xml文件为准
        map.put("title","标题2");
        map.put("id","22aa25b5932947f788bada74516bfb3d");

        int blogs = mapper.updateSetBlog(map);
        System.out.println(blogs);
        sqlSession.close();
    }
}