学生 和 课程
一个学生可以选择多门课程:getCourses()
一门课程，也可以被多个学生选择:getStudents()
学生
Student(id,name,sex,pwd,phone,grade,photo)
课程
Course(id,name,type,hours)
数据库的设计：
中间表
student_course(sid,cid）
数据库表关系
sid cid
2 3
2 3(x)
2 4
13 4
sid和cid的组合不能重复
primary key , 复合主键,联合主键(composite key)
提取学生的时候，要把学生选择的所有课程也提取出来。(三个表的左连接查询，相对比较复杂)
left jion ：学生即使没有选择课程，也能把学生的信息提取出来。
from student,course where （=内连接:inner join）
… 如果学生没有选择课程，学生的信息会提取不出来
三表左连接的思路：
(一) 提取学生信息的时候，能够把学生的所选课程都提取出来
A表-学生表(id,...) B表-学生课程表(sid,cid),C表（id,...）-课程表
（学生表-学生课程表）-课程表 左连接
(二) 在提取课程信息的时候，能够把选择该课程的学生的信息也提取出来
课程表course - （学生课程表 student_course - 学生表student） 左连接
模型提升：
查询A对象的信息：A( AB B)
查询B对象的信息：B (BA A)
1、新建student、course和Student_course数据表
Student_course表中的sid和cid要分别设置为student表和course表的外键
2、新建Student、Course和StudentCourse 实体类 通过右键source做set和get方法 ，重写通toString方法
Student 属性：
private int id;
private String name;
private String sex;
private String pwd;
private String phone;
private String grade;
private String photo;
private List<Course> courses;
Course属性：
private int id;
private String name;
private String type;
private String hours;
private List<Student> students;
StudentCourse属性（//建立StudentCourse类用来和student_course表对应）：
private Student student;
private Course course;
3、新建StudentMapper、CourseMapper和StudentCourseMapper接口
package javastudy;
public interface StudentMapper {
public void add(Student student);
public Student get(int id);
}
package javastudy;
public interface CourseMapper {
public void add(Course course);
public Course get(int id);
}
package javastudy;
public interface StudentCourseMapper {
public void add(StudentCourse studentCourse);
}
4、新建StudentMapper.xml、CourseMapper.xml和StudentCourseMapper.xml
StudentMapper.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="javastudy.StudentMapper">
<insert id="add" parameterType="javastudy.Student" useGeneratedKeys="true" keyProperty="id">
insert into student
(
name,
sex,
pwd,
phone,
grade,
photo
)
values
(
#{name},
#{sex},
#{pwd},
#{phone},
#{grade},
#{photo}
)
</insert>
<select id="get" parameterType="javastudy.Student" resultMap="s">
select
sid,sname,ssex,spwd,sphone,sgrade,sphoto,
c.id cid,
c.name cname,
c.type ctype,
c.hours chours
from
(
select
s.id sid,
s.name sname,
s.sex ssex,
s.pwd spwd,
s.phone sphone,
s.grade sgrade,
s.photo sphoto,
sc.cid sccid
from
student s left join student_course sc
on
s.id=sc.sid
) temp
left join course c
on temp.sccid=c.id
where sid=#{id}
</select>
<resultMap type="javastudy.Student" id="s">
<id property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="sex" column="ssex"/>
<result property="pwd" column="spwd"/>
<result property="phone" column="sphone"/>
<result property="grade" column="sgrade"/>
<result property="photo" column="sphoto"/>
<collection property="courses" ofType="javastudy.Course">
<id property="id" column="cid"/>
<result property="name" column="cname"/>
<result property="type" column="ctype"/>
<result property="hours" column="chours"/>
</collection>
</resultMap>
</mapper>
CourseMapper.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="javastudy.CourseMapper">
<insert id="add" parameterType="javastudy.Course">
insert into course
(
name,
type,
hours
)
values
(
#{name},
#{type},
#{hours}
)
</insert>
<select id="get" parameterType="javastudy.Course" resultMap="c">
select
sid,sname,ssex,spwd,sphone,sgrade,sphoto,
c.id cid,
c.name cname,
c.type ctype,
c.hours chours
from
course c
left join
(
select
s.id sid,
s.name sname,
s.sex ssex,
s.pwd spwd,
s.phone sphone,
s.grade sgrade,
s.photo sphoto,
sc.cid sccid
from
student_course sc
left join
student s
on sc.sid=s.id
) temp
on c.id=temp.sccid
where c.id=#{id}
</select>
<resultMap type="javastudy.Course" id="c">
<id property="id" column="cid"/>
<result property="name" column="cname"/>
<result property="type" column="ctype"/>
<result property="hours" column="chours"/>
<collection property="students" ofType="javastudy.Student">
<id property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="sex" column="ssex"/>
<result property="pwd" column="spwd"/>
<result property="phone" column="sphone"/>
<result property="grade" column="sgrade"/>
<result property="photo" column="sphoto"/>
</collection>
</resultMap>
</mapper>
StudentCourseMapper.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="javastudy.StudentCourseMapper">
<insert id="add" parameterType="javastudy.StudentCourse">
insert into student_course (sid,cid) values (#{student.id},#{course.id})
</insert>
</mapper>
5、在mybatis-config.xml文件中增加
<!-- mapping 文件路径配置 -->
<mappers>
<mapper class="javastudy.CourseMapper"/>
<mapper class="javastudy.StudentMapper"/>
<mapper class="javastudy.StudentCourseMapper"/>
</mappers>
6、在测试类中写代码，增加两个学生和两门课程
@Test
public void testAdd() throws IOException {
SqlSession session=MyBatisUtils.openSession();
StudentMapper studentMapper=session.getMapper(StudentMapper.class);
Student student=new Student();
student.setName("刘德华");
student.setSex("男");
student.setPwd("12345678");
student.setPhone("1882288005");
student.setGrade("二年级");
studentMapper.add(student);
Student student1=new Student();
student1.setName("朱丽倩");
student1.setSex("女");
student1.setPwd("123456");
student1.setPhone("188888888888");
student1.setGrade("一年级");
studentMapper.add(student1);
CourseMapper courseMapper=session.getMapper(CourseMapper.class);
Course course=new Course();
course.setName("JAVA入门与提高");
course.setType("必修");
course.setHours("60");
courseMapper.add(course);
course=new Course();
course.setName("精通Java web");
course.setType("选修");
course.setHours("30");
courseMapper.add(course);
session.commit();
session.close();
7、插入中间表信息，把学生和课程对应起来
@Test
public void testStudentCourseAdd() throws IOException
{
SqlSession session=MyBatisUtils.openSession();
StudentCourseMapper studentCourseMapper=session.getMapper(StudentCourseMapper.class);
StudentMapper studentMapper=session.getMapper(StudentMapper.class);
CourseMapper courseMapper=session.getMapper(CourseMapper.class);
StudentCourse studentCourse=new StudentCourse();
studentCourse.setStudent(studentMapper.get(26));
studentCourse.setCourse(courseMapper.get(5));
studentCourseMapper.add(studentCourse);
studentCourse=new StudentCourse();
studentCourse.setStudent(studentMapper.get(26));
studentCourse.setCourse(courseMapper.get(6));
studentCourseMapper.add(studentCourse);
session.commit();
session.close();
}
（2）查询数据
//查询学生信息及对应的课程信息
@Test
public void testSelect() throws IOException {
SqlSession session=MyBatisUtils.openSession();
// 查学生信息，同时可以提取该学生所选的课程
StudentMapper studentMapper=session.getMapper(StudentMapper.class);
Student student=studentMapper.get(26);
System.out.println(student);
//查课程信息，同时可以提取选该课程的学生
// CourseMapper courseMapper=session.getMapper(CourseMapper.class);
// Course course=courseMapper.get(4);
// System.out.println(course);
// session.close();
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
