MyBatis学习笔记——08动态SQL

最新推荐文章于 2024-08-21 23:39:15 发布

Jiang锋时刻

最新推荐文章于 2024-08-21 23:39:15 发布

阅读量111

点赞数

分类专栏： Mybatis 文章标签： mybatis

本文链接：https://blog.csdn.net/bingque6535/article/details/108187997

版权

Mybatis 专栏收录该内容

15 篇文章 1 订阅

订阅专栏

                    
                        
                    
                    1. IF标签和where标签 
1. 说明 
当满足if条件时, 会将if中的sql语句加入到SQL中
where只有当其中的条件至少满足一个时才会生效
where可以自动删掉条件中多余的and, 而不能添加缺失的and
 
2. 案例 
 <select id="queryArticleIF" resultType="article" parameterType="map">
   select * from article
    <where>
        <if test="title != null">
            title = #{title}
        </if>
        <if test="author != null">
           and author = #{author}
        </if>
    </where>
</select>
 
 
2. Choose 
1. 说明 
choose中至少有一个条件满足时才会生效
如果有多个when, 当满足第一个when时, 就会跳过后续的判断
如果when中的条件都不满足时, 则会走otherwise
 
2. 案例 
 <select id="queryArticleChoose" resultType="article" parameterType="map">
    select * from article
    <where>
        <choose>
            <when test="title != null">
                title = #{title}
            </when>
            <when test="author">
                author = #{author}
            </when>
            <otherwise>
                views = #{views}
            </otherwise>
        </choose>
    </where>
</select>
 
 
3. set 
1. 说明 
set只有在至少满足一个条件的时候才会生效
set会自动删掉内部代码中末尾多余的",", 而不会填上缺失的","
 
2. 案例 
 <update id="updateArticle" parameterType="map">
    update article
    <set>
        <if test="author != null">
            author = #{author},
        </if>
        <if test="title != null">
            title = #{title},
        </if>
    </set>
    where id = #{id}
</update>
 
 
4.sql片段 
1. 说明 
sql标签的作用是提出公共的sql语句, 实现多是利用
sql片段的标签: sql
< include refid=“片段id”>< /include>
 
2. 案例 
 <sql id="sql">
   <if test="title != null">
        title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</sql>

<select id="queryArticleIF2" resultType="article" parameterType="map">
    select * from article
    <where>
        <include refid="sql"></include>
    </where>
</select>
 
 
5. foreach 
1. 说明 
collection: 集合名
item: 变量名
open: 集合前标志
close: 集合后标志
separator: 分隔符
 
2. 案例 
代码1<!-- select * from article WHERE id in ( ? , ? ) -->
<select id="queryArticleForeach" parameterType="map" resultType="article">
    select * from article
    <where>
        id in
        <foreach collection="ids" item="id" open ="(" separator="," close=")">
            #{id}
        </foreach>
    </where>
</select>
 
代码2<!--select * from article WHERE id = ? or id = ? -->
<select id="queryArticleForeach2" parameterType="map" resultType="article">
    select * from article
    <where>
        <foreach collection="ids" item="id" open ="" separator="or" close="">
            id = #{id}
        </foreach>
    </where>
</select>
 
测试@Test
public void testQueryArticleForeach(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    ArticleMapper mapper = sqlSession.getMapper(ArticleMapper.class);
    Map<String, Object> map = new HashMap<String, Object>();
    ArrayList<Integer> ids = new ArrayList<Integer>();
    ids.add(1);
    ids.add(3);
    map.put("ids", ids);
    mapper.queryArticleForeach(map);
}
 

                