operatestudent.java

编程之道--java程序设计入门源代码

/*
 * 完成student表的相关操作
 */
import java.util.Vector;

import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;

public class OperateStudent
{
    private Statement stmt = null;
    private Connection con = null;
    private ResultSet rs = null;
    
    /*
     * 用于学生维护信息界面首次加载时，完成组合框的初始化
     */
    public StudentStorage queryAllStudent()
    {        
        Vector vec = new Vector();
        StudentStorage store = new StudentStorage();
          
        con = GetDBConnection.getConnection();
        try
        {
            String strSql = "select stu_name from student";
            stmt = con.createStatement();
            rs = stmt.executeQuery(strSql);
            
            while(rs.next())
            {
                Student one = new Student(rs.getString("stu_name"));
                vec.add(one);
            }
            
            store.setStudentVec(vec);
        }
        catch (SQLException ex)
        {
            System.out.println("不能创建该连接的会话！！！");
            ex.printStackTrace();
        }
        
        GetDBConnection.destroyConnection(con, stmt);
        return store;
    }
    
    /*
     * 用于查询单条学生记录
     */
    public Student queryOneStudent(String name)
    {   
        String number   = "";
        String sex      = "";
        String special  = ""; 
        String birthday = "";
        String entryday = "";
        String address  = "";
        String course   = "";
        String comment  = "";
        
        Student one = null;
      
        con = GetDBConnection.getConnection();
        try
        {
            String strSql = "select * from student where stu_name='" + name + "'";
            stmt = con.createStatement();
            rs = stmt.executeQuery(strSql);
            
            while(rs.next())
            {
                number = rs.getString("stu_id");
                sex = rs.getString("stu_sex");
                special = rs.getString("stu_Special");
                birthday = rs.getString("stu_birthday");
                entryday = rs.getString("stu_entrayday");
                address = rs.getString("stu_address");
                course = rs.getString("stu_course");
                comment = rs.getString("stu_comment");
                
                one = new Student(name, number, sex);
                one.setStudentBirthday(birthday);
                one.setStudentSpeciality(special);
                one.setStudentEntryday(entryday);
                one.setStudentAddress(address);
                one.setStudentCourse(course); 
                one.setStudentComment(comment);               
            }
            rs.close();
                        
        }
        catch (SQLException ex)
        {
            System.out.println("不能创建该连接的会话！！！");
            ex.printStackTrace();
        }
        
        GetDBConnection.destroyConnection(con, stmt);
        return one;
    }
        
    public int addStudentEss(Student one)
    {
        String stuName = one.getStudentName();
        String stuNumber = one.getStudentNumber();
        String stuSex = one.getStudentSex();
        String stuBirthday = one.getStudentBirthday();
        String stuEntryday = one.getStudentEntryday();
        String stuSpcial = one.getStudentSpeciality();
        String stuAddress = one.getStudentAddress();
        String stuCourse = one.getStudentCourse();
        String stuComment = one.getStudentComment();
        
        StringBuffer sql = new StringBuffer();
        sql.append("insert into student(stu_id,stu_name,stu_sex,stu_Special,");
        sql.append("stu_birthday, stu_entrayday, stu_address, stu_course, stu_comment) ");
        sql.append("values ('" +stuNumber + "', '" + stuName + "', '");
        sql.append(stuSex + "', '" + stuSpcial + "', '");
        sql.append(stuBirthday + "', '" + stuEntryday + "', '" + stuAddress + "','");
        sql.append(stuCourse + "', '" + stuComment + "')");
        return execAction(sql.toString());
    }
    
    public void deleteStudent(String name)
    {
        String sql0 = "select stu_id from student where stu_name='" + name + "'";
        String sql1 = "delete from student where stu_name='" + name + "'";
        String stuId = queryStuId(sql0);
        String sql2 = "delete from studentcourse where stu_id='" + stuId + "'";
        String sql3 = "delete from studentcomment where stu_id='" + stuId + "'";
        
        execAction(sql1);
            System.out.println(sql1 + " has executed");
        execAction(sql2);
            System.out.println(sql2 + " has executed");
        execAction(sql3);
            System.out.println(sql3 + " has executed");
    }
    
    private int execAction(String sql)
    {
        int i = 0;
        con = GetDBConnection.getConnection();
        try
        {
            stmt = con.createStatement();
            i = stmt.executeUpdate(sql);
        }
        catch (SQLException ex)
        {
            System.out.println("操作学生记录出错！！！");
            ex.printStackTrace();
        }
        GetDBConnection.destroyConnection(con, stmt);
        return i;
    }
    
    private String queryStuId(String sql)
    {
        String number   = "";
        
        con = GetDBConnection.getConnection();
        try
        {
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
            
            while(rs.next())
            {
                number = rs.getString("stu_id");               
            }
            rs.close();
        }
        catch (SQLException ex)
        {
            System.out.println("查询学号出错！！！");
            ex.printStackTrace();
        }
        GetDBConnection.destroyConnection(con, stmt);
        return number;
    }
}