Mybatis动态Sql 关于if，where，choose，when，otherwise，set，sql，foreach标签+List结合的用法

• 可以根据参数不同，生成的不同sql语句
• 标签：if，where，choose，when，otherwise，set，sql，foreach

建表

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

Pojo类

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

映射器接口

public interface BlogMapper {
    //动态Sql
    //choose if
    List<Blog> getBlog(Map<String,Object> map);

    //Foreach
    List<Blog> getBlogForeach(Map map);

    //set
    int updateBlog(Map map);
}

1. where+if 的用法

where标签 当符合条件的小于一个，会自动去掉and避免因为and拼接出错

<select id="getBlog" 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>

2. choose+when+otherwise的用法

choose+when选择一个或者多个，也会自动去掉and，当when内全都不符合就会拼接otherwise内的sql

    <select id="getBlog" 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>

测试

    //动态SQl choose
    @Test
    public void test4(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map<String,Object> map = new HashMap<String,Object>();
        //map.put("title","你好");
        map.put("author","小凯");
        map.put("views",9999);
        List<Blog> blog = mapper.getBlog(map);
        for (Blog blog1 : blog) {
            System.out.println(blog1);
        }
        sqlSession.close();
    }

3. set+sql

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

<!--可以将if内容放到 sql标签内 ，并通过id进行调用-->
    <sql id="set-title-author">
        <if test="title != null">
            title = #{title},
        </if>
        <if test="author != null">
            author = #{author}
        </if>
    </sql>

    //set
    @Test
    public void test5(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map<String,Object> map = new HashMap<String, Object>();
        map.put("title","哈哈");
        map.put("author","开凯");
        map.put("id",1);
        int i = mapper.updateBlog(map);
        System.out.println(i);

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

4. foreach

foreach实现分页查询
select * from blog where 1=1 and (id=1 or id=2 or id=3);
collection 存放需要id的集合 item每一个属性的名, open开头 close 结尾 separator中间拼接

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

    //foreach
    @Test
    public void test6(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        List<Integer> ids = new ArrayList<Integer>();
        ids.add(1);
        ids.add(2);
        ids.add(3);
        Map<String,Object> map = new HashMap<String, Object>();
        map.put("ids",ids);
        List<Blog> blogForeach = mapper.getBlogForeach(map);
        for (Blog foreach : blogForeach) {
            System.out.println(foreach);
        }
        sqlSession.close();
    }

5.foreach结合传入list参数

@Data
@NoArgsConstructor
@AllArgsConstructor
public class DemoData {
    private String string;
    private Date date;
    private Double doubleData;
}

@Mapper
@Repository
public interface  DemoDataMapper {
    int insertDemoData(List<DemoData> demoData);
}

<?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="demo.test.Mapper.DemoDataMapper">
    <insert id="insertDemoData" parameterType="java.util.List">
        insert into db1.demodata (string, date, doubleData)
        VALUES
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.string},#{item.date},#{item.doubleData})
        </foreach>
    </insert>
    
</mapper>

    @Autowired
    private DemoDataMapper mapper;

    @Test
    public void testDemoMapper(){
        List<DemoData> list = new ArrayList<DemoData>();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串"+i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        System.out.println(list.toString());
        mapper.insertDemoData(list);
    }