EmployeeMapper.xml例子，學習佟剛老師的myBatis課程，記錄下的EmployeeMapper.xml，注釋詳細

標籤：bean   select   序列   mys   如何擷取   add   如何   oracle   into   

<?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值
這個是介面裡面的方法，擷取資料對象：public Employee getEmpById(Integer id);

-->

<select id="getEmpById" resultType="com.atguigu.mybatis.bean.Employee" databaseId="mysql">
select id, last_name LastName, email from tbl_employee where id = #{id}
</select>

<select id="getEmpById" resultType="com.atguigu.mybatis.bean.Employee" databaseId="oracle">
select EMPLOYEE_ID id, LAST_NAME lastName, GENDER gender, EMAIL email
from EMPLOYEE where EMPLOYEE_ID = #{id}
</select>

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

<!-- public void addEmp(Employee employee); -->
<!--擷取自增主鍵的值：
MYSQL支援自增主鍵，自增主索引值的擷取，myBatis也是利用Statement.getGeneratedKeys()來擷取
在insert標籤中添加useGeneratedKeys="true";使用自增主鍵擷取主索引值策略
keyProperty:用來指定擷取到的主索引值賦值到這個值中，這個值可以封裝到JavaBean的Employee.id中
-->
<!-- useGeneratedKeys="true" keyProperty="id" -->
<insert id="addEmp" databaseId="mysql">
insert into tbl_employee(id, last_name, gender, email) values(#{id}, #{lastName}, #{gender}, #{email})
</insert>
<!-- Oracle不支援自增，但是Oracle可以利用序列來類比自增
每次插入的資料的主索引值是從序列中拿到的值；如何擷取到這個值呢？
-->
<!-- keyProperty:查出主鍵並且封裝給JavaBean的屬性ID
order = "BEFORE"指定當前SQL在插入操作之前運行
= "AFTER" 指定在當前的SQL之後
resultType：查出來的主鍵要封裝的類型是什麼，需要在這裡指定
BEFORE運行順序： 先運行selectKey查詢id的SQL:查出id封裝給javaBean的id屬性
再運行插入的SQL，就可以取出id屬性對應的值
AFTER 運行順序： 先運行插入的SQL（從序列中取出新值作為id
再運行selectKey查出id的SQL-->
<insert id="addEmp" databaseId="oracle" >
<!-- <selectKey keyProperty="id" order = "BEFORE">
select EMPLOYEE_SEQ.nextVal from dual
</selectKey> -->
<!-- 插入時的主鍵是從序列中拿到的-->
<!-- BEFORE --><!-- ,jdbcType=NULL -->
insert into EMPLOYEE(EMPLOYEE_ID, LAST_NAME,GENDER,EMAIL) values(#{id}, #{lastName}, #{gender}, #{email})
</insert>

<!-- 還可以這麼來寫 -->
<!-- AFTER -->
<!--
<insert id="addEmp" databaseId="oracle" >
<selectKey keyProperty="id" order = "AFTER">
select EMPLOYEE_SEQ.currVal from dual</selectKey>
-->
<!--insert into EMPLOYEE(EMPLOYEE_ID, LAST_NAME,GENDER,EMAIL)
values(EMPLOYEE_SEQ.nextVal, #{lastName}, #{gender}, #{email}) -->
<!-- 多參數查詢 -->
<select id="getEmpByIdAndLastName" resultType="com.atguigu.mybatis.bean.Employee">
select * from EMPLOYEE where EMPLOYEE_ID = #{id} and LAST_NAME = #{lastName}
</select>
<!-- 多參數查詢，使用map封裝參數 -->
<select id="getEmpByMap" resultType="com.atguigu.mybatis.bean.Employee">
select * from EMPLOYEE where EMPLOYEE_ID = #{id} and LAST_NAME = #{lastName}
</select>
<!-- public void deleteEmp(Integer id); -->
<delete id="deleteEmpById">
delete from EMPLOYEE where EMPLOYEE_ID = #{id}
</delete>

<!-- public void updateEmp(Employee employee); -->
<update id="updateEmp">
update EMPLOYEE
set LAST_NAME = #{lastName}, EMAIL = #{email}, GENDER = #{gender}
where id = #{id}
</update>

</mapper>

EmployeeMapper.xml例子，學習佟剛老師的myBatis課程，記錄下的EmployeeMapper.xml，注釋詳細