Mybatis-复杂查询多对一&&一对多

生活中有许多常见的多对一和一对多的场景：老师与学生
student： id,name,tid
teacher： id,name

多对一：查询出所有的学生信息以及学生的老师
SQL语句：

SELECT s.id,s.`name`,t.`name`
from student s,teacher t
where s.tid=t.id;

Bean:

public class Student {
	
	private int id;
	private String name;
	private Teacher teacher; //注意此处不是tid
		
	public Student() {
		super();
		// TODO Auto-generated constructor stub
	}
	public Student(int id, String name, Teacher teacher) {
		super();
		this.id = id;
		this.name = name;
		this.teacher = teacher;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Teacher getTeacher() {
		return teacher;
	}
	public void setTeacher(Teacher teacher) {
		this.teacher = teacher;
	}
	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + ", teacher=" + teacher + "]";
	}
	
}

思考：我们查询结果为Student，要将列映射到属性Teacher，解决列与属性不一致问题

<select id="getStudents" resultMap="studentTeacher">
  	select s.id sid, s.name sname, t.name tname
  	from student s,teacher t
  	where s.tid = t.id;
  </select>
  
  <resultMap type="Student" id="studentTeacher">
  	<result column="sid"  property="id"/>
  	<result column="sname"  property="name"/>
  	<association property="teacher" javaType="Teacher">
  		<result column="tname" property="name"/>
  	</association>
  </resultMap>

一对多：根据老师的id查询老师信息以及他的所有学生

select t.id tid,t.name tname,s.id sid,s.name sname,s.tid stid
from student s,teacher t
where s.tid=t.id and t.id=1;

Bean:

public class Teacher {
	
	private int id;
	private String name;
	private List<Student> students;
	public Teacher() {
		super();
		// TODO Auto-generated constructor stub
	}
	public Teacher(int id, String name, List<Student> students) {
		super();
		this.id = id;
		this.name = name;
		this.students = students;
	}
	public int getId() {
		return id;
	}
	public void setId(int 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 "Teacher [id=" + id + ", name=" + name + ", students=" + students + "]";
	}
	
	
}

  <select id="getTeacher" resultMap="teacherStudent">
  	select t.id tid,t.name tname,s.id sid,s.name sname,s.tid stid
  	from student s,teacher t
  	where s.tid=t.id and t.id=#{id};
  </select>
  
  <resultMap type="Teacher" id="teacherStudent">
  	<result column="tid" property="id"/>
  	<result column="tname" property="name"/>
  	<collection property="students" ofType="Student">
  		<result column="sid" property="id"/>
  		<result column="sname" property="name"/>
  		<result column="stid" property="tid"/>
  	 </collection>
  </resultMap>

总结：

1.遇到查询结果Bean中有对象类型,集合时，使用结果集映射ResultMap
2.ResultMap灵魂：SQL查询列与Bean的属性一一映射，只要懂了这个关系，在复杂的对象，集合都能分解成一个个简单类型
3.关联：association 【多对一】
 集合：collection 【一对多】
 4.JavaType和ofType都是用来指定对象类型的
JavaType是用来指定pojo中属性的类型
ofType指定的是 映射到list集合属性中pojo的类型 

。