sysUserList = sysUserMapper.selectByUser(query); Assert.assertTrue(sysUserList.size() > 0);// 只按邮箱查询
query = new SysUser();
query.setUserEmail(\"test@mybatis.tk\");
sysUserList = sysUserMapper.selectByUser(query); Assert.assertTrue(sysUserList.size() > 0); // 同时按⽤户民和邮箱查询 query = new SysUser(); query.setUserName(\"ad\");
query.setUserEmail(\"test@mybatis.tk\");
sysUserList = sysUserMapper.selectByUser(query);
// 由于没有同时符合这两个条件的⽤户，因此查询结果数为0 Assert.assertTrue(sysUserList.size() == 0); } finally {
sqlSession.close(); }}
运⾏测试代码，测试通过，输出⽇志如下：
DEBUG [main] - ==> Preparing: SELECT id, user_name, user_password, user_email, create_time FROM sys_user WHERE 1= 1 AND user_name LIKE CONCAT('%',?,'%')DEBUG [main] - ==> Parameters: ad(String)
TRACE [main] - <== Columns: id, user_name, user_password, user_email, create_timeTRACE [main] - <== Row: 1, admin, 123456, admin@mybatis.tk, 2019-06-27 18:21:07.0DEBUG [main] - <== Total: 1
DEBUG [main] - ==> Preparing: SELECT id, user_name, user_password, user_email, create_time FROM sys_user WHERE 1= 1 AND user_email = ?
DEBUG [main] - ==> Parameters: test@mybatis.tk(String)
TRACE [main] - <== Columns: id, user_name, user_password, user_email, create_timeTRACE [main] - <== Row: 1001, test, 123456, test@mybatis.tk, 2019-06-27 18:21:07.0DEBUG [main] - <== Total: 1
DEBUG [main] - ==> Preparing: SELECT id, user_name, user_password, user_email, create_time FROM sys_user WHERE 1= 1 AND user_name LIKE CONCAT('%',?,'%') AND user_email = ?DEBUG [main] - ==> Parameters: ad(String), test@mybatis.tk(String)DEBUG [main] - <== Total: 0
2. 使⽤if标签实现动态更新
假设有这样1个需求：更新⽤户信息的时候不能将原来有值但没有发⽣变化的字段更新为空或null，即只更新有值的字段。⾸先，我们在接⼝SysUserMapper中添加如下⽅法：
/**
* 根据主键选择性更新⽤户信息 *
* @param sysUser * @return */
int updateByIdSelective(SysUser sysUser);
然后在对应的SysUserMapper.xml中添加如下代码：
UPDATE sys_user SET user_name = #{userName},
user_password = #{userPassword},
user_email = #{userEmail},
user_info = #{userInfo},
head_img = #{headImg,jdbcType=BLOB},
create_time = #{createTime,jdbcType=TIMESTAMP},
id = #{id}
WHERE id = #{id}
最后，在SysUserMapperTest测试类中添加如下测试⽅法：
@Test
public void testUpdateByIdSelective() {
SqlSession sqlSession = getSqlSession();
try {
SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class); SysUser sysUser = new SysUser(); // 更新id=1的⽤户 sysUser.setId(1L); // 修改邮箱
sysUser.setUserEmail(\"test@mybatis.tk\");
int result = sysUserMapper.updateByIdSelective(sysUser); Assert.assertEquals(1, result);
// 查询id=1的⽤户
sysUser = sysUserMapper.selectById(1L); // 修改后的名字保持不变，但是邮箱变成了新的
Assert.assertEquals(\"admin\
Assert.assertEquals(\"test@mybatis.tk\ } finally {
sqlSession.close(); }}
运⾏测试代码，测试通过，输出⽇志如下：
DEBUG [main] - ==> Preparing: UPDATE sys_user SET user_email = ?, id = ? WHERE id = ?DEBUG [main] - ==> Parameters: test@mybatis.tk(String), 1(Long), 1(Long)DEBUG [main] - <== Updates: 1
DEBUG [main] - ==> Preparing: SELECT id, user_name, user_password, user_email, create_time FROM sys_user WHERE id= ?
DEBUG [main] - ==> Parameters: 1(Long)
TRACE [main] - <== Columns: id, user_name, user_password, user_email, create_timeTRACE [main] - <== Row: 1, admin, 123456, test@mybatis.tk, 2019-06-27 18:21:07.0DEBUG [main] - <== Total: 1
3. 使⽤if标签实现动态插⼊
假设有这样1个需求：往数据库表中插⼊数据的时候，如果某⼀列的参数值不为空，就使⽤传⼊的值，如果传⼊的参数值为空，就使⽤数据库中的默认值(通常是空)，⽽不使⽤传⼊的空值。
为了更好的理解该⽰例，我们先给sys_user表的user_email字段设置默认值：test@mybatis.tk，Sql语句如下：
ALTER TABLE sys_user
MODIFY COLUMN user_email VARCHAR(50) NULL DEFAULT 'test@mybatis.tk'COMMENT '邮箱'
AFTER user_password;
⾸先，我们在接⼝SysUserMapper中添加如下⽅法：
/**
* 根据传⼊的参数值动态插⼊列 *
* @param sysUser * @return */
int insertSelective(SysUser sysUser);
然后在对应的SysUserMapper.xml中添加如下代码：
INSERT INTO sys_user(user_name, user_password, user_email, user_info, head_img, create_time)
VALUES (#{userName},#{userPassword},
#{userEmail},
#{userInfo},#{headImg,jdbcType=BLOB},#{createTime,jdbcType=TIMESTAMP})
最后，在SysUserMapperTest测试类中添加如下测试⽅法：
@Test
public void testInsertSelective() {
SqlSession sqlSession = getSqlSession();
try {
SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class); SysUser sysUser = new SysUser(); sysUser.setUserName(\"test-selective\"); sysUser.setUserPassword(\"123456\"); sysUser.setUserInfo(\"test info\"); sysUser.setCreateTime(new Date()); sysUserMapper.insertSelective(sysUser);
// 获取刚刚插⼊的数据
sysUser = sysUserMapper.selectById(sysUser.getId()); // 因为没有指定userEmail,所以⽤的是数据库的默认值
Assert.assertEquals(\"test@mybatis.tk\ } finally {
sqlSession.close(); }}
运⾏测试代码，测试通过，输出⽇志如下：
DEBUG [main] - ==> Preparing: INSERT INTO sys_user(user_name, user_password, user_info, head_img, create_time)VALUES (?,?, ?,?,?)
DEBUG [main] - ==> Parameters: test-selective(String), 123456(String), test info(String), null, 2019-07-0811:40:36.927(Timestamp)DEBUG [main] - <== Updates: 1
DEBUG [main] - ==> Preparing: SELECT id, user_name, user_password, user_email, create_time FROM sys_user WHERE id= ?
DEBUG [main] - ==> Parameters: 1021(Long)
TRACE [main] - <== Columns: id, user_name, user_password, user_email, create_timeTRACE [main] - <== Row: 1021, test-selective, 123456, test@mybatis.tk, 2019-07-08 11:40:37.0DEBUG [main] - <== Total: 1
4. 源码及参考
源码地址：，欢迎下载。刘增辉《MyBatis从⼊门到精通》
