package teacherman;

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

import util.stringUtil;
import db.dbconn;
/**
 * 封裝對教師表的所有操作
 */
public class teacher_operation {
    /**
     * 功能:往教師表增加一條記錄
     * 輸入參數:teacher_name為教師姓名
     * 輸出:返回1表成功，返回2表數據連接參數配置不正確或連接數據庫失敗或數據庫操作失敗，
     * 返回3表此班級已存在，返回4表輸入參數class_name為空;
     */
    public int teacher_add_one(String teacher_name){
        dbconn dbconnOBject=new dbconn();//數據庫連接對象
        String sqlString=null;//SQL語句字符串
        ResultSet rs=null;//結果記錄集
        Connection dbconn=dbconnOBject.getDBConn();//得到數據庫連接
        if(dbconn==null) return 2;//連接失敗
        //--------輸入參數不正確---------
        if(teacher_name==null||teacher_name.trim().length()==0)
           return 4;
        //--------輸入參數編碼轉換-------
        stringUtil stringCode=new stringUtil();
        teacher_name=stringCode.codeToString(teacher_name.trim());
        //--------插入記錄，先判斷是否已存在------
        sqlString="select * from teacher where teacher_name=?";
        try{
           PreparedStatement preSQLSelect=dbconn.prepareStatement(sqlString);
           preSQLSelect.setString(1,teacher_name);
           rs=preSQLSelect.executeQuery();
           if(rs.next()) return 3;//已存在此教師
           sqlString="insert into teacher(teacher_name) values(?)";
           PreparedStatement preSQLInsert=dbconn.prepareStatement(sqlString);
           preSQLInsert.setString(1,teacher_name);
           preSQLInsert.executeUpdate();
           return 1;
         }catch(Exception e){
           System.out.print(e);
           return 2;
         }             
    }
    /**
     * 功能:查詢出所有的教師
     * 輸入參數：無
     * 輸出:所有教師的記錄集,如果沒有記錄或操作失敗返回null
     */
    public ResultSet teacher_select_all(){
        String sqlString=null;//SQL語句字符串
        Statement sql=null;//SQL語句對象
        ResultSet rs=null;//結果記錄集
        dbconn dbconnOBject=new dbconn();//數據庫連接對象
        Connection dbconn=dbconnOBject.getDBConn();//得到數據庫連接
        if(dbconn==null) return null;//連接失敗
        try{
           //-------查詢出數據------------
           sqlString="select * from teacher"; 
           sql=dbconn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
           rs=sql.executeQuery(sqlString);
           return rs;
        }catch(Exception e){
           System.out.print(e);
           return null;
        }  
    }
    /**
     * 功能：刪除一個教師的信息
     * 輸入參數：教師的ID號
     * 輸出：返回1表成功，返回2表數據連接參數配置不正確或連接數據庫失敗，
     * 返回4表輸入參數teacher_id為0，即輸入參數不正確
     */
    public int teacher_delete(int teacher_id){
        String sqlString=null;//SQL語句字符串
        ResultSet rs=null;//結果記錄集
        dbconn dbconnOBject=new dbconn();//數據庫連接對象
        Connection dbconn=dbconnOBject.getDBConn();//得到數據庫連接
        if(dbconn==null) return 2;//連接失敗
        if(teacher_id==0) return 4;//輸入參數不正確
        //---------刪除數據--------------
        sqlString="delete from teacher where teacher_id=?";
        try{
            PreparedStatement preSQLDelete=dbconn.prepareStatement(sqlString);
            preSQLDelete.setInt(1,teacher_id);
            preSQLDelete.executeUpdate();
            return 1;
          }catch(Exception e){
            System.out.print(e);
            return 2;
          }   
    }
    /**
     * 功能:查詢出某一教師的信息
     * 輸入參數:教師的ID
     * 輸出:此條教師記錄,如果沒有記錄或操作失敗返回null
     */
    public ResultSet teacher_select_one(int teacher_id){
        String sqlString=null;//SQL語句字符串
        ResultSet rs=null;//結果記錄集
        dbconn dbconnOBject=new dbconn();//數據庫連接對象
        Connection dbconn=dbconnOBject.getDBConn();//得到數據庫連接
        if(dbconn==null) return null;//連接失敗
        if(teacher_id==0) return null;//輸入參數不正確
        try{
           //-------查詢出數據------------
           sqlString="select * from teacher where teacher_id=?"; 
           PreparedStatement preSQLSelect=dbconn.prepareStatement(sqlString);
           preSQLSelect.setInt(1,teacher_id);
           rs=preSQLSelect.executeQuery();
           return rs;
        }catch(Exception e){
           System.out.print(e);
           return null;
        }  
    }
    /**
     * 功能:更新教師表的一條記錄
     * 輸入參數:teacher_id為教師號，teacher_name為教師名稱
     * 輸出:返回1表成功，返回2表數據連接參數配置不正確或連接數據庫失敗或數據庫操作失敗，
     * 返回3表記錄已存在，返回4表輸入參數為空或不正確;
     */
    public int teacher_update(int teacher_id,String teacher_name){
        String sqlString=null;//SQL語句字符串
        dbconn dbconnOBject=new dbconn();//數據庫連接對象
        ResultSet rs=null;//結果記錄集
        Connection dbconn=dbconnOBject.getDBConn();//得到數據庫連接
        if(dbconn==null) return 2;//連接失敗
        if(teacher_id==0||teacher_name.trim().length()==0) return 4;//輸入參數不正確
        //--------輸入參數編碼轉換-------
        stringUtil stringCode=new stringUtil();
        teacher_name=stringCode.codeToString(teacher_name.trim());
        //--------更新記錄，先判斷是否已存在------
        sqlString="select * from teacher where teacher_name=? and teacher_id<>"+teacher_id;
        try{
            PreparedStatement preSQLSelect=dbconn.prepareStatement(sqlString);
            preSQLSelect.setString(1,teacher_name);
            rs=preSQLSelect.executeQuery();
            if(rs.next()) return 3;//已存在此教師
            //--------更新記錄-----------
            sqlString="update teacher set teacher_name=? where teacher_id=?";
            PreparedStatement preSQLUpdate=dbconn.prepareStatement(sqlString);
            preSQLUpdate.setString(1,teacher_name);
            preSQLUpdate.setInt(2,teacher_id);
            preSQLUpdate.executeUpdate();
            return 1;
        }catch(Exception e){
            System.out.print(e);
            return 2;
        }     
    }  
    /**
     * 功能:根據姓名查詢出部分老師的信息
     * 輸入參數：老師姓名
     * 輸出:部分教師的記錄集,如果沒有記錄或操作失敗返回null
     * 說明:支持模糊查詢
     */
    public ResultSet teacher_select_part_by_name(String teacher_name){
        String sqlString=null;//SQL語句字符串
        Statement sql=null;//SQL語句對象
        ResultSet rs=null;//結果記錄集
        if(teacher_name==null||teacher_name.trim().length()==0)
            return teacher_select_all();
        dbconn dbconnOBject=new dbconn();//數據庫連接對象
        Connection dbconn=dbconnOBject.getDBConn();//得到數據庫連接
        if(dbconn==null) return null;//連接失敗
        try{
           //-------查詢出數據------------
           sqlString="select * from teacher where teacher_name like '%"+teacher_name+"%'"; 
           sql=dbconn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
           rs=sql.executeQuery(sqlString);
           return rs;
        }catch(Exception e){
           System.out.print(e);
           return null;
        }  
    }
}