package TestClass;

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

public class ManagerDBbean
{
	private Connection conn=null;
	private PreparedStatement AddStmt=null;
	private PreparedStatement GetAll=null;
	private PreparedStatement UpStmt=null;
	private PreparedStatement DelStmt=null;
	private PreparedStatement GetOne=null;
	
	public ManagerDBbean()
	{
		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);
			AddStmt=conn.prepareStatement("Insert into Management values(?,?,?,?,?,?)");
			GetAll=conn.prepareStatement("Select * From Management");
			UpStmt=conn.prepareStatement("update Management set 姓名=? ,所在單位=?,單位地址=?,密碼=?,身份=? where 學號=?");
			DelStmt=conn.prepareStatement("delete from Management where  學號=?");
			GetOne=conn.prepareStatement("Select * From Management where 學號=?");
		}
		catch(java.lang.ClassNotFoundException e) 
		{
			System.err.println("ManagerDBbean(): 0" + e.getMessage());
		}
		catch(java.sql.SQLException e)
		{
			System.err.println("ManagerDBbean(): 1" + e.getMessage());
		}
		catch(Exception e)
		{
			System.err.println("ManagerDBbean(): 2" + e.getMessage());	
		}
	}
	
	public boolean InsertDB(ManagerBean obj)
	{
		boolean rt=false;
		try 
		{
			AddStmt.setString(1,obj.getStuID());
			AddStmt.setString(2,obj.getStuName());
			AddStmt.setString(3,obj.getStuCo());
			AddStmt.setString(4,obj.getStuAddress());
			AddStmt.setString(5,obj.getStuPwd());
			AddStmt.setString(6,obj.getStuPro());
			
			rt=(AddStmt.executeUpdate()==1);
		}
		catch(SQLException ex) 
		{
			System.err.println("ManagerDBbean.InsertDB: " + ex.getMessage());
		}
		return rt;
	}
	
	public ArrayList GetAllData()
	{
		ArrayList rt=new ArrayList();
		
		try 
		{
			ResultSet Rs=GetAll.executeQuery();
			
			ManagerBean beanObj=null;
			while(Rs.next())
			{
				beanObj=new ManagerBean();
				beanObj.setStuID(Rs.getString(1));
				beanObj.setStuName(Rs.getString(2));
				beanObj.setStuCo(Rs.getString(3));
				beanObj.setStuAddress(Rs.getString(4));
				beanObj.setStuPwd(Rs.getString(5));
				beanObj.setStuPro(Rs.getString(6));
				rt.add(beanObj);
			}
			Rs.close();
		}
		catch(SQLException ex) 
		{
			System.err.println("ManagerDBbean.GetAllData: " + ex.getMessage());
		}
		return rt;
	}
	
	public boolean UpdateDB(ManagerBean obj)
	{
		boolean rt=false;
		try 
		{
			UpStmt.setString(6,obj.getStuID());
			UpStmt.setString(1,obj.getStuName());
			UpStmt.setString(2,obj.getStuCo());
			UpStmt.setString(3,obj.getStuAddress());
			UpStmt.setString(4,obj.getStuPwd());
			UpStmt.setString(5,obj.getStuPro());
			
			rt=(UpStmt.executeUpdate()==1);
		}
		catch(SQLException ex) 
		{
			System.err.println("ManagerDBbean.UpdateDB: " + ex.getMessage());
		}	
		
		return rt;
	}
	
	public boolean DeleteDB(String PK)
	{
		boolean rt=false;
		try 
		{
			DelStmt.setString(1,PK);
			rt=(DelStmt.executeUpdate()==1);
		}
		catch(SQLException ex) 
		{
			System.err.println("ManagerDBbean.DeleteDB: " + ex.getMessage());
		}
		
		return rt;		
	}
	
	public ManagerBean GetData(String PK)
	{
		ManagerBean rt=null;
		try 
		{
			GetOne.setString(1,PK);
			ResultSet Rs=GetOne.executeQuery();
			
			if (Rs.next())
			{
				rt=new ManagerBean();
				rt.setStuID(Rs.getString(1));
				rt.setStuName(Rs.getString(2));
				rt.setStuCo(Rs.getString(3));
				rt.setStuAddress(Rs.getString(4));
				rt.setStuPwd(Rs.getString(5));
				rt.setStuPro(Rs.getString(6));
			}
			Rs.close();
		}
		catch(SQLException ex) 
		{
			System.err.println("ManagerDBbean.GetData: " + ex.getMessage());
		}
		return rt;
	}
	public void Close()
	{
		try
		{
			if(conn != null)
			{
				GetAll.close();
				AddStmt.close();
				UpStmt.close();
				DelStmt.close();
				GetOne.close();
				conn.close();
				conn=null;
			}
		}
		catch(java.sql.SQLException e)
		{
			System.err.println("ManagerDBbean():close " + e.getMessage());
		}
	}
}