当前位置:首页 > 开发 > 开源软件 > 正文

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

发表于: 2014-09-07   作者:bit1129   来源:转载   浏览:
摘要: 以教员和课程为例介绍一对多关联关系,在这里认为一个教员可以叫多门课程,而一门课程只有1个教员教,这种关系在实际中不太常见,通过教员和课程是多对多的关系。   示例数据:   地址表:   CREATE TABLE ADDRESSES ( ADDR_ID INT(11) NOT NULL AUTO_INCREMENT, STREET VAR

以教员和课程为例介绍一对多关联关系,在这里认为一个教员可以叫多门课程,而一门课程只有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','john@gmail.com','111-222-3333','1980-05-20','MALE',NULL,NULL,1),
 (2,'Ken','ken@gmail.com','111-222-3333','1980-05-20','MALE',NULL,NULL,1),
 (3,'Paul','paul@gmail.com','123-321-4444','1981-03-15','FEMALE',NULL,NULL,2),
 (4,'Mike','mike@gmail.com','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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
MyBatis的主配置文件对MyBatis进行配置和定制,包括 全局属性定义(properties) MyBatis的行为设置(s
MyBatis的主配置文件对MyBatis进行配置和定制,包括 全局属性定义(properties) MyBatis的行为设置(s
MyBatis的主配置文件对MyBatis进行配置和定制,包括 全局属性定义(properties) MyBatis的行为设置(s
这篇博文介绍的是多表中的一对多表关联查询 还是按照上一篇的步骤,先做一些准备工作;创建两张表:
转载请注明: TheViper http://www.cnblogs.com/TheViper autoMappingBehavior是一个容易被忽略的属
开发环境: System:Windows WebBrowser:IE6+、Firefox3+ JavaEE Server:tomcat5.0.2.8、tomcat6
开发环境: System:Windows WebBrowser:IE6+、Firefox3+ JavaEE Server:tomcat5.0.2.8、tomcat6
开发环境: System:Windows WebBrowser:IE6+、Firefox3+ JavaEE Server:tomcat5.0.2.8、tomcat6
1.pom.xml文件增加mybatis3的jar包如下,本人用的spring版本为4.1.6.RELEASE !-- 添加mybatis的核心
目录结构: 步骤: 1、创建User表 DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(1
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号