MyBatis的动态SQL与日志Log4J、SQL语句构造器

2020-03-15 本文已影响0人低调的灬攻城狮

一、MyBatis动态SQL

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。
使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。

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

if

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

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

这条语句提供了可选的查找文本功能。如果不传入 “title”，那么所有处于 “ACTIVE” 状态的 BLOG 都会返回；如果传入了 “title” 参数，那么就会对 “title” 一列进行模糊查找并返回对应的 BLOG 结果。

通过 “title” 和 “author” 两个参数进行可选搜索，只需要加入另一个条件即可。

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

choose、when、otherwise

MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。传入了 “title” 就按 “title” 查找，传入了 “author” 就按 “author” 查找的情形。若两者都没有传入，就返回标记为 featured 的 BLOG。

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

trim、where、set

将 “state = ‘ACTIVE’” 设置成动态条件。

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

如果没有匹配的条件，最终这条 SQL 会变成这样：SELECT * FROM BLOG WHERE，这会导致查询失败。如果匹配的只是第二个条件，这条 SQL 会是这样：
SELECT * FROM BLOG WHERE AND title like ‘someTitle’，这个查询也会失败。

MyBatis 有一个简单且适合大多数场景的解决办法。而在其他场景中，可以对其进行自定义以符合需求。

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

where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。如果 where 元素与你期望的不太一样，你也可以通过自定义 trim 元素来定制 where 元素的功能。
```
<trim  prefix="WHERE"  prefixOverrides="AND |OR "> ... </trim>
```
prefixOverrides 属性会忽略通过管道符分隔的文本序列。上述例子会移除所有 prefixOverrides 属性中指定的内容，并且插入 prefix 属性中指定的内容。用于动态更新语句的类似解决方案叫做 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 元素等价的自定义 trim 元素

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

foreach

动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候）。

<select  id="selectPostIn"  resultType="domain.blog.Post">
  SELECT * FROM POST P WHERE ID in
  <foreach  item="item"  index="index"  collection="list"  open="("  separator=","  close=")">
     #{item}
  </foreach>
</select>

foreach 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。

script

要在带注解的映射器接口类中使用动态 SQL，可以使用 script 元素。

 @Update({"<script>",  "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}",  "</script>"}) 
void updateAuthorValues(Author author);

bind

bind 元素允许你在 OGNL 表达式以外创建一个变量，并将其绑定到当前的上下文。

<select  id="selectBlogsLike"  resultType="Blog">
  <bind  name="pattern"  value="'%' + _parameter.getTitle() + '%'"  />
  SELECT * FROM BLOG WHERE title LIKE #{pattern}
</select>

多数据库支持

如果配置了 databaseIdProvider，你就可以在动态代码中使用名为 “_databaseId” 的变量来为不同的数据库构建特定的语句。

<insert  id="insert">
  <selectKey  keyProperty="id"  resultType="int"  order="BEFORE">
    <if  test="_databaseId == 'oracle'">
      select seq_users.nextval from dual
    </if>
    <if  test="_databaseId == 'db2'">
      select nextval for seq_users from sysibm.sysdummy1"
    </if>
  </selectKey>
    insert into users values (#{id}, #{name})
</insert>

动态 SQL 中的插入脚本语言

MyBatis 从 3.2 版本开始支持插入脚本语言，这允许你插入一种语言驱动，并基于这种语言来编写动态 SQL 查询语句。

public  interface  LanguageDriver  {
    ParameterHandler createParameterHandler(MappedStatement mappedStatement, Object parameterObject,  BoundSql boundSql); 
    SqlSource createSqlSource(Configuration configuration,  XNode script,  Class<?> parameterType); 
    SqlSource createSqlSource(Configuration configuration,  String script,  Class<?> parameterType);  
}

实现自定义语言驱动后，你就可以在 mybatis-config.xml 文件中将它设置为默认语言：

<typeAliases>
  <typeAlias  type="org.sample.MyLanguageDriver"  alias="myLanguage"/>
</typeAliases>
<settings>
  <setting  name="defaultScriptingLanguage"  value="myLanguage"/>
</settings>

你也可以使用 lang 属性为特定的语句指定语言：

<select  id="selectBlog"  lang="myLanguage">
  SELECT * FROM BLOG
</select>

在你的 mapper 接口上添加 @Lang 注解：

public  interface  Mapper  {
    @Lang(MyLanguageDriver.class)
    @Select("SELECT * FROM BLOG")
    List<Blog> selectBlog();
}

二、MyBatis缓存

默认情况下，只启用了本地的会话缓存(SqlSession)，它仅仅对一个会话中的数据进行缓存，会话缓存也称为一级缓存。二级缓存是全局缓存(SqlSessionFactory)，需要手动开启：在SQL映射文件中添加一行：<cache/>
缓存的清除策略：
- LRU(Least Recently Uses):最近最少使用，移除最长时间不被使用的对象
- FIFO(First In First Out)：先进先出，按照对象进入缓存的顺序来移除他们。这也是数据结构中队列(Queue)的元素存取方式。
- SOFT:软引用，基于来及回收器状态和软引用规则移除对象
- WEAK:弱引用，更积极地基于垃圾收集器状态和弱引用规则来移除对象
默认的清除策略是LRU

三、日志 log4j

Mybatis 通过使用内置的日志工厂提供日志功能。内置日志工厂将会把日志工作委托给下面的实现之一：
- SLF4J
- Apache Commons Logging
- Log4j 2
- Log4j
- JDK logging
MyBatis 内置日志工厂会基于运行时检测信息选择日志委托实现。它会（按上面罗列的顺序）使用第一个查找到的实现。当没有找到这些实现时，将会禁用日志功能。
可以在mybatis配置文件中设置日志选项，可选的值有：SLF4J、LOG4J、LOG4J2、JDK_LOGGING、COMMONS_LOGGING、STDOUT_LOGGING、NO_LOGGING，或者是实现了 org.apache.ibatis.logging.Log 接口，且构造方法以字符串为参数的类完全限定名。
全局日志配置可以配置打印的日志级别，日志的输出对象。日志的四个级别：error、warn、info、debug，优先级从高到低；如果设置为error，只打印error信息，如果设置为debug，所有级别的信息都会打印；只会打印大于等于这个级别的日志。stdout是标准输出：打印在控制台。还可以将日志输出到文件，最后一行指定了最小输出宽度以及对齐方式。
MyBatis日志配置设置了跟踪的包、类或方法，对特定包下的类、特定类、特定方法才会打印日志。

四、测试动态SQL和日志log4j

添加junit，mysql，mybatis 以及 log4j 的依赖到 pom.xml 中

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com</groupId>
    <artifactId>MyBatisSQL</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.19</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.6</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
    </dependencies>
</project>

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>
    <properties resource="db.properties">
    </properties>
    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>
    <typeAliases>
        <package name="com.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="${pass}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>    
        <package name="com.pojo" />
    </mappers>
</configuration>

db.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3307/mysqls?serverTimezone=UTC
user=root
pass=123456

log4j.properties

log4j.rootLogger=debug, stdout, F
# MyBatis 日志配置
log4j.logger.com.qfedu=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
log4j.appender.F = org.apache.log4j.DailyRollingFileAppender
log4j.appender.F.File =myproj.log
log4j.appender.F.Append = true
log4j.appender.F.Threshold = DEBUG
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

User.java

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

IUserDao.java

public interface IUserDao {
    @Select("select * from user")
    List<User> getAll();
}

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.pojo.IUserDao">
    <sql id="all">
        select * from user
    </sql>
    <select id="selectAll" resultType="user">
        <include refid="all"/>
    </select>
    <select id="selectUserByUid" resultType="user">
        <include refid="all"/>
        where uid = #{uid}
    </select>
    <select id="selectIf" resultType="user">
        <include refid="all"/>
        <where>
            <if test="username != null">
                username = #{username}
            </if>
            <if test="password != null">
                and password = #{password}
            </if>
        </where>
    </select>
    <select id="selectIn" resultType="user">
        <include refid="all" />
       <where>
            uid in
            <foreach collection="ids" item="id" index="index" open="(" close=")" separator=",">
                #{id}
            </foreach>
        </where>
    </select>
</mapper>

SessionUtils.java

public class SessionUtils {
    private static SqlSession mSession = null;
    private static SqlSessionFactory mFactory = null;
    static {
        try {
            mFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis.xml"));
        } catch (IOException e) {
              e.printStackTrace();
        }
    }
    /**
     * 获取SqlSession对象
     * @return
     */
    public static SqlSession getSession(){
        mSession = mFactory.openSession(true);
        return mSession;
    }
    /**
     * 关闭SqlSession对象
     * @param session 要关闭的SqlSession对象
     */
    public static void closeSession(SqlSession session){
        if(session != null){
            session.close();
            session = null;
        }
    }
}

TestLog.java

public class TestLog {
    private SqlSession session = null;
    @Before
    public void setUp() {
        session = SessionUtils.getSession();
    }
    @After
    public void tearDown() {
        SessionUtils.closeSession(session);
    }
    @Test
    public void testLog(){
        IUserDao userDao = session.getMapper(IUserDao.class);
        List<User> list = userDao.getAll();
        for (User u : list) {
            System.out.println(u);
        }
    }
}

测试结果

 DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
 DEBUG [main] - Class not found: org.jboss.vfs.VFS
 DEBUG [main] - JBoss 6 VFS API is not available in this environment.
 DEBUG [main] - Class not found: org.jboss.vfs.VirtualFile
 DEBUG [main] - VFS implementation org.apache.ibatis.io.JBoss6VFS is not valid in this environment.
 DEBUG [main] - Using VFS adapter org.apache.ibatis.io.DefaultVFS
 DEBUG [main] - Find JAR URL: file:/F:/IDEA-Work/MyBatisSQLAndLog4J/target/classes/com/pojo
 DEBUG [main] - Not a JAR: file:/F:/IDEA-Work/MyBatisSQLAndLog4J/target/classes/com/pojo
 DEBUG [main] - Reader entry: IUserDao.class
 DEBUG [main] - Reader entry: User.class
 DEBUG [main] - Listing file:/F:/IDEA-Work/MyBatisSQLAndLog4J/target/classes/com/pojo
 DEBUG [main] - Find JAR URL: file:/F:/IDEA-Work/MyBatisSQLAndLog4J/target/classes/com/pojo/IUserDao.class
 DEBUG [main] - Not a JAR: file:/F:/IDEA-Work/MyBatisSQLAndLog4J/target/classes/com/pojo/IUserDao.class
 DEBUG [main] - Reader entry: ����   4 �� 
 DEBUG [main] - Find JAR URL: file:/F:/IDEA-Work/MyBatisSQLAndLog4J/target/classes/com/pojo/User.class
 DEBUG [main] - Not a JAR: file:/F:/IDEA-Work/MyBatisSQLAndLog4J/target/classes/com/pojo/User.class
 DEBUG [main] - Reader entry: ����   4 n   � Q     � R     � S     � T     � U� V
 DEBUG [main] - Checking to see if class com.pojo.IUserDao matches criteria [is assignable to Object]
 DEBUG [main] - Checking to see if class com.pojo.User matches criteria [is assignable to Object]
 DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
 DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
 DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
 DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
 DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
 DEBUG [main] - Find JAR URL: file:/F:/IDEA-Work/MyBatisSQLAndLog4J/target/classes/com/pojo
 DEBUG [main] - Not a JAR: file:/F:/IDEA-Work/MyBatisSQLAndLog4J/target/classes/com/pojo
 DEBUG [main] - Reader entry: IUserDao.class
 DEBUG [main] - Reader entry: User.class
 DEBUG [main] - Listing file:/F:/IDEA-Work/MyBatisSQLAndLog4J/target/classes/com/pojo
 DEBUG [main] - Find JAR URL: file:/F:/IDEA-Work/MyBatisSQLAndLog4J/target/classes/com/pojo/IUserDao.class
 DEBUG [main] - Not a JAR: file:/F:/IDEA-Work/MyBatisSQLAndLog4J/target/classes/com/pojo/IUserDao.class
 DEBUG [main] - Reader entry: ����   4 �� 
 DEBUG [main] - Find JAR URL: file:/F:/IDEA-Work/MyBatisSQLAndLog4J/target/classes/com/pojo/User.class
 DEBUG [main] - Not a JAR: file:/F:/IDEA-Work/MyBatisSQLAndLog4J/target/classes/com/pojo/User.class
 DEBUG [main] - Reader entry: ����   4 n   � Q     � R     � S     � T     � U� V
 DEBUG [main] - Checking to see if class com.pojo.IUserDao matches criteria [is assignable to Object]
 DEBUG [main] - Checking to see if class com.pojo.User matches criteria [is assignable to Object]
 DEBUG [main] - Opening JDBC Connection
 DEBUG [main] - Created connection 2030538903.
 DEBUG [main] - ==>  Preparing: select * from user 
 DEBUG [main] - ==> Parameters: 
 DEBUG [main] - <==      Total: 13
  User(uid=1, username=aaa, password=111111, age=10, addr=aaaa)
  User(uid=2, username=bbb, password=222222, age=11, addr=aaaa)
  User(uid=3, username=ccc, password=333333, age=12, addr=aaaa)
  User(uid=4, username=ddd, password=444444, age=13, addr=aaaa)
  User(uid=5, username=eee, password=555555, age=14, addr=aaaa)
  User(uid=6, username=fff, password=666666, age=15, addr=aaaa)
  User(uid=7, username=ggg, password=777777, age=16, addr=aaaa)
  User(uid=8, username=hhh, password=888888, age=17, addr=aaaa)
  User(uid=9, username=iii, password=999999, age=18, addr=aaaa)
  User(uid=10, username=jjj, password=000000, age=19, addr=aaaa)
  User(uid=11, username=kkk, password=000000, age=20, addr=aaaa)
  User(uid=12, username=lkt, password=888888, age=20, addr=suzhou)
  User(uid=15, username=ABC, password=111111, age=20, addr=南京)
 DEBUG [main] - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@79079097]
 DEBUG [main] - Returned connection 2030538903 to pool.

TestUserSQL.java

public class TestUserSQL {
    private SqlSession session = null;
    @Before
    public void setUp() { session = SessionUtils.getSession(); }
    @After
    public void tearDown() { SessionUtils.closeSession(session); }
    @Test
    public void testGetAllUsers() {
        List<User> list = session.selectList("com.pojo.IUserDao.selectAll");
        for (User u : list) {
            System.out.println(u);
        }
    }
    @Test
    public void testGetAllUsersByUid() {
        User u = session.selectOne("com.pojo.IUserDao.selectUserByUid", 1);
        System.out.println(u);
    }
    @Test
    public void testGetUserByIf(){
        Map<String, Object> map = new HashMap<>();
        map.put("password", "333333");
        List<User> list = session.selectList("com.pojo.IUserDao.selectIf", map);
        for (User u : list) {
          System.out.println(u);
        }
    }
    @Test
    public void testGetUsersByIn(){
        List<Integer> ids = new ArrayList<>();
        Collections.addAll(ids, 1, 2, 3, 4, 100);
        System.out.println(ids);
        Map<String, Object> map = new HashMap<>();
        map.put("ids", ids);
        List<User> list = session.selectList("com.pojo.IUserDao.selectIn", map);
        for (User u : list) {
            System.out.println(u);
        }
    }
}