mysql 多对多拆分成 一对多(学生，选修课，成绩)

包结构

数据库表结构

 

entity

 

 1 package com.jdbc.dao;
 2 
 3 import java.sql.SQLException;
 4 import java.util.List;
 5 
 6 import org.apache.commons.dbutils.QueryRunner;
 7 import org.apache.commons.dbutils.handlers.BeanHandler;
 8 import org.apache.commons.dbutils.handlers.BeanListHandler;
 9 
10 import com.chinasofti.jdbc.TxQueryRunner;
11 import com.jdbc.entity.Course;
12 import com.jdbc.entity.Student;
13 
14 public class StudentDao {
15     /*
16      * 获取一个学生的所有选修课
17      */
18 
19     public Student getStuByCourse(int sid) throws SQLException {
20         QueryRunner qr =new TxQueryRunner();
21         String sql = "select * from student where sid = ?";
22         Student student = qr.query(sql, new BeanHandler<Student>(Student.class),sid);
23          sql ="select * from course c,score sc where sc.cid =c.cid and sid = ?";
24          List<Course> courses = qr.query(sql, new BeanListHandler<Course>(Course.class),student.getSid());
25          student.setCourses(courses);
26         return student;
27          
28     }
29     /*
30      * 获取所有学生
31      */
32     public List<Student> getStusByCourses() throws SQLException{
33         QueryRunner qr =new TxQueryRunner();
34         String sql = "select * from student";
35         List<Student> students = qr.query(sql, new BeanListHandler<Student>(Student.class));
36         for(Student stu:students) {
37              sql ="select * from course c,score sc where sc.cid =c.cid and sid = ?";
38              List<Course> courses = qr.query(sql, new BeanListHandler<Course>(Course.class),stu.getSid());
39              stu.setCourses(courses);
40         }
41         return students;
42         
43     }
44 }
StudentDao
 1 package com.jdbc.dao;
 2 
 3 import java.sql.SQLException;
 4 import java.util.List;
 5 
 6 import org.apache.commons.dbutils.QueryRunner;
 7 import org.apache.commons.dbutils.handlers.BeanHandler;
 8 import org.apache.commons.dbutils.handlers.BeanListHandler;
 9 
10 import com.chinasofti.jdbc.TxQueryRunner;
11 import com.jdbc.entity.Course;
12 import com.jdbc.entity.Student;
13 
14 public class CourseDao {
15     /*
16      * 获取一个课程 对象 包含选修的学生
17      */
18     public Course getCourse(int cid) throws SQLException {
19         QueryRunner qr = new TxQueryRunner();
20         String sql = "select * from course where cid = ?";
21         Course course = qr.query(sql, new BeanHandler<Course>(Course.class),cid);
22         sql = "select * from student s, score sc where s.sid = sc.sid and cid = ?";
23         List<Student> students = qr.query(sql, new BeanListHandler<Student>(Student.class),course.getCid());
24         course.setStudents(students);
25         return course;
26         
27     }
28     /*
29      * 获取所有课程  每个课程包含多个学生
30      */
31     public List<Course> getCourses() throws SQLException {
32         QueryRunner qr = new TxQueryRunner();
33         //获取所有课程
34         String sql = "select * from course";
35         List<Course> courses = qr.query(sql, new BeanListHandler<Course>(Course.class));
36         for(Course c :courses) {
37             sql = "select * from student s,score sc where s.sid = sc.sid and sc.cid =?";
38             List<Student> students = qr.query(sql, new BeanListHandler<Student>(Student.class),c.getCid());
39             c.setStudents(students);
40         }
41         return courses;
42     }
43 }
CourseDao
 1 package com.jdbc.dao;
 2 
 3 import java.sql.SQLException;
 4 import java.util.List;
 5 
 6 import org.apache.commons.dbutils.QueryRunner;
 7 import org.apache.commons.dbutils.handlers.BeanHandler;
 8 import org.apache.commons.dbutils.handlers.BeanListHandler;
 9 
10 import com.chinasofti.jdbc.TxQueryRunner;
11 import com.jdbc.entity.Course;
12 import com.jdbc.entity.Score;
13 import com.jdbc.entity.Student;
14 
15 public class ScoreDao {
16     /*
17      * 获取成绩 通过一个学生
18      */
19     public List<Score> getScoreByStudent(int sid) throws SQLException {
20         QueryRunner qr = new TxQueryRunner();
21         String sql = "select * from score where sid = ?";
22         List<Score> stuScore = qr.query(sql, new BeanListHandler<Score>(Score.class),sid);
23         for(Score sc :stuScore) { //得到每个成绩对应的学生姓名和 科目
24              //通过学生id获取对应的成绩id,有多个成绩
25              sql = "select * from score sc,student s,course c where " +
26                     " sc.sid = s.sid and sc.cid = c.cid and sc.scid = ?"; 
27              //每一行的分数id对应一个学生，一个课程
28              Course StuCourse = qr.query(sql, new BeanHandler<Course>(Course.class),sc.getScid());
29              Student stu = qr.query(sql, new BeanHandler<Student>(Student.class),sc.getScid());
30              //一个分数只有一个课程 ，也只有一个学生
31              sc.setCourse(StuCourse);
32              sc.setStudent(stu); 
33         }
34         return stuScore;
35         
36     }
37     /*
38      * 获取成绩  所有学生
39      * 
40      */
41     public List<Score> getScoresByStudent() throws SQLException {
42         QueryRunner qr = new TxQueryRunner();
43         //获取所有成绩 scid升序
44         String sql = "select * from score order by scid";
45         List<Score> scores = qr.query(sql, new BeanListHandler<Score>(Score.class));
46         for(Score sc:scores) {
47             //三表联接获取学生姓名和学科名字
48             sql = "select * from score sc,student s,course c "
49                     + " where s.sid = sc.sid and c.cid = sc.cid and scid = ?";
50             Course course = qr.query(sql, new BeanHandler<Course>(Course.class),sc.getScid());
51             Student stu = qr.query(sql, new BeanHandler<Student>(Student.class),sc.getScid());
52             sc.setCourse(course);
53             sc.setStudent(stu);
54         }
55         return scores;
56                 
57     }
58 }
ScoreDao
 1 package com.jdbc.test;
 2 
 3 import java.sql.SQLException;
 4 
 5 import org.junit.Test;
 6 
 7 import com.jdbc.dao.StudentDao;
 8 
 9 public class StudentTest {
10     @Test
11     public void getStuByCourseTest() throws SQLException {
12         System.out.println(new StudentDao().getStuByCourse(1));
13 //    执行结果
14 //        Student [sid=1, sname=刘备, courses=[Course [cid=1, cname=语文, students=null], Course [cid=2, cname=数学, students=null]]]
15     }
16     @Test
17     public void getStusByCourseTest() throws SQLException {
18         System.out.println(new StudentDao().getStusByCourses());
19 //    执行结果
20 //[Student [sid=1, sname=刘备, courses=[Course [cid=1, cname=语文, students=null], Course [cid=2, cname=数学, students=null]]], Student [sid=2, sname=关羽, courses=[Course [cid=3, cname=英语, students=null]]], Student [sid=3, sname=张飞, courses=[Course [cid=1, cname=语文, students=null], Course [cid=2, cname=数学, students=null]]]]
21     }
22 }
StudentTest
 1 package com.jdbc.test;
 2 
 3 import java.sql.SQLException;
 4 import java.util.List;
 5 
 6 import org.junit.Test;
 7 
 8 import com.jdbc.dao.CourseDao;
 9 import com.jdbc.entity.Course;
10 
11 public class CourseTest {
12     @Test
13     public void getCourseTest() throws SQLException {
14         System.out.println(new CourseDao().getCourse(1));
15 // 执行结果
16 //Course [cid=1, cname=语文, students=[Student [sid=1, sname=刘备, courses=null], Student [sid=3, sname=张飞, courses=null]]]
17     }
18     @Test
19     public void getCoursesTest() throws SQLException {
20         List<Course> courses = new CourseDao().getCourses();
21         for(Course c:courses) {
22             System.out.println(c);
23 //    执行结果
24 //Course [cid=1, cname=语文, students=[Student [sid=1, sname=刘备, courses=null], Student [sid=3, sname=张飞, courses=null]]]
25 //Course [cid=2, cname=数学, students=[Student [sid=1, sname=刘备, courses=null], Student [sid=3, sname=张飞, courses=null]]]
26 //Course [cid=3, cname=英语, students=[Student [sid=2, sname=关羽, courses=null]]]
27         }
28     }
CourseTest
 1 package com.jdbc.test;
 2 
 3 import java.sql.SQLException;
 4 import java.util.List;
 5 
 6 import org.junit.Test;
 7 
 8 import com.jdbc.dao.ScoreDao;
 9 import com.jdbc.entity.Score;
10 
11 public class ScoreTest {
12     @Test
13     public void getScoreTest() throws SQLException {
14         List<Score> scoreByStudent = new ScoreDao().getScoreByStudent(1);
15         System.out.println(scoreByStudent.get(0));
16         System.out.println(scoreByStudent.get(1));
17 //执行结果
18 //Score [scid=1, grade=91, sid=1, cid=1, student=Student [sid=1, sname=刘备, courses=null], course=Course [cid=1, cname=语文, students=null]]
19 //Score [scid=2, grade=81, sid=1, cid=2, student=Student [sid=1, sname=刘备, courses=null], course=Course [cid=2, cname=数学, students=null]]
20 
21     }
22     @Test
23     public void getScoresTest() throws SQLException {
24         List<Score> scoresByStudent = new ScoreDao().getScoresByStudent();
25             for(Score sc: scoresByStudent) {
26                 System.out.println(sc);
27             }
28 //执行结果
29 //Score [scid=1, grade=91, sid=1, cid=1, student=Student [sid=1, sname=刘备, courses=null], course=Course [cid=1, cname=语文, students=null]]
30 //Score [scid=2, grade=81, sid=1, cid=2, student=Student [sid=1, sname=刘备, courses=null], course=Course [cid=2, cname=数学, students=null]]
31 //Score [scid=3, grade=92, sid=2, cid=3, student=Student [sid=2, sname=关羽, courses=null], course=Course [cid=3, cname=英语, students=null]]
32 //Score [scid=4, grade=93, sid=3, cid=2, student=Student [sid=3, sname=张飞, courses=null], course=Course [cid=2, cname=数学, students=null]]
33 //Score [scid=5, grade=83, sid=3, cid=1, student=Student [sid=3, sname=张飞, courses=null], course=Course [cid=1, cname=语文, students=null]]
34 
35     }
36     
37 }
ScoreTest

 

posted @ 2019-04-29 09:35  我差两天十八岁  阅读(718)  评论(0)  编辑  收藏  举报