03 映射文件-增删改查

增删改查

<?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.atguigu.mybatis.dao.EmployeeMapper">

    <!--
  		namespace:名称空间；指定为接口的全类名
       id：唯一标识
       resultType：返回值类型
       #{id}：从传递过来的参数中取出id
    -->

    <select id="selectEmp" resultType="com.atguigu.mybatis.bean.Employee">
        select id,last_name lastName,email,gender from tbl_employee where id = #{id}
    </select>


<!--    public void addEmp(Employee employee);-->
<!--    parameterType 可以省略
        mysql支持自增主键，自增主键值的获取，mybatis也是利用statement.getGeneratedKeys()
        useGeneratedKeys="true" 使用自增主键获取主键值策略
        keyProperty:指定对应的主键属性，也就是mybatis获取到主键值以后，将这个值封装给javaBean的哪个属性
-->
    <insert id="addEmp" parameterType="com.atguigu.mybatis.bean.Employee" useGeneratedKeys="true" keyProperty="id">

        insert into tbl_employee(last_name,email,gender)
        values (#{lastName},#{email},#{gender})
    </insert>

    <!--
        Oracle不支持自增：Oracle使用序列来模拟自增
        每次插入的数据的主键是从序列中拿到的值，如何获取到这个值
    -->

    <insert id="addEmp" databaseId="oracle">
        <!--  keyProperty：查处的主键值封装给javaBean的哪个属性
              order:当前 sql 在插入sql之前运行
              resultType:返回值类型

              BEFORE运行顺序：
                    先运行selectKey查询id的sql，查出id值封装给javaBean的id属性
                    在运行插入的sql，就可以取出id属性的值
             AFTER运行顺序
                先运行插入的sql，从序列中取出新值作为id
                再运行selectKey 查询id 的 key
        -->
        <selectKey keyProperty="id" order="BEFORE" resultType="Integer">
            <!-- 编写查询主键的sql语句-->
            select EMPLOYEES_SEQ.nextval from dual
        </selectKey>

        <!-- 插入时的主键是从序列中拿到的-->
        isnert into employees(EMPLOYEE_ID,LAST_NAME,EMAIL)
        values(#{id},#{lastName},#{email},#{gender})
    </insert>



<!--    public void updateEmp(Employee employee);-->

    <update id="updateEmp">
        update tbl_employee
        set last_name = #{lastName},gender=#{gender},email=#{email}
        where id = #{id}
    </update>

<!--    public void deleteEmpById(Integer id);-->
    <delete id="deleteEmpById">
        delete from tbl_employee where id=#{id}
    </delete>


</mapper>

    //测试增删改
    /*
    1. mybatis 允许增删改直接定义以下类型返回值
               Long Boolean Integer
    2. 需要手动提交数据

        sqlSessionFactory.openSession()===> 需要手动提交
sqlSessionFactory.openSession(true)===> 自动提交
     */

    @Test
    public void test03() throws IOException {
        String resources = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resources);

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession openSession = sqlSessionFactory.openSession();


        try {
            EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
            //测试添加
//            Employee employee= new Employee(null,"jerry","jerry@guigu.com","1");
//            mapper.addEmp(employee);

            //测试修改
//            Employee employee= new Employee(1,"jerry","jerry@guigu.com","0");
//            mapper.updateEmp(employee);

            //测试删除
            mapper.deleteEmpById(1);

            //手动提交
            openSession.commit();
        } finally {
            openSession.close();
        }
        
    }