动态SQL

• 动态SQL概述和环境搭建
• • 编写一个基础工程
  • 编写辅助工具
  • 测试数据插入
  • 根据传入参数查询博客
• 解决if标签问题
• • 解决方案1
  • 解决方案2
• 测试相关操作
• • 更新博客数据
  • 导入sql片段
  • 测试foreach标签

动态SQL概述和环境搭建

什么是动态SQL：就是根据不同的条件生成不同的SQL语句，例如拼接SQL

编写一个基础工程

• 导包导依赖
• 编写配置文件
• 编写实体类
• 编写实体类对应的Mapper接口和xml文件

编写辅助工具

开启驼峰命名转换

<?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>
    <!-- Write labels in order -->
    <properties resource="db.properties" />

    <settings>
        <setting name="logImpl" value="LOG4J" />
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <typeAliases>
        <package name="com.bkms.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="${user}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/bkms/dao/BlogMapper.xml"/>
    </mappers>
</configuration>

编写id随机生成工具类

package com.bkms.utils;

import java.util.UUID;

/**
 * 获取随机id值
 * @author bkms
 */
public class IdUtils {
    public static String getRandomId() {
        return UUID.randomUUID().toString().replaceAll("-","");
    }
}

测试数据插入

<?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.bkms.dao.BlogMapper">
    <!-- 插入数据 -->
    <insert id="incrementBlog" parameterType="blog">
        insert into mydb.blog (id,title,author,create_time,views)
        values (#{id},#{title},#{author},#{createTime},#{views});
    </insert>
</mapper>

    @Test
    // 测试插入数据
    public void incrementBlogTest() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Blog blog = new Blog();

        blog.setId(IdUtils.getRandomId());
        blog.setTitle("Mybatis");
        blog.setAuthor("Philip");
        blog.setCreateTime(new Date());
        blog.setViews(9999);

        mapper.incrementBlog(blog);

        blog.setId(IdUtils.getRandomId());
        blog.setTitle("Java");
        mapper.incrementBlog(blog);

        blog.setId(IdUtils.getRandomId());
        blog.setTitle("Spring");
        mapper.incrementBlog(blog);

        blog.setId(IdUtils.getRandomId());
        blog.setTitle("微服务");
        mapper.incrementBlog(blog);

        sqlSession.commit();
        sqlSession.close();
    }

根据传入参数查询博客

    @Test
    // 测试博客查询
    public void searchBlogIfTest() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap<String,Object> map = new HashMap<String,Object>();
        map.put("title","Spring");
        List<Blog> blogs = mapper.searchBlogIf(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

    <!-- 查询博客 -->
    <select id="searchBlogIf" parameterType="map" resultType="blog">
        select * from mydb.blog
        where 1=1
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </select>

解决if标签问题

解决方案1

	<!-- 查询博客(改进1) -->
    <select id="searchBlogIf2" parameterType="map" resultType="blog">
        select * from mydb.blog
        <where>
            <if test="title != null">
                title = #{title}
            </if>
            <if test="author != null">
                and name = #{name}
            </if>
        </where>
    </select>

解决方案2

<!-- 查询博客(改进2) -->
    <!-- 
    where-choose标签只能选择1个满足条件的sql语句 
    都满足的话还是走第1个
    -->
    <select id="searchBlogIf2" parameterType="map" resultType="blog">
        select * from mydb.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>

测试相关操作

更新博客数据

	<!-- 更新博客数据 -->
    <!--
    必须要满足至少1个if的test语句
    否则会因为语句不完整报错
    set标签会自动前置set关键字
    同时会删除无关的逗号
    -->
    <update id="updateBlogData" parameterType="map">
        update mydb.blog
        <set>
            <if test="title != null">
                title = #{title},
            </if>
            <if test="author != null">
                name = #{name}
            </if>
        </set>
    </update>

导入sql片段

	<!-- sql片段 -->
    <sql id="if-title-author">
        <if test="title != null">
            title = #{title}
        </if>
        <if test="author != null">
            and name = #{name}
        </if>
    </sql>
    
    <!-- 查询博客(改进1) -->
    <select id="searchBlogIf2" parameterType="map" resultType="blog">
        select * from mydb.blog
        <where>
            <include refid="if-title-author"></include>
        </where>
    </select>

sql片段使用注意事项

• 最好基于单表定义sql字段，不要封装太过复杂的操作
• 不要存在where标签

测试foreach标签

	<!-- 通过foreach查询博客 -->
    <!--
     效果等同于
     select * from mydb.blog where 1=1 and (id = 1 or id = 2 or id = 3)
     假设这个List集合叫做ids，遍历的每一项叫做id
     如果ids集合是空的，where标签会自动省略
     -->
    <select id="searchblogByForeach" parameterType="map" resultType="blog">
        <where>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id = #{id}
            </foreach>
        </where>
    </select>