MyBatis如何实现多表查询（一对一、一对多）

Mytabis中实现多表查询有两种方式

一、在sqlMapper配置文件中进行

        一对一：resultMap中使用association标签

        一对多：resultMap中使用collection标签

二、注解的方式

        一对一：@Results中@Result的@One

        一对多：@Results中@Result的@Many

java中需要实体类去接数据库中的数据，映射可以让类中的属性和数据库中的字段可以对的上

举例说明：

        学生对班级：一对一的关系

        班级对学生：一对多的关系

创建学生、班级实体类，学生班级接口，对应的mapper配置文件

package com.ape.bean;
 
import java.util.List;
 
public class Banji {
	
	private int classid;
	private String classname;
	
	private List<Student> slist;
	
	
	public Banji() {
		super();
		// TODO Auto-generated constructor stub
	}
	
	public Banji(int classid, String classname, List<Student> slist) {
		super();
		this.classid = classid;
		this.classname = classname;
		this.slist = slist;
	}
 
	public int getClassid() {
		return classid;
	}
	public void setClassid(int classid) {
		this.classid = classid;
	}
	public String getClassname() {
		return classname;
	}
	public void setClassname(String classname) {
		this.classname = classname;
	}
	
	public List<Student> getSlist() {
		return slist;
	}
 
	public void setSlist(List<Student> slist) {
		this.slist = slist;
	}
 
	@Override
	public String toString() {
		return "Banji [classid=" + classid + ", classname=" + classname + ", slist=" + slist + "]";
	}
 
}

 

package com.ape.bean;
 
import java.util.Date;
 
public class Student {
	
	private Integer sid;
	private String sname;
	private Date birthday;
	private String ssex;
	private int classid;
	
	// 外部属性
	private Banji bj;
	
	
	public Student() {
		super();
		// TODO Auto-generated constructor stub
	}
	
	public Student(Integer sid, String sname, Date birthday, String ssex, int classid, Banji bj) {
		super();
		this.sid = sid;
		this.sname = sname;
		this.birthday = birthday;
		this.ssex = ssex;
		this.classid = classid;
		this.bj = bj;
	}
 
	public Integer getSid() {
		return sid;
	}
	public void setSid(Integer sid) {
		this.sid = sid;
	}
	public String getSname() {
		return sname;
	}
	public void setSname(String sname) {
		this.sname = sname;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	public String getSsex() {
		return ssex;
	}
	public void setSsex(String ssex) {
		this.ssex = ssex;
	}
	public int getClassid() {
		return classid;
	}
	public void setClassid(int classid) {
		this.classid = classid;
	}
	
	public Banji getBj() {
		return bj;
	}
 
	public void setBj(Banji bj) {
		this.bj = bj;
	}
 
	@Override
	public String toString() {
		return "Student [sid=" + sid + ", sname=" + sname + ", birthday=" + birthday + ", ssex=" + ssex + ", classid="
				+ classid + ", bj=" + bj + "]";
	}
 
	
}

sqlmapper配置文件 班级的mapper一对多（一个班有多个学生）

<?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.ape.maper.ClassMaper">
 
<resultMap type="Classs" id="Class_student_Maper">
		<result column="classid" property="classid"/>
		<result column="classname" property="classname"/>
<collection property="slist" ofType="Student">
		<result column="sid" property="sid"/>
		<result column="sname" property="sname"/>
		<result column="birthday" property="birthday"/>
		<result column="ssex" property="ssex"/>
		<result column="classid" property="classid"/>
 
</collection>
</resultMap>
 
 <select id="findAllBanji" resultMap="Class_student_Maper" >
 select * from class inner join student on class.classid = student.classid;
 </select>
 
</mapper>

sqlMapper配置文件 学生mapper一对一（一个学生对应一个班级）

<?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.ape.maper.StudentMaper">
 
	<resultMap type="Student" id="student_class_Map">
		<result column="sid" property="sid"/>
		<result column="sname" property="sname"/>
		<result column="birthday" property="birthday"/>
		<result column="ssex" property="ssex"/>
		<result column="classid" property="classid"/>
		<association property="banji">
		
			<result column="classid" property="classid"/>
			<result column="classname" property="classname"/>
		</association>
	</resultMap>
 
 <select id="findAllStudent" resultType="Student">
 select * from student
 </select>
 
</mapper>

注解 一对一：

学生接口：

package com.ape.mapper;
 
import java.util.List;
 
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
 
import com.ape.bean.Student;
 
public interface StudentMapper {
	// one 一对一的表联查 @One(select="能得到属性对象的反射字符串",fetchType="加载机制")
	
	@Results({
		@Result(column = "classid", property = "classid"),
		@Result(column = "classid",property = "bj", 
			one=@One(
				select = "com.ape.mapper.BanjiMapper.findBanjiByClassid")
		)
	})
 
	@Select("select * from student")
	public List<Student> findAllStudent();
 
	@Select("select * from student where classid = #{v}")
	public List<Student> findStudentByClassid(int classid);

注解 一对多：

班级接口：

package com.ape.mapper;
 
import java.util.List;
 
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
 
import com.ape.bean.Banji;
 
public interface BanjiMapper {
	
	
	@Select("select * from class where classid = #{v}")
	public Banji findBanjiByClassid(int classid);
	
	// many 一对多的映射 @Many(select ="通过字符串反射得到的集合对象")
	@Results({
		@Result(column ="classid",property = "classid"),
		@Result(column = "classid",property = "slist", 
			many = @Many( 
				select = "com.ape.mapper.StudentMapper.findStudentByClassid")
		)
	})
 
	@Select("select * from class")
	public List<Banji> findAllBanji();
 
}