MyBatis自关联一对多和多对一查询
自关联查询就是自己充当多方,同时也充当一方,即多和一都在同一张表中,一般这样的表其实可以看做是一个树形结构,在数据库表中有一个外键,该外键表示当前数据的父节点。下面以公司职位为例创建一张自关联的表。
- 建表语句
CREATE TABLE `t_employee` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`job` varchar(20) DEFAULT NULL,
`mgr` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
id:主键
name:员工姓名
job:工作岗位
mgr:上级领导的id,充当外键
- 插入数据
INSERT INTO `t_employee` VALUES (1001,'king','董事长',NULL),(1002,'jack','技术总监',1001),(1003,'paul','财务总监',1001),(1004,'tom','销售总监',1001),(1005,'tomas','技术一部经理',1002),(1006,'linda','技术二部经理',1002),(1007,'lucy','会计',1003),(1008,'lily','出纳',1003),(1009,'terry','销售一部经理',1004),(1010,'emma','销售二部经理',1004),(1011,'may','软件工程师',1005),(1012,'bella','软件工程师',1005),(1013,'kelly','软件工程师',1006);
model层
package com.h3c.bean;
public class Employee {
private int id;
private String name;
private String job;
//用于一对多,表示多的一方,即当前员工的所有下属
private List<Employee> childrens;
//用于多对一,表示当前员工的上级领导
private Employee leader;
//省略constructor、getter、setter
@Override
public String toString(){
return "Employee{" +
"id=" + id +
", name='" + name + '\'' +
", job='" + job+ '\'' +
", children='" + children +
", leader='" + leader +
'}';
}
}
dao层
package com.h3c.dao;
import com.h3c.bean.Employee;
import java.util.List;
public interface EmployeeDao {
//一对多查询,查询所有下属
List<Employee> selectChildrensById(int mgr);
//一对多查询,查询自己及所有下属
List<Employee> selectEmployeeById(int mgr);
//多对一查询,查询当前员工的直属领导
Employee selectLeaderById(int id);
}
mapper
<?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.h3c.dao.EmployeeDao">
<!--形成递归查询-->
<resultMap id="ChildrenMap" type="Employee">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="job" property="job"/>
<collection property="childrens"
ofType="Employee"
select="selectChildrensById"
column="id"/>
</resultMap>
<!--一对多,查询所有下属-->
<select id="selectChildrensById" resultMap="ChildrenMap">
SELECT id, name ,job
FROM t_employee
WHERE mgr=#{id}
</select>
<!--一对多,查询自己及所有下属-->
<select id="selectEmployeeById" resultMap="ChildrenMap">
SELECT id, name, job
FROM t_employee
WHERE id=#{id}
</select>
<!--多对一-->
<resultMap id="LeaderMap" type="Employee">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="job" property="job"/>
<association property="leader"
javaType="Employee"
select="selectLeaderById"
column="mgr"/>
</resultMap>
<select id="selectLeaderById" resultMap="LeaderMap">
SELECT id, name, job, mgr
FROM t_employee
WHERE id=#{id}
</select>
</mapper>