【持久化框架MyBatis3五】MyBatis3一对多关联查询
编程技术 / houtizong 发布于 3年前 70
以教员和课程为例介绍一对多关联关系，在这里认为一个教员可以叫多门课程，而一门课程只有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','[email protected]','111-222-3333','1980-05-20','MALE',NULL,NULL,1), (2,'Ken','[email protected]','111-222-3333','1980-05-20','MALE',NULL,NULL,1), (3,'Paul','[email protected]','123-321-4444','1981-03-15','FEMALE',NULL,NULL,2), (4,'Mike','[email protected]','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 >= #{startDate} </if> <if test="endDate != null"> AND end_date <= #{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_dateFROM 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_idwhere 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, countryFROM tutors t left outer join addresses a on t.addr_id=a.addr_idwhere 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
请勿发布不友善或者负能量的内容。与人为善，比聪明更重要！
技术博客集 - 网站简介：
前后端技术：
后端基于Hyperf2.1框架开发,前端使用Bootstrap可视化布局系统生成
网站主要作用：
1.编程技术分享及讨论交流，内置聊天系统;
2.测试交流框架问题，比如：Hyperf、Laravel、TP、beego;
3.本站数据是基于大数据采集等爬虫技术为基础助力分享知识，如有侵权请发邮件到站长邮箱，站长会尽快处理;
4.站长邮箱：[email protected];
文章归档
文章标签
友情链接
