包结构
数据库表结构
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 }
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 }
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 }
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 }
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 }
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 }