teacherdao.java

学生信息管理系统

/**
 * @(#)TeacherDao.java
 *
 *
 * @author 
 * @version 1.00 2007/6/28
 */

package dao;
import java.sql.*;
import conn.ConnectDB;
import vo.*;
import java.util.*;
public class TeacherDao 
{
	public TeacherDao()
	{
		
	}
    public boolean checkTeacher(String teacher_id)
    {
    	ResultSet rs=null;
    	boolean isexist=false;
    	try
    	{
    		String sql="select * from teacherInformation where teacher_id='"+teacher_id+"'";
			Connection conn = ConnectDB.getConnection();
			Statement stmt =conn.createStatement();
			rs=stmt.executeQuery(sql);
		    if(rs.next())
    		{
    			isexist=true;
    		}
    	}		
    		catch(Exception e)
			{
				e.printStackTrace();
			}
			
			return isexist;
    
    }
    public TeacherVO getTeacherById(String teacher_id)
    {
     	ResultSet rs=null;
     	TeacherVO teachervo=null;
     	try
     	{
     		teachervo=new TeacherVO();
     		String sql="select * from teacherInformation  where teacherInformation.teacher_id='"+teacher_id+"'";
     		Connection conn = ConnectDB.getConnection();
			Statement stmt =conn.createStatement();
			rs=stmt.executeQuery(sql);
     		if(rs.next())
     		{
     			teachervo.setTeacherId(rs.getString("teacher_id"));
     			teachervo.setName(rs.getString("name"));
     			teachervo.setSex(rs.getString("sex"));
     			teachervo.setdepartment_id(rs.getString("department_id"));
     			
     		}
     	
     		rs=stmt.executeQuery("select * from teacherphone where teacherphone.teacher_id='"+teacher_id+"'");
     		if(rs.next())
     		{
     			teachervo.setPhone(rs.getString("telephone"));
     			
     		}
     	
     		rs=stmt.executeQuery("select * from teacherEmail where teacherEmail.teacher_id='"+teacher_id+"'");
     		if(rs.next())
     		{
     			teachervo.setEmail(rs.getString("email"));
     			
     		}
     	}
     		catch(Exception e)
			{
				e.printStackTrace();
			}
   		return teachervo;
    }
    public ArrayList getTeacherId(String teacher_name)
    {
    	ArrayList teacher_ids=new ArrayList();
     	try
     	{
     		String sql="select teacher_id from teacherInformation where name='"+teacher_name+"'";
     		Connection conn = ConnectDB.getConnection();
			Statement stmt =conn.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			String teacher_id=null;
			while(rs.next())
			{
				
				teacher_id=(String)rs.getString("teacher_id");
				teacher_ids.add(teacher_id);
			}
			
		}
		catch(Exception e)
		{
			
		}
		return teacher_ids;
    }
    public ArrayList getTeacherByName(String teacher_name)
    {
     	ArrayList teachervos=new ArrayList();
     	ResultSet rs=null;
     	try
     	{
     		TeacherVO teachervo=null;
     		ArrayList teacher_ids=getTeacherId(teacher_name);
     		Connection conn = ConnectDB.getConnection();
			Statement stmt =conn.createStatement();
			for(int i=0;i<teacher_ids.size();i++)
			{
				String teacher_id=(String)teacher_ids.get(i);
				rs=stmt.executeQuery("select teacher_id from teacherInformation where teacher_id='"+teacher_id+"'");
				
				teachervo=new TeacherVO();
			
				if(rs.next())
				{
					
					teachervo.setTeacherId(rs.getString("teacher_id"));
	     			teachervo.setName(rs.getString("name"));
	     			teachervo.setSex(rs.getString("sex"));
	     			teachervo.setdepartment_id(rs.getString("department_id"));
	     		}
	     		rs=stmt.executeQuery("select * from teacherphone where teacherphone.teacher_id='"+teacher_id+"'");
		     	if(rs.next())
		     	{
		     			teachervo.setPhone(rs.getString("telephone"));
		     			
		     	}
		     	
		     	rs=stmt.executeQuery("select * from teacherEmail where teacherEmail.teacher_id='"+teacher_id+"'");
		     	if(rs.next())
		     	{
		     		teachervo.setEmail(rs.getString("email"));
		     			
		     	}		
				
			}
			
   
     	
     		
     	
     	}
     		catch(Exception e)
			{
				e.printStackTrace();
			}
   		return teachervos;
    }
      public ArrayList getTeachers()
    {
     	ResultSet rs=null;
     	ArrayList teachers=new ArrayList();
     	try
     	{
     		
     		String sql="select * from teacherInformation ";
     		Connection conn = ConnectDB.getConnection();
			Statement stmt =conn.createStatement();
			rs=stmt.executeQuery(sql);
     		while(rs.next())
     		{
     			TeacherVO teachervo=new TeacherVO();
     			teachervo.setTeacherId(rs.getString("teacher_id"));
     			teachervo.setName(rs.getString("name"));
     			teachervo.setSex(rs.getString("sex"));
     		//	teachervo.setPhone(rs.getString("telephone"));
     		//	teachervo.setEmail(rs.getString("email"));
     			teachervo.setdepartment_id(rs.getString("department_id"));
     			teachers.add(teachervo);
     		}
     		rs=stmt.executeQuery("select telepone from teacherphone ");
     		while(rs.next())
     		{
     			TeacherVO teachervo=new TeacherVO();	
     			teachervo.setPhone(rs.getString("telephone"));
     			teachers.add(teachervo);
     		}
     		rs=stmt.executeQuery("select email from teacheremail ");
     		while(rs.next())
     		{
     			TeacherVO teachervo=new TeacherVO();	
     			teachervo.setEmail(rs.getString("email"));
     			teachers.add(teachervo);
     		}
     	}
     		catch(Exception e)
			{
				e.printStackTrace();
			}
   		return teachers;
    }
   public void insertTeacher(TeacherVO teachervo )
   {
    	
   		try
   		{
   			ConnectDB.executeUpdate("insert into teacherInformation values('"+teachervo.getTeacherId()+"','"+teachervo.getName()+"','"+teachervo.getdepartment_id()+"','"+teachervo.getSex()+"')");
    		ConnectDB.executeUpdate("insert into teacherphone (teacher_id,telephone) values ('"+teachervo.getTeacherId()+"','"+teachervo.getPhone()+"')");
    		ConnectDB.executeUpdate("insert into teacherEmail (teacher_id,email) values ('"+teachervo.getTeacherId()+"','"+teachervo.getEmail()+"')");

   		}
   		catch(Exception e)
   		{
			e.printStackTrace();
   			
   		}
   }
   public void alterTeacherInfor(TeacherVO teachervo)
   {
   		ResultSet rs=null;
   		
   		try
   		{
   			
     		String sql="select * from teacherInformation where teacher_id='"+teachervo.getTeacherId()+"'";
     		Connection conn = ConnectDB.getConnection();
			Statement stmt =conn.createStatement();
			rs=stmt.executeQuery(sql);
			if(rs.next())
			{
				ConnectDB.executeUpdate("update teacherInformation set name='"+teachervo.getName()+"',department_id='"+teachervo.getdepartment_id()+"',sex='"+teachervo.getSex()+"'where teacher_id='"+teachervo.getTeacherId()+"'");
				
				ConnectDB.executeUpdate("update teacherphone set telephone='"+teachervo.getPhone()+"'where teacher_id='"+teachervo.getTeacherId()+"'");
				ConnectDB.executeUpdate("update teacherEmail set email='"+teachervo.getEmail()+"'where teacher_id='"+teachervo.getTeacherId()+"'");
			
			}
   		}
   		
     	catch(Exception e)
		{
			e.printStackTrace();
		}
   }
   public void deleteTeacherInfor(String teacher_id)
   {
   		ResultSet rs=null;
   		try
   		{
   			Connection conn=ConnectDB.getConnection();
   			Statement stat=conn.createStatement();
   			rs=stat.executeQuery("select * from teacherInformation where teacher_id='"+teacher_id+"'");
   			if(rs.next())
   			{
   				ConnectDB.executeUpdate("delete from teacherphone where teacher_id='"+teacher_id+"'");
   				ConnectDB.executeUpdate("delete from teacherEmail where teacher_id='"+teacher_id+"'");
   				ConnectDB.executeUpdate("delete from teacherInformation   where teacher_id='"+teacher_id+"'");
   			}
   		}
   		catch(Exception e)
		{
			e.printStackTrace();
		}
   }
}