学生成绩管理系统全部代码（包括SQL）

package demo; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.Statement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; /** * Student的业务逻辑类 * @author Administrator * */ public class StudentService{ private static final int PAGE_SIZE = 10; //每页大小 public static int getPageSize() { return PAGE_SIZE; } /** * 判断给定学生的学号是否唯一 * * @param studentNumber 学号 * @return * @throws Exception */ public boolean isStudentNumberAddUnique(String studentNumber) throws Exception { Connection conn = null; PreparedStatement prep = null; ResultSet rs = null; int count; try { conn = new DBConnection().getConnection(); // 得到数据库连接 prep = conn .prepareStatement("SELECT count(*) as count FROM Student WHERE studentNumber = ?"); prep.setString(1, studentNumber); rs = prep.executeQuery(); rs.next(); count = rs.getInt("count"); } finally { if (rs != null) rs.close(); if (prep != null) prep.close(); if (conn != null) conn.close(); } return (count == 0); } /** * 判断要修改的学生的学号是否唯一 * 先根据学号查找学生，如果没有找到相同学号的学生，则该学号唯一 * 如果找到的学生和要修改的学生是同一个学生，则该学号也是唯一 * @param student * @return * @throws Exception */ public boolean isStudentNumberUpdateUnique(StudentBean student) throws Exception { Connection conn = null; PreparedStatement prep = null; ResultSet rs = null; boolean result = true; try{ conn = new DBConnection().getConnection(); String sql = "select * from Student WHERE studentNumber = ?"; prep = conn.prepareStatement(sql); prep.setString(1, student.getStudentNumber()); rs = prep.executeQuery(); if(rs.next()){ //有一个该学号学生 if(student.getId() == rs.getInt("id")) //查找到的学生和要修改的是同一个学生，则可以修改 result = true; else result = false; }else{ //没有该学号的学生，则可以修改学生为该学号 result = true; } }finally{ if (rs != null) rs.close(); if (prep != null) prep.close(); if (conn != null) conn.close(); } return result; } /** * 向数据库表中添加新学生 * @param student * @throws Exception */ public void save(StudentBean student) throws Exception{ Connection conn = null; PreparedStatement prep = null; try{ conn = new DBConnection().getConnection(); String sql = "INSERT STUDENT(studentNumber,name,password,sex,birthday,address,polity,brief) " + "VALUES(?,?,?,?,?,?,?,?)"; prep = conn.prepareStatement(sql); prep.setString(1,student.getStudentNumber()); prep.setString(2, student.getName()); prep.setString(3,student.getPassword()); prep.setInt(4, student.getSex()); prep.setDate(5, new java.sql.Date(student.getBirthday().getTime())); prep.setString(6, student.getAddress()); prep.setInt(7, student.getPolity()); prep.setString(8, student.getBrief()); prep.execute(); }finally{ if (prep != null) prep.close(); if (conn != null) conn.close(); } } /** * 更新学生信息 * @param student * @throws Exception */ public void update(StudentBean student) throws Exception { Connection conn = null; PreparedStatement prep = null; try { conn = new DBConnection().getConnection(); String sql = "UPDATE STUDENT set studentNumber=?,name=?,password=?,sex=?," + "birthday=?,address=?,polity=?,brief=? WHERE id = ? "; prep = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); prep.setString(1, student.getStudentNumber()); prep.setString(2, student.getName()); prep.setString(3,student.getPassword()); prep.setInt(4, student.getSex()); prep.setDate(5, new java.sql.Date(student.getBirthday().getTime())); prep.setString(6, student.getAddress()); prep.setInt(7, student.getPolity()); prep.setString(8, student.getBrief()); prep.setLong(9, student.getId()); prep.execute(); } finally { if (prep != null) { prep.close(); } if (conn != null) { conn.close(); } } } /** * 通过主键(id)得到学生Bean * @param id * @return * @throws Exception */ public StudentBean getStudentBeanByPK(int id) throws Exception { Connection conn = null; PreparedStatement prep = null; ResultSet rs = null; StudentBean student = null; try{ conn = new DBConnection().getConnection(); prep = conn.prepareStatement("SELECT * FROM Student where id=?"); prep.setInt(1, id); rs = prep.executeQuery(); if(rs.next()){ student = new StudentBean(); student.setId(rs.getInt("id")); student.setStudentNumber(rs.getString("studentNumber")); student.setName(rs.getString("name")); student.setPassword(rs.getString("password")); student.setSex(rs.getInt("sex")); student.setBirthday(rs.getDate("birthday")); student.setAddress(rs.getString("address")); student.setPolity(rs.getInt("polity")); student.setBrief(rs.getString("brief")); } }finally{ if (rs != null) { rs.close(); } if (prep != null) { prep.close(); } if (conn != null) { conn.close(); } } return student; } /** * 返回数据库中符号条件的学生数目 * @param strFilter * @return * @throws Exception */ public int getCount(String strFilter) throws Exception{ if(strFilter==null || strFilter.length()==0){ strFilter="1=1"; } Connection conn = null; Statement stmt = null; ResultSet rs = null; int count = 0; try{ conn = new DBConnection().getConnection(); String sql = "SELECT count(*) as count FROM Student where "+strFilter; stmt = conn.createStatement(); rs = stmt.executeQuery(sql); rs.next(); count = rs.getInt("count"); }finally{ if (rs != null) { rs.close(); } if (stmt != null) { stmt.close(); } if (conn != null) { conn.close(); } } return count; } /** * 分页查找学生 * @param currentPage 当前要查找的页号 * @param strFilter 条件 * @return * @throws Exception */ public List getPageStudent(int currentPage,String strFilter) throws Exception{ Connection conn = null; Statement stmt = null; ResultSet rs = null; if(strFilter==null || strFilter.length()==0){ strFilter="1=1"; } List studentList = new ArrayList(); //保存超找到的学生的集合 try{ conn = new DBConnection().getConnection(); int pageSize = getPageSize(); String orderBy = "order by id"; /* * 从前currentPage*pageSize中找 */