Mybatis-06-动态SQL使用

1、动态 SQL

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

  • 利用动态 SQL,可以彻底摆脱这种痛苦。

如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器,你对动态 SQL 元素可能会感觉似曾相识。
在 MyBatis 之前的版本中,需要花时间了解大量的元素。
借助功能强大的基于 OGNL 的表达式,MyBatis 3 替换了之前的大部分元素,大大精简了元素种类,现在要学习的元素种类比原来的一半还要少。

  • if
  • choose (when, otherwise)
  • trim (where, set)
  • foreach

2、搭建环境

2.1、建库,建表

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

2.2、创建基础工程

  1. 导入依赖 【mysql驱动,Mybatis,junit,lombok】

  2. 编写配置文件 mybatis-config.xml

  3. 编写实体类Blog

    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class Blog {
        private String id;
        private String title;
        private String author;
        private Date createTime; // 属性名和字段名不一致 
        private String views;
    }
    
  4. 编写实体类对应的BlogMapper接口和 BlogMapper.xml 文件

  5. Mybatis 配置文件中绑定 BlogMapper

解决属性名和字段名不一致:

  • Mybatis 配置文件中添加设置

  • mapUnderscoreToCamelCase 是否开启驼峰命名自动映射 true | false

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

2.3、工具类:MybatisUtils

public class MybatisUtils {
    static SqlSessionFactory sqlSessionFactory;

    static {
        String resource = "mybatis-config.xml";
        try {
            InputStream is = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession(true);
    }
}

2.4、完善添加数据

BlogMapper

public interface BlogMapper {
    // 插入数据
    int addBlog(Blog blog);
}

BlogMapper.xml

<insert id="addBlog" parameterType="Blog">
    insert into mybatis.blog(id, title, author, create_time, views)
    values (#{id}, #{title}, #{author}, #{createTime}, #{views})
</insert>

测试

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

    Blog blog = new Blog();

    blog.setId("1");
    blog.setTitle("MySQL 如此简单");
    blog.setAuthor("遇见星光");
    blog.setCreateTime(new Date());
    blog.setViews("10000");
    mapper.addBlog(blog);

    blog.setId("2");
    blog.setTitle("Java 如此简单");
    mapper.addBlog(blog);

    blog.setId("3");
    blog.setTitle("Soring 如此简单");
    mapper.addBlog(blog);

    blog.setId("4");
    blog.setTitle("微服务 如此简单");
    mapper.addBlog(blog);

    blog.setId("5");
    blog.setTitle("Spring MVC 如此简单");
    mapper.addBlog(blog);

    sqlSession.close();
}

结果

Mybatis-06-动态SQL使用

3、测试

1、if

BlogMapper

public interface BlogMapper {

    // 查询博客
    List<Blog> queryBlogIF(Map map);
}

BlogMapper.xml

<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);
    Map<String, Object> map = new HashMap<String, Object>();
    map.put("author","遇见星光"); 
    map.put("title","Java 如此简单");

    List<Blog> blogs = mapper.queryBlogIF(map);
    for (Blog blog : blogs) {
        System.out.println(blog.toString());
    }

    sqlSession.close();
}

结果

Mybatis-06-动态SQL使用
Mybatis-06-动态SQL使用

【注意】控制台的输出的其他信息是开启了日志功能

设置方法:

  • Mybatis 配置文件中添加设置

  • logImpl 指定 MyBatis 所用日志的具体实现,未指定时将自动查找。 LOG4J | STDOUT_LOGGING

  • <settings>
    	<setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    

2、choose (when, otherwise)

where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。

有时候,我们不想使用所有的条件,而只是想从多个条件中选择一个使用。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。

BlogMapper

public interface BlogMapper {

    List<Blog> queryChoose(Map map);
}

BlogMapper.xml

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

测试

@Test
public void queryChoose() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();

    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    Map<String, Object> map = new HashMap<String, Object>();
    map.put("author","遇见星光");
    map.put("title","Java 如此简单");

    List<Blog> blogs = mapper.queryChoose(map);
    for (Blog blog : blogs) {
        System.out.println(blog.toString());
    }

    sqlSession.close();
}

结果

Mybatis-06-动态SQL使用

所谓的动态SQL,本质还是SQL语句,只是我们可以在SQL层面,去执行一个逻辑代码

3、trim (where, set)

set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号(这些逗号是在使用条件语句给列赋值时引入的)。

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

注意,我们覆盖了后缀值设置,并且自定义了前缀值。

BlogMapper

public interface BlogMapper {

    // 更新博客
    int updateBlog(Map map);
}

BlogMapper.xml

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

测试

@Test
public void updateBlog() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();

    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    Map<String, Object> map = new HashMap<String, Object>();
    map.put("id", "2");
    map.put("author", "青柠");
    map.put("title", "学习 Java 如此简单");

    int updateRows = mapper.updateBlog(map);
    System.out.println(updateRows);

    sqlSession.close();
}

结果

Mybatis-06-动态SQL使用

4、foreach

它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符,看它多智能!

<where>
    <!--
		collection:遍历的集合 ids,
		item 遍历出来的元素 id,
		open:指定开头,
		separator: 集合项迭代之间的分隔符,
		close:指定结尾
		and (id=2 or id=5)
		-->
    <foreach collection="ids" item="id" open="and (" separator="or" close=")">
        id = #{id}
    </foreach>
</where>

BlogMapper

public interface BlogMapper {

    // 查询 指定 ID 记录的博客
    List<Blog> queryByForeach(Map map);
}

BlogMapper.xml

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

测试

@Test
public void queryByForeach() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();

    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    List<String> list = new ArrayList(); // 先定义一个集合 放ID
    list.add("2");
    list.add("5");

    Map<String, Object> map = new HashMap<String, Object>();
    map.put("ids", list);// 将集合添加到 Map 中

    List<Blog> blogs = mapper.queryByForeach(map);
    for (Blog blog : blogs) {
        System.out.println(blog.toString());
    }

    sqlSession.close();
}

结果

Mybatis-06-动态SQL使用

5、SQL片段

有时候,我们可能将一些功能部分抽取出来,方便复用

  1. 使用SQL标签抽取公共部分
  2. 在需要使用的地方使用include标签应用即可
<sql id="if-title-author">
    <if test="title!=null">
        and title =#{title}
    </if>
    <if test="author!=null">
        and author =#{author}
    </if>
</sql>

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

</select>

注意事项:

  • 最好基于单表来定义SQL片段!
  • 片段中不要存在where标签

6、总结

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

建议:

  • 现在Mysql中写出完整的SQL ,
  • 再对应的去修改成为动态SQL,
  • 实现通用即可

Mybatis-06-动态SQL使用

上一篇:Flink-cdc实时读postgresql


下一篇:SQL问题:com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value