Mybatis关联映射

Mybatis关联映射

一、Mybatis的关联映射有两种不同的实现形式：

1.嵌套查询：通过执行另一个SQL映射语句来返回关联数据（查询两次）

**2.嵌套结果查询：**执行一个表关联查询SQL，然后将查询结果映射成关联对象（查询一次）

1.一对一映射

create table t_teacher(
t_id int(4) primary key auto_increment,
t_name varchar(10) not null
);

create table t_class(
c_id int(2) primary key auto_increment,
c_name varchar(20),
teacher_id int(4)
);

alter table t_class add constraint fk_teacher_id foreign key(teacher_id)
references t_teacher(t_id);

insert into t_teacher(t_name) values("张三丰");
insert into t_teacher(t_name) values("孙悟空");
insert into t_class(c_name,teacher_id) values("三年二班",2);
insert into t_class(c_name,teacher_id) values("五年六班",1);

一对一的嵌套查询

<!--一对一的嵌套查询-->
	<resultMap id="teacherMapOne" type="Teacher">
		<id property="id" column="t_id"/>
		<result property="name" column="t_name"/>
		<association property="cls" column="t_id" select="findClass"/>
	</resultMap>
	<select id="findTeacherByIdOne" parameterType="int" resultMap="teacherMapOne">
		select * from t_teacher where t_id=#{id};
	</select>

	<resultMap id="classMap" type="Class">
		<id property="id" column="c_id"/>
		<result property="name" column="c_name"/>
		<result property="teacherId" column="teacher_id"/>
	</resultMap>
	<select id="findClass" parameterType="int" resultMap="classMap">
		select * from t_class where teacher_id=#{id};
	</select>

一对一嵌套结果查询

<!--一对一嵌套结果查询-->
	<resultMap id="teacherMapTwo" type="Teacher">
		<id property="id" column="t_id"/>
		<result property="name" column="t_name"/>
		<association property="cls" column="t_id" javaType="Class">
			<id property="id" column="c_id"/>
			<result property="name" column="c_name"/>
			<result property="teacherId" column="teacher_id"/>
		</association>
	</resultMap>
	<select id="findTeacherByIdTwo" parameterType="int" resultMap="teacherMapTwo">
		select t.*,c.* from t_teacher t join t_class c
		on t.t_id=c.teacher_id where t.t_id=#{id};
	</select>

2.一对多映射

一对多的嵌套查询

<!-- 一对多嵌套查询 -->
	<resultMap id="deptMapOne" type="Dept">
		<id property="id" column="d_id"/>
		<result property="name" column="d_name"/>
		<collection property="emps" column="d_id" ofType="Emp" javaType="list" select="findEmps"/>
	</resultMap>
	<select id="findDeptByIdOne" parameterType="int" resultMap="deptMapOne">
		select * from t_dept where d_id=#{id};
	</select>

	<resultMap id="empMapOne" type="Emp">
		<id property="id" column="e_id"/>
		<result property="name" column="e_name"/>
		<result property="salary" column="e_salary"/>
		<result property="bonus" column="e_bonus"/>
		<result property="hiredate" column="e_hiredate"/>
		<result property="deptno" column="e_deptno"/>
	</resultMap>
	<select id="findEmps" parameterType="Integer" resultMap="empMapOne">
		select * from t_emp where e_deptno=#{id}
	</select>

一对多嵌套结果查询

<resultMap id="empMapTwo" type="Dept">
   <id property="id" column="d_id"/>
   <result property="name" column="d_name"/>
   <collection property="emps" column="d_id" ofType="Emp" javaType="list">
      <id property="id" column="e_id"/>
      <result property="name" column="e_name"/>
      <result property="salary" column="e_salary"/>
      <result property="bonus" column="e_bonus"/>
      <result property="hiredate" column="e_hiredate"/>
      <result property="deptno" column="e_deptno"/>
   </collection>
</resultMap>
<select id="findDeptByIdTwo" parameterType="int" resultMap="empMapTwo">
   select d.*,e.* from t_dept d join t_emp e
   on d.d_id=e.e_deptno where d.d_id=#{id}
</select>

3.扩展：多对多映射

下面用于演示多对多关系映射的数据库建表语句

create table t_student(
s_id int(4) primary key auto_increament,
    s_name varchar(10) not null
);
create table t_course(
c_id int(4) primary key auto_increament,
    c_name varchar(10) not null
);
create table student_course(
student_id int(4),
    course_id int(4)
);
insert into t_student(s_name) values('张三'),('李四'),('王五'),('赵六');
insert into t_course(c_name) values('语文'),('数学'),('英语');
insert into student_course values(1,1),(1,3),(2,3),(2,2),(3,2),(3,3),(4,1),(4,2);

多对多的嵌套查询

<resultMap id="studentMap" type="Student">
	<id property="id" column="s_id"/>
	<result property="name" column="s_name"/>
	<collection property="sandcs" column="s_id" ofType="SandC" javaType="list" select="sAndcSelect"/>
    <!--
		property：所放的值是在该类中通过私有传入的要查询的类的List集合
		ofType中的SandC：是嵌套要查询的下一个实体类
		select中的sAndcSelect：是下一个查询的识别id
		javaType：查询的返回值类型，这里在配置文件中通<typeAliases>标签起了别名，即可使用list或_list
	-->
</resultMap>

	<select id="findStudentByIdStudent" parameterType="int" resultMap="studentMap">
		select * from t_student where s_id=#{id};
	</select>

	<resultMap id="sandcMap" type="SandC">
		<result property="sid" column="student_id"/>
		<result property="cid" column="course_id"/>
		<collection property="courses" column="course_id" ofType="Course" javaType="list" 										select="courseSelect"/>
	</resultMap>
	<select id="sAndcSelect" parameterType="int" resultMap="sandcMap">
		select * from student_course where student_id=#{id}
	</select>

	<resultMap id="courseMap" type="Course">
		<id property="id" column="c_id"/>
		<result property="name" column="c_name"/>
	</resultMap>
	<select id="courseSelect" parameterType="int" resultMap="courseMap">
		select * from t_course where c_id=#{id};
	</select>

多对多的嵌套结果查询

<resultMap id="studentMapTwo" type="Student">
	<id property="id" column="s_id"/>
	<result property="name" column="s_name"/>
	<collection property="courses" column="s_id" javaType="list" ofType="Course">
		<id property="id" column="c_id"/>
		<result property="name" column="c_name"/>
	</collection>
</resultMap>
	<select id="findStudentByIdStudentTwo" parameterType="int" resultMap="studentMapTwo">
		select s.*,c.* from t_student s
		join student_course sc
		on s.s_id=sc.student_id
		join t_course c on sc.course_id=
		c.c_id where s.s_id=#{id}
	</select>