/*
 *定義人員、試卷數據操作組件
 */
package TestClass;

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

public class AttToExDBbean
{
	//數據庫連接指向空
	private Connection conn=null;
	//根據人員編號列出人員、試卷內容
	private PreparedStatement GetpNoOne=null;
	//引入外部查詢語句
	private Statement stmt=null;
	//根據試卷編號列出人員、試卷內容
	private PreparedStatement GetExNoOne=null;
	//列出所有人員、試卷內容
	private PreparedStatement GetAll=null;
	//添加人員、試卷記錄
	private PreparedStatement AddStmt=null;
	//修改人員、試卷記錄
	private PreparedStatement UpStmt=null;
	private PreparedStatement UpStmt1=null;
	//刪除人員、試卷記錄
	private PreparedStatement DelStmt=null;	
	
	public AttToExDBbean()
	{
		//聲明數據庫連接
		String driver,user,pass,dbURL;
		PropertyResourceBundle resourceBundle = (PropertyResourceBundle) PropertyResourceBundle.getBundle("Config");

		driver = resourceBundle.getString("jdbc.driver");
		user = resourceBundle.getString("jdbc.user");
		pass = resourceBundle.getString("jdbc.password");
		dbURL = resourceBundle.getString("jdbc.dbURL");

		try  
		{
			Class.forName(driver);
			conn =DriverManager.getConnection(dbURL,user,pass);
			//根據人員編號列出人員、試卷內容
			GetpNoOne=conn.prepareStatement("Select * From AttToEx Where pNo=?");
			//根據試卷編號列出人員、試卷內容
			GetExNoOne=conn.prepareStatement("Select * From AttToEx Where ExNo=?");
			//引入外部查詢語句
			stmt=conn.createStatement();
			//列出所有人員、試卷內容
			GetAll=conn.prepareStatement("Select * From AttToEx");
			//添加人員、試卷記錄
			AddStmt=conn.prepareStatement("Insert into AttToEx values(?,?,?)");
			//修改人員、試卷記錄
			UpStmt=conn.prepareStatement("Update AttToEx set pScore=? Where pNo=?)");
			UpStmt1=conn.prepareStatement("Update AttToEx set pNo=?,pScore=? Where ExNo=?)");
			//刪除人員、試卷記錄
			DelStmt=conn.prepareStatement("Delete From AttToEx Where ExNo=? and pNo=?)");
		}
		catch(java.lang.ClassNotFoundException e) 
		{
			System.err.println("AttToExDBbean(): 0" + e.getMessage());
		}
		catch(java.sql.SQLException e)
		{
			System.err.println("AttToExDBbean(): 1" + e.getMessage());
		}
		catch(Exception e)
		{
			System.err.println("AttToExDBbean(): 2" + e.getMessage());	
		}
	}
	
	/*
	 * 數據訪問
	 */	
	 
	//列出所有人員、試卷內容
	public ArrayList GetAllData()
	{
		ArrayList rt=new ArrayList();
		
		try 
		{
			ResultSet Rs=GetAll.executeQuery();
			
			AttToExBean beanObj=null;
			while(Rs.next())
			{
				beanObj=new AttToExBean();
				beanObj.setExNo(Rs.getString(1));
				beanObj.setPNo(Rs.getString(2));
				beanObj.setPScore(Rs.getDouble(3));
				rt.add(beanObj);
			}
			Rs.close();
		}
		catch(SQLException ex) 
		{
			System.err.println("AttToExDBbean.GetAllData: " + ex.getMessage());
		}
		return rt;
	}
	
	//根據人員編號列出人員、試卷內容
	public AttToExBean GetpNoOne(String PK_pNo)
	{
		AttToExBean rt=null;
		try 
		{
			GetpNoOne.setString(1,PK_pNo);
			
			ResultSet Rs=GetpNoOne.executeQuery();
			
			if (Rs.next())
			{
				rt=new AttToExBean();
				rt.setExNo(Rs.getString(1));
				rt.setPNo(Rs.getString(2));
				rt.setPScore(Rs.getDouble(3));
			}
			Rs.close();
		}
		catch(SQLException ex) 
		{
			System.err.println("AttToExDBbean.GetOne: " + ex.getMessage());
		}
		return rt;
	}

	//根據試卷編號列出人員、試卷內容
	public AttToExBean GetExNoOne(String PK_ExNo)
	{
		AttToExBean rt=null;
		try 
		{
			GetExNoOne.setString(1,PK_ExNo);
			
			ResultSet Rs=GetExNoOne.executeQuery();
			
			if (Rs.next())
			{
				rt=new AttToExBean();
				rt.setExNo(Rs.getString(1));
				rt.setPNo(Rs.getString(2));
				rt.setPScore(Rs.getDouble(3));
			}
			Rs.close();
		}
		catch(SQLException ex) 
		{
			System.err.println("AttToExDBbean.GetOne: " + ex.getMessage());
		}
		return rt;
	}
	
	//引入外部查詢語句
	public boolean executeUpdate(String sql)
	{
		boolean rt=false;
		try
		{
			if(conn!=null)
			{
				stmt.executeUpdate(sql);
			}
		}
		catch(SQLException ex) 
		{
			System.err.println("AttendmentDBbean.ResultSet executeQuery: " + ex.getMessage());
		}
		return rt;
	}
	
	//添加人員、試卷記錄
	public boolean InsertDB(AttToExBean obj)
	{
		boolean rt=false;
		try 
		{
			AddStmt.setString(1,obj.getExNo());
			AddStmt.setString(2,obj.getPNo());
			AddStmt.setDouble(3,obj.getPScore());
			
			rt=(AddStmt.executeUpdate()==1);
		}
		catch(SQLException ex) 
		{
			System.err.println("AttToExDBbean.InsertDB: " + ex.getMessage());
		}
		return rt;
	}
		
	//修改人員、試卷記錄
	public boolean UpdateDB(AttToExBean obj)
	{
		boolean rt=false;
		try 
		{
			UpStmt.setString(2,obj.getPNo());
			UpStmt.setDouble(1,obj.getPScore());
			
			rt=(UpStmt.executeUpdate()==1);
		}
		catch(SQLException ex) 
		{
			System.err.println("AttToExDBbean.UpdateDB: " + ex.getMessage());
		}	
		
		return rt;
	}
	public boolean UpdateDB1(AttToExBean obj)
	{
		boolean rt=false;
		try 
		{
			UpStmt.setString(3,obj.getExNo());
			UpStmt.setString(1,obj.getPNo());
			UpStmt.setDouble(2,obj.getPScore());
			
			rt=(UpStmt.executeUpdate()==1);
		}
		catch(SQLException ex) 
		{
			System.err.println("AttToExDBbean.UpdateDB: " + ex.getMessage());
		}	
		
		return rt;
	}

	//刪除人員、試卷記錄
	public boolean DeleteDB(String PK_pNo)
	{
		boolean rt=false;
		try 
		{
			DelStmt.setString(1,PK_pNo);
			rt=(DelStmt.executeUpdate()==1);
		}
		catch(SQLException ex) 
		{
			System.err.println("AttToExDBbean.DeleteDB: " + ex.getMessage());
		}
		
		return rt;		
	}	
	
	//關閉數據	
	public void Close()
	{
		try
		{
			if(conn != null)
			{
				GetAll.close();
				AddStmt.close();
				UpStmt.close();
				UpStmt1.close();
				DelStmt.close();
				GetpNoOne.close();
				GetExNoOne.close();
				conn.close();
				conn=null;
			}
		}
		catch(java.sql.SQLException e)
		{
			System.err.println("DataBaseBean():close " + e.getMessage());
		}
	}
}