www.pudn.com > employees.rar > EmployeeData.java

package com.wrox; 
 
import javax.sql.DataSource; 
import java.util.ArrayList; 
import java.sql.Connection; 
import java.sql.Statement; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
 
public class EmployeeData { 
 
  public static Employee getEmployee(String username, DataSource dataSource) 
    throws Exception { 
 
    Connection conn = null; 
    Statement stmt = null; 
    ResultSet rs = null; 
    Employee employee = null; 
    try { 
 
      conn = dataSource.getConnection(); 
      stmt = conn.createStatement(); 
      rs = stmt.executeQuery("select * from employees where username=\'" 
        + username + "'"); 
 
      if ( rs.next() ) { 
 
        employee = new Employee(); 
 
        employee.setUsername(rs.getString("username")); 
        employee.setPassword(rs.getString("password")); 
        employee.setDepid(new Integer(rs.getInt("depid"))); 
        employee.setRoleid(new Integer(rs.getString("roleid"))); 
        String name = rs.getString("name"); 
        employee.setName(name); 
        employee.setPhone(rs.getString("phone")); 
        employee.setEmail(rs.getString("email")); 
      } 
      else { 
 
        throw new Exception("Employee " + username + " not found!"); 
      } 
    } 
    finally { 
 
      if ( rs != null ) { 
 
        rs.close(); 
      } 
      if ( stmt != null ) { 
 
        stmt.close(); 
      } 
      if ( conn != null ) { 
 
        conn.close(); 
      } 
    } 
    return employee; 
  } 
 
  public static ArrayList getEmployees(DataSource dataSource) { 
 
    Employee employee = null; 
    ArrayList employees = new ArrayList(); 
    Connection conn = null; 
    Statement stmt = null; 
    ResultSet rs = null; 
 
    try { 
 
      conn = dataSource.getConnection(); 
      stmt = conn.createStatement(); 
      rs = 
        stmt.executeQuery("select * from employees, roles, " 
        + "departments where employees.roleid=roles.roleid " 
        + "and employees.depid=departments.depid"); 
 
      while (rs.next()) { 
 
        employee = new Employee(); 
 
        employee.setUsername(rs.getString("username")); 
        employee.setName(rs.getString("name")); 
        employee.setRolename(rs.getString("rolename")); 
        employee.setPhone(rs.getString("phone")); 
        employee.setEmail(rs.getString("email")); 
        employee.setRoleid(new Integer(rs.getInt("roleid"))); 
        employee.setDepid(new Integer(rs.getInt("depid"))); 
        employee.setDepartment(rs.getString("depname")); 
 
        employees.add(employee); 
 
        System.err.println("Username : " 
          + employee.getUsername() 
          + " Department : " + rs.getString("depname")); 
      } 
    } 
    catch ( SQLException e ) { 
 
      System.err.println(e.getMessage()); 
    } 
    finally { 
 
      if ( rs != null ) { 
 
        try { 
 
          rs.close(); 
        } 
        catch ( SQLException sqle ) { 
 
          System.err.println(sqle.getMessage()); 
        } 
        rs = null; 
      } 
      if ( stmt != null ) { 
 
        try { 
 
          stmt.close(); 
        } 
        catch ( SQLException sqle ) { 
 
          System.err.println(sqle.getMessage()); 
        } 
        stmt = null; 
      } 
      if ( conn != null ) { 
 
        try { 
 
          conn.close(); 
        } 
        catch ( SQLException sqle ) { 
 
          System.err.println(sqle.getMessage()); 
        } 
        conn = null; 
      } 
    } 
    return employees; 
  } 
 
  public static void removeEmployee(String username, DataSource dataSource) 
    throws Exception { 
 
    Connection conn = null; 
    Statement stmt = null; 
    ResultSet rs = null; 
 
    try { 
 
      conn = dataSource.getConnection(); 
      stmt = conn.createStatement(); 
 
      StringBuffer sqlString = 
        new StringBuffer("delete from employees "); 
      sqlString.append("where username='" + username + "'"); 
 
      stmt.execute(sqlString.toString()); 
    } 
    finally { 
 
      if ( rs != null ) { 
 
        rs.close(); 
      } 
      if ( stmt != null ) { 
 
        stmt.close(); 
      } 
      if ( conn != null ) { 
 
        conn.close(); 
      } 
    } 
  } 
 
  public static void addEmployee(Employee employee, DataSource dataSource) 
    throws Exception { 
 
    Connection conn = null; 
    Statement stmt = null; 
    ResultSet rs = null; 
 
    try { 
 
      conn = dataSource.getConnection(); 
      stmt = conn.createStatement(); 
 
      StringBuffer sqlString = 
        new StringBuffer("insert into employees "); 
 
      sqlString.append("values (\"" 
        + employee.getUsername() + "\", "); 
      sqlString.append("\"" + 
        employee.getPassword() + "\", "); 
      sqlString.append("\"" 
        + employee.getRoleid() + "\", "); 
      sqlString.append("\"" 
        + employee.getName() + "\", "); 
      sqlString.append("\"" 
        + employee.getPhone() + "\", "); 
      sqlString.append("\"" 
        + employee.getEmail() + "\", "); 
      sqlString.append("\"" 
        + employee.getDepid() + "\")"); 
 
      stmt.execute(sqlString.toString()); 
    } 
    finally { 
 
      if ( rs != null ) { 
 
        rs.close(); 
      } 
      if ( stmt != null ) { 
 
        stmt.close(); 
      } 
      if ( conn != null ) { 
 
        conn.close(); 
      } 
    } 
  } 
 
  public static void updateEmployee(Employee employee, DataSource dataSource) 
    throws Exception { 
 
    Connection conn = null; 
    Statement stmt = null; 
    ResultSet rs = null; 
 
    try { 
 
      conn = dataSource.getConnection(); 
      stmt = conn.createStatement(); 
 
      StringBuffer sqlString = 
        new StringBuffer("update employees "); 
 
      sqlString.append("set password='" 
        + employee.getPassword() + "', "); 
      sqlString.append("roleid=" 
        + employee.getRoleid() + ", "); 
      sqlString.append("name='" 
        + employee.getName() + "', "); 
      sqlString.append("phone='" 
        + employee.getPhone() + "', "); 
      sqlString.append("email='" 
        + employee.getEmail() + "', "); 
      sqlString.append("depid=" 
        + employee.getDepid()); 
      sqlString.append(" where username='" 
        + employee.getUsername() + "'"); 
 
      stmt.execute(sqlString.toString()); 
    } 
    finally { 
 
      if ( rs != null ) { 
 
        rs.close(); 
      } 
      if ( stmt != null ) { 
 
        stmt.close(); 
      } 
      if ( conn != null ) { 
 
        conn.close(); 
      } 
    } 
  } 
}