MyBatis-联合查询

• 1.javaBean 定义：

package com.edu.bean;
import java.util.List;
public class School {
	private Integer id;
	private String name;
	private List<Student> students;
	public School() {
		super();
	}
	public School(Integer id, String name) {
		super();
		this.id = id;
		this.name = name;
	}
	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;
	}
	@Override
	public String toString() {
		return "School [id=" + id + ", name=" + name + ", students=" + students + "]";
	}
}

package com.edu.bean;
import java.util.List;
public class Student {
	private Integer id;
	private String name;
	private List<Teacher> teachers;
	private School school;
	public Student() {
		super();
	}
	public Student(Integer id, String name) {
		super();
		this.id = id;
		this.name = name;
	}
	public Student(Integer id, String name, School school) {
		super();
		this.id = id;
		this.name = name;
		this.school = school;
	}
	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<Teacher> getTeachers() {
		return teachers;
	}
	public void setTeachers(List<Teacher> teachers) {
		this.teachers = teachers;
	}
	public School getSchool() {
		return school;
	}
	public void setSchool(School school) {
		this.school = school;
	}
	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + ", teachers=" + teachers + ", school=" + school + "]";
	}
}	

• 2.学生Dao查询时，同时查询其所在的学校：
  从学生角度看，属于1对1，可以直接使用association

package com.edu.dao;
import com.edu.bean.Student;
public interface StudentDao {
	public Student getStudentSchoolById(Integer id);
}

<?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.edu.dao.StudentDao">
	  <select id="getStudentSchoolById" resultMap="st2Map">
	  		select st.id tid,st.name tname,sc.id cid,sc.name cname from t_schools sc left join t_students st on sc.id = st.schoolId where st.id = #{id} 			
	  </select>
	  <!-- 使用[级联属性]封装联合查询出的结果 -->
	  <resultMap type="com.edu.bean.Student" id="studentSchoolMap">
	  	<id property="id" column="tid"/>
	  	<result property="name" column="tname"/>
	  	<result property="school.id" column="cid"/>
	  	<result property="school.name" column="cname"/>
	  </resultMap>
	  
	  <!-- 使用association封装联合查询出的结果 -->
	  <resultMap type="com.edu.bean.Student" id="st2Map">
	  	<id property="id" column="tid"/>
	  	<result property="name" column="tname"/>
 		  <!-- javaType 指定封装的类型 -->
	  	<association property="school" javaType="com.edu.bean.School">
	  		<id property="id" column="cid"/>
	  		<result property="name" column="cname"/>
	  	</association>
	  </resultMap>
	  <!-- 1对n 或 n对1 ： 外键一定放在n的一端 -->
	  <!-- n对n : 使用中间表存储对应关系 -->
  </mapper>

	@Test
	public void TestJoinSelect01() {
		SqlSession sqlSession = factory.openSession();
		StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
		Student student = studentDao.getStudentSchoolById(1);
		System.out.println(student);
	}

• 3.学校Dao查询时，同时查询所有校内学生
  从学校角度看，属于1对多

package com.edu.dao;
import com.edu.bean.School;
public interface SchoolDao {
	/**
	 *  查学校的时候，将所有学生查出来
	 * @param id
	 * @return
	 */
	public School getSchoolById(Integer id);
}

<?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.edu.dao.SchoolDao">
  	<select id="getSchoolById" resultMap="scMap">
		select sc.id schoolid,sc.name schoolname,st.id studentid,st.name studentname from 
		t_schools sc left join t_students st on st.schoolID = sc.id where sc.id = #{id};
  	</select>
  	<resultMap type="com.edu.bean.School" id="scMap">
  		<id property="id" column="schoolid"/>
  		<result property="name" column="schoolname"/>
  		<!-- ofType指定集合元素类型 -->
  		<collection property="students" ofType="com.edu.bean.Student">
  			<id property="id" column="studentid"/>
  			<result property="name" column="studentname"/>
  			<association property="school" javaType="com.edu.bean.School">
  				<id property="id" column="schoolid"/>
  				<result property="name" column="schoolname"/>
  			</association>
  		</collection>
  	</resultMap>
  </mapper>

	@Test
	public void TestJointSelect02() {
		SqlSession sqlSession = factory.openSession();
		SchoolDao schoolDao = sqlSession.getMapper(SchoolDao.class);
		School school = schoolDao.getSchoolById(1);
		System.out.println(school);
	}