MyBatis学习（十五）——动态SQL（if，where，choose，set，trim，foreach）

淡淡的烟草味﹌ 2022-12-29 09:38 172阅读 0赞

### 动态SQL ###

*  搭建环境
 *  插入数据
 *  动态SQL之if
 *  使用where标签
 *  动态SQL之choose (when, otherwise)
 *  set标签
 *  trim标签
 *  SQL片段
 *  foreach

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

**动态SQL就是在拼接SQL语句，我们只要保证SQL的正确性，按照SQL的格式，去排列组合就可以了**

> 动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。
> 
> if  
> choose (when, otherwise)  
> trim (where, set)  
> 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

![在这里插入图片描述][20201222124908595.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70]  
在核心配置文件中设置  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 1]

导包和之前的项目一样

**配置文件**

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE configuration
            PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-config.dtd">
    
    <configuration>
    
        
        <properties resource="pp.properties"/>
    
        <settings>
            
    
            <setting name="logImpl" value="STDOUT_LOGGING"/>
           
    
        </settings>
    
    
        
        <typeAliases>
    
            <package name="com.my.pojo"/>
        </typeAliases>
    
    
    
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC"/>
                <dataSource type="POOLED">
                    <property name="driver" value="${driver}"/>
                    <property name="url" value="${url}"/>
                    <property name="username" value="${username}"/>
                    <property name="password" value="${password}"/>
                </dataSource>
            </environment>
        </environments>
    
        <mappers>
            <mapper class="com.my.dao.BlogMapper"/>
    
        </mappers>
    
    
    </configuration>

driver=com.mysql.jdbc.Driver
    url=jdbc:mysql://localhost:3306/mybatis?useSSL=true&userUnicode=true&characterEncoding=UTF-8
    username=root
    password=123456

![在这里插入图片描述][20201221224846412.png]

**实体类**

import lombok.Data;
    
    import java.util.Date;
    @Data
    public class Blog {
        
        private String id;
        private String title;
        private String author;
        private Date createTime;
        private int views;
    
    }

**连接类**  
![在这里插入图片描述][20201221224958442.png]

package com.my.utils;
    
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    
    import java.io.IOException;
    import java.io.InputStream;
    
    //sqlSessionFactory  构建sqlSession
    public class MybatisUtils {
        
        private static SqlSessionFactory sqlSessionFactory;
        static {
        
            try{
        
                //使用mybatis第一步 获取sqlSessionFactory对象
                String resource = "mybatis-config.xml";
                InputStream inputStream = Resources.getResourceAsStream(resource);
                sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            } catch (IOException e) {
        
                e.printStackTrace();
            }
        }
    
        public static SqlSession getSqlSession(){
        
            return sqlSessionFactory.openSession(true);
        }
    }

**接口和xml**

package com.my.dao;
    
    public interface BlogMapper {
        
    }

<?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.my.dao.BlogMapper" >
    
    
    
    
    </mapper>

# 插入数据 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 2]

**测试代码**

import com.my.dao.BlogMapper;
    import com.my.pojo.Blog;
    import com.my.utils.IDUtils;
    import com.my.utils.MybatisUtils;
    import org.apache.ibatis.session.SqlSession;
    import org.junit.Test;
    
    import java.util.Date;
    
    public class MyTest {
        
        @Test
        public void addBlogTest(){
        
            SqlSession sqlSession = MybatisUtils.getSqlSession();
            BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
            Blog blog = new Blog();
            blog.setId(IDUtils.getId());
            blog.setTitle("Mybatis");
            blog.setAuthor("狂神");
            blog.setCreateTime(new Date());
            blog.setViews(9999);
            mapper.addBook(blog);
    
            blog.setId(IDUtils.getId());
            blog.setTitle("Java");
            mapper.addBook(blog);
    
            blog.setId(IDUtils.getId());
            blog.setTitle("Spring");
            mapper.addBook(blog);
    
            blog.setId(IDUtils.getId());
            blog.setTitle("微服务");
            mapper.addBook(blog);
    
           // 因为在前面的连接中设置自动提交事务所以这里不用commit
            sqlSession.close();
    
        }
    }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 3]

# 动态SQL之if #

![在这里插入图片描述][202012221321504.png]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 4]

# 使用where标签 #

**如果两个条件都成立，where标签会自动将第一个语句的起始and去掉，如果都不成立，会自动将where去掉**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 5]

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 6]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 7]

**条件成立进入if标签**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 8]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 9]

# 动态SQL之choose (when, otherwise) #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 10]

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 11]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 12]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 13]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 14]

# set标签 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 15]  
**只要满足if的条件就可以拼接在sql中，最后一个修改的内容后不加逗号**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 16]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 17]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 18]

# trim标签 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 19]  
![在这里插入图片描述][20201222144737759.png]

# SQL片段 #

**可以使用sql标签解决代码复用问题**

*  使用sql标签抽取公共的部分
 *  在需要使用的地方使用include标签引用即可

**最好基于单表来定义SQL  
不要存在where标签，因为where涉及一些条件的判断**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 20]

# foreach #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 21]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 22]

<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();
            List<Integer> ids = new ArrayList<Integer>();
            map.put("ids",ids);
            ids.add(1);
            ids.add(2);
            ids.add(4);
    
            List<Blog> blogList = mapper.queryBlogForeach(map);
            for (Blog blog : blogList) {
        
                System.out.println(blog);
            }
            sqlSession.close();
        }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 23]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 24]

[20201222124908595.png]: /images/20221120/fcc581fa4d284857a796cf7db1a4437d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70]: /images/20221120/e9bdd024807b4da89b29d8a19dd9006a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 1]: /images/20221120/617cc34f63f1474989d3cbf1b5f3c9c0.png
[20201221224846412.png]: /images/20221120/627a328e3afc4ffe8a276dc1a2b17757.png
[20201221224958442.png]: /images/20221120/d4399731729548e3817e638340bceb46.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 2]: /images/20221120/ad206794d0f04853bc80650e11c40f3d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 3]: /images/20221120/1518906499ed40beb8bbc56c0b448bfd.png
[202012221321504.png]: /images/20221120/f22430024b3b4b4b93b3a53d8dd3f0ae.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 4]: /images/20221120/573f5a3cacf94ab3ade70cf5566dfbdd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 5]: /images/20221120/2445a247eed8458e8552401cb6262bdf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 6]: /images/20221120/4a858ddd0d344c07bd3531d41271bcb5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 7]: /images/20221120/606eabfe609640038419045b42e77719.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 8]: /images/20221120/9f462ff85d4b42839d58a19d1a51338f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 9]: /images/20221120/4c5e4b5673fb454a8d0d974bb887bfb5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 10]: /images/20221120/0d6b3e08d4b540ea8544b66803ce489d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 11]: /images/20221120/8add89c7d379426bb1c518f537df12c8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 12]: /images/20221120/34f6a895f51244c191f991f67d66bc45.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 13]: /images/20221120/3e7572b6225e4d218d38331a4c8a06a0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 14]: /images/20221120/e9c22d2daeff40b2add302b79f305321.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 15]: /images/20221120/f1c724ac32ae4ece9b6e2f18339e9a7a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 16]: /images/20221120/8b7bfafb4a1f41fe9ab0d657bf455bd7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 17]: /images/20221120/cb991fd306df48e3a484274c806a7a5d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 18]: /images/20221120/772bdacf83eb4b8ea20c737fb8f7dfaf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 19]: /images/20221120/cafed31b0199431da92c679ea998bb59.png
[20201222144737759.png]: /images/20221120/9e081852f57d4c58b098fb50d146b198.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 20]: /images/20221120/4cd7fd363971440cb65df10bb29976a4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 21]: /images/20221120/78ac89b779fe44e1a038190339908238.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 22]: /images/20221120/61f9c96f90724f9fae25c5747bff4f9a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 23]: /images/20221120/248e7dea298d4948ab9156015f7eaf2d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoYXJfbQ_size_16_color_FFFFFF_t_70 24]: /images/20221120/4736441a30974a36a8d130c32a7be10a.png