2021-01-30

Mybatis--联合查询

1、新建Deptment.java，部门与员工一对多的关系。

public class Department {
	private Integer id;
	private String departmentName;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getDepartmentName() {
		return departmentName;
	}
	public void setDepartmentName(String departmentName) {
		this.departmentName = departmentName;
	}
	@Override
	public String toString() {
		return "Department [id=" + id + ", departmentName=" + departmentName + "]";
	}
	
	
}

public class Employee {
	
	private Integer id;
	private String lastName;
	private String email;
	private String gender;
	private Department dept;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getLastName() {
		return lastName;
	}
	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getGender() {
		return gender;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}
	
	
	public Department getDept() {
		return dept;
	}
	public void setDept(Department dept) {
		this.dept = dept;
	}
	
	public Employee() {
		super();
	}
	
	public Employee(Integer id, String lastName, String email, String gender) {
		super();
		this.id = id;
		this.lastName = lastName;
		this.email = email;
		this.gender = gender;
	}
	
	@Override
	public String toString() {
		return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]";
	}
	
	
}

2、新建tbl_dept数据库

3、xml配置

<!-- 查询Employee的同时查员工对应的部门 -->
	 <!-- 联合查询：级联属性封装结果集 -->
	 <resultMap type="com.fenga.mybatis.bean.Employee" id="MyDifEmp">
	 	<id column="id" property="id"/>
	 	<result column="last_name" property="lastName"/>
	 	<result column="gender" property="gender"/>
	 	<result column="did" property="dept.id"/>
	 	<result column="dept_name" property="dept.departmentName"/> 	
	 </resultMap>
	 <resultMap type="com.fenga.mybatis.bean.Employee" id="MyDifEmp2">
	 	<id column="id" property="id"/>
	 	<result column="last_name" property="lastName"/>
	 	<result column="gender" property="gender"/>
	 	<!-- association可以指定联合的javaBean对象
	 		property指定哪个属性是联合的对象
	 	 	javaType指定这个属性对象的类型[不能省略]
	 	 --> 	
	 	<association property="dept" javaType="com.fenga.mybatis.bean.Department">
	 		<id column="did" property="id"/>
	 		<result column="dept_name" property="departmentName"/>
	 	</association>
	 </resultMap>
	 <select id="getEmpAndDept" resultMap="MyDifEmp2">
	 	SELECT e.id id,e.last_name last_name,e.gender gender,e.d_id d_id,
		d.id did,d.dept_name dept_name FROM tbl_employee e,tbl_dept d
		WHERE e.d_id=d.id AND e.id=1
	 </select>

4、结果