动态SQL标签语法

MyBatis动态SQL

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

1 IF标签

BlogMapper文件

/**
 * 查询博客
 * 动态SQL之If标签
 * @param map 参数
 * @return 返回Blog的list
 */
List<Blog> queryBlogIf(Map map);

BlogMapper.xml文件

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

BlogMapperTest文件

@Test
public void queryBlogIfTest(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap hashMap = new HashMap();
    hashMap.put("author","张三");
    List<Blog> blogs = mapper.queryBlogIf(hashMap);
    sqlSession.close();
    blogs.forEach(System.out::println);
}

2 CHOOSE标签

when

otherwise

只能执行choose中when的其中一个，类似于switch-case

当一个满足条件的时候执行并退出choose

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

3 TRIM

可以定制前缀后缀及要替换掉的内容

<trim prefix="" prefixOverrides="" suffix="" suffixOverrides=""/>

where

<trim prefix="where" prefixOverrides="AND|OR"/>

<where>
    <if test="title != null">
        title = #{title}
	</if>
    <if test="author != null">
        and author = #{author}
	</if>
</where>

set

<trim prefix="set" suffixOverrides=","/>

<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>

4 SQL片段

方便复用

1 提取公共SQL语句

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

2 使用的时候引入include标签

<select id="queryBlogIf" parameterType="map" resultType="blog">
        select * from blog
        <where>
            <include refid="sql-demo01"></include>
        </where>
</select>
    
<update id="updateBlog" parameterType="map">
        update blog
        <set>
            <include refid="sql-demo02"></include>
        </set>
        where id = #{id}
</update>

5 Foreach标签

原生语句

select * from blog where 1=1 and (id=2 or id=3 or id=4)

遍历某个集合

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

测试

@Test
public void queryBlogForeachTest(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap hashMap = new HashMap();
    ArrayList<String> ids = new ArrayList<>();
    ids.add("2");
    ids.add("3");
    ids.add("4");
    hashMap.put("ids",ids);
    mapper.queryBlogForeach(hashMap);
    sqlSession.close();
}