概述
接着上篇博客 Mybatis-04Mybatis XML方式之insert元素，我们接着来学习 update和delete， 比较简单。
update 用法
需求： 通过主键更新sys_user
UserMapper接口中增加接口方法
/** * * * @Title: updateSysUserById * * @Description: 修改SysUser * * @param sysUser * @return * * @return: int 受影响的行数,这里仅仅是为了做记录，void在实际业务中更常用 */ int updateSysUserById(SysUser sysUser);
UserMapper.xml中配置update 元素
<!-- 根据id更改SysUser --> <update id="updateSysUserById"> 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} where id = #{id} </update>
单元测试
@Test public void updateSysUserByIdTest() { logger.info("updateSysUserByIdTest"); // 获取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("adminUpdated"); // 修改邮件 sysUser.setUserEmail("updateSysUser@artisan.com"); // 修改用户 ,返回受影响的行数 int result = userMapper.updateSysUserById(sysUser); // 只插入一条数据 ,期望是1 Assert.assertEquals(1, result); logger.info("受影响的行数:" + result); logger.info("userName:" + sysUser.getUserName() + ",userEmail:" + sysUser.getUserEmail()); // 期望的用户名为adminUpdated Assert.assertEquals("adminUpdated", sysUser.getUserName()); // 期望的邮箱为updateSysUser@artisan.com Assert.assertEquals("updateSysUser@artisan.com", sysUser.getUserEmail()); } catch (Exception e) { e.printStackTrace(); } finally { // 为了保持测试数据的干净，这里选择回滚 // 由于默认的sqlSessionFactory.openSession()是不自动提交的 // 除非显式的commit，否则不会提交到数据库 sqlSession.rollback(); logger.info("为了保持测试数据的干净，这里选择回滚,不写入mysql,请观察日志，回滚完成"); sqlSession.close(); logger.info("sqlSession close successfully "); } }
日志
2018-04-16 00:55:44,785 INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully 2018-04-16 00:55:44,788 INFO [main] (BaseMapperTest.java:29) - reader close successfully 2018-04-16 00:55:44,790 INFO [main] (UserMapperTest.java:271) - updateSysUserByIdTest 2018-04-16 00:55:45,193 DEBUG [main] (BaseJdbcLogger.java:142) - ==> 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-16 00:55:45,247 DEBUG [main] (BaseJdbcLogger.java:142) - ==> Parameters: 1(Long) 2018-04-16 00:55:45,270 TRACE [main] (BaseJdbcLogger.java:148) - <== Columns: id, user_name, user_password, user_email, user_info, head_img, create_time 2018-04-16 00:55:45,270 TRACE [main] (BaseJdbcLogger.java:148) - <== Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0 2018-04-16 00:55:45,277 DEBUG [main] (BaseJdbcLogger.java:142) - <== Total: 1 2018-04-16 00:55:45,278 DEBUG [main] (BaseJdbcLogger.java:142) - ==> Preparing: update sys_user set user_name = ?, user_password = ?, user_email = ?, user_info = ?, head_img = ?, create_time = ? where id = ? 2018-04-16 00:55:45,281 DEBUG [main] (BaseJdbcLogger.java:142) - ==> Parameters: adminUpdated(String), 123456(String), updateSysUser@artisan.com(String), 管理员用户(String), java.io.ByteArrayInputStream@74c176bf(ByteArrayInputStream), 2018-04-13 21:12:47.0(Timestamp), 1(Long) 2018-04-16 00:55:45,283 DEBUG [main] (BaseJdbcLogger.java:142) - <== Updates: 1 2018-04-16 00:55:45,284 INFO [main] (UserMapperTest.java:292) - 受影响的行数:1 2018-04-16 00:55:45,284 INFO [main] (UserMapperTest.java:294) - userName:adminUpdated,userEmail:updateSysUser@artisan.com 2018-04-16 00:55:45,288 INFO [main] (UserMapperTest.java:309) - 为了保持测试数据的干净，这里选择回滚,不写入mysql,请观察日志，回滚完成 2018-04-16 00:55:45,289 INFO [main] (UserMapperTest.java:312) - sqlSession close successfully
update的基本用法就这么简单，更复杂的情况后面通过动态SQL中阐述。
delete用法
UserMapper接口中增加接口方法
/** * * * @Title: deleteSysUserById * * @Description: 根据ID删除sysuser * * @param id * @return * * @return: int */ int deleteSysUserById(Long id);
UserMapper.xml中配置delete元素
<!-- 根据ID删除用户 --> <delete id="deleteSysUserById"> delete from sys_user where id = #{id} </delete>
单元测试
@Test public void deleteSysUserByIdTest() { logger.info("deleteSysUserByIdTest"); // 获取SqlSession SqlSession sqlSession = getSqlSession(); try { // 获取UserMapper接口 UserMapper userMapper = sqlSession.getMapper(UserMapper.class); // 调用删除接口 int result = userMapper.deleteSysUserById((long) 1); // 期望影响的结果条数为 1 Assert.assertEquals(1, result); // 再次查询 SysUser sysUser = userMapper.selectSysUserById((long) 1); // 期望查询出来的sysUser 为 null Assert.assertNull(sysUser); } catch (Exception e) { e.printStackTrace(); } finally { // 为了保持测试数据的干净，这里选择回滚 // 由于默认的sqlSessionFactory.openSession()是不自动提交的 // 除非显式的commit，否则不会提交到数据库 sqlSession.rollback(); logger.info("为了保持测试数据的干净，这里选择回滚,不写入mysql,请观察日志，回滚完成"); sqlSession.close(); logger.info("sqlSession close successfully "); } }
日志
2018-04-16 00:56:01,547 INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully 2018-04-16 00:56:01,550 INFO [main] (BaseMapperTest.java:29) - reader close successfully 2018-04-16 00:56:01,553 INFO [main] (UserMapperTest.java:320) - deleteSysUserByIdTest 2018-04-16 00:56:01,945 DEBUG [main] (BaseJdbcLogger.java:142) - ==> Preparing: delete from sys_user where id = ? 2018-04-16 00:56:02,023 DEBUG [main] (BaseJdbcLogger.java:142) - ==> Parameters: 1(Long) 2018-04-16 00:56:02,029 DEBUG [main] (BaseJdbcLogger.java:142) - <== Updates: 1 2018-04-16 00:56:02,031 DEBUG [main] (BaseJdbcLogger.java:142) - ==> 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-16 00:56:02,032 DEBUG [main] (BaseJdbcLogger.java:142) - ==> Parameters: 1(Long) 2018-04-16 00:56:02,055 DEBUG [main] (BaseJdbcLogger.java:142) - <== Total: 0 2018-04-16 00:56:02,060 INFO [main] (UserMapperTest.java:343) - 为了保持测试数据的干净，这里选择回滚,不写入mysql,请观察日志，回滚完成 2018-04-16 00:56:02,061 INFO [main] (UserMapperTest.java:346) - sqlSession close successfully
