sql_homework_servlet_JDBC.zip_JDBC增删改查_jdbc_jdbc学生管理

package com.zx.dao; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import com.mysql.jdbc.Connection; import com.mysql.jdbc.PreparedStatement; import com.mysql.jdbc.Statement; import com.zx.model.DBCon; import com.zx.model.SC; public class SCDao { private static SCDao instance = null; public static SCDao getInstance() { if (instance == null) instance = new SCDao(); return instance; } // 插入数据 public boolean saveSC(SC sc) { boolean result = false; Connection con = null; try { con = DBCon.getMySQLCon(); String sql = "insert into SC(Sno,Cno,Grade) values(?,?,?)"; PreparedStatement stmt = (PreparedStatement) con .prepareStatement(sql); stmt.setString(1, sc.getSno()); stmt.setString(2, sc.getCno()); stmt.setInt(3, sc.getGrade()); int i = stmt.executeUpdate(); if (i == 1) { // System.out.println("插入数据成功"); result = true; } else { // System.out.println("插入数据失败"); result = false; } } catch (Exception e) { e.printStackTrace(); } finally { try { con.close(); } catch (Exception e) { e.printStackTrace(); } } return result; } // 修改数据 public boolean updateSC(SC sc) { boolean result = false; Connection con = null; try { con = DBCon.getMySQLCon(); String sql1 = "delete from SC where Sno=? and Cno=?"; String sql2 = "insert into SC(Sno,Cno,Grade) values(?,?,?)"; PreparedStatement stmt1 = (PreparedStatement) con .prepareStatement(sql1); PreparedStatement stmt2 = (PreparedStatement) con .prepareStatement(sql2); stmt1.setString(1, sc.getSno()); stmt1.setString(2, sc.getCno()); stmt2.setString(1, sc.getSno()); stmt2.setString(2, sc.getCno()); stmt2.setInt(3, sc.getGrade()); stmt1.executeUpdate(); int i = stmt2.executeUpdate(); if (i == 1) { // System.out.println("插入数据成功"); result = true; } else { // System.out.println("插入数据失败"); result = false; } } catch (Exception e) { e.printStackTrace(); } finally { try { con.close(); } catch (Exception e) { e.printStackTrace(); } } return result; } // 删除数据 public boolean deleteSC(SC sc) { boolean result = false; Connection con = null; try { con = DBCon.getMySQLCon(); String sql1 = "delete from SC where Sno=? and Cno=?"; PreparedStatement stmt = (PreparedStatement) con .prepareStatement(sql1); stmt.setString(1, sc.getSno()); stmt.setString(2, sc.getCno()); // System.out.println(stmt.toString()); int i = stmt.executeUpdate(); if (i == 1) { // System.out.println("插入数据成功"); result = true; } else { // System.out.println("插入数据失败"); result = false; } } catch (Exception e) { e.printStackTrace(); } finally { try { con.close(); } catch (Exception e) { e.printStackTrace(); } } return result; } // 查询列表 public List<SC> selectSC() { List<SC> scList = new ArrayList<SC>(); Connection conn = DBCon.getMySQLCon(); try { Statement stmt = (Statement) conn.createStatement(); ResultSet rs = stmt.executeQuery("select * from SC "); while (rs.next()) { SC sc = new SC(); sc.setCno(rs.getString("Cno")); sc.setSno(rs.getString("Sno")); sc.setGrade(rs.getInt("Grade")); scList.add(sc); } } catch (Exception e) { e.printStackTrace(); } finally { try { conn.close(); } catch (Exception e) { e.printStackTrace(); } } return scList; } // 通过学号查询数据 public List<SC> selectSCBySno(String Sno) { List<SC> scList = new ArrayList<SC>(); Connection conn = DBCon.getMySQLCon(); try { Statement stmt = (Statement) conn.createStatement(); ResultSet rs = stmt.executeQuery("select * from SC where Sno = " + Sno); while (rs.next()) { SC sc = new SC(); sc.setCno(rs.getString("Cno")); sc.setSno(rs.getString("Sno")); sc.setGrade(rs.getInt("Grade")); scList.add(sc); } } catch (Exception e) { e.printStackTrace(); } finally { try { conn.close(); } catch (Exception e) { e.printStackTrace(); } } return scList; } // 通过课程号查询 public List<SC> selectSCByCno(String Cno) { List<SC> scList = new ArrayList<SC>(); Connection conn = DBCon.getMySQLCon(); try { Statement stmt = (Statement) conn.createStatement(); ResultSet rs = stmt.executeQuery("select * from SC where Cno=" + Cno); while (rs.next()) { SC sc = new SC(); sc.setCno(rs.getString("Cno")); sc.setSno(rs.getString("Sno")); sc.setGrade(rs.getInt("Grade")); scList.add(sc); } } catch (Exception e) { e.printStackTrace(); } finally { try { conn.close(); } catch (Exception e) { e.printStackTrace(); } } return scList; } // 获得个人平均成绩 public int selectAVGGradeBySno(String Sno) { int max = 0; Connection conn = DBCon.getMySQLCon(); try { Statement stmt = (Statement) conn.createStatement(); ResultSet rs = stmt .executeQuery("select AVG(GRade) from SC where Sno = " + Sno); while (rs.next()) { max = rs.getInt(1); } } catch (Exception e) { e.printStackTrace(); } finally { try { conn.close(); } catch (Exception e) { e.printStackTrace(); } } return max; } // 获得个人总学分 public int selectAllCcreditBySno(String Sno) { int max = 0; Connection conn = DBCon.getMySQLCon(); try { String sql = "select SUM(Ccredit) from SC,Course where Sno=? AND SC.Cno=Course.Cno"; PreparedStatement stmt = (PreparedStatement) conn .prepareStatement(sql); stmt.setString(1, Sno); ResultSet rs = stmt.executeQuery(); while (rs.next()) { max = rs.getInt(1); } } catch (Exception e) { e.printStackTrace(); } finally { try { conn.close(); } catch (Exception e) { e.printStackTrace(); } } return max; } // 获得个人最高成绩 public int selectMAXGradeBySno(String Sno) { int max = 0; Connection conn = DBCon.getMySQLCon(); try { Statement stmt = (Statement) conn.createStatement(); ResultSet rs = stmt .executeQuery("select MAX(Grade) from SC where Sno = " + Sno); while (rs.next()) { max = rs.getInt(1); } } catch (Exception e) { e.printStackTrace(); } finally { try { conn.close(); } catch (Exception e) { e.printStackTrace(); } } return max; } // 获得个人最低成绩 public int selectMINGradeBySno(String Sno) { int max = 0; Connection conn = DBCon.getMySQLCon(); try { Statement stmt = (Statement) conn.createStatement(); ResultSet rs = stmt .executeQuery("select MIN(Grade) from SC where Sno = " + Sno); while (rs.next()) { max = rs.getInt(1); } } catch (Exception e) { e.printStackTrace(); } finally { try { conn.close(); } catch (Exception e) { e.printStackTrace(); } } return max; } // 获得课程平均成绩 public int selectAVGGradeByCno(String Cno) { int max = 0; Connection conn = DBCon.getMySQLCon(); try { Statement stmt = (Statement) conn.createStatement(); ResultSet rs = stmt .executeQuery("select AVG(GRade) from SC where Cno = " + Cno); while (rs.next()) { max = rs.getInt(1); } } catch (Exception e) { e.printStackTrace(); } finally { try { conn.close(); } catch (Exception e) { e.printStackTrace(); } } return max; } // 获得个人总人数 public int selectAllCcreditByCno(String Cno) { int max = 0; Connection conn = DBCon.getMySQLCon(); try { String sql = "select Count(Sno) from SC where Cno=?"; PreparedStatement stmt = (Pre