studentdaoimpl.java

掌握 JDBC 驱动程序的加载方法和JDBC-ODBC桥URL的形式； 掌握使用JDBC连接数据库的步骤； 掌握使用JDBC发送SQL语句的基本步骤； 掌握使用JDBC处理SQL查询结果集

package cn.edu.csu.oo.gui.project.dao.studentdao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import cn.edu.csu.oo.gui.project.dao.common.DbConnection;
import cn.edu.csu.oo.gui.project.dao.common.DbException;
import cn.edu.csu.oo.gui.project.dao.common.DbSql;
import cn.edu.csu.oo.gui.project.dao.studentdao.StudentDao;
import cn.edu.csu.oo.gui.project.vo.StudentVo;

public class StudentDaoImpl implements StudentDao{
  /**
   * 查找数据库中是否存在相同条件的学生记录
   * @param con
   * @param studentNo
   * @return
   */
  public boolean checkStudent(Connection con,int studentNo){
    boolean flag = false;
    PreparedStatement pstmt = null;
    ResultSet set = null;
    try{
      pstmt = con.prepareStatement(DbSql.STUDENT_SELECT);
      pstmt.setInt(1,studentNo);
      set = pstmt.executeQuery();
      if(set.next()){
        flag = true;
      }
    }catch(SQLException e){
      System.out.println("student db check: " + e.getMessage());
    }
    return flag;
  }
  /**
   * 添加一条学生记录
   */
  public boolean registStudent(StudentVo value) throws DbException{
    boolean flag = false;
    PreparedStatement pstmt = null;
    Connection con = null;
    try{
      con = DbConnection.getConnection();
      if(checkStudent(con,value.getStudentNo())){
        throw new DbException("数据库中存在相同条件的记录,请重新输入");
      }
      pstmt = con.prepareStatement(DbSql.STUDENT_INSERT);
      pstmt.setInt(1,value.getStudentNo());
      pstmt.setString(2,value.getStudentName());
      pstmt.setString(3,value.getStudentSex());
      pstmt.setString(4,value.getStudentMajor());
      pstmt.setInt(5,value.getStudentGrade());
      pstmt.setString(6,value.getStudentMail());
      pstmt.setString(7,value.getStudentAddress());
      pstmt.setString(8,value.getStudentMobile());
      pstmt.setString(9,value.getStudentDate());
      int count = pstmt.executeUpdate();
      if(count != 0){
        flag = true;
      }
    }catch(SQLException e){
      System.out.println("学生信息插入dao信息:= " + e.getMessage());
    }
    return flag;
  }
  /**
   * 获取学生专业信息
   */
  public Vector findMajor(){
    Vector v = null;
    Statement stmt = null;
    Connection con = null;
    ResultSet set = null;
    try{
      con = DbConnection.getConnection();
      stmt = con.createStatement();
      set = stmt.executeQuery("select student_major from student");
      v = new Vector();
      while(set.next()){
        String major = set.getString("student_major");
        v.addElement(major);
      }
    }catch(Exception e){
      System.out.println("专业查找db:= " + e.getMessage());
    }
    return v;
  }
  /**
   * 根据学生学号查找学生
   * @param stuId
   * @return
   */
  public Vector getStudentInfoByStuId(int stuId){
    Vector v = null;
    PreparedStatement pstmt = null;
    ResultSet set = null;
    Connection con = null;
    try{
      con = DbConnection.getConnection();
      pstmt = con.prepareStatement(DbSql.STUDENT_SELECT);
      pstmt.setInt(1,stuId);
      set = pstmt.executeQuery();
      v = new Vector();
      while(set.next()){
        int stuNo = set.getInt("student_id");
        String stuName = set.getString("student_name");
        String stuSex = set.getString("student_sex");
        String major = set.getString("student_major");
        int stuGrade = set.getInt("student_grade");
        String stuMail = set.getString("student_mail");
        String stuAddress = set.getString("student_address");
        String stuMobile = set.getString("student_mobile");
        String stuDate = set.getString("STUDENT_REGIST_TIME");
        v.addElement(new StudentVo(stuNo,stuName,stuSex,major,stuGrade,stuMail,stuAddress,stuMobile,stuDate));
      }
    }catch(SQLException e){
      System.out.println("student db check: " + e.getMessage());
    }
    return v;
  }
  /**
   * 根据学生姓名查找学生信息
   * @param stuName
   * @return
   */
  public Vector getStudentByName(String stuName){
    Vector v = new Vector();
    Connection con = null;
    PreparedStatement stmt = null;
    boolean flag = false;
    ResultSet set = null;
    String sql = "";
    try{
      con = new DbConnection().getConnection();
      stmt = con.prepareStatement(DbSql.SELECT + DbSql.STU_TABLE + DbSql.STU_WHERE +
                                  DbSql.STU_NAME);
      stmt.setString(1,stuName);
      set = stmt.executeQuery();
      while(set.next()){
        int studentID = set.getInt("student_id");
        String studentName = set.getString("student_name");
        String studentSex = set.getString("student_sex");
        String studentMajor = set.getString("student_major");
        int studentGrade = set.getInt("student_grade");
        String studentMail = set.getString("student_mail");
        String studentAddress = set.getString("student_address");
        String studentMobile = set.getString("student_mobile");
        String studentRegistDate = set.getString("student_regist_time");
        StudentVo vo  = new StudentVo(studentID,studentName,studentSex,studentMajor,
                           studentGrade,studentMail,studentAddress,studentMobile,studentRegistDate);
        v.addElement(vo);
      }
    }catch(SQLException e){
      System.out.println(e.getMessage());
    }
   return v;
  }
  /**
   * 根据学生专业查找学生信息
   * @param stuGrade
   * @return
   */
  public Vector getStudentByMajor(String stuMajor){
    Vector v = new Vector();
    Connection con = null;
    PreparedStatement stmt = null;
    boolean flag = false;
    ResultSet set = null;
    String sql = "";
    try{
      con = new DbConnection().getConnection();
      stmt = con.prepareStatement(DbSql.SELECT + DbSql.STU_TABLE + DbSql.STU_WHERE +
                                  DbSql.STU_MAJOR);
      stmt.setString(1,stuMajor);
      set = stmt.executeQuery();
      while(set.next()){
        int studentID = set.getInt("student_id");
        String studentName = set.getString("student_name");
        String studentSex = set.getString("student_sex");
        String studentMajor = set.getString("student_major");
        int studentGrade = set.getInt("student_grade");
        String studentMail = set.getString("student_mail");
        String studentAddress = set.getString("student_address");
        String studentMobile = set.getString("student_mobile");
        String studentRegistDate = set.getString("student_regist_time");
        StudentVo vo  = new StudentVo(studentID,studentName,studentSex,studentMajor,
                           studentGrade,studentMail,studentAddress,studentMobile,studentRegistDate);
        v.addElement(vo);
      }
    }catch(SQLException e){
      System.out.println(e.getMessage());
    }
   return v;
  }  
  /**
   * 得到所有学生信息并保存在Vector中
   */
  public Vector getStudentInfo(){
    Vector v = new Vector();
    Connection con = null;
    PreparedStatement stmt = null;
    boolean flag = false;
    ResultSet set = null;
    try{
      con = new DbConnection().getConnection();
      stmt = con.prepareStatement(DbSql.STU_ALL_SELECT);
      set = stmt.executeQuery();
      while(set.next()){
        int studentID = set.getInt("student_id");
        String studentName = set.getString("student_name");
        String studentSex = set.getString("student_sex");
        String studentMajor = set.getString("student_major");
        int studentGrade = set.getInt("student_grade");
        String studentMail = set.getString("student_mail");
        String studentAddress = set.getString("student_address");
        String studentMobile = set.getString("student_mobile");
        String studentRegistDate = set.getString("student_regist_time");
        StudentVo vo  = new StudentVo(studentID,studentName,studentSex,studentMajor,
                           studentGrade,studentMail,studentAddress,studentMobile,studentRegistDate);
        v.addElement(vo);
      }
    }catch(SQLException e){
      System.out.println(e.getMessage());
    }
   return v;
  }
  /**
   * 根据学生编号修改一条学生记录信息
   */
  public boolean updateStudentInfo(StudentVo value){
    boolean flag = false;
    PreparedStatement pstmt = null;
    Connection con = null;
    try{
      con = DbConnection.getConnection();
      pstmt = con.prepareStatement(DbSql.STUDENT_UPDATE);
      pstmt.setString(1,value.getStudentName());
      pstmt.setString(2,value.getStudentSex());
      pstmt.setString(3,value.getStudentMajor());
      pstmt.setInt(4,value.getStudentGrade());
      pstmt.setString(5,value.getStudentMail());
      pstmt.setString(6,value.getStudentAddress());
      pstmt.setString(7,value.getStudentMobile());
      pstmt.setString(8,value.getStudentDate());
      pstmt.setInt(9,value.getStudentNo());
      int count = pstmt.executeUpdate();
      if(count != 0){
        flag = true;
      }
    }catch(SQLException e){
      System.out.println("学生信息修改dao信息:= " + e.getMessage());
    }
  
    return flag;
  }
  /**
   * 根据学生学号删除一条学生记录
   */
  public boolean deleteStudentByStuId(int studentId) {
    boolean flag = false;
    PreparedStatement pstmt = null;
    Connection con = null;
    try{
      con = DbConnection.getConnection();
      pstmt = con.prepareStatement(DbSql.STUDENT_DELETE);
      pstmt.setInt(1,studentId);
      int count = pstmt.executeUpdate();
      if(count != 0){
        flag = true;
      }
    }catch(Exception e){
      System.out.println("学生信息删除dao信息:= " + e.getMessage());
    }
    return flag;
  }
}