package tryChat;

import java.sql.*;
import java.util.*;

public class DataBaseOfChatRoom { // 用于執行靜態 SQL 語句并返回它所生成結果的對象
	private static Statement sta; // 用于數據庫操作的Statement	
	private static ResultSet rs;	//表示數據庫結果集的數據表
	private Connection con;//與特定數據庫的連接（會話）。
	
	public static void main(String args[]){
//完成所有的方法和數據測試		
		DataBaseOfChatRoom trydata=new DataBaseOfChatRoom();
		String str;
//		trydata.setUserInformation("小明", "123456", "man", "Face", "Emile","QQ", "CountryAndProvince", "Favore");
//顯示所有用戶信息					
//		String[] str=trydata.getAllUserInformation();
//		 for(int i=0;i<str.length;i++){
//		 System.out.println(str[i]);
//	 }
		 
//顯示所有用戶名單		 
//		 String[] string=trydata.getAllUserNames();
//			 for(int i=0;i<string.length;i++){
//				 System.out.println(string[i]);
//			 }

//		 str=trydata.getPassword("小明"); 
//		trydata.setFavore("a1","nn");
//		System.out.println(trydata.getFavore("a1"));

	}
	
	
	/**初始化各個數據*/
	public DataBaseOfChatRoom(){
		try {
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");// 加載數據庫驅動
		con = DriverManager.getConnection("jdbc:odbc:ChatDataBase","", "");//連接數據庫
		sta = con.createStatement(); // 創建一個 Statement對象來將 SQL 語句發送到數據庫				
		
		}catch(Exception e){System.out.println(e.toString());}
	}
	/**執行 修改,刪除,插入等SQL語句*/
	public void executeUpdate(String commandStr) {
		try {
			sta.executeUpdate(commandStr);
		}catch(Exception e){System.out.println(e.toString());}
	}

	/**處理查詢語句返回 ResultSet對象*/
	public ResultSet executeQuery(String commandStr){	
		try
		{
		rs=sta.executeQuery(commandStr);
		
		}catch(Exception e){System.out.println(e.toString());}			
		return rs;
	}
	/**添加新的用戶*/
	public void addUser(String Name,String Password,String Sex,String Face,String Emile,String QQ,String CountryAndProvince,String favore){
		if(this.isAvailability(Name)){
			String commandStr="insert into InformationOfUser(Name,Password,Sex,Face,Emile,QQ,CountryAndProvince,Favore) " +
			"VALUES(\'" +Name+"\',"+"\'"+Password+"\',"+"\'"+Sex+"\',"+"\'"+Face+"\',"
			+"\'"+Emile+"\',"+"\'"+QQ+"\',"+"\'"+CountryAndProvince+"\',"+"\'"+favore+"\')";
		this.executeUpdate(commandStr);
		}		
	}
	/**刪除用戶*/
	public void removeUser(String userName){
		String commandStr="delete from InformationOfUser where Name=\'"+userName+"\'";
		this.executeUpdate(commandStr);
	}
	
	/**返回指定用戶個人信息所有信息*/
	public String getUserInformation(String userName){
		String str="";
		str+="用戶姓名:\t"+userName+"\n";
		str+="用戶密碼:\t"+getPassword(userName)+"\n";
		str+="用戶性別:\t"+getSex(userName)+"\n";
		str+="用戶地址:\t"+getCountryAndProvince(userName)+"\n";
		str+="用戶郵箱:\t"+getEmile(userName)+"\n";
		str+="用戶QQ : \t"+getQQ(userName)+"\n";
		str+="注冊日期:\t"+getRegisterDate(userName)+"\n";
		str+="個人留言:\t"+getFavore(userName)+"\n";
		return str;		
	}
	/**返回指定用戶密碼*/
	public String getPassword(String userName){
		String str="";
		ResultSet rs=this.executeQuery("select Password from InformationOfUser where Name=\'"+userName+"\'");
		try{
			rs.next();
			str=rs.getString("Password");
			rs.close();
		}catch(Exception e){System.out.println(e.toString());}
		return str;
	}	
	/**返回指定用戶性別*/
	public String getSex(String userName){
		String str="";
		ResultSet rs=this.executeQuery("select Sex from InformationOfUser where Name=\'"+userName+"\'");
		try{
			rs.next();
			str=rs.getString("Sex");
			rs.close();
		}catch(Exception e){System.out.println(e.toString());}
		return str;
	}
	   /**返回指定用戶頭像*/
	public String getFace(String userName){
		String str="";
		ResultSet rs=this.executeQuery("select Face from InformationOfUser where Name=\'"+userName+"\'");
		try{
			rs.next();
			str=rs.getString("Face");
			rs.close();
		}catch(Exception e){System.out.println(e.toString());}
		return str;
	}
	
	/**返回指定用戶郵箱*/
	public String getEmile(String userName){
		String str="";
		ResultSet rs=this.executeQuery("select Emile from InformationOfUser where Name=\'"+userName+"\'");
		try{
			rs.next();
			str=rs.getString("Emile");
			rs.close();
		}catch(Exception e){System.out.println(e.toString());}
		return str;
	}
	  /**返回指定用戶QQ*/
	public String getQQ(String userName){
		String str="";
		ResultSet rs=this.executeQuery("select QQ from InformationOfUser where Name=\'"+userName+"\'");
		try{
			rs.next();
			str=rs.getString("QQ");
			rs.close();
		}catch(Exception e){System.out.println(e.toString());}
		return str;
	}
	/**返回指定用戶注冊日期*/
	public String getRegisterDate(String userName){
		String str="";
		rs=this.executeQuery("select RegisterDate from InformationOfUser where Name=\'"+userName+"\'");
		try{
			rs.next();
			str=rs.getString("RegisterDate");
			rs.close();
		}catch(Exception e){System.out.println(e.toString());}
		return str;
	}
	/**返回指定用戶所屬地區*/
	public String getCountryAndProvince(String userName){
		String str="";
		ResultSet rs=this.executeQuery("select CountryAndProvince from InformationOfUser where Name=\'"+userName+"\'");
		try{
			rs.next();
			str=rs.getString("CountryAndProvince");
			rs.close();
		}catch(Exception e){System.out.println(e.toString());}
		return str;
	}	
	/**返回用戶留言*/
	public String getFavore(String userName){
		String str="";
		ResultSet rs=this.executeQuery("select Favore from InformationOfUser where Name=\'"+userName+"\'");
		try{
			rs.next();
			str=rs.getString("Favore");
			rs.close();
		}catch(Exception e){System.out.println(e.toString());}
		return str;
	}
	
	
	
	/**獲取所有用戶姓名*/
	public String[] getAllUserNames(){
		Vector v=new Vector();
		String str="";
		try{			
			ResultSet rs=this.executeQuery("select Name from InformationOfUser ");	
			rs.next();			
			for(int i=0;i<rs.getRow();i++){
				str=rs.getString("Name");
				v.add(str);
				rs.next();
			}				
			rs.close();
		}catch(Exception e){System.out.println(e.toString());}
		String[] string=new String[v.size()];
		//復制成對象數組
		v.copyInto(string);
		
	 return string;	
	}
	/**獲取所有用戶的用戶信息*/
	public String[] getAllUserInformation(){		
		Vector v=new Vector();
		String str="";
		
		try{	
			
			ResultSet rs=this.executeQuery("select* from InformationOfUser ");
			rs.next();							
			for(int i=0;i<rs.getRow();i++){				
				str=rs.getString("Name")+"\t"+rs.getString("Password")+"\t"+rs.getString("Sex")
				+"\t"+rs.getString("Face")+"\t"+rs.getString("Emile")+"\t"+rs.getString("QQ")
				+"\t"+rs.getString("CountryAndProvince")+"\t"+rs.getString("Favore")+"\n";
				rs.next();
				v.add(str);
			}						 			
		}catch(Exception e){System.out.println(e.toString());}
		String[] string=new String[v.size()];
		v.copyInto(string);
		
		
		return string;
	}
	//Name,Password,Sex,Face,Emile,QQ,CountryAndProvince,Favore
/**修改用戶信息*/	
public void setUserInformation(String userName,String Password,String Sex,String Face,String Emile,String QQ,String CountryAndProvince,String Favore){
	this.setPassword(userName, Password);
	this.setSex(userName, Sex);
	this.setFace(userName, Face);
	this.setEmile(userName, Emile);
	this.setQQ(userName, QQ);
	this.setCountryAndProvince(userName, CountryAndProvince);
	this.setFavore(userName, Favore);
}
/**修改用戶密碼*/
public void setPassword(String userName,String Password){
	this.executeUpdate("update InformationOfUser set Password=\'"+Password+"\'"+"where Name=\'"+userName+"\'");
}
	
/**修改用戶性別*/
public void setSex(String userName,String Sex){
	this.executeUpdate("update InformationOfUser set Sex=\'"+Sex+"\'"+"where Name=\'"+userName+"\'");
}

/**修改用戶頭像*/
public void setFace(String userName,String Face){
	this.executeUpdate("update InformationOfUser set Face=\'"+Face+"\'"+"where Name=\'"+userName+"\'");
}
/**修改用戶郵箱*/
public void setEmile(String userName,String Emile){
	this.executeUpdate("update InformationOfUser set Emile=\'"+Emile+"\'"+"where Name=\'"+userName+"\'");
}
/**修改用戶QQ*/
public void setQQ(String userName,String QQ){
	this.executeUpdate("update InformationOfUser set QQ=\'"+QQ+"\'"+"where Name=\'"+userName+"\'");
}
/**修改用戶地址*/
public void setCountryAndProvince(String userName,String CountryAndProvince){
	this.executeUpdate("update InformationOfUser set CountryAndProvince=\'"+CountryAndProvince+"\'"+"where Name=\'"+userName+"\'");
}
/**修改用戶留言*/	
public void setFavore(String userName,String Favore){
	executeUpdate("update InformationOfUser set Favore=\'"+Favore+"\'"+"where Name=\'"+userName+"\'");
}
/**查看該用戶列表中是否包含此用戶.不包含return ture否則return false*/
public boolean isAvailability(String name){
	String[] str=this.getAllUserNames();
	for(int i=0;i<str.length;i++){
		if(str[i].equals(name)){
			return false;//有重名不可用
		}
	}
	return true;
}
/**修改指定用戶信息*/
public void setSpecialUserInfo(String userName,String Password,String Sex,String Face,
		String Emile,String QQ,String CountryAndProvince,String favore ){
	setPassword(userName, Emile);
	setSex(userName, Sex);
	setFace(userName, Face);
	setEmile(userName, Emile);
	setQQ(userName, QQ);
	setCountryAndProvince(userName, CountryAndProvince);
	setFavore(userName, favore);
}

/**釋放資源,在程序未終止前勿使用*/
	public void close(){
		try
		{
		rs.close();
		sta.close();
		con.close();
		}catch(Exception e)
		{
			System.out.println(e.toString());
		}
	}
}