03. Mybatis 环境搭建-完善单表的CRUD

笔者认为, 数据库的最基础的操作, 应该是单表的增删改查了吧. 让我们来完善一下对Employee 表的增删改查操作.

1. 完善Mapper接口 和 sql映射文件

1.1 完善Mapper 接口

• Mybatis 对于增删改操作, 支持自动封装成int, long, boolean 以及包装类型
• 在xml文件中通过${}引用参数时, 接口中必须使用@Param 注解定义参数

public interface EmployeeMapper {

    // 保存
    boolean save(EmployeePO employeePO);

    // 删除
    boolean delete(Long id);

    // 修改
    long update(EmployeePO employeePO);

    // 通过id查询
    EmployeePO findById(Long id);

    // 查询所有, 需要使用@Param 注解定义变量名
    List<EmployeePO> queryAll(@Param("orderType") String orderType);

    // 清空表
    void clear();

}

2.2 完善sql 映射文件

<?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类全限定名称 -->
<mapper namespace="org.zongf.learn.mybatis3.l01.mapper.EmployeeMapper">

    <!-- 新增 -->
    <insert id="save" useGeneratedKeys="true" keyProperty="id">
        insert into t_employee(id, name, sex, age, entryDate) values (null, #{name}, #{sex}, #{age}, #{entryDate})
    </insert>

    <delete id="delete">
        delete from t_employee where id = #{id}
    </delete>

    <!-- 更新 -->
    <update id="update">
        update t_employee
        set id = #{id}, name = #{name}, sex=#{sex}, entryDate =#{entryDate}
        where id = #{id}
    </update>

    <!-- 通过id 查询实体  -->
    <select id="findById" resultType="EmployeePO">
        select * from t_employee where id = #{id}
    </select>

    <!-- 查询所有, 使用${} 引用排序类型, 需要注意接口对应方法需要使用@Param修饰变量名 -->
    <select id="queryAll" resultType="EmployeePO">
        select * from t_employee order by id ${orderType}
    </select>

    <!-- 清空表中数据, 同时重置自增序列从0开始 -->
    <delete id="clear">
        truncate table t_employee;
    </delete>

</mapper>

2. 测试

2.1 测试工具类

• 笔者将加载Mybatis 配置文件, 获取Mapper封装成一个工具类, 用于便捷获取Mapper 对象
• 按道理而言, 每次都需要手工关闭sqlSession, 但是在单元测试中, 可以不关闭
• 一个sqlSession 相当于一次数据库会话, 每个单元测试用例都应该新打开一个sqlSession, 然后根据新的sqlSession 获取Mapper 对象

/**
 * @Description: SqlSessionUtil工具类
 * @author: zongf
 * @date: 2019-05-27 10:55
 */
public class SqlSessionUtil {

    private static SqlSession openSession(boolean autoCommitTx) {
        try {
            // 全局配置文件
            String globalConfigFile = "mybatis-config.xml";

            // 1. 使用配置文件构建SqlSessionFactory 工厂对象
            SqlSessionFactory  sqlSessionFactory = new SqlSessionFactoryBuilder()
                    .build(Resources.getResourceAsStream(globalConfigFile));

            // 2. 获取sqlSession,相当于数据库连接, sqlSession 是非线程安全的, 所以每次数据库操作都需要重新获取sqlSession对象
            return sqlSessionFactory.openSession(autoCommitTx);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * @Description: 获取Mapper 对象, 自动提交事务
     * @param clz Mapper 类型
     * @param autoCommitTx 是否需要自动提交事务
     * @return: T
     * @author: zongf
     * @time: 2019-05-27 15:29:06
     */
    public static <T> T getMapper(Class<T> clz, boolean autoCommitTx){
        return openSession(autoCommitTx).getMapper(clz);
    }

}

2.2 测试用例

public class Test_EmployeeMapper {

    // 测试请空表
    @Test
    public void clear() {
        EmployeeMapper employeeMapper = SqlSessionUtil.getMapper(EmployeeMapper.class, true);
        employeeMapper.clear();
    }

    // 测试保存方法
    @Test
    public void save(){

        EmployeeMapper employeeMapper = SqlSessionUtil.getMapper(EmployeeMapper.class, true);

        for (int i = 1; i <= 10; i++) {
            EmployeePO employeePO = new EmployeePO("zhangsan_" + i, 20, "M", LocalDate.of(2018, 05, i));

            boolean flag = employeeMapper.save(employeePO);

            // 校验是否保存成功
            Assert.assertEquals(true, flag);

            // 校验是否回填id
            Assert.assertNotNull(employeePO.getId());
        }
    }

    // 测试删除方法
    @Test
    public void delete(){
        EmployeeMapper employeeMapper = SqlSessionUtil.getMapper(EmployeeMapper.class, true);
        boolean flag = employeeMapper.delete(9L);
        Assert.assertEquals(true, flag);
    }

    // 测试查询方法
    @Test
    public void findById(){

        EmployeeMapper employeeMapper = SqlSessionUtil.getMapper(EmployeeMapper.class, false);

        EmployeePO employeePO = employeeMapper.findById(1L);

        Assert.assertNotNull(employeePO);
    }

    // 测试更新方法
    @Test
    public void update(){

        EmployeeMapper employeeMapper = SqlSessionUtil.getMapper(EmployeeMapper.class, true);

        EmployeePO employeePO = employeeMapper.findById(1L);

        employeePO.setName(employeePO.getId() + "_" + employeePO.getName());

        long flag = employeeMapper.update(employeePO);

        Assert.assertEquals(1L, flag);
    }

    // 测试查询所有方法
    @Test
    public void queryAll(){

        EmployeeMapper employeeMapper = SqlSessionUtil.getMapper(EmployeeMapper.class, true);

        List<EmployeePO> list = employeeMapper.queryAll("desc");

        list.forEach(System.out::println);
    }
}