学生信息管理系统(基于struts框架)源代码

package com.cnwi.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import com.cnwi.entity.Student; import com.cnwi.util.DBHelp; /** * ѧ���DAO * @author Administrator * */ public class StudentDAO_Impl implements IStudentDAO { /** * 添加新用户信息 */ @Override public int addStudent(Student student) { int rt=0; Connection conn=null; PreparedStatement pstmt=null; ResultSet rs=null; try { conn=DBHelp.getConn(); if (conn!=null) { //INSERT INTO students VALUES('C001','jack','15','女','大学','备注') String sql="INSERT INTO students VALUES(?,?,?,?,?,?)"; pstmt=conn.prepareStatement(sql); pstmt.setString(1, student.getStuClass().getId()); pstmt.setString(2, student.getStuName()); pstmt.setInt(3, student.getAge()); pstmt.setString(4, student.getSex()); pstmt.setString(5, student.getEduLevel()); pstmt.setString(6, student.getRemark()); rt=pstmt.executeUpdate(); } } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); }finally{ try { DBHelp.closeAll(rs, pstmt, conn); } catch (SQLException e) { e.printStackTrace(); } } return rt; } /** * 根据id删除用户信息 */ @Override public int deleteStudentsByIds(String studentIds) { int rt=0; Connection conn=null; Statement stmt=null; ResultSet rs=null; try { conn=DBHelp.getConn(); if (conn!=null) { String sql="DELETE FROM students WHERE id IN("+studentIds+")"; stmt=conn.createStatement(); rt=stmt.executeUpdate(sql); } } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); }finally{ try { DBHelp.closeAll(rs, stmt, conn); } catch (SQLException e) { e.printStackTrace(); } } return rt; } /** * 根据学生编号查询学生信息 */ @Override public Student findStudentById(int stuId) { Student rt=null; Connection conn=null; PreparedStatement pstmt=null; ResultSet rs=null; try { conn=DBHelp.getConn(); if (conn!=null) { String sql="SELECT * FROM students WHERE id=?"; pstmt=conn.prepareStatement(sql); pstmt.setInt(1, stuId); rs=pstmt.executeQuery(); if (rs.next()) { rt=new Student(); rt.setId(stuId); rt.setId(rs.getInt("id")); rt.setStuName(rs.getString("stuName")); rt.setAge(rs.getInt("age")); rt.setSex(rs.getString("sex")); rt.setEduLevel(rs.getString("eduLevel")); rt.setRemark(rs.getString("remark")); } } } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally{ try { DBHelp.closeAll(rs, pstmt, conn); } catch (SQLException e) { e.printStackTrace(); } } return rt; } /** * 按班级及页面索引显示学生信息 */ @Override public List<Student> findUsersByClassIdPageIndex(String classId,int pageNow,int pageSize) { List<Student> rt=new ArrayList<Student>(); Connection conn=null; Statement stmt=null; ResultSet rs=null; try { conn=DBHelp.getConn(); if (conn!=null) { String sql="SELECT TOP "+pageSize+" * FROM students WHERE classId='"+classId+"' AND id NOT IN(SELECT TOP "+(pageNow-1)*pageSize+" id FROM students)"; stmt=conn.createStatement(); rs=stmt.executeQuery(sql); while (rs.next()) { Student stu=new Student(); stu.setId(rs.getInt("id")); String stuClassId=rs.getString("classId"); stu.setStuClass(new ClassDAO_Impl().findClassById(stuClassId)); stu.setStuName(rs.getString("stuName")); stu.setAge(rs.getInt("age")); stu.setSex(rs.getString("sex")); stu.setEduLevel(rs.getString("eduLevel")); stu.setRemark(rs.getString("remark")); rt.add(stu); } } } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally{ try { DBHelp.closeAll(rs, stmt, conn); } catch (SQLException e) { e.printStackTrace(); } } return rt; } /** * 更新学生信息 */ @Override public int updateStudentById(Student student) { int rt=0; Connection conn=null; PreparedStatement pstmt=null; ResultSet rs=null; try { conn=DBHelp.getConn(); if (conn!=null) { //INSERT INTO students VALUES('C001','jack','15','女','大学','备注') String sql="UPDATE students SET classId=?,stuName=?,age=?,sex=?,eduLevel=?,remark=? WHERE id=?"; pstmt=conn.prepareStatement(sql); pstmt.setString(1, student.getStuClass().getId()); pstmt.setString(2, student.getStuName()); pstmt.setInt(3, student.getAge()); pstmt.setString(4, student.getSex()); pstmt.setString(5, student.getEduLevel()); pstmt.setString(6, student.getRemark()); pstmt.setInt(7, student.getId()); rt=pstmt.executeUpdate(); } } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); }finally{ try { DBHelp.closeAll(rs, pstmt, conn); } catch (SQLException e) { e.printStackTrace(); } } return rt; } /** * 获取某班级所有学生总数 */ @Override public int getTotalRowsByClassId(String classId) { int rt=0; Connection conn=null; Statement stmt=null; ResultSet rs=null; try { conn=DBHelp.getConn(); if (conn!=null) { String sql="SELECT COUNT(*) AS rows FROM students WHERE classId='"+classId+"'"; stmt=conn.createStatement(); rs=stmt.executeQuery(sql); if(rs.next()) rt=rs.getInt("rows"); } } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally{ try { DBHelp.closeAll(rs, stmt, conn); } catch (SQLException e) { e.printStackTrace(); } } return rt; } }