javaweb大作业——简单的学生成绩管理系统

package dao; import bean.Student; import util.DBUtil; import java.sql.*; import java.util.ArrayList; import java.util.List; public class StudentDAO { public int getTotal() { int total = 0; String sql = "SELECT COUNT(*) FROM student"; try (Connection c = DBUtil.getConnection(); Statement st = c.createStatement()) { ResultSet rs = st.executeQuery(sql); while (rs.next()) { total = rs.getInt(1); } } catch (SQLException e) { e.printStackTrace(); } return total; } public void add(Student student) { String sql = "INSERT INTO student VALUES(NULL,?,?,?,?)"; try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) { ps.setInt(1,student.getStudentID()); ps.setString(2,student.getName()); ps.setInt(3,student.getAge()); ps.setString(4,student.getSex()); ps.execute(); } catch (SQLException e) { e.printStackTrace(); } } public void delete(int id) { String sql = "DELETE FROM student WHERE ID = ?"; try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) { ps.setInt(1, id); ps.execute(); } catch (SQLException e) { e.printStackTrace(); } } public void update(Student student) { String sql = "update student set studentId = ?, name = ?, age = ?, sex = ? where id = ? "; try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) { ps.setInt(1, student.getStudentID()); ps.setString(2, student.getName()); ps.setInt(3, student.getAge()); ps.setString(4, student.getSex()); ps.setInt(5, student.getId()); ps.execute(); } catch (SQLException e) { e.printStackTrace(); } } public Student get(int id) { Student student = new Student(); String sql = "SELECT * FROM student WHERE ID = " + id; try (Connection c = DBUtil.getConnection(); Statement st = c.createStatement()) { ResultSet rs = st.executeQuery(sql); if (rs.next()) { int student_id = rs.getInt("studentId"); String name = rs.getString("name"); int age = rs.getInt("age"); String sex = rs.getString("sex"); student.setStudentID(student_id); student.setName(name); student.setAge(age); student.setSex(sex); student.setId(id); } } catch (SQLException e) { e.printStackTrace(); } return student; } public ArrayList<Student> getStudent(String type, String content) { String sql = null;; if (type.equals("sno")){ sql = "SELECT * FROM student WHERE studentId = " + content; }else if(type.equals("sname")){ sql = "SELECT * FROM student WHERE name = '"+content+"'";//注意中文查询要加引号 }else return null; ArrayList<Student> students = new ArrayList<>(); try (Connection c = DBUtil.getConnection(); Statement st = c.createStatement()) { ResultSet rs = st.executeQuery(sql); while (rs.next()) { Student student = new Student(); int student_id = rs.getInt("studentID"); String name = rs.getString("name"); int age = rs.getInt("age"); String sex = rs.getString("sex"); student.setStudentID(student_id); student.setName(name); student.setAge(age); student.setSex(sex); student.setId(rs.getInt("id")); students.add(student); } } catch (SQLException e) { e.printStackTrace(); } return students; } public List<Student> list() { return list(0, Short.MAX_VALUE); } public List<Student> list(int start, int count) { List<Student> students = new ArrayList<>(); String sql = "SELECT * FROM student ORDER BY studentId desc limit ?,?"; try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) { ps.setInt(1, start); ps.setInt(2, count); ResultSet rs = ps.executeQuery(); while (rs.next()) { Student student = new Student(); int id = rs.getInt("id"); int studentID = rs.getInt("studentId"); String name = rs.getString("name"); int age = rs.getInt("age"); String sex = rs.getString("sex"); student.setId(id); student.setStudentID(studentID); student.setName(name); student.setAge(age); student.setSex(sex); students.add(student); } rs.close(); } catch (SQLException e) { e.printStackTrace(); } return students; } public static void main(String[] args){ StudentDAO studentDAO = new StudentDAO(); List<Student> students = studentDAO.list(); System.out.println(students.get(0).getName()); } }