import java.io.*;
import java.net.*;
import java.sql.*;
import java.util.Vector;
class ServerThread extends Thread{//繼承線程
private Socket socket;//定義套接口
private BufferedReader in;//定義輸入流
private PrintWriter out;//定義輸出流
int no;//定義申請的jicq號碼
public ServerThread(Socket s) throws IOException {//線程構造函數
   socket=s;//取得傳遞參數
  in=new BufferedReader(new InputStreamReader(socket.getInputStream()));//創建輸入流
  out=new PrintWriter(new BufferedWriter(new OutputStreamWriter(socket.getOutputStream())),true);//創建輸出流
   start();//啟動線程
   }

public void run(){//線程監聽函數
 try{ while(true){
                    String str=in.readLine();//取得輸入字符串
                    if(str.equals("end"))break;//如果是結束就關閉連接
       else if(str.equals("login")) {//如果是登錄
          try{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//連接數據庫
         Connection c=DriverManager.getConnection("jdbc:odbc:javaicq"," "," ");
         String sql="select nickname,password from icq where icqno=?";                                                               
           //準備從數據庫選擇呢稱和密碼                                                            
  PreparedStatement prepare=c.prepareCall(sql);//設定數據庫查尋條件
          String icqno=in.readLine();
                int g=Integer.parseInt(icqno);//取得輸入的jicq號碼
                 System.out.println(icqno);
                 String passwd=in.readLine().trim();//取得輸入的密碼
                 System.out.println(passwd);
                 prepare.clearParameters();
                 prepare.setInt(1,g);//設定參數
                 ResultSet r=prepare.executeQuery();//執行數據庫查尋
                 if(r.next()){//以下比較輸入的號碼于密碼是否相同
                     String pass=r.getString("password").trim();
                     System.out.println(pass);
                     if(passwd.regionMatches(0,pass,0,pass.length()))
{ out.println("ok");
//如果相同就告訴客戶ok
//并且更新數據庫用戶為在線
//以及注冊用戶的ip 地址
                      //*************register ipaddress
                      String setip="update icq set ip=? where icqno=?";
                      PreparedStatement prest=c.prepareCall(setip);
                      prest.clearParameters();
                      prest.setString(1,socket.getInetAddress().getHostAddress());
                      prest.setInt(2,g);
                      int set=prest.executeUpdate();
                      System.out.println(set);
                      //*************ipaddress
                      //set status online
                      String status="update icq set status=1 where icqno=?";
                      PreparedStatement prest2=c.prepareCall(status);
                       prest2.clearParameters();
                       prest2.setInt(1,g);
                       int set2=prest2.executeUpdate();
                      System.out.println(set2);
                      //set online
}
//否者告訴客戶失敗
                      else out.println("false");r.close();c.close();}
                 else{ out.println("false");
                 System.out.println("false");
                r.close();
                c.close();}
                }catch (Exception e){e.printStackTrace();}
                socket.close();
                }//end login
             //登錄結束
  //以下為處理客戶的新建請求    
else  if(str.equals("new")){
   try{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//連接數據庫
        Connection c2=DriverManager.getConnection("jdbc:odbc:javaicq"," "," ");
String newsql="insert into icq(nickname,password,email,info,place,pic) values(?,?,?,?,?,?)";
//準備接受用戶的呢稱，密碼，email，個人資料，籍貫，頭像等信息
       PreparedStatement prepare2=c2.prepareCall(newsql);
       String nickname=in.readLine().trim();
       String password=in.readLine().trim();
       String email=in.readLine().trim();
       String info=in.readLine().trim();
       String place=in.readLine().trim();
       int picindex=Integer.parseInt(in.readLine());
       prepare2.clearParameters();
       prepare2.setString(1,nickname);
       prepare2.setString(2,password);
       prepare2.setString(3,email);
       prepare2.setString(4,info);
       prepare2.setString(5,place);
       prepare2.setInt(6,picindex);
       int r3=prepare2.executeUpdate();//執行數據庫添加
String sql2="select icqno from icq where nickname=?";
//以下告訴客戶其注冊的號碼
       PreparedStatement prepare3=c2.prepareCall(sql2);
        prepare3.clearParameters();
       prepare3.setString(1,nickname);
       ResultSet r2=prepare3.executeQuery();
     while(r2.next()){
      //out.println(r2.getInt(1));
      no=r2.getInt(1);
      System.out.println(no);
     }
      out.println(no);
      out.println("ok");
c2.close();
//完畢
     }catch (Exception e){e.printStackTrace();out.println("false");}
     socket.close();
   }//end new
//新建用戶結束
//以下處理用戶查找好友
else if(str.equals("find")){
try{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection c3=DriverManager.getConnection("jdbc:odbc:javaicq"," "," ");
  //以下連接數據庫，并且返回其他用戶的呢稱，性別，籍貫，個人資料等信息
     String find="select nickname,sex,place,ip,email,info from icq";
    Statement st=c3.createStatement();
    ResultSet result=st.executeQuery(find);
     while(result.next()){
     out.println(result.getString("nickname"));
     out.println(result.getString("sex"));
     out.println(result.getString("place"));
     out.println(result.getString("ip"));
      out.println(result.getString("email"));
       out.println(result.getString("info"));
     }//while end
     out.println("over");
     ////////GET ICQNO
     int d,x;
boolean y;
//以下返回用戶的jicq號碼，頭像號，及是否在線
     ResultSet iset=st.executeQuery("select icqno,pic,status from icq");
     while(iset.next()){
     d=iset.getInt("icqno");
     out.println(d);
     x=iset.getInt("pic");//pic info
     out.println(x);
     y=iset.getBoolean("status");
      if (y){out.println("1");}
           else {out.println("0");}
      //System.out.println(d);
     }
    // end send jicqno
     iset.close();
     /////////icqno end
      c3.close();result.close();
}catch (Exception e){e.printStackTrace();System.out.println("false");}
//socket.close();
}//end find
//查找好友結束
//以下處理用戶登錄時讀取其好友資料
else if(str.equals("friend")){
try{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection c4=DriverManager.getConnection("jdbc:odbc:javaicq"," "," ");
//以下連接好友表，返回用戶的好友名單
     String friend="select friend from friend where icqno=?";
     PreparedStatement prepare4=c4.prepareCall(friend);
        prepare4.clearParameters();
         int icqno=Integer.parseInt(in.readLine());
         System.out.println(icqno);
         prepare4.setInt(1,icqno);
       ResultSet r4=prepare4.executeQuery();
       Vector friendno=new Vector();//該矢量保存好友號碼
        while(r4.next()){
      friendno.add(new Integer(r4.getInt(1)));
     }
//read friend info
//以下告訴客戶其好友的呢稱，號碼，ip地址，狀態，頭像，個人資料等信息
    out.println(friendno.size());
          for(int i=0;i<friendno.size();i++){
         String friendinfo="select nickname,icqno,ip,status,pic,email,info from icq where icqno=?";
      PreparedStatement prepare5=c4.prepareCall(friendinfo);
      prepare5.clearParameters();
       prepare5.setObject(1,friendno.get(i));
      ResultSet r5=prepare5.executeQuery();
      boolean status;
         while(r5.next()){
       out.println(r5.getString("nickname"));
           out.println(r5.getInt("icqno"));
           out.println(r5.getString("ip"));
         status=r5.getBoolean("status");
         if (status)out.println("1");
           else {out.println("0");}
        out.println(r5.getInt("pic"));