学生管理系统及课程管理系统+SQL源码

package com.svse.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.svse.entity.StudentInfo; import com.svse.util.DBConnect; public class StudentInfoDao { private DBConnect db; private Connection conn; private Statement stmt; private PreparedStatement pstmt; private ResultSet rs; public StudentInfoDao() { // 用new創建一個對象，用於獲取連接 db = new DBConnect(); } // 添加學員信息 public int addStu(StudentInfo stu) { int result = 0; // 創建執行對象 String sql = "insert into student values (?,?,?) "; // 獲得連接 conn = db.getDBConnect(); try { // 創建需要傳遞的SQL pstmt = conn.prepareStatement(sql); // 設置傳遞參數 pstmt.setString(1, stu.getSname()); pstmt.setString(2, stu.getSsex()); pstmt.setString(3, stu.getSage()); // 更新修改后的信息 result = pstmt.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); // 不論何種情況下都關閉連接 } finally { db.closeConnect(conn); } return result; } // 修改学员信息 public int editStu(StudentInfo stu) { int result = 0; // 創建執行對象根據學員編號修改學員信息 String sql = "update student set sname = ?,ssex = ?,sage = ? where sno = ?"; conn = db.getDBConnect(); try { pstmt = conn.prepareStatement(sql); pstmt.setString(1, stu.getSname()); pstmt.setString(2, stu.getSsex()); pstmt.setString(3, stu.getSage()); pstmt.setString(4, stu.getSno()); result = pstmt.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { db.closeConnect(conn); } return result; } // 根據學員編號刪除學員信息 public int deleteStu(String sno) { int result = 0; // 創建執行對象根據學員編號刪除學員的信息 String sql = "delete student where sno = ?"; conn = db.getDBConnect(); try { pstmt = conn.prepareStatement(sql); pstmt.setString(1, sno); result = pstmt.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { db.closeConnect(conn); } return result; } // 根據學員編號精確查詢學員的信息 public StudentInfo selectSnoStu(String sno) { StudentInfo stu = null; // 創建執行對象根據學員的編號精確查詢學員信息 String sql = "select * from student where sno = ?"; conn = db.getDBConnect(); try { pstmt = conn.prepareStatement(sql); pstmt.setString(1, sno); rs = pstmt.executeQuery(); if (rs.next()) { stu = new StudentInfo(); stu.setSno(rs.getString("sno")); stu.setSname(rs.getString("sname")); stu.setSsex(rs.getString("ssex")); stu.setSage(rs.getString("sage")); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { db.closeConnect(conn); } return stu; } // 根據學員姓名模糊查詢學員的信息 public List<StudentInfo> selectSnameStu(String sname) { List<StudentInfo> list = new ArrayList<StudentInfo>(); // 創建執行對象根據學員的姓名模糊查詢學員信息 String sql = "select * from student where sname = ?"; conn = db.getDBConnect(); try { pstmt = conn.prepareStatement(sql); pstmt.setString(1, sname); rs = pstmt.executeQuery(); while (rs.next()) { StudentInfo stu = new StudentInfo(); stu.setSno(rs.getString("sname")); stu.setSname(rs.getString("sno")); stu.setSsex(rs.getString("ssex")); stu.setSage(rs.getString("sage")); list.add(stu); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { db.closeConnect(conn); } return list; } // 查詢所有學員信息 public List<StudentInfo> getStuf() { List<StudentInfo> list = new ArrayList<StudentInfo>(); // 創建查詢所有學員的執行對象 String sql = "select * from student"; conn = db.getDBConnect(); try { stmt = conn.createStatement(); rs = stmt.executeQuery(sql); while (rs.next()) { // new一個StudentInfo對象 StudentInfo stu = new StudentInfo(); stu.setSno(rs.getString("sno")); stu.setSname(rs.getString("sname")); stu.setSsex(rs.getString("ssex")); stu.setSage(rs.getString("sage")); list.add(stu); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { db.closeConnect(conn); } return list; } }