增删改查
<?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();
}
}