※事前規劃
一個部門有很多員工，多個員工屬於一個部門
DROP TABLE DEPARTMENT; DROP TABLE EMPLOYEE; DROP SEQUENCE EMPLOYEE_SEQUENCE; CREATE TABLE DEPART( DEPTNO NUMBER(9), DNAME VARCHAR(20), CONSTRAINT DEPART_PK PRIMARY KEY(DEPTNO) ); COMMENT ON TABLE DEPART IS '部門表'; COMMENT ON COLUMN DEPART.DEPTNO IS '部門代號'; COMMENT ON COLUMN DEPART.DNAME IS '部門名稱'; CREATE TABLE EMPLOYEE( EMPNO NUMBER(9), ENAME VARCHAR(10), DEPTNO NUMBER(9), CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EMPNO), CONSTRAINT EMPLOYEE_FK FOREIGN KEY(DEPTNO) REFERENCES DEPART(DEPTNO) ON DELETE CASCADE ); COMMENT ON TABLE EMPLOYEE IS '員工表'; COMMENT ON COLUMN EMPLOYEE.EMPNO IS '員工編號'; COMMENT ON COLUMN EMPLOYEE.ENAME IS '員工姓名'; COMMENT ON COLUMN EMPLOYEE.DEPTNO IS '部門代號'; CREATE SEQUENCE EMPLOYEE_SEQUENCE; INSERT INTO DEPART VALUES(10, '財務部'); INSERT INTO DEPART VALUES(20, '研發部'); INSERT INTO DEPART VALUES(30, '業務部'); INSERT INTO DEPART VALUES(40, '生管部'); INSERT INTO EMPLOYEE VALUES(EMPLOYEE_SEQUENCE.NEXTVAL, 'king', 10); INSERT INTO EMPLOYEE VALUES(EMPLOYEE_SEQUENCE.NEXTVAL, 'blake', 30); INSERT INTO EMPLOYEE VALUES(EMPLOYEE_SEQUENCE.NEXTVAL, 'clark', 10); INSERT INTO EMPLOYEE VALUES(EMPLOYEE_SEQUENCE.NEXTVAL, 'jones', 20); INSERT INTO EMPLOYEE VALUES(EMPLOYEE_SEQUENCE.NEXTVAL, 'martin', 30); INSERT INTO EMPLOYEE VALUES(EMPLOYEE_SEQUENCE.NEXTVAL, 'allen', 30); INSERT INTO EMPLOYEE VALUES(EMPLOYEE_SEQUENCE.NEXTVAL, 'turner', 30); INSERT INTO EMPLOYEE VALUES(EMPLOYEE_SEQUENCE.NEXTVAL, 'james', 30); INSERT INTO EMPLOYEE VALUES(EMPLOYEE_SEQUENCE.NEXTVAL, 'ward', 30); INSERT INTO EMPLOYEE VALUES(EMPLOYEE_SEQUENCE.NEXTVAL, 'ford', 20); INSERT INTO EMPLOYEE VALUES(EMPLOYEE_SEQUENCE.NEXTVAL, 'smith', 20); INSERT INTO EMPLOYEE VALUES(EMPLOYEE_SEQUENCE.NEXTVAL, 'scott', 20); INSERT INTO EMPLOYEE VALUES(EMPLOYEE_SEQUENCE.NEXTVAL, 'adams', 20); INSERT INTO EMPLOYEE VALUES(EMPLOYEE_SEQUENCE.NEXTVAL, 'miller', 20);
※開始戰鬥
兩張類也加一下，一對多可用List或Setpublic class Department {
private Integer deptno;
private String dname;
private Set employees;
//setter/getter...
}
public class Employee {
private Integer empno;
private String ename;
private Integer deptno;
private Department department;
//setter/getter...
}
Employee.xml是多對一，所以要設定「一」
<mapper namespace="Emp">
<sql id="column">
EMPNO, ENAME, DEPTNO
</sql>
<resultMap type="Employee" id="EmpInterface">
<id property="empno" column="EMPNO" />
<result property="ename" column="ENAME" />
<result property="deptno" column="DEPTNO" />
<!-- 多個員工有「一」個部門 -->
<association property="department" column="DEPTNO"
javaType="Department" select="Dept.findById" />
<!-- resultMap="Dept.DeptInterface" /> 用這個非關聯字段會null，和select擇其一-->
</resultMap>
<select id="findAllByDepartment" resultMap="EmpInterface">
SELECT <include refid="column" />
FROM EMPLOYEE
<where>
DEPTNO = #{ooxx}
</where>
</select>
<select id="findById" parameterType="java.lang.Integer"
resultMap="EmpInterface">
SELECT <include refid="column" />
FROM EMPLOYEE
<where>
EMPNO = #{ooo}
</where>
</select>
</mapper>
Department.xml是一對多，所以要設定「多」，這是一對一沒有的
javaType="java.util.Set" ofType="Employee"，其實就是Set<Employee>
<mapper namespace="Dept">
<sql id="column">
DEPTNO, DNAME
</sql>
<resultMap type="Department" id="DeptInterface">
<id property="deptno" column="DEPTNO" />
<result property="dname" column="DNAME" />
<!-- 一個部門有很「多」員工 -->
<collection property="employees" column="DEPTNO" javaType="java.util.Set"
ofType="Employee" select="Emp.findAllByDepartment" />
</resultMap>
<select id="findById" parameterType="java.lang.Integer"
resultMap="DeptInterface">
SELECT <include refid="column" />
FROM DEPARTMENT
<where>
DEPTNO = #{xxx}
</where>
</select>
</mapper>
測試類：
SqlSession sqlSession = MybatisUtil.getSession();
System.out.println("--------------多對一測試--------------");
Employee e = sqlSession.selectOne("Emp.findById", 2);
System.out.println(e.getEmpno());
System.out.println(e.getEname());
System.out.println(e.getDeptno());
// 如果Employee.xml的resultMap的association用resultMap，非關聯字段會null；
Department dept = e.getDepartment();
System.out.println(dept.getDeptno());
System.out.println(dept.getDname() + "\r\n");//非關聯字段
System.out.println("--------------一對多測試--------------");
Department d = sqlSession.selectOne("Dept.findById", 20);
System.out.println(d.getDeptno());
System.out.println(d.getDname() + "\r\n");
Set<Employee> l = d.getEmployees();
for (Employee emp : l) {
System.out.println(emp.getEmpno());
System.out.println(emp.getEname());
System.out.println(emp.getDeptno());
System.out.println("================================");
}
sqlSession.commit();
MybatisUtil.closeSession();
※一對多關聯的兩種方式
<resultMap type="ooo" id="collection1">
<!-- java bean 就算和資料庫名稱一樣也要寫，不寫就是 null -->
<id column="deptno" property="deptNo" />
<result column="dname" property="dName" />
<!-- <result column="loc" property="loc" /> -->
<collection property="emps" javaType="java.util.Set" ofType="mp.bean.Emp">
<!-- java bean 就算和資料庫名稱一樣也要寫，不寫就是 null -->
<id column="empno" property="empno" />
<result column="ename" property="ename" />
</collection>
</resultMap>
<select id="getDeptAndEmpsById" resultMap="collection1">
select empno, ename, job, mgr, hiredate, sal, comm, e.deptno,
d.dname, d.loc
from emp e, dept d
where e.deptno = d.deptno
and d.deptno = #{id}
</select>
<resultMap type="ooo" id="collection2">
<!-- java bean 和 資料庫名稱一樣可不寫，但關聯字段一定要 -->
<id column="deptno" property="deptNo" />
<result column="dname" property="dName" />
<!-- <result column="loc" property="loc" /> -->
<collection property="emps" select="mp.bean.dao.EmpMapper.getEmpByDeptno"
javaType="java.util.Set" column="deptno" fetchType="lazy">
<!-- java bean 和 資料庫名稱一樣可不寫 -->
<id column="empno" property="empno" />
<result column="ename" property="ename" />
</collection>
</resultMap>
<select id="getDept2Step" resultMap="collection2">
select * from dept where deptno = #{id}
</select>
※一對一用的是 association；一對多用的是 collection，一對多只有兩種關聯方式，沒有用「.」的方式
※第一種是 SQL 的功，連 resultMap 裡的東西全部都要打才會有值
※第二種是分步的方式，和資料庫名稱一樣可不打，一樣和懶加載有關
※javaType 可以不寫，不管用 List 或 Set 去接都不會有問題，但要寫就一定要寫正確
沒有留言:
張貼留言