21.关联查询一对多

Posted on Edited on In

 

第一种方法

给vo添加属性，在查询的时候多表连接查询，查询出添加的字段

vo

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
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
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;
	
	private String department_name;
	private String location;
	
	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;
		this.department_name = department_name;
		this.location = location;
	}

	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;
	}

	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;
	}

	@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
				+ ", department_name=" + department_name + ", location=" + location + "]";
	}

}
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
package com.course.vo;

public class Department {

	private Integer department_id;
	private String department_name;
	private String location;
	
	
	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;
	}

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

dao

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

import java.util.List;

import com.course.vo.Employee;

public interface EmployeeDao {

	public List<Employee> getAllEmployees1();
	
}

mapper.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
<?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="getAllEmployees1" resultType="Employee">
		select e.employee_id,
			e.employee_name,
			(
			CASE e.employee_gender
			WHEN 'M' THEN '男'
			ELSE '女'
			END
			) as "employee_gender",
			e.age, e.hire_date, e.department_id,
			d.department_name, d.location 
		from employee e
		INNER JOIN department d
		ON e.department_id = d.department_id
	</select>
</mapper>

mybatis.cfg.xml

1
2
3
4
5
6
7
8
9
<!-- MyBatis的整体配置 -->
<configuration>
	
	<!-- 配置Mapper.xml文件 -->
	<mappers>
		<mapper resource="com/course/mapper/EmployeeMapper.xml" />
	</mappers>
	
</configuration>

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
27
package com.course.test;

import java.util.List;

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

import com.course.dao.EmployeeDao;
import com.course.util.MyBatisUtil;
import com.course.vo.Employee;

public class MainTest {

	private SqlSession sqlSession = MyBatisUtil.openSqlSession();
	private EmployeeDao employeeDao = sqlSession.getMapper(EmployeeDao.class);
	
	@Test
	public void queryUser01() {
		List<Employee> employeeList = employeeDao.getAllEmployees1();
		for (Employee employee : employeeList) {
			System.out.println(employee);
		}
		
		MyBatisUtil.closeSqlSession(sqlSession);
	}
	
}
1
2
3
4
5
6
7
8
9
10
11
12
13
DEBUG [main] - ==>  Preparing: select e.employee_id, e.employee_name, ( CASE e.employee_gender WHEN 'M' THEN '男' ELSE '女' END ) as "employee_gender", e.age, e.hire_date, e.department_id, d.department_name, d.location from employee e INNER JOIN department d ON e.department_id = d.department_id 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 10
Employee [employee_id=1, employee_name=staff1, employee_gender=男, age=31, hire_date=Wed Sep 01 00:00:00 CST 2021, department_id=1, department_name=Retail Department, location=5 floor]
Employee [employee_id=2, employee_name=staff2, employee_gender=女, age=32, hire_date=Thu Sep 02 00:00:00 CST 2021, department_id=2, department_name=Operations Management Department, location=6 floor]
Employee [employee_id=3, employee_name=staff3, employee_gender=男, age=33, hire_date=Fri Sep 03 00:00:00 CST 2021, department_id=3, department_name=Information technology department, location=9 floor]
Employee [employee_id=4, employee_name=staff4, employee_gender=女, age=34, hire_date=Sat Sep 04 00:00:00 CST 2021, department_id=4, department_name=accounting department, location=10 floor]
Employee [employee_id=5, employee_name=staff5, employee_gender=男, age=35, hire_date=Sun Sep 05 00:00:00 CST 2021, department_id=5, department_name=Marketing Department, location=12 floor]
Employee [employee_id=6, employee_name=staff6, employee_gender=女, age=36, hire_date=Mon Sep 06 00:00:00 CST 2021, department_id=1, department_name=Retail Department, location=5 floor]
Employee [employee_id=7, employee_name=staff7, employee_gender=男, age=37, hire_date=Tue Sep 07 00:00:00 CST 2021, department_id=2, department_name=Operations Management Department, location=6 floor]
Employee [employee_id=8, employee_name=staff8, employee_gender=女, age=38, hire_date=Wed Sep 08 00:00:00 CST 2021, department_id=3, department_name=Information technology department, location=9 floor]
Employee [employee_id=9, employee_name=staff9, employee_gender=男, age=39, hire_date=Thu Sep 09 00:00:00 CST 2021, department_id=4, department_name=accounting department, location=10 floor]
Employee [employee_id=10, employee_name=staff10, employee_gender=女, age=40, hire_date=Fri Sep 10 00:00:00 CST 2021, department_id=5, department_name=Marketing Department, location=12 floor]

第二种方法

创建EmployeeVo继承Employee

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
package com.course.vo;

public class EmployeeVo extends Employee {

	private String department_name;
	private String location;
	
	public EmployeeVo() {
		super();
	}

	public EmployeeVo(String department_name, String location) {
		super();
		this.department_name = department_name;
		this.location = location;
	}

	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;
	}

	@Override
	public String toString() {
		return super.toString() + "EmployeeVo [department_name=" + department_name + ", location=" + location + "]";
	}
	
}

dao

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

import java.util.List;

import com.course.vo.Employee;
import com.course.vo.EmployeeVo;

public interface EmployeeDao {
	
	public List<EmployeeVo> getAllEmployees2();
	
}
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
<?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="getAllEmployees2" resultType="EmployeeVo">
		select e.employee_id,
			e.employee_name,
			(
			CASE e.employee_gender
			WHEN 'M' THEN '男'
			ELSE '女'
			END
			) as "employee_gender",
			e.age, e.hire_date, e.department_id,
			d.department_name, d.location 
		from employee e
		INNER JOIN department d
		ON e.department_id = d.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
27
28
package com.course.test;

import java.util.List;

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

import com.course.dao.EmployeeDao;
import com.course.util.MyBatisUtil;
import com.course.vo.Employee;
import com.course.vo.EmployeeVo;

public class MainTest {

	private SqlSession sqlSession = MyBatisUtil.openSqlSession();
	private EmployeeDao employeeDao = sqlSession.getMapper(EmployeeDao.class);
	
	@Test
	public void queryUser02() {
		List<EmployeeVo> employeeList = employeeDao.getAllEmployees2();
		for (EmployeeVo employeeVo : employeeList) {
			System.out.println(employeeVo);
		}
		
		MyBatisUtil.closeSqlSession(sqlSession);
	}
	
}
1
2
3
4
5
6
7
8
9
10
11
12
13
DEBUG [main] - ==>  Preparing: select e.employee_id, e.employee_name, ( CASE e.employee_gender WHEN 'M' THEN '男' ELSE '女' END ) as "employee_gender", e.age, e.hire_date, e.department_id, d.department_name, d.location from employee e INNER JOIN department d ON e.department_id = d.department_id 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 10
Employee [employee_id=1, employee_name=staff1, employee_gender=男, age=31, hire_date=Wed Sep 01 00:00:00 CST 2021, department_id=1]EmployeeVo [department_name=Retail Department, location=5 floor]
Employee [employee_id=2, employee_name=staff2, employee_gender=女, age=32, hire_date=Thu Sep 02 00:00:00 CST 2021, department_id=2]EmployeeVo [department_name=Operations Management Department, location=6 floor]
Employee [employee_id=3, employee_name=staff3, employee_gender=男, age=33, hire_date=Fri Sep 03 00:00:00 CST 2021, department_id=3]EmployeeVo [department_name=Information technology department, location=9 floor]
Employee [employee_id=4, employee_name=staff4, employee_gender=女, age=34, hire_date=Sat Sep 04 00:00:00 CST 2021, department_id=4]EmployeeVo [department_name=accounting department, location=10 floor]
Employee [employee_id=5, employee_name=staff5, employee_gender=男, age=35, hire_date=Sun Sep 05 00:00:00 CST 2021, department_id=5]EmployeeVo [department_name=Marketing Department, location=12 floor]
Employee [employee_id=6, employee_name=staff6, employee_gender=女, age=36, hire_date=Mon Sep 06 00:00:00 CST 2021, department_id=1]EmployeeVo [department_name=Retail Department, location=5 floor]
Employee [employee_id=7, employee_name=staff7, employee_gender=男, age=37, hire_date=Tue Sep 07 00:00:00 CST 2021, department_id=2]EmployeeVo [department_name=Operations Management Department, location=6 floor]
Employee [employee_id=8, employee_name=staff8, employee_gender=女, age=38, hire_date=Wed Sep 08 00:00:00 CST 2021, department_id=3]EmployeeVo [department_name=Information technology department, location=9 floor]
Employee [employee_id=9, employee_name=staff9, employee_gender=男, age=39, hire_date=Thu Sep 09 00:00:00 CST 2021, department_id=4]EmployeeVo [department_name=accounting department, location=10 floor]
Employee [employee_id=10, employee_name=staff10, employee_gender=女, age=40, hire_date=Fri Sep 10 00:00:00 CST 2021, department_id=5]EmployeeVo [department_name=Marketing Department, location=12 floor]

第三种方法

vo里添加所要包含的对象属性，mapper.xml通过<resultMap>和<association>建立对象间的关联关系

EmployeeVo3

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
package com.course.vo;

import java.util.Date;

public class EmployeeVo3 {

	private Integer employee_id;
	private String employee_name;
	private String employee_gender;
	private Integer age;
	private Date hire_date;
	private Integer department_id;
	
	private Department department;

	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;
	}

	public Department getDepartment() {
		return department;
	}

	public void setDepartment(Department department) {
		this.department = department;
	}

	@Override
	public String toString() {
		return "EmployeeVo3 [employee_id=" + employee_id + ", employee_name=" + employee_name + ", employee_gender="
				+ employee_gender + ", age=" + age + ", hire_date=" + hire_date + ", department_id=" + department_id
				+ ", department=" + department + "]";
	}
	
}

dao

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

import java.util.List;

import com.course.vo.Employee;
import com.course.vo.EmployeeVo;
import com.course.vo.EmployeeVo3;

public interface EmployeeDao {

	public List<EmployeeVo3> getAllEmployees3();
	
}

mapper.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
33
34
35
36
37
38
39
40
41
42
43
44
<?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">
	
	<resultMap id="myEmployeeBaseMap" type="EmployeeVo3">
		<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" />
	</resultMap>
	
	<resultMap id="myEmployeeMap" type="EmployeeVo3" extends="myEmployeeBaseMap">
		<!-- 建立关联关系 -->
        <!--  -->
		<association property="department" javaType="Department">
			<id property="department_id" column="department_id" />
			<result property="department_name" column="department_name" />
			<result property="location" column="location" />
		</association>
	</resultMap>
	
	<select id="getAllEmployees3" resultMap="myEmployeeMap">
		select e.employee_id,
			e.employee_name,
			(
			CASE e.employee_gender
			WHEN 'M' THEN '男'
			ELSE '女'
			END
			) as "employee_gender",
			e.age, e.hire_date, e.department_id,
			d.department_name, d.location 
		from employee e
		INNER JOIN department d
		ON e.department_id = d.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
27
28
29
package com.course.test;

import java.util.List;

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

import com.course.dao.EmployeeDao;
import com.course.util.MyBatisUtil;
import com.course.vo.Employee;
import com.course.vo.EmployeeVo;
import com.course.vo.EmployeeVo3;

public class MainTest {

	private SqlSession sqlSession = MyBatisUtil.openSqlSession();
	private EmployeeDao employeeDao = sqlSession.getMapper(EmployeeDao.class);
	
	@Test
	public void queryUser03() {
		List<EmployeeVo3> employeeList = employeeDao.getAllEmployees3();
		for (EmployeeVo3 employeeVo3 : employeeList) {
			System.out.println(employeeVo3);
		}
		
		MyBatisUtil.closeSqlSession(sqlSession);
	}
	
}
1
2
3
4
5
6
7
8
9
10
11
12
13
DEBUG [main] - ==>  Preparing: select e.employee_id, e.employee_name, ( CASE e.employee_gender WHEN 'M' THEN '男' ELSE '女' END ) as "employee_gender", e.age, e.hire_date, e.department_id, d.department_name, d.location from employee e INNER JOIN department d ON e.department_id = d.department_id 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 10
EmployeeVo3 [employee_id=1, employee_name=staff1, employee_gender=男, age=31, hire_date=Wed Sep 01 00:00:00 CST 2021, department_id=1, department=Department [department_id=1, department_name=Retail Department, location=5 floor]]
EmployeeVo3 [employee_id=2, employee_name=staff2, employee_gender=女, age=32, hire_date=Thu Sep 02 00:00:00 CST 2021, department_id=2, department=Department [department_id=2, department_name=Operations Management Department, location=6 floor]]
EmployeeVo3 [employee_id=3, employee_name=staff3, employee_gender=男, age=33, hire_date=Fri Sep 03 00:00:00 CST 2021, department_id=3, department=Department [department_id=3, department_name=Information technology department, location=9 floor]]
EmployeeVo3 [employee_id=4, employee_name=staff4, employee_gender=女, age=34, hire_date=Sat Sep 04 00:00:00 CST 2021, department_id=4, department=Department [department_id=4, department_name=accounting department, location=10 floor]]
EmployeeVo3 [employee_id=5, employee_name=staff5, employee_gender=男, age=35, hire_date=Sun Sep 05 00:00:00 CST 2021, department_id=5, department=Department [department_id=5, department_name=Marketing Department, location=12 floor]]
EmployeeVo3 [employee_id=6, employee_name=staff6, employee_gender=女, age=36, hire_date=Mon Sep 06 00:00:00 CST 2021, department_id=1, department=Department [department_id=1, department_name=Retail Department, location=5 floor]]
EmployeeVo3 [employee_id=7, employee_name=staff7, employee_gender=男, age=37, hire_date=Tue Sep 07 00:00:00 CST 2021, department_id=2, department=Department [department_id=2, department_name=Operations Management Department, location=6 floor]]
EmployeeVo3 [employee_id=8, employee_name=staff8, employee_gender=女, age=38, hire_date=Wed Sep 08 00:00:00 CST 2021, department_id=3, department=Department [department_id=3, department_name=Information technology department, location=9 floor]]
EmployeeVo3 [employee_id=9, employee_name=staff9, employee_gender=男, age=39, hire_date=Thu Sep 09 00:00:00 CST 2021, department_id=4, department=Department [department_id=4, department_name=accounting department, location=10 floor]]
EmployeeVo3 [employee_id=10, employee_name=staff10, employee_gender=女, age=40, hire_date=Fri Sep 10 00:00:00 CST 2021, department_id=5, department=Department [department_id=5, department_name=Marketing Department, location=12 floor]]

第四种方法

新建DepartmentMapper.xml，在EmployeeMapper.xml中的<resultMap>中的<association>建立关联查询

vo

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
package com.course.vo;

import java.util.Date;

public class EmployeeVo3 {

	private Integer employee_id;
	private String employee_name;
	private String employee_gender;
	private Integer age;
	private Date hire_date;
	private Integer department_id;
	
	private Department department;

	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;
	}

	public Department getDepartment() {
		return department;
	}

	public void setDepartment(Department department) {
		this.department = department;
	}

	@Override
	public String toString() {
		return "EmployeeVo3 [employee_id=" + employee_id + ", employee_name=" + employee_name + ", employee_gender="
				+ employee_gender + ", age=" + age + ", hire_date=" + hire_date + ", department_id=" + department_id
				+ ", department=" + department + "]";
	}
	
}

dao

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

import java.util.List;

import com.course.vo.Employee;
import com.course.vo.EmployeeVo;
import com.course.vo.EmployeeVo3;

public interface EmployeeDao {
	
	public List<EmployeeVo3> getAllEmployees4();
	
}

新建DepartmentMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
<?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">
	<select id="getDepartmentById" resultType="Department">
		select * from department 
		where department_id = #{value}
	</select>
</mapper>

EmployeeMapper.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.EmployeeDao">

	<resultMap id="myEmployeeBaseMap" type="EmployeeVo3">
		<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" />
	</resultMap>
	
	<resultMap id="myEmployeeMap2" type="EmployeeVo3" extends="myEmployeeBaseMap">
		<!-- 建立关联关系 -->
		<!-- 
		column="department_id": 从当前查询的结果集中取出department_id字段，传给com.course.dao.DepartmentDao.getDepartmentById
		 -->
		<association property="department" column="department_id" select="com.course.dao.DepartmentDao.getDepartmentById">
		</association>
	</resultMap>
	
	<select id="getAllEmployees4" resultMap="myEmployeeMap2">
	    select * from employee
	</select>
	
</mapper>

mybatis.cfg.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
  
<!-- MyBatis的整体配置 -->
<configuration>
	
	<!-- 配置Mapper.xml文件 -->
	<mappers>
		<mapper resource="com/course/mapper/EmployeeMapper.xml" />
		<mapper resource="com/course/mapper/DepartmentMapper.xml" />
	</mappers>
	
</configuration>

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
27
28
29
package com.course.test;

import java.util.List;

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

import com.course.dao.EmployeeDao;
import com.course.util.MyBatisUtil;
import com.course.vo.Employee;
import com.course.vo.EmployeeVo;
import com.course.vo.EmployeeVo3;

public class MainTest {

	private SqlSession sqlSession = MyBatisUtil.openSqlSession();
	private EmployeeDao employeeDao = sqlSession.getMapper(EmployeeDao.class);
	
	@Test
	public void queryUser04() {
		List<EmployeeVo3> employeeList = employeeDao.getAllEmployees4();
		for (EmployeeVo3 employeeVo3 : employeeList) {
			System.out.println(employeeVo3);
		}
		
		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
28
DEBUG [main] - ==>  Preparing: select * from employee 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - ====>  Preparing: select * from department where department_id = ? 
DEBUG [main] - ====> Parameters: 1(Integer)
DEBUG [main] - <====      Total: 1
DEBUG [main] - ====>  Preparing: select * from department where department_id = ? 
DEBUG [main] - ====> Parameters: 2(Integer)
DEBUG [main] - <====      Total: 1
DEBUG [main] - ====>  Preparing: select * from department where department_id = ? 
DEBUG [main] - ====> Parameters: 3(Integer)
DEBUG [main] - <====      Total: 1
DEBUG [main] - ====>  Preparing: select * from department where department_id = ? 
DEBUG [main] - ====> Parameters: 4(Integer)
DEBUG [main] - <====      Total: 1
DEBUG [main] - ====>  Preparing: select * from department where department_id = ? 
DEBUG [main] - ====> Parameters: 5(Integer)
DEBUG [main] - <====      Total: 1
DEBUG [main] - <==      Total: 10
EmployeeVo3 [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 [department_id=1, department_name=Retail Department, location=5 floor]]
EmployeeVo3 [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 [department_id=2, department_name=Operations Management Department, location=6 floor]]
EmployeeVo3 [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 [department_id=3, department_name=Information technology department, location=9 floor]]
EmployeeVo3 [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 [department_id=4, department_name=accounting department, location=10 floor]]
EmployeeVo3 [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 [department_id=5, department_name=Marketing Department, location=12 floor]]
EmployeeVo3 [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 [department_id=1, department_name=Retail Department, location=5 floor]]
EmployeeVo3 [employee_id=7, employee_name=staff7, employee_gender=M, age=37, hire_date=Tue Sep 07 00:00:00 CST 2021, department_id=2, department=Department [department_id=2, department_name=Operations Management Department, location=6 floor]]
EmployeeVo3 [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 [department_id=3, department_name=Information technology department, location=9 floor]]
EmployeeVo3 [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 [department_id=4, department_name=accounting department, location=10 floor]]
EmployeeVo3 [employee_id=10, employee_name=staff10, employee_gender=F, age=40, hire_date=Fri Sep 10 00:00:00 CST 2021, department_id=5, department=Department [department_id=5, department_name=Marketing Department, location=12 floor]]

第四种方法不推荐，先查询出员工信息，然后根据员工的部门id查询部门信息，执行的查询sql多。