MyBatis动态Sql之if标签的用法详解

/**
 * 根据动态条件查询用户信息
 *
 * @param sysUser
 * @return
 */
List selectByUser(SysUser sysUser);

  SELECT id,
      user_name,
      user_password,
      user_email,
      create_time
  FROM sys_user
  WHERE 1 = 1
  
    AND user_name LIKE CONCAT('%',#{userName},'%')
  
  
    AND user_email = #{userEmail}
  

@Test
public void testSelectByUser() {
  SqlSession sqlSession = getSqlSession();
  try {
    SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
    // 只按用户名查询
    SysUser query = new SysUser();
    query.setUserName("ad");
    List 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_time
TRACE [main] - <== Row: 1, admin, 123456, admin@mybatis.tk, 2019-06-27 18:21:07.0
DEBUG [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_time
TRACE [main] - <== Row: 1001, test, 123456, test@mybatis.tk, 2019-06-27 18:21:07.0
DEBUG [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

/**
 * 根据主键选择性更新用户信息
 *
 * @param sysUser
 * @return
 */
int updateByIdSelective(SysUser sysUser);

  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}

@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", sysUser.getUserName());
    Assert.assertEquals("test@mybatis.tk", sysUser.getUserEmail());
  } 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_time
TRACE [main] - <== Row: 1, admin, 123456, test@mybatis.tk, 2019-06-27 18:21:07.0
DEBUG [main] - <== Total: 1

ALTER TABLE sys_user
MODIFY COLUMN user_email VARCHAR(50) NULL DEFAULT 'test@mybatis.tk'
COMMENT '邮箱'
AFTER user_password;

/**
 * 根据传入的参数值动态插入列
 *
 * @param sysUser
 * @return
 */
int insertSelective(SysUser sysUser);

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

@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", sysUser.getUserEmail());
  } 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-08 11: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_time
TRACE [main] - <== Row: 1021, test-selective, 123456, test@mybatis.tk, 2019-07-08 11:40:37.0
DEBUG [main] - <== Total: 1