//數據庫操作
package myQQ;

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

class DBWork
{
	/*
	String DBDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
	String url = "jdbc:odbc:QQ";	
	*/
	
	String DBDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
	String url = "jdbc:microsoft:sqlserver://127.0.0.1:1433";
	
	String username; //用戶名
	String pw; //密碼
	Connection con = null; //聯接
	Statement stmt = null; //中間載體
	PreparedStatement pstmt = null; //傳入參數中間載體
	CallableStatement cstmt = null; //調用存儲過程中間載體
	ResultSet rt = null; //結果集
	ResultSetMetaData rtmd = null; //元數據
	Vector vData = new Vector(); //返回結果的二維集合
	Vector vCell;
	
	DBWork(String username,String pw)
	{
		this.username = username;
		this.pw = pw;	
	}
	
	public void setCon(int type) throws Exception
	{
		//加載驅動程序
		Class.forName(this.DBDriver);
		//建立連接
		con = DriverManager.getConnection(this.url,this.username,this.pw);
		//創建中間載體
		if(type==0)
			stmt = con.createStatement();
	}
	
	public void closeCon(int type) throws Exception
	{
		//關閉中間載體及連接 
		if(type==0)
			stmt.close(); 
		else if(type==1)
			pstmt.close();
		else
			cstmt.close();
		con.close();	
	}
	
	public boolean exe(String sql)
	{	//執行查詢外的語句
		boolean f = false;
		try {
			this.setCon(0);
			//執行sql語句
			stmt.execute(sql);
			this.closeCon(0);
			f = true;	
	    }
	    catch (Exception ex) {
	    	System.out.println (ex);
	    }
	    return f;
	}
	
	public Vector exeQuery(String sql)
	{	//查詢--不調用存儲過程
		try {
			this.setCon(0);
			//執行spl語句
			rt = stmt.executeQuery(sql);
			this.setData(); //設置所取得的數據
			rt.close();
	    	this.closeCon(0);		
	    }
	    catch (Exception ex) {
	    	System.out.println (ex);
	    }
	    return vData;
	}
	
	public boolean Pexe(String sql,Object[] o)
	{	//利用PreparedStatement執行sql語句
		boolean f = false;
		try {
			this.setCon(1);
			pstmt = con.prepareCall(sql); //建立參數中間載體
			for (int i = 1; i<=o.length; i++)
			{
				pstmt.setObject(i,o[i-1]); //設置參數
			}
			pstmt.executeUpdate(); //執行插入、修改、刪除語句
			this.closeCon(1);
			f = true;
	    }
	    catch (Exception ex) {
	    	System.out.println (ex);
	    }
	    return f;
	}
	
	public Vector exePro(String pro)
	{	//調用存儲過程
		try {
			this.setCon(2);
			//執行spl語句
			cstmt = con.prepareCall("{call "+pro+"}");
			rt = cstmt.executeQuery();
			this.setData(); //設置所取得的數據
			rt.close();
	    	this.closeCon(2);		
	    }
	    catch (Exception ex) {
	    	System.out.println (ex);
	    }
	    return vData;
	}
	
	public void setData() throws Exception
	{	//設置所取得的數據
		//獲得元數據
		rtmd = rt.getMetaData();
		//獲得列數
		int c = rtmd.getColumnCount();
		vData.clear(); //預先清空
		while(rt.next())
		{
			vCell = new Vector();
			for (int i = 1; i<=c; i++)
			{
				vCell.addElement(rt.getString(i));
			}
			vData.addElement(vCell);
		}
	}
}