聊天室源码聊天室源码聊天室源码聊天室源码聊天室源码

package flex.samples.crm.employee; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.Statement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.util.ArrayList; import java.util.List; import flex.samples.ConnectionHelper; import flex.samples.DAOException; import flex.samples.crm.ConcurrencyException; import flex.samples.crm.company.Company; public class EmployeeDAO { public List getEmployees() throws DAOException { List list = new ArrayList(); Connection c = null; try { c = ConnectionHelper.getConnection(); Statement s = c.createStatement(); ResultSet rs = s.executeQuery("SELECT * FROM employee ORDER BY last_name"); Employee employee; while (rs.next()) { employee = new Employee(); employee.setEmployeeId(rs.getInt("employee_id")); employee.setFirstName(rs.getString("first_name")); employee.setLastName(rs.getString("last_name")); employee.setTitle(rs.getString("title")); employee.setEmail(rs.getString("email")); employee.setPhone(rs.getString("phone")); Company company = new Company(); company.setCompanyId(rs.getInt("company_id")); employee.setCompany(company); list.add(employee); } } catch (SQLException e) { e.printStackTrace(); throw new DAOException(e); } finally { ConnectionHelper.close(c); } return list; } public List findEmployeesByCompany(Integer companyId) throws DAOException { List list = new ArrayList(); Connection c = null; try { Company company = new Company(); company.setCompanyId(companyId.intValue()); c = ConnectionHelper.getConnection(); PreparedStatement ps = c.prepareStatement("SELECT * FROM employee WHERE company_id = ? ORDER BY last_name"); ps.setInt(1, companyId.intValue()); ResultSet rs = ps.executeQuery(); while (rs.next()) { Employee employee = new Employee(); employee.setEmployeeId(rs.getInt("employee_id")); employee.setFirstName(rs.getString("first_name")); employee.setLastName(rs.getString("last_name")); employee.setTitle(rs.getString("title")); employee.setEmail(rs.getString("email")); employee.setPhone(rs.getString("phone")); employee.setCompany(company); list.add(employee); } } catch (SQLException e) { e.printStackTrace(); throw new DAOException(e); } finally { ConnectionHelper.close(c); } return list; } public List findEmployeesByName(String name) throws DAOException { List list = new ArrayList(); Connection c = null; try { c = ConnectionHelper.getConnection(); PreparedStatement ps = c.prepareStatement("SELECT * FROM employee WHERE first_name LIKE ? OR last_name LIKE ? ORDER BY last_name"); ps.setString(1, "%" + name + "%"); ps.setString(2, "%" + name + "%"); ResultSet rs = ps.executeQuery(); Employee employee; while (rs.next()) { employee = new Employee(); employee.setEmployeeId(rs.getInt("employee_id")); employee.setFirstName(rs.getString("first_name")); employee.setLastName(rs.getString("last_name")); employee.setTitle(rs.getString("title")); employee.setEmail(rs.getString("email")); employee.setPhone(rs.getString("phone")); Company company = new Company(); company.setCompanyId(rs.getInt("company_id")); list.add(employee); } } catch (SQLException e) { e.printStackTrace(); throw new DAOException(e); } finally { ConnectionHelper.close(c); } return list; } public Employee getEmployee(int employeeId) throws DAOException { Employee employee = null; Connection c = null; try { c = ConnectionHelper.getConnection(); PreparedStatement ps = c.prepareStatement("SELECT * FROM employee WHERE employee_id= ?"); ps.setInt(1, employeeId); ResultSet rs = ps.executeQuery(); if (rs.next()) { employee = new Employee(); employee.setEmployeeId(rs.getInt("employee_id")); employee.setFirstName(rs.getString("first_name")); employee.setLastName(rs.getString("last_name")); employee.setTitle(rs.getString("title")); employee.setEmail(rs.getString("email")); employee.setPhone(rs.getString("phone")); } } catch (SQLException e) { e.printStackTrace(); throw new DAOException(e.getMessage()); } finally { ConnectionHelper.close(c); } return employee; } public Employee createEmployee(Employee employee) throws DAOException { Connection c = null; PreparedStatement ps = null; try { c = ConnectionHelper.getConnection(); ps = c.prepareStatement("INSERT INTO employee (first_name, last_name, title, email, phone, company_id) VALUES (?, ?, ?, ?, ?, ?)"); ps.setString(1, employee.getFirstName()); ps.setString(2, employee.getLastName()); ps.setString(3, employee.getTitle()); ps.setString(4, employee.getEmail()); ps.setString(5, employee.getPhone()); if (employee.getCompany() != null) ps.setInt(6, employee.getCompany().getCompanyId()); else ps.setNull(6, Types.INTEGER); ps.execute(); ps.close(); Statement s = c.createStatement(); // HSQLDB Syntax to get the identity (employee_id) of inserted row ResultSet rs = s.executeQuery("CALL IDENTITY()"); rs.next(); // Update the id in the returned object. This is important as this // value must get returned to the client. employee.setEmployeeId(rs.getInt(1)); } catch (SQLException e) { e.printStackTrace(); throw new DAOException(e); } finally { ConnectionHelper.close(c); } return employee; } public void updateEmployee(Employee newVersion, Employee previousVersion, List changes) throws DAOException, ConcurrencyException { Connection c = null; try { c = ConnectionHelper.getConnection(); PreparedStatement ps = c.prepareStatement("UPDATE employee SET first_name=?, last_name=?, title=?, email=?, phone=?, company_id=? WHERE employee_id=? AND first_name=? AND last_name=? AND title=? AND email=? AND phone=? AND company_id=?"); ps.setString(1, newVersion.getFirstName()); ps.setString(2, newVersion.getLastName()); ps.setString(3, newVersion.getTitle()); ps.setString(4, newVersion.getEmail()); ps.setString(5, newVersion.getPhone()); if (newVersion.getCompany() != null) ps.setInt(6, newVersion.getCompany().getCompanyId()); else ps.setNull(6,Types.INTEGER); ps.setInt(7, newVersion.getEmployeeId()); ps.setString(8, previousVersion.getFirstName()); ps.setString(9, previousVersion.getLastName()); ps.setString(10, previousVersion.getTitle()); ps.setString(11, previousVersion.getEmail()); ps.setString(12, previousVersion.getPhone()); if (previousVersion.getCompany() != null) ps.setInt(13, previousVersion.getCompany().getCompanyId()); else ps.setNull(13, Types.INTEGER); if (ps.executeUpdate() == 0) { throw new ConcurrencyException("Item not found"); } } catch (SQLException e) { e.printStackTrace(); throw new DAOException(e.getMessage()); } finally { ConnectionHelper.close(c); } }