Mybatis的一对一、一对多 输出类型映射实例讲解

resultType （简单、不过多讲解）

使用resultType进行输出映射时，要求sql语句中查询的列名和要映射的pojo的属性名一致

重点：输出类型映射（resultMap）

1、如果sql查询列名和pojo的属性名不一致，可以通过resultMap将列名和属性名作一个对应关系，最终将查询结果映射
到指定的pojo对象中。resultType底层也是通过resultMap完成映射的。

property:代表pojo类的属性名，column：代表数据库的字段名

<select id="querUserById" resultMap="userMapper"
parameterType="int">
select id,name,pwd,email from users where id = #{id}
</select>
<!--结果映射 type:要映射成哪个类型的对象 -->
<resultMap type="user2" id="userMapper">
<!--对表中的主键进行映射 -->
<id property="id" column="id" />
<result property="username" column="name" />
<result property="pwd" column="pwd" />
<result property="email" column="email" />
</resultMap>

2、关联查询(重点)
- 多表查询,表与表之间存在相应的关系,1对1,1对多,多对对,关联查询的数据如何进行映射.关联查询是比较复杂的映射,
一般使用resultMap来进行复杂映射

2.1、一对一查询

（1）直接联表查询的方式来实现一对一的关系

Teacher:

public class Teacher {
	private Integer id;
	private String t_name;
	private String sex;
	private String birthday;
	private DriverLicense driverLicense=null;//一对一关系,老师拥有驾照的属性
	
	public DriverLicense getDriverLicense() {
		return driverLicense;
	}
	public void setDriverLicense(DriverLicense driverLicense) {
		this.driverLicense = driverLicense;
	}
	

DriverLicense:

public class DriverLicense {
	private Integer id;
	private String dno;
	private String address;

TeacherMapper.java

public interface TeacherMapper {
	public List<Teacher> findAllTeacher() throws Exception;
	public Teacher findTeacherById(Integer id) throws Exception;

}

DriverLicenseMapper:

public interface DriverLicenseMapper {
	//根据id来找到驾照信息
   DriverLicense findDriverLicenseById(Integer id) throws Exception;
}

TeacherMapper.xml:直接联表查询的方式来实现一对一的关系

使用关键字：association

<mapper namespace="com.hwua.mapper.TeacherMapper">

	<select id="findAllTeacher" resultMap="teacherMapper"> 
		select t.id tid,t_name,sex,birthday, d.id did,dno,address 
		from teacher t left join driverlicense d 
		on did = d.id 
	</select> 
	
	<resultMap type="teacher" id="teacherMapper"> 
		<id property="id" column="tid" /> 
		<result property="t_name" column="t_name" /> 
		<result property="sex" column="sex" /> 
		<result property="birthday" column="birthday" /> 
		<!-- 配置1对1的关系 ：一名老师拥有一个驾照 -->
		<association property="driverLicense" javaType="driverLicense"> 
		<id property="id" column="did" /> 
		<result property="dno" column="dno" /> 
		<result property="address" column="address" /> 
		</association> 
	</resultMap>

（2）分表查询的方式来实现1对1的关系(类似于子查询)

<select id="findAllTeacher" resultMap="teacherMapper">
		select id,t_name,sex,birthday,did from teacher
	</select>
	
	<resultMap type="teacher" id="teacherMapper">
		<id property="id" column="id" />
		<result property="t_name" column="t_name" />
		<result property="sex" column="sex" />
		<result property="birthday" column="birthday" />
		<!-- column指的要传递个DriverLicense对象中的findDriverLicenseById中的参数  -->
		<association property="driverLicense" javaType="DriverLicense" column="did" 
			select="com.hwua.mapper.DriverLicenseMapper.findDriverLicenseById">
		</association>
	</resultMap>

与 DriverLicenseMapper.xml 中的 findDriverLicenseById 关联：

<mapper namespace="com.hwua.mapper.DriverLicenseMapper">
	<select id="findDriverLicenseById" parameterType="int" resultType="driverLicense">
		select id,dno,address from driverlicense where id = #{id}
	</select>
</mapper>

2.2、一对多查询

Teacher

public class Teacher {
	private Integer id;
	private String t_name;
	private String sex;
	private String birthday;
	private DriverLicense driverLicense = null;// 一对一关系,老师拥有驾照的属性
	private List<Course> cList = null;//1对多的关系
	
	public List<Course> getcList() {
		return cList;
	}

	public void setcList(List<Course> cList) {
		this.cList = cList;
	}
	public DriverLicense getDriverLicense() {
		return driverLicense;
	}

	public void setDriverLicense(DriverLicense driverLicense) {
		this.driverLicense = driverLicense;
	}

DriverLicense

public class DriverLicense {
	private Integer id;
	private String dno;
	private String address;

Course:

public class Course {
	private Integer id;
	private String cno;
	private String cname;

TeacherMapper.java:

public interface TeacherMapper {
	public List<Teacher> findAllTeacher() throws Exception;
	public Teacher findTeacherById(Integer id) throws Exception;

}

 

（1）第一种方式: 分表查询 

CourseMapper.xml:

<mapper namespace="com.hwua.mapper.CourseMapper">
	<select id="findCoursesById" parameterType="int" resultType="course">
		select id,cno,cname from course where tid=#{tid}
	</select>
</mapper>

TeacherMapper.xml:

	<select id="findAllTeacher" resultMap="teacherMapper">
		select id,t_name,sex,birthday,did from teacher
	</select>
	
	<resultMap type="teacher" id="teacherMapper">
		<id property="id" column="id" />
		<result property="t_name" column="t_name" />
		<result property="sex" column="sex" />
		<result property="birthday" column="birthday" />
		<!--column指的要传递个DriverLicense对象中的findDriverLicenseById中的参数  -->
		<association 
		 	property="driverLicense" 
		 	javaType="DriverLicense" 
		 	column="did"
		 	select="com.hwua.mapper.DriverLicenseMapper.findDriverLicenseById" fetchType="lazy">
		</association>
		<!--配置一对多的关系  -->
		<collection property="cList" javaType="list" ofType="course" column="id"
			select="com.hwua.mapper.CourseMapper.findCoursesById" fetchType="lazy">
		</collection>
	</resultMap>

(2)第二种方式:多表查询实现

多表做左连接

<select id="findAllTeacher" resultMap="teacherMapper">
		select t.id,t_name,sex,birthday,d.id did, dno,address, c.id cid,cno,cname 
		from teacher t 
		left join driverlicense d on t.did = d.id 
		left JOIN course c on t.id = c.tid
	</select>
	
	<resultMap type="teacher" id="teacherMapper">
		<id property="id" column="id" />
		<result property="t_name" column="t_name" />
		<result property="sex" column="sex" />
		<result property="birthday" column="birthday" />
		<!-- 一对一 -->
		<association property="driverLicense" javaType="driverLicense">
		  <id property="id" column="did"/>
		  <result property="dno" column="dno" />
		  <result property="address" column="address" />
		</association>
		<!-- 一对多 -->
		<collection property="cList" javaType="list" ofType="course">
		  <id property="id" column="cid"/>
		  <result property="cno" column="cno" />
		  <result property="cname" column="cname" />
		</collection>
	</resultMap>

3、当查询下面这个方法时，

public Teacher findTeacherById(Integer id) throws Exception;

可以使用 resultMap，

<select id="findTeacherById" resultMap="teacherMapper" parameterType="int">
		select id,t_name,sex,birthday,did from teacher where id = #{id}
	</select>

因为前面已经有配置好的id为 teacherMapper的resultMap了，可以拿来使用。

<resultMap type="teacher" id="teacherMapper">