Mybatis动态SQL

最新推荐文章于 2024-10-31 12:15:00 发布

Remote_Li

最新推荐文章于 2024-10-31 12:15:00 发布

阅读量84

点赞数

分类专栏： SSM 文章标签： sql java mybatis ssm mysql

本文链接：https://blog.csdn.net/TvTToT/article/details/120183738

版权

SSM 专栏收录该内容

31 篇文章 0 订阅

订阅专栏

本文介绍了如何使用动态SQL技术，通过Map参数动态构建SQL查询，实现根据不同条件（如标题、作者、浏览量）在Mybatis中执行查询。涵盖了if、choose、trim以及foreach等多种方式的应用实例。

摘要由CSDN通过智能技术生成

根据不同的条件生成不同的SQL语句

所谓的动态SQL，本质还是SQL语句，只是在SQL层面执行了一个逻辑代码

SQL文件

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

1. if

编写接口
```
List<Blog> queryBlogIF(Map map);
```

mapper文件

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

测试

@Test
public void queryBlogIF(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    
    HashMap map = new HashMap();
    map.put("title","Java入门");
    
    List<Blog> blogs = mapper.queryBlogIF(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }
    sqlSession.close();
}

2. choose (when, otherwise)

只走满足条件的第一个

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

3. trim(where,set)

where

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

set

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

4. foreach

编写接口

//查询第1-2号记录的博客
List<Blog> queryBlogForeach(Map map);

mapper文件

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

测试

@Test
public void queryBlogForeach(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

    HashMap map = new HashMap();

    ArrayList<String> ids = new ArrayList<String>();
    ids.add("554f9da97e074ab3a3edf5055d9ff01d");
    ids.add("9c66f86960e948d5b07753d970afdad4");

    map.put("ids",ids);

    List<Blog> blogs = mapper.queryBlogForeach(map);

    for (Blog blog : blogs) {
        System.out.println(blog);
    }
    sqlSession.close();
}

5. SQL片段

将一些功能的部分抽取，方便复用

使用SQL标签抽取公共部分

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

在需要的地方使用include标签引用

<select id="queryBlogIF" parameterType="map" resultType="blog">
    select * from mybatis.blog
    <where>
        <include refid="if-title-author"></include>
    </where>
</select>