package com.jspdev.ch17;
import com.jspdev.util.*;
import java.sql.*;
import javax.sql.*;
import java.io.*;
import java.util.*;
public class DepartmentBean
{
	private   Connection conn ;
	
	public DepartmentBean()throws Exception
	{
		this.conn=DatabaseConn.getConnection();
	}
	public void addDepartment(Department department)throws Exception
	{
	     Statement stmt=conn.createStatement();
		 stmt.executeUpdate("insert into Department values('"+department.getId()+"','"+department.getName()+"','"+department.getDescription()+"','"+department.getLeader()+"')");
	}
	public Collection getLeaders()throws Exception
	{
		 Statement stmt=conn.createStatement();
	     ResultSet rst=stmt.executeQuery("select * from Employee where id not in (select leader from Department)");
	     Collection ret=new ArrayList();
	     while(rst.next())
	     {
	     	Employee temp=new Employee();
	     	temp.setId(rst.getString("id"));
	     	temp.setName(rst.getString("name"));
	        ret.add(temp);
	     }
	     return ret;
	  }
	  
	public Collection getDepartments()throws Exception
	{
	     Statement stmt=conn.createStatement();
	     ResultSet rst=stmt.executeQuery("select * from Department");
	     Collection ret=new ArrayList();
	     while(rst.next())
	     {
	     	Department temp=new Department();
	     	temp.setId(rst.getString("id"));
	     	temp.setName(rst.getString("name"));
	     	temp.setDescription(rst.getString("description"));
	     	temp.setLeader(rst.getString("leader"));
	        ret.add(temp);
	     }
	     return ret;
	  }
	     
}
