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

不念不忘少年蓝@ 2024-04-17 14:56 67阅读 0赞

笔者认为, 数据库的最基础的操作, 应该是单表的增删改查了吧. 让我们来完善一下对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 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>
    
        
        <select id="findById" resultType="EmployeePO">
            select * from t_employee where id = #{id}
        </select>
    
        
        <select id="queryAll" resultType="EmployeePO">
            select * from t_employee order by id ${orderType}
        </select>
    
        
        <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);
        }
    }