动态SQL概述

MyBatis的强大特性之一就是使用动态SQL,我们在使用JDBC的时候,根据不同的条件拼接SQL语句不仅不能忘了必要的空格,还要注意省略掉列名列表最后的逗号,处理方式显得很繁琐。 MyBatis的动态SQL则让你摆脱这种痛苦。

在MyBatis3之前的版本,使用动态SQL需要学习很多标签,现在MyBatis采用了强大的OGNL(Object Graph Navigation Language)表达式语言消除了许多其他的标签。

以下是MyBatis的动态SQL在xml中支持的几种标签

  • if
  • choose(when、otherwise)
  • trim(where、set)
  • foreach
  • bind

本篇博文我们来探索下 【if】的用法


if概述

if标签通常用于WHERE语句中,通过判断参数来决定是否使用某个查询条件,它也经常用于UPDATE语句中判断是否更新某一个字段,还可以在INSERT中用来判断是否插入某个字段的值。


在WHERE条件中使用if

需求

假设有个需求: 实现一个用户管理的高级查询功能,根据用户输入的条件去检索用户信息

  • 当用户只输入用户名时,需要根据用户名模糊查询
  • 当用户只输入邮箱时,根据邮箱进行完全匹配
  • 当用户同时输入用户名和密码时,用这两个条件查询匹配的用户

1.UserMapper接口中增加接口方法

/**
*
*
* @Title: selectSysUsersAdvanced
*
* @Description: 根据动态条件查询用户信息
*
* @param sysUser
* @return
*
* @return: List<SysUser>
*/
List<SysUser> selectSysUsersAdvanced(SysUser sysUser);

2.UserMapper.xml配置动态SQL

<select id="selectSysUsersAdvanced" resultType="com.artisan.mybatis.xml.domain.SysUser">
SELECT
a.id,
a.user_name userName,
a.user_password userPassword,
a.user_email userEmail,
a.user_info userInfo,
a.head_img headImg,
a.create_time createTime
FROM
sys_user a
WHERE 1=1
<if test="userName != null and userName != '' ">
and user_name like concat('%',#{userName},'%')
</if>
<if test="userEmail != null and userEmail != '' ">
and user_email = #{userEmail}
</if>
</select>

if标签有一个必填的属性test , test的属性值是一个符合OGN要求的判断表达式,表达式的结果可以为true或者false,除此之外所有非0值都为true,只有0为false。 不过建议在表达式中只用true或者false作为结果。

  • 判断条件property != null 或者 property == null ,适用于任何类型的字段,判断属性值是否为空
  • 判断条件property != ” 或者 property == ”,适用于String类型的字段,判断属性值是否为空
  • and 和 or :当有多个判断条件时,使用and或者or进行连接,嵌套的判断可以使用小括号分组,and 相当于 java中的 和(&&) , or相当于java中的或(||)

注意事项:

1. 注意SQL中where关键字后面的条件 ​​where 1 = 1​​​, 又有两个条件都是动态的,如果没有1=1这个默认条件,当两个if都不满足时,最后生成的SQL就会以where结束,不符合SQL规范,会报错,因此加上 ​​1 = 1​​这个条件可以避免SQL语法错误导致的异常。 这种写法并不美观,后续介绍使用where标签替换这种写法。

2. 注意条件中的 and(或者 or):当这部分条件拼接到​​where 1 = 1​​ 后面时仍然是合法的SQL。因为有默认的1=1这个条件,我们才不需要判断第一个动态条件是否需要加上and(或者or)。 因为这种情况下and(或者or)是必须有的


3.单元测试

@Test
public void selectSysUsersAdvancedTest() {
logger.info("selectSysUsersAdvanced");
// 获取SqlSession
SqlSession sqlSession = getSqlSession();

List<SysUser> userList = null;
try {
// 获取UserMapper接口
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

logger.info("===========1.当用户只输入用户名时,需要根据用户名模糊查询===========");
// 模拟前台传参 1.当用户只输入用户名时,需要根据用户名模糊查询
SysUser sysUser = new SysUser();
sysUser.setUserName("ad");
// 调用selectSysUsersAdvanced,根据查询条件查询用户
userList = userMapper.selectSysUsersAdvanced(sysUser);
// 根据数据库sys_user表中的记录,可以匹配到admin, 期望userList不为空
Assert.assertNotNull(userList);
// 根据查询条件,期望只有1条数据
Assert.assertTrue(userList.size() == 1);
logger.info("userList:" + userList);

// 为了测试 匹配多条记录的情况,我们将id=1001这条数据的userName 由test 改为artisan
sysUser.setUserName("i");
// 调用selectSysUsersAdvanced,根据查询条件查询用户
userList = userMapper.selectSysUsersAdvanced(sysUser);
// 根据数据库sys_user表中的记录,可以匹配到admin和artisan, 期望userList不为空
Assert.assertNotNull(userList);
// 根据查询条件,期望只有2条数据
Assert.assertTrue(userList.size() == 2);

logger.info("userList:" + userList);

logger.info("===========2.当用户只输入邮箱使,根据邮箱进行完全匹配===========");
// 模拟前台传参 2.当用户只输入邮箱使,根据邮箱进行完全匹配
sysUser.setUserEmail("admin@artisan.com");
userList = userMapper.selectSysUsersAdvanced(sysUser);
Assert.assertNotNull(userList);
Assert.assertTrue(userList.size() == 1);
logger.info(userList);

sysUser.setUserEmail("1admin@artisan.com");
userList = userMapper.selectSysUsersAdvanced(sysUser);
Assert.assertTrue(userList.size() == 0);

logger.info("===========当用户同时输入用户名和密码时,用这两个条件查询匹配的用户===========");

// 模拟组合查询条件,存在记录的情况
sysUser.setUserName("i");
sysUser.setUserEmail("admin@artisan.com");
userList = userMapper.selectSysUsersAdvanced(sysUser);
Assert.assertNotNull(userList);
Assert.assertEquals("admin@artisan.com", sysUser.getUserEmail());
Assert.assertTrue(userList.size() == 1);
logger.info(userList);

// 模拟组合查询条件,不存在记录的情况
sysUser.setUserName("x");
sysUser.setUserEmail("admin@artisan.com");
userList = userMapper.selectSysUsersAdvanced(sysUser);
Assert.assertTrue(userList.size() == 0);
logger.info(userList);

} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
logger.info("sqlSession close successfully ");
}
}

日志

2018-04-21 02:01:38,930  INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully
2018-04-21 02:01:38,934 INFO [main] (BaseMapperTest.java:29) - reader close successfully
2018-04-21 02:01:38,944 INFO [main] (UserMapperTest.java:356) - selectSysUsersAdvanced
2018-04-21 02:01:38,981 INFO [main] (UserMapperTest.java:365) - ===========1.当用户只输入用户名时,需要根据用户名模糊查询===========
2018-04-21 02:01:39,533 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE 1=1 and user_name like concat('%',?,'%')
2018-04-21 02:01:39,606 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: ad(String)
2018-04-21 02:01:39,639 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
2018-04-21 02:01:39,640 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0
2018-04-21 02:01:39,651 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 1
2018-04-21 02:01:39,658 INFO [main] (UserMapperTest.java:375) - userList:[SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@artisan.com, userInfo=管理员用户, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018]]
2018-04-21 02:01:39,658 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE 1=1 and user_name like concat('%',?,'%')
2018-04-21 02:01:39,659 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: i(String)
2018-04-21 02:01:39,661 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
2018-04-21 02:01:39,661 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0
2018-04-21 02:01:39,662 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1001, artisan, 123456, test@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0
2018-04-21 02:01:39,664 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 2
2018-04-21 02:01:39,666 INFO [main] (UserMapperTest.java:386) - userList:[SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@artisan.com, userInfo=管理员用户, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018], SysUser [id=1001, userName=artisan, userPassword=123456, userEmail=test@artisan.com, userInfo=测试用户, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018]]
2018-04-21 02:01:39,667 INFO [main] (UserMapperTest.java:388) - ===========2.当用户只输入邮箱使,根据邮箱进行完全匹配===========
2018-04-21 02:01:39,667 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE 1=1 and user_name like concat('%',?,'%') and user_email = ?
2018-04-21 02:01:39,668 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: i(String), admin@artisan.com(String)
2018-04-21 02:01:39,669 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
2018-04-21 02:01:39,670 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0
2018-04-21 02:01:39,671 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 1
2018-04-21 02:01:39,671 INFO [main] (UserMapperTest.java:394) - [SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@artisan.com, userInfo=管理员用户, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018]]
2018-04-21 02:01:39,672 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE 1=1 and user_name like concat('%',?,'%') and user_email = ?
2018-04-21 02:01:39,673 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: i(String), 1admin@artisan.com(String)
2018-04-21 02:01:39,674 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 0
2018-04-21 02:01:39,675 INFO [main] (UserMapperTest.java:400) - ===========当用户同时输入用户名和密码时,用这两个条件查询匹配的用户===========
2018-04-21 02:01:39,675 INFO [main] (UserMapperTest.java:409) - [SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@artisan.com, userInfo=管理员用户, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018]]
2018-04-21 02:01:39,676 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE 1=1 and user_name like concat('%',?,'%') and user_email = ?
2018-04-21 02:01:39,677 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: x(String), admin@artisan.com(String)
2018-04-21 02:01:39,679 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 0
2018-04-21 02:01:39,679 INFO [main] (UserMapperTest.java:416) - []
2018-04-21 02:01:39,681 INFO [main] (UserMapperTest.java:422) - sqlSession close successfully

在UPDATE条件中使用if

需求

假设我们需要实现这样一个需求: 只更新有变化的字段,需要注意的是:更新的时候不能讲原来有值但是没有发生变化的值更新为空或者是null.

通过if标签可以实现这种动态列更新


1.UserMapper接口中增加接口方法

/**
*
*
* @Title: updateSysUserByIdSelective
*
* @Description: 根据主键更新SysUser
*
* @param sysUser
* @return
*
* @return: int
*/
int updateSysUserByIdSelective(SysUser sysUser);

一般情况下,mybatis中选择性更新的方法命名会以Selective作为后缀。


2.UserMapper.xml配置动态SQL

<update id="updateSysUserByIdSelective">
update sys_user
set
<if test="userName != null and userName != ''">
user_name = #{userName},
</if>
<if test="userPassword != null and userPassword != ''">
user_password = #{userPassword},
</if>
<if test="userEmail != null and userEmail != ''">
user_email = #{userEmail},
</if>
<if test="userInfo != null and userInfo != ''">
user_info = #{userInfo},
</if>
<if test="headImg != null">
head_img = #{headImg, jdbcType=BLOB},
</if>
<if test="createTime != null">
create_time = #{createTime, jdbcType=TIMESTAMP},
</if>
id = #{id}
where id = #{id}
</update>

这里要结合业务层的判断逻辑,确保最终生成的SQL没有语法错误。

注意事项

1. 每个if元素里面SQL语句后面的逗号

2. where关键字前面的​​id = #{id}​​ 这个条件


举个例子为什么要加 ​​id = #{id}​

  • 如果全部的查询条件都是null 或者是空,最终的sql如下
udpate sys_user set id = #{id} where id = #{id}
如果没有 `id = #{id}`,则SQL为
udpate sys_user set  where id = #{id}
很明显SQL语法是错误的。
  • 查询条件只有一个不是null 也不是空 (比如userName)
    如果有​​​id = #{id}​​这个条件则SQL为
update sys_user set user_name = #{userName} , id = #{id} where  id = #{id}
如果没有  `id = #{id}`这个条件,最终SQL如下
update sys_user set user_name = #{userName} , where  id = #{id}
where关键字前面有个逗号,很明显SQL语法是错误的

从上面两种情况来看,id = #{id} 这个条件可以最大限度保证方法不出错。 除了使用这种方式外,还可以结合业务层的逻辑判断调整XML文件中的SQL来确保最终的SQL的正确性,也可以通过where 和 set标签来解决这些问题


3.单元测试

@Test
public void updateSysUserByIdSelectiveTest() {
logger.info("updateSysUserByIdSelectiveTest");
// 获取SqlSession
SqlSession sqlSession = getSqlSession();
try {
// 获取UserMapper接口
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

// 先根据ID查询出对应的sysuser
SysUser sysUser = userMapper.selectSysUserById((long) 1);
// 当前数据库用户的userName期望为admin
Assert.assertEquals("admin", sysUser.getUserName());

// 修改用户名
sysUser.setUserName("dynamicUpdate");
// 修改邮件
sysUser.setUserEmail("dynamicUpdate@artisan.com");
// 修改用户 ,返回受影响的行数
int result = userMapper.updateSysUserByIdSelective(sysUser);

// 只插入一条数据 ,期望是1
Assert.assertEquals(1, result);
logger.info("受影响的行数:" + result);

// 重新查询(虽然未提交但是在一个会话中)
sysUser = userMapper.selectSysUserById((long) 1);
// 期望的用户名为dynamicUpdate
Assert.assertEquals("dynamicUpdate", sysUser.getUserName());
// 期望的邮箱为dynamicUpdate@artisan.com
Assert.assertEquals("dynamicUpdate@artisan.com", sysUser.getUserEmail());

// 检查其他字段有没有被更新为null 或者 空值
Assert.assertEquals("123456", sysUser.getUserPassword());
Assert.assertEquals("管理员用户", sysUser.getUserInfo());
logger.info(sysUser);

} catch (Exception e) {
e.printStackTrace();
} finally {
// 为了保持测试数据的干净,这里选择回滚
// 由于默认的sqlSessionFactory.openSession()是不自动提交的
// 除非显式的commit,否则不会提交到数据库
sqlSession.rollback();
logger.info("为了保持测试数据的干净,这里选择回滚,不写入mysql,请观察日志,回滚完成");

sqlSession.close();
logger.info("sqlSession close successfully ");
}
}

日志

2018-04-21 21:34:32,025  INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully
2018-04-21 21:34:32,031 INFO [main] (BaseMapperTest.java:29) - reader close successfully
2018-04-21 21:34:32,037 INFO [main] (UserMapperTest.java:430) - updateSysUserByIdSelectiveTest
2018-04-21 21:34:32,673 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: select a.id, a.user_name, a.user_password, a.user_email, a.user_info, a.head_img, a.create_time from sys_user a where id = ?
2018-04-21 21:34:32,751 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 1(Long)
2018-04-21 21:34:32,787 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, user_name, user_password, user_email, user_info, head_img, create_time
2018-04-21 21:34:32,788 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0
2018-04-21 21:34:32,794 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 1
2018-04-21 21:34:32,845 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: update sys_user set user_name = ?, user_password = ?, user_email = ?, user_info = ?, head_img = ?, create_time = ?, id = ? where id = ?
2018-04-21 21:34:32,851 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: dynamicUpdate(String), 123456(String), dynamicUpdate@artisan.com(String), 管理员用户(String), java.io.ByteArrayInputStream@4a3e0e88(ByteArrayInputStream), 2018-04-13 21:12:47.0(Timestamp), 1(Long), 1(Long)
2018-04-21 21:34:32,855 DEBUG [main] (BaseJdbcLogger.java:145) - <== Updates: 1
2018-04-21 21:34:32,856 INFO [main] (UserMapperTest.java:451) - 受影响的行数:1
2018-04-21 21:34:32,856 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: select a.id, a.user_name, a.user_password, a.user_email, a.user_info, a.head_img, a.create_time from sys_user a where id = ?
2018-04-21 21:34:32,857 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 1(Long)
2018-04-21 21:34:32,858 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, user_name, user_password, user_email, user_info, head_img, create_time
2018-04-21 21:34:32,859 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1, dynamicUpdate, 123456, dynamicUpdate@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0
2018-04-21 21:34:32,862 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 1
2018-04-21 21:34:32,863 INFO [main] (UserMapperTest.java:463) - SysUser [id=1, userName=dynamicUpdate, userPassword=123456, userEmail=dynamicUpdate@artisan.com, userInfo=管理员用户, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018]
2018-04-21 21:34:32,872 INFO [main] (UserMapperTest.java:472) - 为了保持测试数据的干净,这里选择回滚,不写入mysql,请观察日志,回滚完成
2018-04-21 21:34:32,874 INFO [main] (UserMapperTest.java:475) - sqlSession close successfully

在INSERT动态插入列中使用if

需求

在数据库表中插入数据的时候,如果某一列的参数值不为空,就使用传入的值,如果传入的值为空,就使用数据库中默认的值,而不是使用传入的空值。

使用if就可以实现这种动态插入列的功能。

先给sys_user的user)email字段增加个默认值。

ALTER TABLE `sys_user` MODIFY COLUMN `user_email` VARCHAR (50) NULL DEFAULT 'default@artisan.com' COMMENT '邮箱' AFTER `user_password`

1.UserMapper接口中增加接口方法

/**
*
*
* @Title: insertSysUserDyn
*
* @Description: insertSysUserDyn
*
* @param sysUser
* @return
*
* @return: int
*/
int insertSysUserDyn(SysUser sysUser);

2.UserMapper.xml配置动态SQL

<insert id="insertSysUserDyn" useGeneratedKeys="true" keyProperty="id">
insert into sys_user(
user_name, user_password,
<if test="userEmail != null">
<if test="userEmail != ''">
user_email,
</if>
</if>
user_info, head_img, create_time)
values(
#{userName}, #{userPassword},
<if test="userEmail != null">
<if test="userEmail != ''">
#{userEmail},
</if>
</if>
#{userInfo}, #{headImg, jdbcType=BLOB}, #{createTime, jdbcType=TIMESTAMP})

</insert>

在insert中使用要注意,若在列的部分增加if条件,则values的部分也要增加相同的if条件,必须保证上下可以相互对应,完全匹配。


3.单元测试

@Test
public void insertSysUserDynTest() {
logger.info("insertSysUserDynTest");
// 获取SqlSession
SqlSession sqlSession = getSqlSession();
try {
// 获取UserMapper接口
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

logger.info("=========不设置email=========");
// 不设置userEmail ,观察是否能够插入默认的email
SysUser sysUser = new SysUser();
sysUser.setUserName("artisanTest");
sysUser.setUserPassword("123456");
sysUser.setUserInfo("测试用户");
// 模拟头像
sysUser.setHeadImg(new byte[] { 1, 2, 3 });
sysUser.setCreateTime(new Date());
// 新增用户 ,返回受影响的行数
int result = userMapper.insertSysUserDyn(sysUser);

// 只插入一条数据 ,期望是1
Assert.assertEquals(1, result);

// 获取这条新插入的sysUser
sysUser = userMapper.selectSysUserById(sysUser.getId());
// 没有设置userEmail ,期望是数据库的默认值
Assert.assertEquals("default@artisan.com", sysUser.getUserEmail());
logger.info(sysUser);

logger.info("=========设置email=========");
// 设置email
sysUser.setUserName("artisanTest");
sysUser.setUserPassword("123456");
sysUser.setUserEmail("artisan@artisan.com");
sysUser.setUserInfo("测试用户");
// 模拟头像
sysUser.setHeadImg(new byte[] { 1, 2, 3 });
sysUser.setCreateTime(new Date());
result = userMapper.insertSysUserDyn(sysUser);

// 获取这条新插入的sysUser
sysUser = userMapper.selectSysUserById(sysUser.getId());
// 有设置userEmail ,期望是传入的值
Assert.assertEquals("artisan@artisan.com", sysUser.getUserEmail());
logger.info(sysUser);
} catch (Exception e) {
e.printStackTrace();
} finally {
// 为了保持测试数据的干净,这里选择回滚
// 由于默认的sqlSessionFactory.openSession()是不自动提交的
// 除非显式的commit,否则不会提交到数据库
sqlSession.rollback();
logger.info("为了保持测试数据的干净,这里选择回滚,不写入mysql,请观察日志,回滚完成");

sqlSession.close();
logger.info("sqlSession close successfully ");
}
}

日志

2018-04-21 04:52:22,046  INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully
2018-04-21 04:52:22,049 INFO [main] (BaseMapperTest.java:29) - reader close successfully
2018-04-21 04:52:22,055 INFO [main] (UserMapperTest.java:477) - insertSysUserDynTest
2018-04-21 04:52:22,094 INFO [main] (UserMapperTest.java:484) - =========不设置email=========
2018-04-21 04:52:22,652 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: insert into sys_user( user_name, user_password, user_info, head_img, create_time) values( ?, ?, ?, ?, ?)
2018-04-21 04:52:22,735 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: artisanTest(String), 123456(String), 测试用户(String), java.io.ByteArrayInputStream@5b20f3ff(ByteArrayInputStream), 2018-04-21 04:52:22.095(Timestamp)
2018-04-21 04:52:22,741 DEBUG [main] (BaseJdbcLogger.java:145) - <== Updates: 1
2018-04-21 04:52:22,744 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: select a.id, a.user_name, a.user_password, a.user_email, a.user_info, a.head_img, a.create_time from sys_user a where id = ?
2018-04-21 04:52:22,745 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 1006(Long)
2018-04-21 04:52:22,778 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, user_name, user_password, user_email, user_info, head_img, create_time
2018-04-21 04:52:22,779 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1006, artisanTest, 123456, default@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-21 04:52:22.0
2018-04-21 04:52:22,785 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 1
2018-04-21 04:52:22,786 INFO [main] (UserMapperTest.java:503) - SysUser [id=1006, userName=artisanTest, userPassword=123456, userEmail=default@artisan.com, userInfo=测试用户, headImg=[1, 2, 3], createTime=Sat Apr 21 04:52:22 BOT 2018]
2018-04-21 04:52:22,790 INFO [main] (UserMapperTest.java:505) - =========设置email=========
2018-04-21 04:52:22,792 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: insert into sys_user( user_name, user_password, user_email, user_info, head_img, create_time) values( ?, ?, ?, ?, ?, ?)
2018-04-21 04:52:22,795 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: artisanTest(String), 123456(String), artisan@artisan.com(String), 测试用户(String), java.io.ByteArrayInputStream@5485687f(ByteArrayInputStream), 2018-04-21 04:52:22.79(Timestamp)
2018-04-21 04:52:22,798 DEBUG [main] (BaseJdbcLogger.java:145) - <== Updates: 1
2018-04-21 04:52:22,799 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: select a.id, a.user_name, a.user_password, a.user_email, a.user_info, a.head_img, a.create_time from sys_user a where id = ?
2018-04-21 04:52:22,800 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 1007(Long)
2018-04-21 04:52:22,801 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, user_name, user_password, user_email, user_info, head_img, create_time
2018-04-21 04:52:22,802 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1007, artisanTest, 123456, artisan@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-21 04:52:23.0
2018-04-21 04:52:22,803 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 1
2018-04-21 04:52:22,803 INFO [main] (UserMapperTest.java:520) - SysUser [id=1007, userName=artisanTest, userPassword=123456, userEmail=artisan@artisan.com, userInfo=测试用户, headImg=[1, 2, 3], createTime=Sat Apr 21 04:52:23 BOT 2018]
2018-04-21 04:52:22,809 INFO [main] (UserMapperTest.java:528) - 为了保持测试数据的干净,这里选择回滚,不写入mysql,请观察日志,回滚完成
2018-04-21 04:52:22,810 INFO [main] (UserMapperTest.java:531) - sqlSession close successfully