myBatis系列之三：增删改查

布满荆棘的人生 2022-04-11 06:22 340阅读 0赞

**一.查询 **

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

IUserMapper接口添加下面方法：

List<User> getUsers(String name);

在User.xml中添加：

<resultMap type="User" id="userList">
    	<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">
    	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">
    	
    	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][http_czj4451.iteye.com_blog_1982653]

[http_czj4451.iteye.com_blog_1982653]: http://czj4451.iteye.com/blog/1982653