【持久化框架MyBatis3五】MyBatis3一对多关联查询

以教员和课程为例介绍一对多关联关系，在这里认为一个教员可以叫多门课程，而一门课程只有1个教员教，这种关系在实际中不太常见，通过教员和课程是多对多的关系。

 

示例数据：

 

地址表：

 

CREATE TABLE ADDRESSES 
(
  ADDR_ID INT(11) NOT NULL AUTO_INCREMENT,
  STREET VARCHAR(50) NOT NULL,
  CITY VARCHAR(50) NOT NULL,
  STATE VARCHAR(50) NOT NULL,
  ZIP VARCHAR(10) DEFAULT NULL,
  COUNTRY VARCHAR(50) NOT NULL,
  PRIMARY KEY (ADDR_ID)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF-8;

 

 

教员表：

 

 

CREATE TABLE TUTORS 
(
  TUTOR_ID INT(11) NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(50) NOT NULL,
  EMAIL VARCHAR(50) NOT NULL,
  PHONE VARCHAR(15) DEFAULT NULL,  
  DOB DATE DEFAULT NULL,
  GENDER VARCHAR(6) DEFAULT NULL,
  BIO LONGTEXT DEFAULT NULL,
  PIC BLOB DEFAULT NULL,
  ADDR_ID INT(11) DEFAULT NULL,
  PRIMARY KEY (TUTOR_ID),
  UNIQUE KEY UK_EMAIL (EMAIL),
  CONSTRAINT FK_TUTORS_ADDR FOREIGN KEY (ADDR_ID) REFERENCES ADDRESSES (ADDR_ID)  
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF-8;

 

 

课程表：

 

 

CREATE TABLE COURSES 
(
  COURSE_ID INT(11) NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(100) NOT NULL,
  DESCRIPTION VARCHAR(512) DEFAULT NULL,
  START_DATE DATE DEFAULT NULL,
  END_DATE DATE DEFAULT NULL,
  TUTOR_ID INT(11) NOT NULL,
  PRIMARY KEY (COURSE_ID),
  CONSTRAINT FK_COURSE_TUTOR FOREIGN KEY (TUTOR_ID) REFERENCES TUTORS (TUTOR_ID)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF-8;

 

 

样例数据：

 

 

INSERT INTO ADDRESSES (ADDR_ID,STREET,CITY,STATE,ZIP,COUNTRY) VALUES 
 (1,'4891 Pacific Hwy','San Diego','CA','92110','San Diego'),
 (2,'2400 N Jefferson St','Perry','FL','32347','Taylor'),
 (3,'710 N Cable Rd','Lima','OH','45825','Allen'),
 (4,'5108 W Gore Blvd','Lawton','OK','32365','Comanche');


INSERT INTO TUTORS (TUTOR_ID,NAME,EMAIL,PHONE,DOB,GENDER,BIO,PIC,ADDR_ID) VALUES 
 (1,'John','[e-mail]','111-222-3333','1980-05-20','MALE',NULL,NULL,1),
 (2,'Ken','[e-mail]','111-222-3333','1980-05-20','MALE',NULL,NULL,1),
 (3,'Paul','[e-mail]','123-321-4444','1981-03-15','FEMALE',NULL,NULL,2),
 (4,'Mike','[e-mail]','123-321-4444','1981-03-15','MALE',NULL,NULL,2);


INSERT INTO COURSES (COURSE_ID,NAME,DESCRIPTION,START_DATE,END_DATE,TUTOR_ID) VALUES 
 (1,'Quickstart Core Java','Core Java Programming','2013-03-01','2013-04-15',1),
 (2,'Quickstart JavaEE6','Enterprise App Development using JavaEE6','2013-04-01','2013-08-30',1),
 (3,'MyBatis3 Premier','MyBatis 3 framework','2013-06-01','2013-07-15',2);

 

 

Course-Mapper.xml

 

<?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.mybatis3.mappers.CourseMapper">
	
	<cache eviction="FIFO" flushInterval="60000" size="512" readOnly="false"/>
        <!--resultMap:课程表与Course实体之间的对应关系-->
  	<resultMap type="Course" id="CourseResult">
  		<id 	column="course_id" property="courseId"/>
  		<result column="name" property="name"/>
  		<result column="description" property="description"/>
  		<result column="start_date" property="startDate"/>
  		<result column="end_date" property="endDate"/>
  	</resultMap>
  	
  	<select id="selectCoursesByTutor" parameterType="int" resultMap="CourseResult">
  		select * from courses where tutor_id=#{tutorId}
  	</select>
  	<!--根据条件查询课程，这里使用了if做动态SQL的组装-->
  	<select id="searchCourses" parameterType="hashmap" resultMap="CourseResult" useCache="false">
  		SELECT * FROM COURSES
  		WHERE TUTOR_ID= #{tutorId}
  		<if test="courseName != null">
  			AND name like #{courseName}
  		</if>
  		<if test="startDate != null">
  			AND start_date  &gt;= #{startDate}
  		</if>
  		<if test="endDate != null">
  			AND end_date  &lt;= #{endDate}
  		</if>
  		
  	</select>
  	<!--查询一组教学所教的课程，这里用到了foreach循环-->
  	<select id="searchCoursesByTutors" parameterType="hashmap" resultMap="CourseResult">
  		SELECT * FROM COURSES
  		<if test="tutorIds != null">
  		<where>
  		tutor_id IN
                <!--foreeach的每个对象是tutorId,使用#{tutorId}表示-->
                <!--open=...close..表示以(开头，以)结果，每个元素用,分开-->
  		<foreach item="tutorId" collection="tutorIds"
	      open="(" separator="," close=")">
	        #{tutorId}
	  	</foreach>
   		</where>  		
   		</if>
  	</select>
  	
</mapper>

 

 

Tutor-Mapping.xml

 

 

<?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.mybatis3.mappers.TutorMapper">
	
	
  	<resultMap type="Tutor" id="TutorWithCoursesNestedResult">
  		<id 	column="tutor_id" property="tutorId"/>
  		<result column="tutor_name" property="name"/>
  		<result column="email" property="email"/>
                <!--assocation定义1对1关联，使用resultMap属性表示address对应的结果类型是AddressResult对应的type，即Address对象-->
  		<association property="address" resultMap="com.mybatis3.mappers.AddressMapper.AddressResult"/>
                <!--collection定义1对多关联，resultMap表示1对多的多的那一方的类型是CourseResult-->
  		<collection property="courses"  resultMap="com.mybatis3.mappers.CourseMapper.CourseResult"  />
  	</resultMap>
  	
  	<resultMap type="Tutor" id="TutorWithCoursesNestedSelect">
  		<id 	column="tutor_id" property="tutorId"/>
  		<result column="tutor_name" property="name"/>
  		<result column="email" property="email"/>
  		<association property="address" resultMap="com.mybatis3.mappers.AddressMapper.AddressResult"/>
                <!--colllection定义1对多关联，通过SQL语句CourseMapper.selectCoursesByTutor获得关联的课程列表-->
                <!---->
  		<collection property="courses"  column="tutor_id" select="com.mybatis3.mappers.CourseMapper.selectCoursesByTutor"/>
  	</resultMap>
  	
        <!--使用Address是通过Assocation + resultMap关联，左外链接查询Address-->      
        <!--使用Course是通过Assocation + resultMap关联，所以左外链接查询Courses-->
  	<select id="selectTutorById" parameterType="int" resultMap="TutorWithCoursesNestedResult">
  		SELECT t.tutor_id, t.name as tutor_name, email, a.addr_id, street, city, state, zip, country,
       			course_id, c.name, description, start_date, end_date
		FROM tutors t left outer join addresses a on t.addr_id=a.addr_id
		  left outer join courses c on t.tutor_id=c.tutor_id
		where t.tutor_id=#{tutorId}
  	</select>
  	<!--使用Address是通过Assocation + resultMap关联，左外链接查询Address-->
        <!--因为Course是使用collection + select进行关联，所以不要对Course表做连接查询-->
  	<select id="selectTutorWithCourses" parameterType="int" resultMap="TutorWithCoursesNestedSelect">
  		SELECT t.tutor_id, t.name as tutor_name, email, a.addr_id, street, city, state, zip, country
		FROM tutors t left outer join addresses a on t.addr_id=a.addr_id
		where t.tutor_id=#{tutorId}
  	</select>
  	
</mapper>

 

 

 

 总结：

1. 1对多关联查询使用collection，collection的属性有两个，resultMap和select

2. collection+resultMap

    这种方式，需要在sql语句中使用连接查询，不能懒加载

3. collection+select

   这种方式，在sql语句中不需要使用连接查询，可以使用懒加载，但是需要两遍查询，a.首先查询出教员信息 b.根据教员ID查询Course表，查出所有的符合条件的Course