mybatis学习 - 多表查询的实现

多表查询的实现

一、添加数据表

添加数据表的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>