MyBatis-11MyBatis动态SQL之【if】
【摘要】
动态SQL概述if概述在WHERE条件中使用if
需求1.UserMapper接口中增加接口方法2.UserMapper.xml配置动态SQL3.单元测试 在UPDATE条件中使用i...
动态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);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
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>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
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 ");
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
日志
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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
在UPDATE条件中使用if
需求
假设我们需要实现这样一个需求： 只更新有变化的字段，需要注意的是：更新的时候不能讲原来有值但是没有发生变化的值更新为空或者是null.
通过if标签可以实现这种动态列更新
1.UserMapper接口中增加接口方法
/**
*
*
* @Title: updateSysUserByIdSelective
*
* @Description: 根据主键更新SysUser
*
* @param sysUser
* @return
*
* @return: int
*/
int updateSysUserByIdSelective(SysUser sysUser);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
一般情况下，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>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
这里要结合业务层的判断逻辑，确保最终生成的SQL没有语法错误。
注意事项
1. 每个if元素里面SQL语句后面的逗号
2. where关键字前面的id = #{id} 这个条件
举个例子为什么要加 id = #{id}
- 如果全部的查询条件都是null 或者是空，最终的sql如下
udpate sys_user set id = #{id} where id = #{id}
- 1
如果没有 `id = #{id}`，则SQL为
- 1
udpate sys_user set where id = #{id}
- 1
很明显SQL语法是错误的。
- 1
- 查询条件只有一个不是null 也不是空 （比如userName）
如果有id = #{id}这个条件则SQL为
update sys_user set user_name = #{userName} , id = #{id} where id = #{id}
- 1
如果没有 `id = #{id}`这个条件，最终SQL如下
- 1
update sys_user set user_name = #{userName} , where id = #{id}
- 1
where关键字前面有个逗号，很明显SQL语法是错误的
- 1
从上面两种情况来看，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 ");
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
日志
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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
在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
1.UserMapper接口中增加接口方法
/**
*
*
* @Title: insertSysUserDyn
*
* @Description: insertSysUserDyn
*
* @param sysUser
* @return
*
* @return: int
*/
int insertSysUserDyn(SysUser sysUser);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
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>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
在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 ");
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
日志
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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
文章来源: artisan.blog.csdn.net，作者：小小工匠，版权归原作者所有，如需转载，请联系作者。
原文链接：artisan.blog.csdn.net/article/details/80013271
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）