简单的一个学生管理系统 servlet+JSP

package impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import pojo.Grade; import pojo.Student; public class StudentImpl { private int size = 5; // 每页显示的记录数 public void saveStudent(Student student) { String sql = "insert into student (sname,spass,sage,slikes,sex,gid) values (?,?,?,?,?,?)"; DBConn conns = new DBConn(); Connection conn = conns.getConnection(); PreparedStatement pre; try { pre = conn.prepareStatement(sql); pre.setString(1, student.getSname()); pre.setString(2, student.getSpass()); pre.setInt(3, student.getSage()); pre.setString(4, student.getSlikes()); pre.setString(5, student.getSex()); pre.setInt(6, student.getGrade().getGid()); pre.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } public List<Student> queryStudent(String sname) { List<Student> students = new ArrayList<Student>(); String sql = "select * from student as s inner join grade as g on s.gid=g.gid where sname like ?"; DBConn conns = new DBConn(); ResultSet rs = null; Connection conn = conns.getConnection(); PreparedStatement pre; try { pre = conn.prepareStatement(sql); pre.setString(1, "%" + sname + "%"); rs = pre.executeQuery(); while (rs.next()) { Student student = new Student(); student.setSid(rs.getInt("sid")); student.setSname(rs.getString("sname")); student.setSpass(rs.getString("spass")); student.setSage(rs.getInt("sage")); student.setSlikes(rs.getString("slikes")); student.setSex(rs.getString("sex")); Grade grade = new Grade(); grade.setGname(rs.getString("gname")); student.setGrade(grade); students.add(student); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } return students; } public List<Student> queryStudent(String sname, int currentPage) { List<Student> students = new ArrayList<Student>(); String sql = null; if (currentPage == 1) { sql = "select top " + size + " * from student as s inner join grade as g on s.gid=g.gid where sname like '%" + sname + "%'"; }else{ sql="select top " + size + " * from student as s inner join grade as g on s.gid=g.gid where sname like '%" + sname + "%' and sid>(select max(sid) from student where sid in(select top ((" + currentPage + "-1)* " + size + ") sid from student where sname like '%"+sname +"%'))"; } System.out.println(sql); DBConn conns = new DBConn(); ResultSet rs = null; Connection conn = conns.getConnection(); PreparedStatement pre; try { pre = conn.prepareStatement(sql); rs = pre.executeQuery(); while (rs.next()) { Student student = new Student(); student.setSid(rs.getInt("sid")); student.setSname(rs.getString("sname")); student.setSpass(rs.getString("spass")); student.setSage(rs.getInt("sage")); student.setSlikes(rs.getString("slikes")); student.setSex(rs.getString("sex")); Grade grade = new Grade(); grade.setGname(rs.getString("gname")); student.setGrade(grade); students.add(student); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } return students; } public void updateStudent(Student student) { String sql = "update student set sname=?,spass=?,sage=?,sex=?,gid=?,slikes=? where sid=?"; DBConn conns = new DBConn(); Connection conn = conns.getConnection(); PreparedStatement pre; try { pre = conn.prepareStatement(sql); pre.setString(1, student.getSname()); pre.setString(2, student.getSpass()); pre.setInt(3, student.getSage()); pre.setString(4, student.getSex()); pre.setInt(5, student.getGrade().getGid()); pre.setString(6, student.getSlikes()); pre.setInt(7, student.getSid()); pre.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } public void deleteStudent(int sid) { String sql = "delete from student where sid=?"; DBConn conns = new DBConn(); Connection conn = conns.getConnection(); PreparedStatement pre; try { pre = conn.prepareStatement(sql); pre.setInt(1, sid); pre.executeUpdate(); conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public int countPage(String sname){ int countPage=0; String sql = "select count(*) as count from student where sname like ?"; DBConn conns = new DBConn(); ResultSet rs = null; Connection conn = conns.getConnection(); PreparedStatement pre; try { pre = conn.prepareStatement(sql); pre.setString(1, "%" + sname + "%"); rs = pre.executeQuery(); if(rs.next()) { if(rs.getInt("count")%size==0){ countPage=rs.getInt("count")/size; }else{ countPage=(rs.getInt("count")/size)+1; } } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } return countPage; } public String[] getSlikes() { String[] slikes = { "看书", "上网", "旅游", "睡觉", "游戏" }; return slikes; } public boolean checkSname(String sname){ //查询数据是否有该字段如果有返回为true; if(sname.equals("admin")) return true; else return false; } public static void main(String[] args) { StudentImpl studentImpl=new StudentImpl(); System.out.println(studentImpl.countPage("t")); } }