employeeaccess.java

Athena酒店小组_Athena酒店管理系统

/*
 * EmployeeAccess.java
 * Copyright (C) 2007 Athena project team
 */

package vincent;

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

import plugin.*;
import helper.*;

/**
 * 实体类 EmployeeInfo 的访问类
 * @author Vincent
 * @version 1.0
 */
public class EmployeeAccess
{
    private IDBResource dbRes;
    
    /**
     * 构造函数
     * @param dbRes 数据库资源
     */
    public EmployeeAccess(IDBResource dbRes)
    {
	this.dbRes = dbRes;
    }
    
    /**
     * 对EmployeeInfo表进行插入操作
     * @param employeeInfo 要插入的员工信息
     * @return 影响的行数，如果发生错误，则返回-1
     */
    public int insert(EmployeeInfo employeeInfo)
    {
	try
	{
	    Connection conn = dbRes.getConnection();
	    String sql = "INSERT INTO EmployeeInfo(EmployeeId, EmployeeName, Alias, Sex, DeptId, BirthDate, " +
		    "Job, WorkState, JoinTime, Study, Professional, School, LeaveSchoolTime, Politics, Email, " +
		    "NetCommunication, HomeAddress, CellPhone, IDCardNo, StartWorkTime, NativePlace, " +
		    "Nation, Health, Marriage, Remark) " +
		    " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	    PreparedStatement ps = conn.prepareStatement(sql);
	    
	    ps.setString(1, employeeInfo.getEmployeeId());
	    ps.setString(2, employeeInfo.getEmployeeName());
	    ps.setString(3, employeeInfo.getAlias());
	    ps.setString(4, employeeInfo.getSex());
	    ps.setString(5, employeeInfo.getDeptId());
	    ps.setObject(6, employeeInfo.getBirthDate());
	    ps.setString(7, employeeInfo.getJob());
	    ps.setString(8, employeeInfo.getWorkState());
	    ps.setObject(9, employeeInfo.getJoinTime());
	    ps.setString(10, employeeInfo.getStudy());
	    ps.setString(11, employeeInfo.getProfessional());
	    ps.setString(12, employeeInfo.getSchool());
	    ps.setObject(13, employeeInfo.getLeaveSchoolTime());
	    ps.setString(14, employeeInfo.getPolitics());
	    ps.setString(15, employeeInfo.getEmail());
	    ps.setString(16, employeeInfo.getNetCommunication());
	    ps.setString(17, employeeInfo.getHomeAddress());
	    ps.setString(18, employeeInfo.getCellPhone());
	    ps.setString(19, employeeInfo.getIDCardNo());
	    ps.setObject(20, employeeInfo.getStartWorkTime());
	    ps.setString(21, employeeInfo.getNativePlace());
	    ps.setString(22, employeeInfo.getNation());
	    ps.setString(23, employeeInfo.getHealth());
	    ps.setString(24, employeeInfo.getMarriage());
	    ps.setString(25, employeeInfo.getRemark());
	    
	    int rs = ps.executeUpdate();
	    return rs;
	}
	catch (SQLException ex)
	{
	    ex.printStackTrace();
	    return -1;
	}
    }
    
    /**
     * 对EmployeeInfo表进行更新操作
     * @param employeeInfo 要更新的员工信息
     * @return 影响的行数，如果发生错误，则返回-1
     */
    public int update(EmployeeInfo employeeInfo)
    {
	try
	{
	    Connection conn = dbRes.getConnection();
	    String sql = "UPDATE EmployeeInfo SET EmployeeName = ?, Alias = ?, Sex = ?, " +
		    "DeptId = ?, BirthDate = ?, Job = ?, WorkState = ?, JoinTime = ?, Study = ?, Professional = ?, " +
		    "School = ?, LeaveSchoolTime = ?, Politics = ?, Email = ?, NetCommunication = ?, " +
		    "HomeAddress = ?, CellPhone = ?, IDCardNo = ?, StartWorkTime = ?, NativePlace = ?, " +
		    "Nation = ?, Health = ?, Marriage = ?, Remark = ?) " +
		    "WHERE EmployeeId = ?";
	    PreparedStatement ps = conn.prepareStatement(sql);
	    
	    ps.setString(1, employeeInfo.getEmployeeName());
	    ps.setString(2, employeeInfo.getAlias());
	    ps.setString(3, employeeInfo.getSex());
	    ps.setString(4, employeeInfo.getDeptId());
	    ps.setObject(5, employeeInfo.getBirthDate());
	    ps.setString(6, employeeInfo.getJob());
	    ps.setString(7, employeeInfo.getWorkState());
	    ps.setObject(8, employeeInfo.getJoinTime());
	    ps.setString(9, employeeInfo.getStudy());
	    ps.setString(10, employeeInfo.getProfessional());
	    ps.setString(11, employeeInfo.getSchool());
	    ps.setObject(12, employeeInfo.getLeaveSchoolTime());
	    ps.setString(13, employeeInfo.getPolitics());
	    ps.setString(14, employeeInfo.getEmail());
	    ps.setString(15, employeeInfo.getNetCommunication());
	    ps.setString(16, employeeInfo.getHomeAddress());
	    ps.setString(17, employeeInfo.getCellPhone());
	    ps.setString(18, employeeInfo.getIDCardNo());
	    ps.setObject(19, employeeInfo.getStartWorkTime());
	    ps.setString(20, employeeInfo.getNativePlace());
	    ps.setString(21, employeeInfo.getNation());
	    ps.setString(22, employeeInfo.getHealth());
	    ps.setString(23, employeeInfo.getMarriage());
	    ps.setString(24, employeeInfo.getRemark());
	    ps.setString(25, employeeInfo.getEmployeeId());
	    
	    int rs = ps.executeUpdate();
	    return rs;
	}
	catch (SQLException ex)
	{
	    ex.printStackTrace();
	    return -1;
	}
    }
    
    /**
     * 对EmployeeInfo表进行删除操作
     * @param employeeInfo 要删除的员工信息
     * @return 影响的行数，如果发生错误，则返回-1
     */
    public int delete(EmployeeInfo employeeInfo)
    {
	try
	{
	    Connection conn = dbRes.getConnection();
	    String sql = "DELETE FROM EmployeeInfo WHERE EmployeeId = ?";
	    PreparedStatement ps = conn.prepareStatement(sql);
	    
	    ps.setString(1, employeeInfo.getEmployeeId());
	    
	    int rs = ps.executeUpdate();
	    return rs;
	}
	catch (SQLException ex)
	{
	    ex.printStackTrace();
	    return -1;
	}
    }
    
    /**
     * 根据Id查询EmployeeInfo表的信息
     * @param employeeId 员工Id
     * @return 查询到的员工信息
     */
    public EmployeeInfo getEmployeeById(String employeeId)
    {
	try
	{
	    Connection conn = dbRes.getConnection();
	    String sql = "SELECT * FROM EmployeeInfo WHERE EmployeeId = ?";
	    PreparedStatement ps = conn.prepareStatement(sql);
	    ps.setString(1, employeeId);
	    ResultSet rs = ps.executeQuery();
	    ArrayList<EmployeeInfo> employees = new ArrayList<EmployeeInfo>();
	    readData(employees, rs);
	    if (employees.size() != 1)
	    {
		return null;
	    }
	    return employees.get(0);
	}
	catch (SQLException ex)
	{
	    ex.printStackTrace();
	    return null;
	}
    }
    
    /**
     * 返回EmployeeInfo表的所有信息
     * @return 所有员工信息
     */
    public ArrayList<EmployeeInfo> getAllEmployees()
    {
	try
	{
	    Connection conn = dbRes.getConnection();
	    String sql = "SELECT * FROM EmployeeInfo";
	    PreparedStatement ps = conn.prepareStatement(sql);
	    ResultSet rs = ps.executeQuery();
	    ArrayList<EmployeeInfo> employees = new ArrayList<EmployeeInfo>();
	    readData(employees, rs);
	    return employees;
	}
	catch (SQLException ex)
	{
	    ex.printStackTrace();
	    return null;
	}
    }
    
    /**
     * 根据条件查询EmployeeInfo表的信息
     * @param names 条件的名称
     * @param values 条件的值
     * @return 查询到的员工信息
     */
    public ArrayList<EmployeeInfo> getEmployeesByCondition(ArrayList<String> names, ArrayList<Object> values)
    {
	try
	{
	    Connection conn = dbRes.getConnection();
	    String sql = "SELECT * FROM EmployeeInfo " + SQLHelper.createCondition(names);
	    PreparedStatement ps = conn.prepareStatement(sql);
	    for(int i = 0; i < values.size(); i++)
	    {
		ps.setObject(i+1, values.get(i));
	    }
	    ResultSet rs = ps.executeQuery();
	    ArrayList<EmployeeInfo> employees = new ArrayList<EmployeeInfo>();
	    readData(employees, rs);
	    return employees;
	}
	catch (SQLException ex)
	{
	    ex.printStackTrace();
	    return null;
	}
    }
    
    //读取数据
    private void readData(ArrayList<EmployeeInfo> employees, ResultSet rs) throws SQLException
    {
	while(rs.next())
	{
	    EmployeeInfo employee = new EmployeeInfo();
	    employee.setEmployeeId(rs.getString("EmployeeId"));
	    employee.setEmployeeName(rs.getString("EmployeeName"));
	    employee.setAlias(rs.getString("Alias"));
	    employee.setSex(rs.getString("Sex"));
	    employee.setDeptId(rs.getString("DeptId"));
	    employee.setBirthDate(rs.getDate("BirthDate"));
	    employee.setJob(rs.getString("Job"));
	    employee.setWorkState(rs.getString("WorkState"));
	    employee.setJoinTime(rs.getDate("JoinTime"));
	    employee.setStudy(rs.getString("Study"));
	    employee.setProfessional(rs.getString("Professional"));
	    employee.setSchool(rs.getString("School"));
	    employee.setLeaveSchoolTime(rs.getDate("LeaveSchoolTime"));
	    employee.setPolitics(rs.getString("Politics"));
	    employee.setEmail(rs.getString("Email"));
	    employee.setNetCommunication(rs.getString("NetCommunication"));
	    employee.setHomeAddress(rs.getString("HomeAddress"));
	    employee.setCellPhone(rs.getString("CellPhone"));
	    employee.setIDCardNo(rs.getString("IDCardNo"));
	    employee.setStartWorkTime(rs.getDate("StartWorkTime"));
	    employee.setNativePlace(rs.getString("NativePlace"));
	    employee.setNation(rs.getString("Nation"));
	    employee.setHealth(rs.getString("Health"));
	    employee.setMarriage(rs.getString("Marriage"));
	    employee.setRemark(rs.getString("Remark"));
	    employees.add(employee);
	}
    }
}