基于JavaWeb的学生信息管理系统
1public class CourseDao {
2 // 获取所有课程的信息，用ArrayList返回
3 public ArrayList<Course> query_all_course() {
4 Connection conn = DBUtils.getConnection();
5 String sql = "select * from course order by cno;";
6 ArrayList<Course> results = new ArrayList<Course>();
7 try {
8 PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
9 ResultSet rs = ps.executeQuery();
10 while (rs.next()) {
11 Course temp = new Course();
12 temp.setCno(rs.getString("Cno"));
13 temp.setCname(rs.getString("Cname"));
14 temp.setCteacher(rs.getString("Cteacher"));
15 temp.setCcredit(rs.getInt("Ccredit"));
16 results.add(temp);
17 }
18 // 关闭资源
19 rs.close();
20 ps.close();
21 } catch (SQLException e) {
22 e.printStackTrace();
23 } finally {
24 DBUtils.closeConnection(conn);
25 }
26 return results;
27 }
28 // 插入课程信息，返回一个int值表示状态,1：成功，0失败
29 public int insert_course(String Cno, String Cname, String Cteacher, double Ccredit) {
30 Connection conn = DBUtils.getConnection();
31 String sql = "insert into course values(?,?,?,?);";
32 int flag = 0;
33 try {
34 PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
35 ps.setString(1, Cno);
36 ps.setString(2, Cname);
37 ps.setString(3, Cteacher);
38 ps.setDouble(4, Ccredit);
39 flag = ps.executeUpdate();
40 ps.close();
41 } catch (SQLException e) {
42 e.printStackTrace();
43 } finally {
44 DBUtils.closeConnection(conn);
45 }
46 return flag;
47 }
48 // 删除课程信息，返回一个int值表示状态,1：成功，0失败
49 public int delete_course(String Cno) {
50 Connection conn = DBUtils.getConnection();
51 String sql = "delete from course where Cno = ?;";
52 int flag = 0;
53 try {
54 PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
55 ps.setString(1, Cno);
56 flag = ps.executeUpdate();
57 ps.close();
58 } catch (SQLException e) {
59 e.printStackTrace();
60 } finally {
61 DBUtils.closeConnection(conn);
62 }
63 return flag;
64 }
65 //修改课程信息，返回一个int值表示状态,1：成功，0失败
66 public int alter_course(String cno,String after_cno,String after_cname,String after_cteacher,double after_ccredit) {
67 Connection conn = DBUtils.getConnection();
68 String sql = "update course set cno = ?,cname = ?,cteacher = ?,ccredit = ? where cno = ?;";
69 int flag = 0;
70 try {
71 PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
72 ps.setString(1, after_cno);
73 ps.setString(2, after_cname);
74 ps.setString(3, after_cteacher);
75 ps.setDouble(4, after_ccredit);
76 ps.setString(5, cno);
77 flag = ps.executeUpdate();
78 ps.close();
79 } catch (SQLException e) {
80 e.printStackTrace();
81 }finally {
82 DBUtils.closeConnection(conn);
83 }
84 return flag;
85 }
86 // 查询课程平均分信息，返回一个ArrayLst集合
87 public ArrayList<Course_avg> course_avg() {
88 Connection conn = DBUtils.getConnection();
89 String sql = "select cno,cname,avg(grade) avg from course,sc where = group by cno order by cno;";
90 ResultSet result = null;
91 ArrayList<Course_avg> course_avg = new ArrayList<Course_avg>();
92 try {
93 PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
94 result = ps.executeQuery();
95 while(result.next()){
96 Course_avg temp = new Course_avg();
97 temp.setCno(result.getString("Cno"));
98 temp.setCname(result.getString("Cname"));
99 temp.setAvg(result.getDouble("avg"));
100 course_avg.add(temp);
101 }
102 ps.close();
103 result.close();
104 } catch (SQLException e) {
105 e.printStackTrace();
106 } finally {
107 DBUtils.closeConnection(conn);
108 }
109 return course_avg;
110 }
111 //查询课程不及格率，返回一个ArrayList集合
112 public ArrayList<Course_fail_rate> fail_rate(){
113 Connection conn = DBUtils.getConnection();
114 String sql = "select cno,(select cname from course where cno = ) cname,cast(100.0*(select count(sno) from sc where grade < 60 and cno = )/(select count(sno) from sc where cno = ) as decimal(18,2)) rate from sc x group by cno order by cno;";
115 ArrayList<Course_fail_rate> fail_rate = new ArrayList<Course_fail_rate>();
116 try {
117 PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
118 ResultSet rs = ps.executeQuery();
119 while(rs.next()){
120 Course_fail_rate temp = new Course_fail_rate();
121 temp.setCno(rs.getString("cno"));
122 temp.setCname(rs.getString("cname"));
123 temp.setFail_rate(rs.getDouble("rate"));
124 fail_rate.add(temp);
125 }
126 rs.close();
127 ps.close();
128 } catch (SQLException e) {
129 e.printStackTrace();
130 } finally {
131 DBUtils.closeConnection(conn);
132 }
133 return fail_rate;
134 }
135 //查询课程排名情况,返回一个ArrayList集合
136 public ArrayList<Course_ranking> course_ranking(String cno){
137 Connection conn = DBUtils.getConnection();
138 String sql = "select Sno,Dname,Clname,Sname,Ssex,Sage,Grade from department,class,student,sc where = and = and = and cno = '"+cno+"' order by grade desc;";
139 ArrayList<Course_ranking> course_ranking = new ArrayList<Course_ranking>();
140 try {
141 PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
142 ResultSet rs = ps.executeQuery();
143 while(rs.next()){
144 Course_ranking temp = new Course_ranking();
145 temp.setSno(rs.getString("Sno"));
146 temp.setDname(rs.getString("Dname"));
147 temp.setClname(rs.getString("Clname"));
148 temp.setSname(rs.getString("Sname"));
149 temp.setSsex(rs.getString("Ssex"));
150 temp.setSage(rs.getInt("Sage"));
151 temp.setGrade(rs.getDouble("Grade"));
152 course_ranking.add(temp);
153 }
154 rs.close();
155 ps.close();
156 } catch (SQLException e) {
157 e.printStackTrace();
158 } finally {
159 DBUtils.closeConnection(conn);
160 }
161 return course_ranking;
162 }
}