SSM（五）使用MyBatis实现增删改操作

一、提纲

• 使用insert完成增加操作
• 使用update完成修改操作
• 使用@Param注解实现多参数入参
• 使用delete完成删除操作

二、insert

使用insert元素完成增加操作

insert

• id:命名空间中的唯一标识
• parameterType：插入的入参的参数类型。可用别名，也可用完全限定名

<insert id="add" parameterType="user">
    insert into smbms_user(userCode, userName, userPassword)
        values(#{userCode}, #{userName}, #{userPassword})
</insert>

UserMapper.java

public int add(User user);

UserMapper.xml

<insert id="add" parameterType="user">
		insert into smbms_user(userCode, userName, userPassword, gender, birthday, phone, address, userRole, createdBy, creationDate)
		values(#{userCode},#{userName},#{userPassword},#{gender},#{birthday},#{phone},#{address},#{userRole},#{createdBy},#{creationDate}) 
	</insert>

UserMapperTest.java

@Test
	public void testAdd() {
		SqlSession sqlSession = null;
		int count = 0;
		User user = new User();
		user.setUserCode("testuser001");
		user.setUserName("测试用户001");
		user.setAddress("测试地址");
		try {
			user.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse("1983-11-11"));
		} catch (ParseException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		user.setUserPassword("121");
		user.setGender(1);
		user.setUserRole(1);
		user.setCreatedBy(1);
		user.setCreationDate(new Date());
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			count = sqlSession.getMapper(UserMapper.class).add(user);
			sqlSession.commit();
		} catch (Exception e){
			e.printStackTrace();
			sqlSession.rollback();
			count = 0;
		} finally {
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		logger.debug("testAdd count = " + count);
	}

注意：insert、update、delete元素均没有resultType属性

三、update

在MyBatis中来实现修改操作。

update元素：

• id：命名空间下的唯一标识。
• parameterType：传入参数类型。

<update id="modify" parameterType="User">
    update smbms_user set userCode=#{userCode}, userName=#{userName},
        userPassword=#{userPassword} where id=#{id}
</update>

UserMapper.java

public int modify(User user);public int modify(User user);

UserMapper.xml

<update id="modify" parameterType="user">
		update smbms_user set userCode=#{userCode}, userName=#{userName}, userPassword=#{userPassword}, gender=#{gender}, 
		birthday=#{birthday},phone=#{phone}, address=#{address}, userRole=#{userRole}, modifyBy=#{modifyBy}, modifyDate=#{modifyDate}
		where id=#{id}
</update>

UserMapperTest.java

@Test
	public void testModify() {
		SqlSession sqlSession = null;
		int count = 0;
		try {
			User user = new User();
			user.setId(13);
			user.setUserCode("testuser001_M");
			user.setUserName(new String("测试用户001_M".getBytes("UTF-8"),"UTF-8"));
			user.setAddress("测试地址_M");
			user.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse("1986-11-11"));
			user.setUserPassword("121");
			user.setGender(1);
			user.setModifyBy(1);
			user.setModifyDate(new Date());
			sqlSession = MyBatisUtil.createSqlSession();
			count = sqlSession.getMapper(UserMapper.class).modify(user);
			sqlSession.commit();
		} catch(Exception e) {
			e.printStackTrace();
			sqlSession.rollback();
			count = 0;
		} finally {
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		logger.debug("modify count = " + count);
	}
	

四、需求——修改个人密码功能

分析：

传入参数（多个）：用户ID和新密码

使用注解@Param来传入多个参数

映射SQL中的参数：#{注解名称}

UserMapper.java

public int updatePwd(@Param("id")Integer id, @Param("pwd")String userPassword);

使用Integer，在参数入参时都要使用Integer，方便动态SQL参数验证。

UserMapper.xml

<update id="updatePwd">
		update smbms_user set userPassword=#{pwd} where id=#{id}
</update>

UserMapperTest.java

@Test
	public void testUpdatePwd() {
		SqlSession sqlSession = null;
		int count = 0;
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			count = sqlSession.getMapper(UserMapper.class).updatePwd(13, "123456");
			sqlSession.commit();
		}catch(Exception e) {
			e.printStackTrace();
			sqlSession.rollback();
			count = 0;
		}finally {
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		logger.debug("udpatePwd count = " + count);
	}

经验：

超过4个以上的参数最好封装成对象入参。

参数固定的业务方法，最好直接使用多参数入参。

五、delete

使用delete元素映射删除的语句

delete：

• id：命名空间唯一标识
• parameterType：传入参数类型

<delete id="deleteUserById" parameterType="int">
    delete from smbms_user where id=#{id}
</delete>

UserMapper.java

public int deleteUserById(@Param("id")Integer delId);

UserMapper.xml

<delete id="deleteUserById" parameterType="Integer">
		delete from smbms_user where id=#{id}
	</delete>

UserMapperTest.java

@Test
	public void testDeleteUserById(){
		SqlSession sqlSession = null;
		int count = 0;
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			count = sqlSession.getMapper(UserMapper.class).deleteUserById(18);
			sqlSession.commit();
		} catch(Exception e) {
			e.printStackTrace();
			sqlSession.rollback();
			count = 0;
		} finally {
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		logger.debug("testDeleteUserById count = " + count);
	}