22.关联查询多对一

Posted on Edited on In

 

方法一

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
package com.course.vo;

import java.util.Date;

public class Employee {

	private Integer employee_id;
	private String employee_name;
	private String employee_gender;
	private Integer age;
	private Date hire_date;
	private Integer department_id;
	
	public Employee() {
		super();
	}

	public Employee(Integer employee_id, String employee_name, String employee_gender, Integer age, Date hire_date,
			Integer department_id, String department_name, String location) {
		super();
		this.employee_id = employee_id;
		this.employee_name = employee_name;
		this.employee_gender = employee_gender;
		this.age = age;
		this.hire_date = hire_date;
		this.department_id = department_id;
	}

	public Integer getEmployee_id() {
		return employee_id;
	}

	public void setEmployee_id(Integer employee_id) {
		this.employee_id = employee_id;
	}

	public String getEmployee_name() {
		return employee_name;
	}

	public void setEmployee_name(String employee_name) {
		this.employee_name = employee_name;
	}

	public String getEmployee_gender() {
		return employee_gender;
	}

	public void setEmployee_gender(String employee_gender) {
		this.employee_gender = employee_gender;
	}

	public Integer getAge() {
		return age;
	}

	public void setAge(Integer age) {
		this.age = age;
	}

	public Date getHire_date() {
		return hire_date;
	}

	public void setHire_date(Date hire_date) {
		this.hire_date = hire_date;
	}

	public Integer getDepartment_id() {
		return department_id;
	}

	public void setDepartment_id(Integer department_id) {
		this.department_id = department_id;
	}

	@Override
	public String toString() {
		return "Employee [employee_id=" + employee_id + ", employee_name=" + employee_name + ", employee_gender="
				+ employee_gender + ", age=" + age + ", hire_date=" + hire_date + ", department_id=" + department_id
				+ "]";
	}
	
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
package com.course.vo;

import java.util.HashSet;
import java.util.Set;

public class Department {

	private Integer department_id;
	private String department_name;
	private String location;
	
	private Set<Employee> employees = new HashSet<>();
	
	public Department() {
		super();
	}

	public Department(String department_name, String location) {
		super();
		this.department_name = department_name;
		this.location = location;
	}
	
	public Integer getDepartment_id() {
		return department_id;
	}
	public void setDepartment_id(Integer department_id) {
		this.department_id = department_id;
	}
	public String getDepartment_name() {
		return department_name;
	}
	public void setDepartment_name(String department_name) {
		this.department_name = department_name;
	}
	public String getLocation() {
		return location;
	}
	public void setLocation(String location) {
		this.location = location;
	}

	public Set<Employee> getEmployees() {
		return employees;
	}

	public void setEmployees(Set<Employee> employees) {
		this.employees = employees;
	}

	@Override
	public String toString() {
		return "Department [department_id=" + department_id + ", department_name=" + department_name + ", location="
				+ location + ", employees=" + employees + "]";
	}

}

Dao

1
2
3
4
5
6
7
8
9
10
11
package com.course.dao;

import java.util.List;

import com.course.vo.Department;

public interface DepartmentDao {

	public List<Department> getAllDepartment();
	
}

DepartmentMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<?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.course.dao.DepartmentDao">
	
	<resultMap id="myDepartmentMapBase" type="Department">
		<id property="department_id" column="department_id" />
		<result property="department_name" column="department_name" />
		<result property="location" column="location" />
	</resultMap>
	
	<resultMap id="myDepartmentMap" type="Department" extends="myDepartmentMapBase">
		<collection property="employees" javaType="Employee">
			<id property="employee_id" column="employee_id" />
			<result property="employee_name" column="employee_name" />
			<result property="employee_gender" column="employee_gender" />
			<result property="age" column="age" />
			<result property="hire_date" column="hire_date" />
			<result property="department_id" column="department_id" />
		</collection>
	</resultMap>
	
	<select id="getAllDepartment" resultMap="myDepartmentMap">
		select * from department d
		left join employee e
		on d.department_id = e.department_id
	</select>
</mapper>

test

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
package com.course.test;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.course.dao.DepartmentDao;
import com.course.util.MyBatisUtil;
import com.course.vo.Department;

public class MainTest {

	private SqlSession sqlSession = MyBatisUtil.openSqlSession();
	private DepartmentDao departmentDao = sqlSession.getMapper(DepartmentDao.class);
	
	@Test
	public void queryDepartment1() {
		List<Department> departments = departmentDao.getAllDepartment();
		for (Department department : departments) {
			System.out.println(department);
		}
		
		MyBatisUtil.closeSqlSession(sqlSession);
	}	
}
1
2
3
4
5
6
7
8
9
DEBUG [main] - ==>  Preparing: select * from department d left join employee e on d.department_id = e.department_id 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 11
Department [department_id=1, department_name=Retail Department, location=5 floor, employees=[Employee [employee_id=6, employee_name=staff6, employee_gender=F, age=36, hire_date=Mon Sep 06 00:00:00 CST 2021, department_id=1], Employee [employee_id=1, employee_name=staff1, employee_gender=M, age=31, hire_date=Wed Sep 01 00:00:00 CST 2021, department_id=1]]]
Department [department_id=2, department_name=Operations Management Department, location=6 floor, employees=[Employee [employee_id=7, employee_name=staff7, employee_gender=M, age=37, hire_date=Tue Sep 07 00:00:00 CST 2021, department_id=2], Employee [employee_id=2, employee_name=staff2, employee_gender=F, age=32, hire_date=Thu Sep 02 00:00:00 CST 2021, department_id=2]]]
Department [department_id=3, department_name=Information technology department, location=9 floor, employees=[Employee [employee_id=8, employee_name=staff8, employee_gender=F, age=38, hire_date=Wed Sep 08 00:00:00 CST 2021, department_id=3], Employee [employee_id=3, employee_name=staff3, employee_gender=M, age=33, hire_date=Fri Sep 03 00:00:00 CST 2021, department_id=3]]]
Department [department_id=4, department_name=accounting department, location=10 floor, employees=[Employee [employee_id=9, employee_name=staff9, employee_gender=M, age=39, hire_date=Thu Sep 09 00:00:00 CST 2021, department_id=4], Employee [employee_id=4, employee_name=staff4, employee_gender=F, age=34, hire_date=Sat Sep 04 00:00:00 CST 2021, department_id=4]]]
Department [department_id=5, department_name=Marketing Department, location=12 floor, employees=[Employee [employee_id=10, employee_name=staff10, employee_gender=F, age=40, hire_date=Fri Sep 10 00:00:00 CST 2021, department_id=5], Employee [employee_id=5, employee_name=staff5, employee_gender=M, age=35, hire_date=Sun Sep 05 00:00:00 CST 2021, department_id=5]]]
Department [department_id=6, department_name=personnel department, location=15 floor, employees=[Employee [employee_id=null, employee_name=null, employee_gender=null, age=null, hire_date=null, department_id=6]]]

方法二

Dao

1
2
3
4
5
6
7
8
9
10
11
package com.course.dao;

import java.util.List;

import com.course.vo.Department;

public interface DepartmentDao {
	
	public List<Department> getAllDepartment2();
	
}

DepartmentMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<?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.course.dao.DepartmentDao">
	
	<resultMap id="myDepartmentMapBase" type="Department">
		<id property="department_id" column="department_id" />
		<result property="department_name" column="department_name" />
		<result property="location" column="location" />
	</resultMap>
	
	<resultMap id="myDepartmentMap2" type="Department" extends="myDepartmentMapBase">
		<!-- 
		ofType="Employee": 集合存放的对象的数据类型，也可以省略不写
        <collection property="employees" 
			column="department_id" select="com.course.dao.EmployeeDao.getEmployeeByDepartmentId">
		</collection>
		 -->
		<collection property="employees" ofType="Employee" 
			column="department_id" select="com.course.dao.EmployeeDao.getEmployeeByDepartmentId">
		</collection>
	</resultMap>
	
	<select id="getAllDepartment2" resultMap="myDepartmentMap2">
		select * from department d
	</select>
	
</mapper>

EmployeeMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?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.course.dao.EmployeeDao">
	
	<select id="getEmployeeByDepartmentId" resultType="Employee">
		select * from employee 
		where department_id = #{value}
	</select>
	
</mapper>

test

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
package com.course.test;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.course.dao.DepartmentDao;
import com.course.util.MyBatisUtil;
import com.course.vo.Department;

public class MainTest {

	private SqlSession sqlSession = MyBatisUtil.openSqlSession();
	private DepartmentDao departmentDao = sqlSession.getMapper(DepartmentDao.class);
	
	@Test
	public void queryDepartment2() {
		List<Department> departments = departmentDao.getAllDepartment2();
		for (Department department : departments) {
			System.out.println(department);
		}
		
		MyBatisUtil.closeSqlSession(sqlSession);
	}	
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
DEBUG [main] - ==>  Preparing: select * from department d 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - ====>  Preparing: select * from employee where department_id = ? 
DEBUG [main] - ====> Parameters: 1(Integer)
DEBUG [main] - <====      Total: 2
DEBUG [main] - ====>  Preparing: select * from employee where department_id = ? 
DEBUG [main] - ====> Parameters: 2(Integer)
DEBUG [main] - <====      Total: 2
DEBUG [main] - ====>  Preparing: select * from employee where department_id = ? 
DEBUG [main] - ====> Parameters: 3(Integer)
DEBUG [main] - <====      Total: 2
DEBUG [main] - ====>  Preparing: select * from employee where department_id = ? 
DEBUG [main] - ====> Parameters: 4(Integer)
DEBUG [main] - <====      Total: 2
DEBUG [main] - ====>  Preparing: select * from employee where department_id = ? 
DEBUG [main] - ====> Parameters: 5(Integer)
DEBUG [main] - <====      Total: 2
DEBUG [main] - ====>  Preparing: select * from employee where department_id = ? 
DEBUG [main] - ====> Parameters: 6(Integer)
DEBUG [main] - <====      Total: 0
DEBUG [main] - <==      Total: 6
Department [department_id=1, department_name=Retail Department, location=5 floor, employees=[Employee [employee_id=1, employee_name=staff1, employee_gender=M, age=31, hire_date=Wed Sep 01 00:00:00 CST 2021, department_id=1], Employee [employee_id=6, employee_name=staff6, employee_gender=F, age=36, hire_date=Mon Sep 06 00:00:00 CST 2021, department_id=1]]]
Department [department_id=2, department_name=Operations Management Department, location=6 floor, employees=[Employee [employee_id=7, employee_name=staff7, employee_gender=M, age=37, hire_date=Tue Sep 07 00:00:00 CST 2021, department_id=2], Employee [employee_id=2, employee_name=staff2, employee_gender=F, age=32, hire_date=Thu Sep 02 00:00:00 CST 2021, department_id=2]]]
Department [department_id=3, department_name=Information technology department, location=9 floor, employees=[Employee [employee_id=3, employee_name=staff3, employee_gender=M, age=33, hire_date=Fri Sep 03 00:00:00 CST 2021, department_id=3], Employee [employee_id=8, employee_name=staff8, employee_gender=F, age=38, hire_date=Wed Sep 08 00:00:00 CST 2021, department_id=3]]]
Department [department_id=4, department_name=accounting department, location=10 floor, employees=[Employee [employee_id=4, employee_name=staff4, employee_gender=F, age=34, hire_date=Sat Sep 04 00:00:00 CST 2021, department_id=4], Employee [employee_id=9, employee_name=staff9, employee_gender=M, age=39, hire_date=Thu Sep 09 00:00:00 CST 2021, department_id=4]]]
Department [department_id=5, department_name=Marketing Department, location=12 floor, employees=[Employee [employee_id=10, employee_name=staff10, employee_gender=F, age=40, hire_date=Fri Sep 10 00:00:00 CST 2021, department_id=5], Employee [employee_id=5, employee_name=staff5, employee_gender=M, age=35, hire_date=Sun Sep 05 00:00:00 CST 2021, department_id=5]]]
Department [department_id=6, department_name=personnel department, location=15 floor, employees=[]]