一.动态SQL简介

• MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。
• 通常使用动态 SQL 不可能是独立的一部分,MyBatis 当然使用一种强大的动态 SQL 语言来改进这种情形,这种语言可以被用在任意的 SQL 映射语句中。
• 动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。在 MyBatis 之前的版本中,有很多的元素需要来了解。MyBatis 3 大大提升了它们,现在用不到原先一半的元素就可以了。MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素。
• mybatis 的动态sql语句是基于OGNL表达式的。可以方便的在 sql 语句中实现某些逻辑. 总体说来mybatis 动态SQL
• 语句主要有以下几类:
  • if 语句 (简单的条件判断)
  • choose (when,otherwize) ,相当于java 语言中的 switch ,与 jstl 中的choose 很类似.
  • trim (对包含的内容加上 prefix,或者 suffix 等，前缀，后缀)
  • where (主要是用来简化sql语句中where条件判断的，能智能的处理 and or ,不必担心多余导致语法错误)
  • set (主要用于更新时)
  • foreach (在实现 mybatis in 语句查询时特别有用)

二.进行判断

a. if元素

• 使用动态 SQL 最常见情景是根据条件包含 where 子句的一部分

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null">
    AND author_name like #{author.name}
  </if>
</select>

• 这条语句提供了可选的查找文本功能。如果不传入 “title”，那么所有处于 “ACTIVE” 状态的 BLOG 都会返回；如果传入了 “title” 参数，那么就会对 “title” 一列进行模糊查找并返回对应的 BLOG 结果（细心的读者可能会发现，“title” 的参数值需要包含查找掩码或通配符字符）。

b. choose、when、otherwise

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

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

三.拼接关键字

a. where

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

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG
  <where>
    <if test="state != null">
         state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

b. set

• 用于动态更新语句的类似解决方案叫做 set。set 元素可以用于动态包含需要更新的列，忽略其它不更新的列

<update id="updateAuthorIfNecessary">
  update Author
    <set>
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </set>
  where id=#{id}
</update>

• 这个例子中，set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）

c. trim

• 如果 where 元素与你期望的不太一样，你也可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为:

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

四.进行循环

a. foreach

• foreach的主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合。 foreach元素的属性主要有 item，index，collection，open，separator，close。
  • item表示集合中每一个元素进行迭代时的别名，
  • index指 定一个名字，用于表示在迭代过程中，每次迭代到的位置，
  • open表示该语句以什么开始，
  • separator表示在每次进行迭代之间以什么符号作为分隔 符，
  • close表示以什么结束
• 在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况下，该属性的值是不一样的，主要有一下3种情况：
  1. 如果传入的是单参数且参数类型是一个List的时候，collection属性值为list
  2. 如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array
  3. 如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map，实际上如果你在传入参数的时候，在breast里面也是会把它封装成一个Map的，map的key就是参数名，所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key

b. 单参数List的类型

<select id="dynamicForeachTest" resultMap="BaseResultMap">
    select * from userinfo where uid in
    <foreach collection="list" index="index" item="item" open="(" separator=","close=")">
        #{item}
    </foreach>
</select>

• 测试代码

public void findByIds(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        UserinfoMapper mapper = sqlSession.getMapper(UserinfoMapper.class);
        List list = new ArrayList();
        list.add(2);
        list.add(3);
        list.add(9);
        List<Userinfo> userList = mapper.dynamicForeachTest(list);
        for (Userinfo userinfo : userList) {
            System.out.println(userinfo.getUserName()+"\t"+userinfo.getUserPass());
        }
        sqlSession.close();
    }

c. 单参数array数组的类型

<select id="dynamicForeach2Test" resultMap="BaseResultMap">
    select * from userinfo where uid in
    <foreach collection="array" index="index" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>
</select>

• 测试代码

public void findByIds2(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        UserinfoMapper mapper = sqlSession.getMapper(UserinfoMapper.class);
        Long[] ids = new Long[]{2l,3l,9l};
        List<Userinfo> userList = mapper.dynamicForeach2Test(ids);
        for (Userinfo userinfo : userList) {
            System.out.println(userinfo.getUserName()+"\t"+userinfo.getUserPass());
        }
        sqlSession.close();
    }

d. 把参数封装成Map的类型

<select id="dynamicForeach3Test" resultMap="BaseResultMap">
    select * from userinfo where user_name like "%"#{userName}"%" and uid in
    <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>
</select>

• 测试类

public void findByIds3(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    UserinfoMapper mapper = sqlSession.getMapper(UserinfoMapper.class);
    List list = new ArrayList();
    list.add(2);
    list.add(3);
    list.add(9);
    Map params = new HashMap();
    params.put("ids",list);
    params.put("userName","db");
    List<Userinfo> userList = mapper.dynamicForeach3Test(params);
    for (Userinfo userinfo : userList) {
        System.out.println(userinfo.getUserName()+"\t"+userinfo.getUserPass());
    }
    sqlSession.close();
}

五.例题

• 通过配置文件+接口的方式实现MyBatis动态SQL功能

• 添加MyBatis的配置文件mybatis.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>
    <!--引入外部配置文件db.properties-->
    <properties resource="db.properties" />

    <!--引入第三方日志包log4j-->
    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>

    <!--起别名使用package标签可以匹配com.yanm.pojo包下的所有类,并且不区分大小写
        也可使用typeAlias标签匹配指定类型-->
    <typeAliases>
        <package name="com.yanm.pojo" />
    </typeAliases>

    <!--配置mybatis环境变量-->
    <environments default="development">
        <environment id="development">
            <!--配置JDBC事务控制,由mybatis进行管理-->
            <transactionManager type="JDBC"/>
            <!--配置数据源,采用mybatis连接池-->
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${user}"/>
                <property name="password" value="${pass}"/>
            </dataSource>
        </environment>
    </environments>
    <!--加载映射文件-->
    <mappers>
        <!--使用资源的路径,匹配dao/IUserDao.xml包下的所有接口-->
        <mapper resource="dao/IUserDao.xml" />
    </mappers>
</configuration>

• 数据路连接的参数db.properties

driver=org.mariadb.jdbc.Driver
url=jdbc:mariadb://localhost:3306/mall
user=root
pass=root

• 第三方日志包log4j的参数设置

# 全局日志配置
# 共有四个级别 ERROE,DEBUG,WARN,INFO
log4j.rootLogger=ERROR, stdout, F
# MyBatis 日志配置,可以指定到包下,也可以指定到类上,也可以指定到类中的某一个方法
log4j.logger.com.yanm.dao.IUserDao=TRACE
# 控制台输出
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%6p [%t] - %m%n

#打印到文件myproj.log中--专门为DAO层服务
log4j.appender.F = org.apache.log4j.DailyRollingFileAppender
log4j.appender.F.File =myproj.log
log4j.appender.F.Append = true
log4j.appender.F.Threshold = ERROE
log4j.appender.F.layout=org.apache.log4j.PatternLayout
log4j.appender.F.layout.ConversionPattern=%-d{yyyy-MM-dd HH\:mm\:ss}-[%p %F\:%L]  %m%n

• 配置映射文件IUserDao.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.yanm.dao.IUserDao">

    <!--可以把重复的语句提取出来,之后可以使用include refid="#"进行调用-->
    <sql id="getAll">
        select * from user
    </sql>

    <select id="getAllUser" resultType="User">
        <include refid="getAll" />
    </select>

    <select id="getUserById" resultType="user">
        <include refid="getAll" />
            where uid=#{uid}
    </select>

    <select id="getXiaoqiaoAndAge" resultType="User">
        <include refid="getAll" />
        <where>
            <if test="username!=null">
                username=#{username}
            </if>

            <if test="age!=null">
                or age=#{age}
            </if>
        </where>
    </select>

    <select id="getUserByIds" resultType="User">
        <include refid="getAll" />
        <where>
            uid in
            <foreach  collection="uids" item="uid" index="index"  open="(" separator="," close=")">
                #{uid}
            </foreach>
        </where>
    </select>

</mapper>

• User类User.java

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private int uid;
    private String username;
    private String password;
    private int age;
    private String addr;
}

• 接口IUserDao.java

public interface IUserDao {

    //获取所有的用户信息
    List<User> getAllUser();

    //根据id获取用户信息
    User getUserById(int uid);

    //查询name是小乔或者大乔的个人信息
    List<User> getXiaoqiaoAndAge(Map map);

    //查询多个id查询用户信息
    List<User> getUserByIds(Map map);
}

• 测试类

package dao;

import com.yanm.dao.IUserDao;
import com.yanm.pojo.User;
import com.yanm.utils.SessionUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.util.*;

public class TestSQL {
    private SqlSession ss=null;

    @Before
    public void beforeSQL(){
        ss= SessionUtils.getSqlSession();
    }

    @After
    public void afterSQL(){
        SessionUtils.SqlSessionClose(ss);
    }

    @Test
    public void getAllUser(){
        IUserDao dao=ss.getMapper(IUserDao.class);

        List<User> users = dao.getAllUser();
        for (User user : users) {
            System.out.println(user);
        }
    }


    @Test
    public void getUserById(){
        IUserDao dao=ss.getMapper(IUserDao.class);

        User user = dao.getUserById(2);
        System.out.println(user);
    }

    @Test
    public void getXiaoqiaoAndAge(){
        IUserDao dao=ss.getMapper(IUserDao.class);

        Map<String,Object> map=new HashMap<String, Object>();
        map.put("username","大乔");
        map.put("age",20);

        List<User> users = dao.getXiaoqiaoAndAge(map);
        for (User user : users) {
            System.out.println(user);
        }
    }

    @Test
    public void getUserByIds(){
        IUserDao dao=ss.getMapper(IUserDao.class);

        List<Integer> uids=new ArrayList<Integer>();
        Collections.addAll(uids,1,2,3,5);

        Map<String,Object> map=new HashMap<String, Object>();

        map.put("uids",uids);

        List<User> users = dao.getUserByIds(map);
        for (User user : users) {
            System.out.println(user);
        }
    }
}

• SqlSession的工具类

public class SessionUtils {
    private static SqlSessionFactory ssf=null;
    private static SqlSession ss=null;

    static {
        try {
            ssf=new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis.xml"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //获取SqlSession对象
    public static SqlSession getSqlSession(){
        ss=ssf.openSession(true);
        return ss;
    }

    //关闭SqlSession对象
    public static void SqlSessionClose(SqlSession s){
        if (s!=null){
            s.close();
            s=null;
        }
    }
}