departmentbean.java

系统项目的一部分 开发工具:JBuilder X. 数据库:mysql. 数据库名:user. 两张表:(1)employee,(2)department

package com.jspdev.ch17;
import java.sql.*;
import java.util.*;

public class DepartmentBean {
  private Connection conn ;
  public DepartmentBean()throws Exception
  {
          this.conn=DatabaseConn.getConnection();
  }
  public void addDepartment(Department department)throws Exception
  {
    PreparedStatement pstmt = conn.prepareStatement("insert into department values(?,?,?,?)");
    pstmt.setString(1, department.getId());
    pstmt.setString(2, department.getName());
    pstmt.setString(3, department.getDescription());
    pstmt.setString(4, department.getLeader());
    try {
      pstmt.execute();
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }

  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;
  }
  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;
  }
}