多表查詢的實現

一、新增資料表

新增資料表的sql文：

-- 建立course
create table course(
	 id int(20) auto_increment primary key,
   courseName varchar(60),
   detail varchar(1024)
);

-- 建立student
create table student(
	id int(20) auto_increment primary key,
	stuName varchar(60),
  gender tinyint(4),
  stuCardId int(20),
  detail varchar(1024)	
);

-- studentCard表
create table studentCard(
	id int(20) auto_increment primary key,
  stuId int(20),
  jiguan varchar(60),
  destTime date,
  endTime date,
  detail varchar(1024),
  foreign key(stuId) references student(id)
);

-- 建立courseGrade表
create table courseGrade(
	id int(20) auto_increment primary key,
  stuId int(20),
  courseId int(20),
  grade decimal(16,2),
  detail varchar(1024),
	foreign key(stuId) references student(id),
  foreign key(courseId) references course(id)
);

二、新增xml配置檔案

1.course.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.xiaopeng.dao.CourseMapper">
 	<resultMap type="com.xiaopeng.model.Course" id="courseMapper">
 		<id column="id" property="id"/>
 		<result column="courseName" property="courseName"/>
 		<result column="detail" property="detail"/>
 	</resultMap>
 	
 	<!-- 通過id查詢課程的資訊 -->
 	<select id="getCourse" parameterType="int" resultMap="courseMapper">
 		select * from course where course.id = #{id}
 	</select>
 </mapper>

2.student.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.xiaopeng.dao.StudentMapper">
 	<resultMap type="com.xiaopeng.model.Student" id="studentMap">
 		<id property="id" column="id"/>
 		<result property="stuName" column="stuName"/>
 		<result property="gender" column="gender"/>
 		<result property="detail" column="detail"/>
 		<!-- 學生證 -->
 		<association property="studentCard" column="id" 
 		select="com.xiaopeng.dao.StudentCardMapper.findStuCardByStuId"></association>
 		<!-- 學生成績(根據學生的編號查詢所有的成績資訊) -->
 		<collection property="courseGrades" column="id" 
 		select="com.xiaopeng.dao.CourseGradeMapper.selectByStuId">
 		</collection>
 	</resultMap>
 	<select id="getStudent" parameterType="int" resultMap="studentMap">
 		select * from student where student.id=#{hehe}
 	</select>
 </mapper>

3.studentcard.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.xiaopeng.dao.StudentCardMapper">
 	<resultMap type="com.xiaopeng.model.StudentCard" id="studentCardMap">
 		<id property="id" column="id"/>
 		<result property="stuId" column="stuId"/>
 		<result property="jiguan" column="jiguan"/>
 		<result property="destTime" column="destTime"/>
 		<result property="endTime" column="endTime"/>
 		<result property="detail" column="detail"/>
 	</resultMap>
  
 	<!-- 通過學生的id查詢學生的學生證件 -->
 	<select id="findStuCardByStuId" parameterType="int" resultMap="studentCardMap">
 		select studentcard.id,studentcard.stuId,
			studentcard.jiguan,studentcard.destTime,
			studentcard.endTime,studentcard.detail
		from studentcard
		where studentcard.stuId=#{amaoagou}
 	</select>
 </mapper>

4、coursegrade.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.xiaopeng.dao.CourseGradeMapper">
 	<resultMap type="com.xiaopeng.model.CourseGrade" id="courseGradeMap">
 		<id column="id" property="id"/> 		
 		<result property="grade" column="grade"/>
 		<result property="detail" column="detail"/>
 		<!-- association放在最後 -->
 		<association property="student" select="com.xiaopeng.dao.StudentMapper.getStudent" column="stuId"> 			
 		</association>
 		<association property="course" select="com.xiaopeng.dao.CourseMapper.getCourse" column="courseId">
 		</association>
 	</resultMap>
 	
 	<!-- 根據學生的學號查詢學生的成績 -->
 	<select id="selectByStuId" parameterType="int" resultMap="courseGradeMap">
 		select * from coursegrade where coursegrade.stuId = #{stuid}
 	</select>
 </mapper>