workthread.java~1~

试题库管理系统 该系统所包含的子系统有：用户管理子系统、课程管理子系统、习题管理子系统和试卷库管理子系统。而用户管理子系统下分的模块有：添加用户、删除用户和修改用户信息；课程管理子系统下分的模块有：创

package server.pool;

import java.sql.SQLException;
import java.util.Date;
import java.io.BufferedReader;
import java.io.PrintWriter;
import java.sql.Connection;
import java.net.Socket;
import java.io.IOException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.io.InputStreamReader;
import java.io.FileReader;
import java.sql.PreparedStatement;
import java.util.Vector;
import java.io.FileOutputStream;

/**
 * 内部类，用来创建工作线程
 * @author not attributable
 * @version 1.0
 */
class WorkThread implements Runnable{
Socket socket=null;
BufferedReader bufin;
PrintWriter prout;
DataBaseConnection DBPool;
Connection conn;
String id;
private Vector userContainer;
CloseBean closed;
/**
 * 构造函数
 * @param socket Socket
 */
public WorkThread(Socket socket,Vector userContainer,CloseBean closed) {
    this.socket=socket;
    this.closed=closed;
    this.userContainer=userContainer;
try {
    bufin = new BufferedReader(new InputStreamReader(socket.getInputStream()));
    prout = new PrintWriter(socket.getOutputStream());
} catch (IOException ex) {
    System.out.println("读取流失败");
}
  DBPool=DataBaseConnection.getInstance();
  conn=DBPool.getConnection();
}
/**
 * 实现Runnable接口，服务器的主要工作线程
 */
public void run(){
    String cmd="";
 while(closed.isClosed()){       //服务器未关闭
        try {
          cmd=bufin.readLine();    //接收客户端命令
        } catch (IOException ex) {
          cmd="";
          System.err.println(ex.getMessage());
          break;
        }
        if(cmd.equals("close")){      //关闭连接命令
         System.out.println(cmd);     //测试用
          break;
        }
        else if(cmd.equals("read")){    //读取习题命令
           System.out.println(cmd);    //测试用
             send();
        }
        else if(cmd.equals("write")){    //修改、添加命令
            System.out.println(cmd);     //测试用
                receive();
            }
        else if(cmd.equals("login")){    //登陆命令
               System.out.println(cmd);    //测试用
               login();
            }
        else if(cmd.equals("load")){    //下载试卷命令
             System.out.println(cmd);    //测试用
                load();
            }
            else if(cmd.equals("transfer")||cmd.equals("savetest")){     //上传试卷命令
                System.out.println(cmd);     //测试用
                transfer();
            }
            else if(cmd.equals("readCourse")){
                System.out.println(cmd);     //测试用
                sendCourse();
            }
            try {
                 Thread.sleep(5000);
                 System.out.println("sleep");   //测试用
            } catch (InterruptedException ex2) {
              System.err.println(ex2.getMessage());
            }
  }
    try{
        userContainer.remove(id);    //从userContainer中移除该用户ID
        bufin.close();      //关闭输入流
        prout.close();      //关闭输出流
        DBPool.freeConnection(conn);   //释放数据库连接
        socket.close();     //关闭socket连接
  }catch(IOException ex){
      System.err.println(ex.getMessage());
    }
}
/**
 * 响应客户端的读取习题命令
 */
public void send(){
    String sql=null;
    ResultSet rt1;
    try {
        sql = bufin.readLine();       //接收客户端的SQL语句
        Statement st = conn.createStatement();
        rt1 = st.executeQuery(sql);    //查询数据库
        while (rt1.next()) {
            prout.println(rt1.getString("id"));
            prout.println(rt1.getString("course_name"));
            prout.println(rt1.getString("type"));
            prout.println(rt1.getString("text"));
            prout.println("over");      //提示客户端习题内容发送完毕
            prout.println(rt1.getString("answer"));
            prout.println("over");       //提示客户端习题答案发送完毕
            prout.println(rt1.getString("chapter"));
            prout.println(rt1.getString("section"));
            prout.println(rt1.getString("difficulty"));
            prout.flush();
        }
        rt1.close();    //关闭结果集
        st.close();     //关闭 Statement
    } catch (IOException ex) {
        System.err.println(ex.toString());
    } catch (SQLException ex1) {
        System.err.println(ex1.toString());
    }finally{
        prout.println("End");        //提示客户端习题发送完毕
        prout.flush();
    }
}
public void sendCourse(){
     String  sql="select * from course;";
      try {
          Statement st = conn.createStatement();
          ResultSet rt1 = st.executeQuery(sql);
         while(rt1.next()){
             prout.println(rt1.getString(1));
             prout.println(rt1.getString(2));
              prout.println(rt1.getString(3));
               prout.println(rt1.getString(4));
                prout.println(rt1.getString(5));
                 prout.println(rt1.getString(6));
             prout.flush();
         }
        prout.println("End");        //提示客户端习题发送完毕
        prout.flush();
         rt1.close();
         st.close();
    } catch (SQLException ex1) {
        System.err.println(ex1.toString());
    }
}
/**
 * 响应客户端的修改、添加命令
 */
public void receive(){
    Vector SQLbean=new Vector();
    String sql;
    try {
        while(true){
            sql=bufin.readLine();     //接收客户端的SQL语句
            if(sql.equals("over")) break;
            SQLbean.addElement(sql);
        }
      Statement  st = conn.createStatement();
      for(int i=0;i<SQLbean.size();i++)
      {
        st.executeUpdate(SQLbean.elementAt(i).toString());  //执行SQL语句
        }
       st.close();
   }catch(SQLException ed){
       System.err.println(ed.toString());
   }catch(IOException ed){
        System.err.println(ed.toString());
   }
}
/**
 * 响应客户端的登录命令
 */
public void login(){
     String ID;
     String password;
     String sql;
     ResultSet rt1,rt2;
    try {
        ID = bufin.readLine();         //接收客户端的登录ID
        password=bufin.readLine();     //接收客户端的密码
    } catch (IOException ex) {
        prout.println("NET error");
        prout.flush();
        System.err.println(ex.toString());
        return;
    }
    for(int i=0;i<userContainer.size();i++){      //检查该用户ID是否已登录
        if(ID.equals(userContainer.elementAt(i))){
            prout.println("该用户已在异地登录");
            prout.flush();
            return;
        }
    }
  sql="select * from user where user_id='"+ID+"' and password='"+password+"';";
    try {
        Statement  st = conn.createStatement();
         rt1=st.executeQuery(sql);   //查询数据库
        if (rt1.next()) {            // 用户ID、密码正确
            prout.println("OK");
            prout.flush();
            id=ID;
            userContainer.addElement(ID);   //将用户ID添加到userContainer中
            prout.println(rt1.getString(2));
            prout.println(rt1.getString(4));
            prout.println(rt1.getBoolean(5));
            prout.println(rt1.getString(6));
            prout.println(rt1.getString(7));
            rt2=st.executeQuery("select course_name from user_course where user_id='"+ID+"';");
            while(rt2.next()){
                 prout.println(rt2.getString(1));   //发送用户所教课程名给客户端
            }
            prout.println("over");
            prout.flush();
            rt2.close();
        }
        else {     // 用户ID、密码不正确
          prout.println("用户ID或密码出错");
          prout.flush();
        }
      rt1.close();
      st.close();
    } catch (SQLException ex2) {
     prout.println("数据库读取失败");
     prout.flush();
     System.err.println(ex2.toString());
    }
}
/**
 * 下载试卷到客户端
 */
public void load(){
    String readin, filechar;
    String filepath,answerpath;
    Statement st,st2;
    ResultSet rt;
    int i;
    try {
        readin = bufin.readLine();    //接收客户端的查询语句
        st = conn.createStatement();
        rt=st.executeQuery(readin);
        BufferedReader fileread=null;
        BufferedReader answerread=null;
        while(rt.next()){
            try{
            filepath=rt.getString("path");
            fileread=new BufferedReader(new FileReader(filepath));
            answerpath=filepath.substring(0,filepath.length()-8)+"answer.txt";
            answerread=new BufferedReader(new FileReader(answerpath));
          }catch (IOException ex) {
                i=rt.getInt(1);
            //    System.err.println(ex.toString()+i);
                try{
                   st2= conn.createStatement();
                   st2.executeUpdate("delete from test where test_id=" + i +";");
                   st2.close();
              }catch (SQLException  exl) {
                  System.err.println(exl.toString()+i);
              }
                continue;
            }
            prout.println(rt.getString("course_name"));
            prout.println(rt.getString("user_name"));
            prout.println(rt.getString("test_type"));
            prout.println(rt.getString("date"));
            prout.flush();
            while((filechar=fileread.readLine())!=null){
               prout.println(filechar);
               prout.flush();
            }
            prout.println("fileover");
            prout.flush();
            fileread.close();
            fileread=null;
            while((filechar=answerread.readLine())!=null){
                prout.println(filechar);
                prout.flush();
            }
            prout.println("answerover");
            prout.flush();
            answerread.close();
            answerread=null;
        }
        rt.close();
        st.close();
      } catch (SQLException ex1) {
          System.err.println(ex1.toString());
      }catch (IOException ex) {
           System.err.println(ex.toString());
     }finally{
        prout.println("End");
        prout.flush();
    }
}
/**
 * 响应客户端上传试卷
 */
public void transfer(){
    String readin,course_name,user_name,type,path;
    String sql="select test_id from test;";
    int test_id;
    String filename;
   PrintWriter fileout,answerout;
    Date datetime=new Date();
    java.text.SimpleDateFormat formatter = new java.text.SimpleDateFormat("yyyy-MM-dd");
    String date=formatter.format(datetime);           //获得当前系统时间
    try {
        Statement st = conn.createStatement();
        ResultSet rt=st.executeQuery(sql);
        if(rt.next()){
            rt.last();        //取最后一条记录
            test_id = rt.getInt(1);
        }else test_id=0;
        rt.close();
        st.close();
        sql="insert into test(course_name,user_name,test_type,path,date) values(?,?,?,?,'"+date+"');";
        PreparedStatement pst=conn.prepareStatement(sql);    //将上传试卷属性插入到数据库中去
            course_name= bufin.readLine();    //获得课程名属性
            test_id++;
            pst.setString(1,course_name);
            user_name=bufin.readLine();      //获得创建者属性
            pst.setString(2,user_name);
            type=bufin.readLine();        //获得试卷类型属性
            pst.setString(3,type);
            filename=test_id+"test.txt";
            path="test/"+filename;
            pst.setString(4,path);     //设计试卷路径属性
            pst.executeUpdate();   //执行插入语句
            pst.close();
           fileout=new PrintWriter(new FileOutputStream(path));
           while(true){
                readin=bufin.readLine();
                if(readin.equals("over"))break;
                fileout.println(readin);
                fileout.flush();
           }
      fileout.close();
      path=path.substring(0,path.length()-8)+"answer.txt";
      answerout=new PrintWriter(new FileOutputStream(path));
                     while(true){
                          readin=bufin.readLine();
                          if(readin.equals("over"))break;
                          answerout.println(readin);
                          answerout.flush();
                     }
                answerout.close();
    } catch (IOException ex) {
         System.err.println(ex.toString());
    }catch (SQLException ex1) {
         System.err.println(ex1.toString());
    }
}
}