package DBOperation;

import java.sql.*;


public class DBConnection {
	public Connection con;     //連接
	public Statement sta;      //語句塊
	public PreparedStatement preSta;   //預處理語句塊
	public ResultSet rs;     //結果集
	
	
	public String classStr;
	public String conStr;
	
	/**
	 * 指定所要操作的數據庫名
	 */
	public DBConnection(String database) {
		classStr = "com.microsoft.jdbc.sqlserver.SQLServerDriver";      //加載驅動
		conStr = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName="+database;     //連接數據庫
	}
	
	/**
	 *
	 * 帶用戶名與密碼的連接
	 */
	public Connection getCon(String user,String pass) {
		if(this.con == null) {
			try {
				Class.forName(classStr);
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
			try {
				this.con = DriverManager.getConnection(conStr,user,pass);
				this.sta = con.createStatement();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
		}
		return this.con;
	}

	/**
	 *
	 * 默認連接
	 */
	public Connection getCon() {
		if(this.con == null) {
			try {
				Class.forName(classStr);
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
			try {
				this.con = DriverManager.getConnection(conStr,"sa","");
				this.sta = con.createStatement();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
		}
		return this.con;
	}
	
	/**
	 *
	 * 執行
	 */
	public boolean executeUpdate(String sql) {
		if(this.con == null) {
			this.getCon();
		}
		try {
			this.sta.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		return true;
		
	}

	/**
	 *
	 * 查詢
	 */
	public ResultSet executeQuery(String sql) {
		if(this.con == null) {
			this.getCon();
		}
		try {
			this.rs= this.sta.executeQuery(sql);
			return this.rs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * 關閉連接
	 */
	public void Close() {
		try {
			this.rs.close();
		} catch(Exception ex) {
		}
		try {
			this.sta.close();
		} catch(Exception ex) {
		}
		try {
			this.preSta.close();
		} catch(Exception ex) {
		}
		try {
			this.con.close();
		} catch(Exception ex) {
		}
	}

}