myBatis系列之三：增删改查

一.查询 

        myBatis系列之二：以接口方式交互数据实现了单条记录的查询，这里查询一组记录： 

        IUserMapper接口添加下面方法： 

List<User> getUsers(String name);

        在User.xml中添加： 

<resultMap type="User" id="userList"><!-- type为返回列表元素的类全名或别名 -->
	<id column="id" property="id" />
	<result column="name" property="name" />
	<result column="age" property="age" />
	<result column="address" property="address" />
</resultMap>

<select id="getUsers" parameterType="string" resultMap="userList"><!-- resultMap为上面定义的User列表 -->
	select * from `user` where name like #{name}
</select>

        测试方法： 

@Test
public void queryListTest() {
	SqlSession session = sqlSessionFactory.openSession();
	try {
		IUserMapper mapper = session.getMapper(IUserMapper.class);
		List<User> users = mapper.getUsers("%a%");//%在sql里代表任意个字符
		for(User user : users) {
			log.info("{}:{}", user.getName(), user.getAddress());
		}
	}finally {
		session.close();
	}
}

 

二.新增 

        IUserMapper接口添加下面方法： 

int addUser(User user);

        User.xml添加： 

<insert id="addUser" parameterType="User" useGeneratedKeys="true" keyProperty="id">
	<!-- useGeneratedKeys指定myBatis使用数据库自动生成的主键，并填充到keyProperty指定的属性上。如果未指定，返回对象拿不到生成的值 -->
	insert into user(name,age,address) values(#{name},#{age},#{address})
</insert>

        测试方法： 

@Test
public void addUserTest() {
	User user = new User("Lucy", 102, "Happy District");
	SqlSession session = sqlSessionFactory.openSession();
	try {
		IUserMapper mapper = session.getMapper(IUserMapper.class);
		int affectedCount = mapper.addUser(user);
		log.info("autoCommit:{}", session.getConnection().getAutoCommit());
		session.commit(); // 默认为不自动提交。调用session.getConnection().getAutoCommit()查看
		log.info("{} new record was inserted successfully whose id: {}", affectedCount, user.getId());
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		session.close();
	}
}

 

三.更新 

        接口添加方法： 

int updateUser(User user);

        User.xml添加： 

<update id="updateUser" parameterType="User">
	update `user` set name=#{name}, age=#{age}, address=#{address}
	where id=#{id}
</update>

        测试方法： 

@Test
public void updateUserTest() {
	SqlSession session = sqlSessionFactory.openSession();
	try {
		IUserMapper mapper = session.getMapper(IUserMapper.class);
		User user = mapper.getUserById(3);
		user.setAddress("Satisfied District");
		// 除了要修改的属性外，user的其它属性也要赋值，否则这些属性会被数据库更新为初始值（null或0等），可以先查询一次，但这样会增加和数据库不必要的交互。后面的条件判断能避免此问题。
		int affectedCount = mapper.updateUser(user);
		log.info("Affected count: {}", affectedCount);
		session.commit();
	} finally {
		session.close();
	}
}

 

四.删除 

        接口添加方法： 

int deleteUser(int id);

        User.xml添加：

<delete id="deleteUser" parameterType="int">
	delete from `user` where id=#{id}
</delete>

        测试方法：

@Test
public void deleteUserTest() {
	SqlSession session = sqlSessionFactory.openSession();
	try {
		IUserMapper mapper = session.getMapper(IUserMapper.class);
		int affectedCount = mapper.deleteUser(4);
		log.info("Affected count:{}",affectedCount);
		session.commit();
	}finally {
		session.close();
	}
}

 

文章来源：http://czj4451.iteye.com/blog/1982653