Mybatis(动态sql: if set where foreach )

mybatis动态sql

注意点：

1. set 最后没有","
2. 传入map 或者传入对象类型 都会自动映射
3. foreach本质还是拼接sql语句
4. 复杂的时候 if set这些（trime）也要结合特殊标记来使用
5. 本案例中，出现create_time和createTime名称不一致的情况，教学视频使用

<setting name="mapUnderscoreToCamelCase" value="true"/>

自动识别驼峰命名，这里使用的是查询使用as，set使用 create_time=#{createTime},这些很灵活，只要知道每个东西就行

代码范例：

BlogMapper.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.mybatis.dao.BlogMapper">
    <select id="getBlogList" resultType="Blog">
        select id,tittle,author,create_time as createTime,views from blog
    </select>
    <select id="getBlogListIF" parameterType="map" resultType="Blog">
        select id,tittle,author,create_time as createTime,views from blog
        <where>
            <if test="id!=null">
                and id=#{id}
            </if>
            <if test="tittle!=null">
                and tittle=#{tittle}
            </if>
            <if test="author!=null">
                and author=#{author}
            </if>
            <if test="views!=null">
                and views=#{views}
            </if>
        </where>
    </select>



    <update id="updateBlogSET" parameterType="map">
        update blog
        <set>
            <if test="tittle != null">
                tittle = #{tittle},
            </if>
            <if test="tittle != null">
                tittle = #{tittle},
            </if>
            <if test="views != null">
                views = #{views},
            </if>
            <if test="createTime != null">
                create_time = #{createTime},
            </if>
            <if test="author != null">
                author = #{author}
            </if>
        </set>
        where id = #{id};
    </update>


    <update id="updateBlogSETByBlog" parameterType="Blog">
        update blog
        <set>
            <if test="tittle != null">
                tittle = #{tittle},
            </if>
            <if test="tittle != null">
                tittle = #{tittle},
            </if>
            <if test="views != null">
                views = #{views},
            </if>
            <if test="createTime != null">
                create_time = #{createTime},
            </if>
            <if test="author != null">
                author = #{author}
            </if>
        </set>
        where id = #{id};
    </update>


    <select id="selectBlogForeach" parameterType="map" resultType="blog">
        select * from blog
        <where>
            <!--
            collection:指定输入对象中的集合属性
            item:每次遍历生成的对象
            open:开始遍历时的拼接字符串
            close:结束时拼接的字符串
            separator:遍历对象之间需要拼接的字符串
            select * from blog where 1=1 and (id=1 or id=2 or id=3)
          -->
            <foreach collection="ids"  item="id" open="and (" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>
</mapper>

BlogMapper.java

package com.mybatis.dao;

import com.mybatis.pojo.Blog;

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

public interface BlogMapper {
    public List<Blog> getBlogList();
    public List<Blog> getBlogListIF(Map map);
    public void updateBlogSET(Map map);
    public void updateBlogSETByBlog(Blog blog);

    public List<Blog> selectBlogForeach(Map map);
}

测试 MyTest.java

import com.mybatis.dao.BlogMapper;
import com.mybatis.pojo.Blog;
import org.apache.ibatis.session.SqlSession;
import utils.MybatisUtil;

import java.util.*;

public class MyTest {
    public static void main(String[] args) {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        List<Blog> blogList = mapper.getBlogList();



//        Map map = new HashMap();
//        map.put("id","1");
//        List<Blog> blogList = mapper.getBlogListIF(map);

        //传入对象
//        Blog blog = new Blog();
//        blog.setId(1);
//        blog.setAuthor("sunzhong");
//        blog.setCreateTime(new Date());
//        blog.setTittle("west blog");
//        blog.setViews(123456);
//        mapper.updateBlogSETByBlog(blog);

        //传入map
//        Map map = new HashMap();
//        map.put("id",1);
//        map.put("tittle","west blog");
//        map.put("author","sunzhong");
//        map.put("createTime",new Date());
//        map.put("views",123456);
//        mapper.updateBlogSET(map);


        //for each
        HashMap map = new HashMap();
        List<Integer> ids = new ArrayList<Integer>();
        ids.add(1);
        ids.add(2);
        ids.add(3);
        map.put("ids",ids);
        List<Blog> blogsList = mapper.selectBlogForeach(map);
        for (Blog blog : blogList) {
            System.out.println(blog);
        }
        sqlSession.commit();
        sqlSession.close();




    }
}

今天有些匆忙，出去玩了。 明天更新Mybatis缓存。