Mybatis如何关联查询

创建Teacher和Student表：

Teacher

Student

创建Teacher和Student类：

Teacher

public class Teacher {
	private Integer id;
	private String name;
	private List<Student> students;

	public Teacher() {
		super();
	}

	public Teacher(Integer id, String name, List<Student> students) {
		super();
		this.id = id;
		this.name = name;
		this.students = students;
	}

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public List<Student> getStudents() {
		return students;
	}

	public void setStudents(List<Student> students) {
		this.students = students;
	}
}

Student

public class Student {
	private Integer id;
	private String name;
	private Integer teacherId;
	private Teacher teacher;

	public Student() {
		super();
	}

	public Student(Integer id, String name, Integer teacherId, Teacher teacher) {
		super();
		this.id = id;
		this.name = name;
		this.teacherId = teacherId;
		this.teacher = teacher;
	}

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Integer getTeacherId() {
		return teacherId;
	}

	public void setTeacherId(Integer teacherId) {
		this.teacherId = teacherId;
	}

	public Teacher getTeacher() {
		return teacher;
	}

	public void setTeacher(Teacher teacher) {
		this.teacher = teacher;
	}
}

我们在Teacher中定义了List<Student>,在Student中定义了Teacher,在关系映射时我们会用到这两个属性

首先是以Map类型返回数据：

	<select id="findTeacherMap" resultType="java.util.Map">
		SELECT
			t.id,
			t. NAME,
			s.id AS sid,
			s. NAME AS sname
		FROM
			teacher t
		LEFT JOIN student s ON t.id = s.teacher_id
	</select>

测试方法：

	@Test
	public void findTeacherMap() {
		try {
			SqlSession session = sessionFactory.openSession();
			List<Map<String, Object>> teachers = session.selectList("mybatis.model.TeacherMapper.findTeacherMap");
			for (Map<String, Object> map : teachers) {
				for (Entry<String, Object> teacher : map.entrySet()) {
					System.out.print(teacher.getKey() + ":" + teacher.getValue() + ",");
				}
				System.out.println();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

这种方法没什么好说的就是两表联查把结果集放到Map中,下面是运行结果

sname:小名,id:1,NAME:keke,sid:1,
sname:大明,id:1,NAME:keke,sid:2,
sname:微微,id:1,NAME:keke,sid:3,
sname:小华,id:2,NAME:array,sid:4,
sname:张三,id:2,NAME:array,sid:5,
sname:李四,id:2,NAME:array,sid:6,

当一对多的关系映射时我们有两种方法来应对：

第一种：我们在mapper.xml中定义resultMap

	<resultMap type="Teacher" id="teacherMap1">
		<id column="id" property="id" />
		<result column="name" property="name" />
		<collection property="students" ofType="Student" fetchType="lazy">
			<id column="sid" property="id" />
			<result column="sname" property="name" />
		</collection>
	</resultMap>

	<select id="findTeacherResultMap1" resultMap="teacherMap1">
		SELECT
			t.id,
			t. NAME,
			s.id AS sid,
			s. NAME AS sname
		FROM
			teacher t
		LEFT JOIN student s ON t.id = s.teacher_id
	</select>

resultMap跟bean的属性结构差不多.type是实际的java类型这里我用到了别名所以直接写的Teacher,id是标识引用这个resultMap时会用到,column是select标签中的列名不可重复(重复用别名),property是bean中属性名称,bean中的students是集合我们用collection,这里的ofType是集合的类型,延迟加载tetchType="lazy",虽然这里启用了延迟加载但实际开发中不建议用这种方法,因为如果数据量大的话会非常耗性能.

测试方法：

	@Test
	public void findTeacherResultMap1() {
		try {
			SqlSession session = sessionFactory.openSession();
			List<Teacher> teachers = session.selectList("mybatis.model.TeacherMapper.findTeacherResultMap1");
			for (Teacher teacher : teachers) {
				System.out.println("id:" + teacher.getId() + ",name:" + teacher.getName());
				for (Student stuent : teacher.getStudents()) {
					System.out.println("sid:" + stuent.getId() + ",sname:" + stuent.getName());
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

运行结果：

id:1,name:keke
sid:1,sname:小名
sid:2,sname:大明
sid:3,sname:微微
id:2,name:array
sid:4,sname:小华
sid:5,sname:张三
sid:6,sname:李四

第二种：我们还是mapper.xml中定义resultMap

	<resultMap type="Teacher" id="teacherMap2">
		<id column="id" property="id" />
		<result column="name" property="name" />
		<collection column="id" property="students" select="findStudentsResultMap2"/>
	</resultMap>

<span style="white-space:pre">	</span><select id="findTeacherResultMap2" resultMap="teacherMap2">
		SELECT
			id,
			NAME
		FROM
			teacher
	</select>
	<select id="findStudentsResultMap2" resultType="Student">
		SELECT
			id,
			NAME
		FROM
			student
		WHERE
			teacher_id = #{id}
		LIMIT 0,2
	</select>

这种方式我们把students集合的部分单独查询并限制条数,collection中的column是查询是要传入的参数,select是要执行那个select语句,这种方式比第一种方式性能方面会好很多也灵活很多.

测试方法：

	@Test
	public void findTeacherResultMap2() {
		try {
			SqlSession session = sessionFactory.openSession();
			List<Teacher> teachers = session.selectList("mybatis.model.TeacherMapper.findTeacherResultMap2");
			for (Teacher teacher : teachers) {
				System.out.println("id:" + teacher.getId() + ",name:" + teacher.getName());
				for (Student stuent : teacher.getStudents()) {
					System.out.println("sid:" + stuent.getId() + ",sname:" + stuent.getName());
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

运行结果：

id:1,name:keke
sid:1,sname:小名
sid:2,sname:大明
id:2,name:array
sid:4,sname:小华
sid:5,sname:张三

多对一的关系映射：

首先是resultMap

	<resultMap type="Student" id="StudentMap">
		<id column="id" property="id" />
		<result column="name" property="name" />
		<association property="teacher" javaType="Teacher">
			<id column="tid" property="id" />
			<result column="tname" property="name" />
		</association>
	</resultMap>

	<select id="findStudents" resultMap="StudentMap">
		SELECT
			s.id,
			s. NAME,
			t.id AS tid,
			t. NAME AS tname
		FROM
			student s
		RIGHT JOIN teacher t ON s.teacher_id = t.id;
	</select>

测试方法：

	@Test
	public void findStudents() {
		try {
			SqlSession session = sessionFactory.openSession();
			List<Student> students = session.selectList("mybatis.model.TeacherMapper.findStudents");
			for (Student student : students) {
				System.out.println("id:" + student.getId() + ",name:" + student.getName());
				System.out.println("老师:"+student.getTeacher().getName());
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

运行结果：

id:1,name:小名
老师:keke
id:2,name:大明
老师:keke
id:3,name:微微
老师:keke
id:4,name:小华
老师:array
id:5,name:张三
老师:array
id:6,name:李四
老师:array