一. 前 言
在上一篇文章中,将mybatis配置好后,接下来用mybatis实现对数据库的增删改查,只需要改动两个文件即可。一个是mapper接口文件,另一个是对应的mapper.xml文件。
两个文件总体内容如下所示:
- mapper接口文件
package com.mybatis.mapper;
import com.mybatis.entity.User;
import java.util.List;
/**
* @Description User接口
* @InterfaceName UserMapper
* @Author yuhuofei
* @Date 2021/11/14 0:55
* @Version 1.0
*/
public interface UserMapper {
//查询所有的记录
List<User> getUserList();
//根据ID查询
User getUserById(int id);
//新增记录
Integer insertRecord(User user);
//更新记录
Integer updateRecord(User user);
//删除记录
Integer deleteRecord(User user);
}
- mapper.xml文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis.mapper.UserMapper">
<select id="getUserList" resultType="com.mybatis.entity.User">
select * from mybatis.user;
</select>
<select id="getUserById" parameterType="int" resultType="com.mybatis.entity.User">
select id,name,password
from mybatis.user
where id = #{id};
</select>
<insert id="insertRecord" parameterType="com.mybatis.entity.User">
insert into `user` (`id`,`name`,`password`)
values(#{id},#{name},#{passWord});
</insert>
<update id="updateRecord" parameterType="com.mybatis.entity.User">
update mybatis.user
set `name` = #{name},`password` = #{passWord}
where `id` = #{id};
</update>
<delete id="deleteRecord" parameterType="com.mybatis.entity.User">
delete
from mybatis.user
where id = #{id};
</delete>
</mapper>
- 测试类
package com.mybatis.entity;
import com.mybatis.mapper.UserMapper;
import com.mybatis.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
/**
* @Description 测试User
* @ClassName UserTest
* @Author yuhuofei
* @Date 2021/11/14 1:19
* @Version 1.0
*/
public class UserTest {
//查询所有记录
@Test
public void test() {
//1、获取SqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//2、执行SQL
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = userMapper.getUserList();
for (User user : userList) {
System.out.println("得到的数据是:" + user);
}
//3、关闭sql连接
sqlSession.close();
}
//根据ID查询记录
@Test
public void getUserById() {
//1、获取SqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//2、执行SQL
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.getUserById(1);
System.out.println(user);
//3、关闭sql连接
sqlSession.close();
}
//新增记录
@Test
public void insertRecord() {
//1、获取SqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//2、执行SQL
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setId(5);
user.setName("老妖");
user.setPassWord("365487");
Integer result = userMapper.insertRecord(user);
if (1 == result) {
System.out.println("新增数据成功!");
}
//3、提交事务
sqlSession.commit();
//4、关闭sql连接
sqlSession.close();
}
//更新记录
@Test
public void updateRecord() {
//1、获取SqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//2、执行SQL
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setId(1);
user.setName("王大");
user.setPassWord("003687");
Integer result = userMapper.updateRecord(user);
if (1 == result) {
System.out.println("更新数据成功!");
}
//3、提交事务
sqlSession.commit();
//4、关闭sql连接
sqlSession.close();
}
//删除记录
@Test
public void deleteRecord() {
//1、获取SqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//2、执行SQL
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setId(5);
Integer result = userMapper.deleteRecord(user);
if (1 == result) {
System.out.println("删除数据成功!");
}
//3、提交事务
sqlSession.commit();
//4、关闭sql连接
sqlSession.close();
}
}
二. CRUD的实现与测试
2.1 新增(insert)
- 在UserMapper接口中,新增一个方法
//新增记录
Integer insertRecord(User user);
- 在UserMapper.xml中新增数据库语句
<insert id="insertRecord" parameterType="com.mybatis.entity.User">
insert into `user` (`id`,`name`,`password`)
values(#{id},#{name},#{passWord});
</insert>
- 在测试类UserTest中,新增测试方法
//新增记录
@Test
public void insertRecord() {
//1、获取SqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//2、执行SQL
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setId(5);
user.setName("老妖");
user.setPassWord("365487");
Integer result = userMapper.insertRecord(user);
if (1 == result) {
System.out.println("新增数据成功!");
}
//3、提交事务
sqlSession.commit();
//4、关闭sql连接
sqlSession.close();
}
- 运行结果
2.2 删除(delete)
- 在UserMapper接口中,新增一个方法
//删除记录
Integer deleteRecord(User user);
- 在UserMapper.xml中新增数据库语句
<delete id="deleteRecord" parameterType="com.mybatis.entity.User">
delete
from mybatis.user
where id = #{id};
</delete>
- 在测试类UserTest中,新增测试方法
//删除记录
@Test
public void deleteRecord() {
//1、获取SqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//2、执行SQL
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setId(5);
Integer result = userMapper.deleteRecord(user);
if (1 == result) {
System.out.println("删除数据成功!");
}
//3、提交事务
sqlSession.commit();
//4、关闭sql连接
sqlSession.close();
}
2.3 修改(update)
- 在UserMapper接口中,新增一个方法
//更新记录
Integer updateRecord(User user);
- 在UserMapper.xml中新增数据库语句
<update id="updateRecord" parameterType="com.mybatis.entity.User">
update mybatis.user
set `name` = #{name},`password` = #{passWord}
where `id` = #{id};
</update>
- 在测试类UserTest中,新增测试方法
//更新记录
@Test
public void updateRecord() {
//1、获取SqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//2、执行SQL
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setId(1);
user.setName("王大");
user.setPassWord("003687");
Integer result = userMapper.updateRecord(user);
if (1 == result) {
System.out.println("更新数据成功!");
}
//3、提交事务
sqlSession.commit();
//4、关闭sql连接
sqlSession.close();
}
2.4 查询(select)
- 在UserMapper接口中,新增一个方法
//根据ID查询
User getUserById(int id);
- 在UserMapper.xml中新增数据库语句
<select id="getUserById" parameterType="int" resultType="com.mybatis.entity.User">
select id,name,password
from mybatis.user
where id = #{id};
</select>
- 在测试类UserTest中,新增测试方法
//根据ID查询记录
@Test
public void getUserById() {
//1、获取SqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//2、执行SQL
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.getUserById(1);
System.out.println(user);
//3、关闭sql连接
sqlSession.close();
}
三. 要注意的问题
- 使用mybatis对数据库进行增删改查操作,查询操作不需要提交事务,而新增、删除、修改三个操作,都需要提交事务,这是容易漏掉的地方。
- 对于新增、删除、修改操作,成功则返回1,失败则返回0。
- 对于mapper.xml中的
parameterType
:
(1)如果SQL语句需要的参数只有一个,比如只传参一个String类型或者int类型或者Boolean类型,parameterType
可以省略不写
(2)如果参数需要有多个,则需要传入实体类,如前面所示parameterType="com.mybatis.entity.User"