05. mybatis多表关联结果处理
1、准备数据
创建表：
dept表：
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`DEPTNO` int(2) NOT NULL,
`DNAME` varchar(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`LOC` varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`DEPTNO`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept` VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO `dept` VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO `dept` VALUES (40, 'OPERATIONS', 'BOSTON');
SET FOREIGN_KEY_CHECKS = 1;
emp表：
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`EMPNO` int(4) NOT NULL,
`ENAME` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`JOB` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`MGR` int(4) NULL DEFAULT NULL,
`HIREDATE` date NULL DEFAULT NULL,
`SAL` double(7, 2) NULL DEFAULT NULL,
`COMM` double(7, 2) NULL DEFAULT NULL,
`DEPTNO` int(2) NOT NULL,
PRIMARY KEY (`EMPNO`) USING BTREE,
INDEX `emp`(`DEPTNO`) USING BTREE,
CONSTRAINT `emp` FOREIGN KEY (`DEPTNO`) REFERENCES `dept` (`DEPTNO`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20);
INSERT INTO `emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30);
INSERT INTO `emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30);
INSERT INTO `emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20);
INSERT INTO `emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30);
INSERT INTO `emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30);
INSERT INTO `emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10);
INSERT INTO `emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000.00, NULL, 20);
INSERT INTO `emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10);
INSERT INTO `emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30);
INSERT INTO `emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100.00, NULL, 20);
INSERT INTO `emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30);
INSERT INTO `emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20);
INSERT INTO `emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 10);
SET FOREIGN_KEY_CHECKS = 1;
如上述两张表，互相有着关联关系：一个部门会有多名员工，一名员工附属于一个部门，因此建立了外键关系。
2、建立Po模型类
DeptPo.java
package com.company.project.Po;
import java.util.List;
public class DeptPo {
private int deptno;
private String dname;
private String loc;
private List<EmpPo> empPos;
public int getDeptno() {
return deptno;
}
public String getDname() {
return dname;
}
public String getLoc() {
return loc;
}
public List<EmpPo> getEmpPos() {
return empPos;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
public void setDname(String dname) {
this.dname = dname;
}
public void setLoc(String loc) {
this.loc = loc;
}
public void setEmpPos(List<EmpPo> empPos) {
this.empPos = empPos;
}
@Override
public String toString() {
return "DeptPo [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc + ", empPos=" + empPos + "]";
}
}
EmpPo.java
package com.company.project.Po;
import java.sql.Date;
public class EmpPo {
private int empno;
private String ename;
private String job;
private int mgr;
private Date hiredate;
private double sal;
private double comm;
private DeptPo deptPo;
public int getEmpno() {
return empno;
}
public String getEname() {
return ename;
}
public String getJob() {
return job;
}
public int getMgr() {
return mgr;
}
public Date getHiredate() {
return hiredate;
}
public double getSal() {
return sal;
}
public double getComm() {
return comm;
}
public DeptPo getDeptPo() {
return deptPo;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public void setEname(String ename) {
this.ename = ename;
}
public void setJob(String job) {
this.job = job;
}
public void setMgr(int mgr) {
this.mgr = mgr;
}
public void setHiredate(Date hiredate) {
this.hiredate = hiredate;
}
public void setSal(double sal) {
this.sal = sal;
}
public void setComm(double comm) {
this.comm = comm;
}
public void setDeptPo(DeptPo deptPo) {
this.deptPo = deptPo;
}
@Override
public String toString() {
return "EmpPo [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + ", hiredate=" + hiredate
+ ", sal=" + sal + ", comm=" + comm + ", deptPo=" + deptPo + "]";
}
}
3、建立Dao接口
EmpDao.java
package com.company.project.dao;
import java.util.List;
import java.util.Map;
import com.company.project.Po.DeptPo;
import com.company.project.Po.EmpPo;
public interface EmpDao {
EmpPo findById(int empno);
Map<String, Object> findById2(int empno);
}
DeptDao.java
package com.company.project.dao;
import java.util.List;
import com.company.project.Po.DeptPo;
import com.company.project.Po.EmpPo;
public interface DeptDao {
DeptPo findById(int deptno);
}
4、建立xml映射文件
利用 resultMap 来组合封装查询结果，利用association标签来组装属性对象的结果集。
EmpMapper.xml
<?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.company.project.dao.EmpDao">
<!-- 把重复的sql代码可以提炼出来，通过定义别名把sql列名和java属性对应 -->
<sql id="selectResult">
e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,d.DNAME,d.LOC
</sql>
<!-- sql查询结果与javaPo类的对应关系 -->
<resultMap type="EmpPo" id="EmpResult">
<id property="empno" column="empno"/>
<result property="ename" column="ename"/>
<result property="job" column="job"/>
<result property="mgr" column="mgr"/>
<result property="hiredate" column="hiredate"/>
<result property="sal" column="sal"/>
<result property="comm" column="comm"/>
<!--利用association标签，建立属性对象的映射 -->
<association property="deptPo" javaType="deptPo">
<id property="deptno" column="deptno"/>
<result property="dname" column="dname"/>
<result property="loc" column="loc"/>
</association>
</resultMap>
<!--第一种方式：利用resultMap定义查询结果的映射关系 -->
<select id="findById" parameterType="int" resultMap="EmpResult">
select
<include refid="selectResult"></include>
from
emp e
join
dept d
on
e.DEPTNO = d.DEPTNO
where e.empno = #{empno}
</select>
<!--第一种方式：利用map使用数据库列名接收结果 -->
<select id="findById2" parameterType="int" resultType="map">
select
<include refid="selectResult"></include>
from
emp e
join
dept d
on
e.DEPTNO = d.DEPTNO
where e.empno = #{empno}
</select>
</mapper>
DeptMapper.xml
<?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.company.project.dao.DeptDao">
<!-- sql查询结果与javaPo类的对应关系 -->
<resultMap type="DeptPo" id="deptMap">
<id column="deptno" property="deptno"/>
<result column="dname" property="dname"/>
<result column="loc" property="loc"/>
<!-- 将一个部门中 的多个员工封装到集合中 -->
<collection property="empPos" javaType="list" ofType="EmpPo">
<id property="empno" column="empno"/>
<result property="ename" column="ename"/>
<result property="job" column="job"/>
<result property="mgr" column="mgr"/>
<result property="hiredate" column="hiredate"/>
<result property="sal" column="sal"/>
<result property="comm" column="comm"/>
</collection>
</resultMap>
<select id="findById" parameterType="int" resultMap="deptMap">
SELECT
e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,d.DNAME,d.LOC
FROM
dept d
JOIN emp e ON d.deptno = e.deptno
WHERE
d.deptno = #{deptno}
</select>
</mapper>
在写完xml映射文件之后，需要在全局配置文件mybatis-config.xml文件中添加映射文件信息
<!-- 将我们写好的sql映射文件一定要注册到全局配置文件中 -->
<mappers>
<!-- 利用package标签可以直接把整个包的 xml配置文件都导入，这块是导入数据库表与java Po类对应的配置文件 -->
<mapper resource="com/company/project/mapper/EmpMapper.xml"/>
<mapper resource="com/company/project/mapper/DeptMapper.xml"/>
</mappers>
5、测试类
EmpTest.java
package com.company.project.test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import com.company.project.Po.EmpPo;
import com.company.project.dao.EmpDao;
import com.company.project.util.MyBatisUtil;
import junit.framework.TestCase;
public class EmpTest extends TestCase {
public void testFindById() {
SqlSession sqlSession = MyBatisUtil.getSqlSession();
EmpDao empDao = sqlSession.getMapper(EmpDao.class);
EmpPo empPo = empDao.findById(7369);
System.out.println(empPo);
}
}
DeptTest.java
package com.company.project.test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import com.company.project.Po.DeptPo;
import com.company.project.Po.EmpPo;
import com.company.project.dao.DeptDao;
import com.company.project.dao.EmpDao;
import com.company.project.util.MyBatisUtil;
import junit.framework.TestCase;
public class DeptTest extends TestCase {
public void testFindById() {
try {
SqlSession sqlSession = MyBatisUtil.getSqlSession();
DeptDao deptDao = sqlSession.getMapper(DeptDao.class);
DeptPo deptPo = deptDao.findById(20);
System.out.println(deptPo);
}catch (Exception e) {
e.printStackTrace();
}finally {
MyBatisUtil.closeSession();
}
}
}
