package data;

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

public class DBConnectionBean {
	Connection dbcon = null;

	Statement stmt = null;

	ResultSet result = null;

	String driver = "";

	String url = "";

	String user = "";

	String password = "";
	

	public DBConnectionBean() {
		try {
			InputStream fis = getClass().getResourceAsStream(
					"jdbcsql.properties");
			Properties ps = new Properties();
			ps.load(fis);
			driver = ps.getProperty("driver");
			url = ps.getProperty("url");
			user = ps.getProperty("username");
			password = ps.getProperty("password");
			Class.forName(this.driver);
			this.getopenConnection();
		} catch (Exception e) {
			System.out.println(e);
		}
	}

	public Connection getConn() {
		return dbcon;
	}

	public Connection getopenConnection() {
		try {
			this.dbcon = DriverManager.getConnection(this.url, this.user,
					this.password);
			System.out.println(".");
		} catch (SQLException e2) {
			System.out.println(e2);
		}
		return dbcon;
	}

	public ResultSet executeQuery(String query) throws SQLException {
		this.stmt = dbcon.createStatement();
		this.result = stmt.executeQuery(query);
		return result;
	}
//檢查
	public boolean checkplayer (String id) throws SQLException {
		result = this.executeQuery("select * from player where id='" + id
				+ "'");
		if (result.next())
			return true;
		return false;
	}
	public boolean checkname (String name) throws SQLException {
		result = this.executeQuery("select * from player where name='" + name
				+ "'");
		if (result.next())
			return true;
		return false;
	}
//插入
	public void insert(String id, String password, String name,String ssex,String question
			,String answer,String pic,String year,String month,String day,String myword,String money,String zctime)
	throws SQLException {
     String query = "insert into player values ('" + id + "','" + password
		+ "','" + name + "','" + ssex+ "','" + question + "','" + answer + "','" + pic + "','" + year + "'," +
				"'" + month + "','" + day + "','" + myword + "','" + money + "','" + zctime+ "')";
        this.executeUpdate(query);
      }
	public void sendmessage(String fromid, String toid, String message,String title,String sendtime)
	throws SQLException {
     String query = "insert into message (fromid,toid,message,title,zjtime) values('"+fromid+"','"+toid+"','"+message+"','"+title+"','"+sendtime+"')";
        this.executeUpdate(query);
      }
	public void friend(String fromid, String toid)
	throws SQLException {
     String query = "insert into friend (fromid,toid) values('"+fromid+"','"+toid+"')";
        this.executeUpdate(query);
      }
	public void insertword(String from,String face,String word,String time,long zjtime)
	throws SQLException {
     String query = "insert into sayword values ('" +from + "','"+face+"','"+word+"','"+time+"',"+zjtime+")";
     this.executeUpdate(query);
      }
//瀏覽
	public ResultSet showid(String id, String password,String question,String answer,String name) throws SQLException {
		this.stmt = dbcon.createStatement();
		this.result = stmt.executeQuery("select * from player where id='" + id + "'");
		return result;

	}
	public ResultSet cfph() throws SQLException {
		this.stmt = dbcon.createStatement();
		this.result = stmt.executeQuery("Select * from player  order  By  money desc");
		return result;

	}
	public ResultSet gonggao(long zjtime) throws SQLException {
		this.stmt = dbcon.createStatement();
		this.result = stmt.executeQuery("Select * from sayword where zjtime>'"+zjtime+"'order by time desc");
		// this.result = stmt.executeQuery("Select * from(Select TOP 5  *  from sayword   Order  By   id   Desc   )   A   Order   By   id   ");
		return result;

	}
//驗(yàn)證
	
	public boolean idtoid(String id, String toid) throws SQLException {
		if(id.equals(toid))
			return false;
		return true;
	}
	public boolean checkplay(String id, String password) throws SQLException {
		result = this.executeQuery("select * from player where id='" + id
				+ "' and password='" + password + "'");
		if (result.next())
			return true;
		return false;
	}
	
	public boolean compare(String fromid, String toid) throws SQLException {
		if (fromid.equals(toid))
			return false;
		return true;
	}
	public boolean chongfu(String fromid,String toid) throws SQLException {
		result = this.executeQuery("select * from friend where toid='" + toid
				+ "' and fromid='" + fromid + "'");
		if (result.next())
			return true;
		return false;
	}
	public boolean findmima(String answer,String id) throws SQLException {
		result = this.executeQuery("select * from player where id='" + id
				+ "' and answer='"+answer+"'");
		if (result.next())
			return true;
		return false;
	}
//搜索
	public ResultSet message(String id) throws SQLException {
		this.stmt = dbcon.createStatement();
		this.result = stmt.executeQuery("select * from message where toid='" + id + "'");
		return result;
	}
	public ResultSet sendbox(String id) throws SQLException {
		this.stmt = dbcon.createStatement();
		this.result = stmt.executeQuery("select * from message where fromid='" + id + "'");
		return result;
	}
	public ResultSet findpassword(String id) throws SQLException {
		this.stmt = dbcon.createStatement();
		this.result = stmt.executeQuery("select * from player where id='" + id + "'");
		return result;
	}
	public ResultSet findfriend1(String id) throws SQLException {//接收到的
		this.stmt = dbcon.createStatement();
		this.result = stmt.executeQuery("select * from friend where toid='"+id+"'");
		return result;
	}

	public ResultSet findfriend2(String id) throws SQLException {//發(fā)送出去的
		this.stmt = dbcon.createStatement();
		this.result = stmt.executeQuery("select * from friend where fromid='"+id+"'");
		return result;
	}
	public ResultSet readmessage(int read) throws SQLException {//發(fā)送出去的
		this.stmt = dbcon.createStatement();
		this.result = stmt.executeQuery("select * from message where num="+read+"");
		return result;
	}
	
	public ResultSet serchmessage1(String message,String id,String box,String kind) throws SQLException {//發(fā)送出去的
		this.stmt = dbcon.createStatement();
		this.result = stmt.executeQuery("select * from message where "+box+"='"+id+"'and message like '%"+message+"%' order by '"+kind+"'");
		return result;
	}//檢查信息，收郵箱，排列類
		
	public ResultSet serchmessage4(String pec,String id,String select,String kind,String kkk) throws SQLException {//發(fā)送出去的
		this.stmt = dbcon.createStatement();
		this.result = stmt.executeQuery("select * from message where "+select+"='"+pec+"' and "+kkk+"='"+id+"'");
		return result;
	}//收信人
	
	public ResultSet playermessage(String id) throws SQLException {//發(fā)送出去的
		this.stmt = dbcon.createStatement();
		this.result = stmt.executeQuery("select * from player where id like '"+id+"'");
		return result;
	}
	
//更新	
	
	public void updateplay(String id, String password,String ssex,String year,String month,String day,String myword,String pic) throws SQLException {
		String query = "update player set password = '" + password
				+ "',ssex ='"+ssex+"',year ='"+year+"',month ='"+month+"',day ='"+day+"',myword ='"+myword+"',pic ='"+pic+"' where id = '" + id + "'";
		this.executeUpdate(query);
	}
	public void updateplay0(String id,String ssex,String year,String month,String day,String myword,String pic) throws SQLException {
		String query = "update player set ssex ='"+ssex+"',year ='"+year+"',month ='"+month+"',day ='"+day+"',myword ='"+myword+"',pic ='"+pic+"' where id = '" + id + "'";
		this.executeUpdate(query);
	}

	public void updatefrommoney(String id,String fromoney) throws SQLException {
		String query = "update player set money= '"+fromoney+"' where id = '" + id + "'";
		this.executeUpdate(query);
	}

	public void updatetomoney(String toid,String money) throws SQLException {
		String query = "update player set money = '"+money+"' where id = '"+toid+"'";
		this.executeUpdate(query);
	}

	
	public void executeUpdate(String query) throws SQLException {
		this.stmt = dbcon.createStatement();
		stmt.executeUpdate(query);
		if (stmt != null)
			stmt.close();
	}
	public void close() throws SQLException {
		if (dbcon != null)
			dbcon.close();
		if (stmt != null)
			stmt.close();
		if (result != null)
			result.close();
	}	
}