Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- package com.itstep.mvn.app.jdbc.dao;
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.util.ArrayList;
- import java.util.List;
- import com.itstep.mvn.app.model.jdbc.Employee;
- import com.itstep.mvn.app.model.jdbc.Office;
- import com.itstep.mvn.app.util.DBUtils;
- public class EmployeeDAOImpl implements EmployeeDAO {
- @Override
- public Employee getEmployee(int employeeNumber) {
- Employee empl = null;
- String sql = "SELECT * FROM employees WHERE employeeNumber = ?";
- try (Connection conn = DBUtils.getConnection();
- PreparedStatement pstmt = conn.prepareStatement(sql);) {
- pstmt.setInt(1, employeeNumber);
- ResultSet rs = pstmt.executeQuery();
- while (rs.next()) {
- empl = new Employee();
- empl.setEmployeeNumber(employeeNumber);
- empl.setEmail(rs.getString("email"));
- empl.setExtension(rs.getString("extension"));
- empl.setFirstName(rs.getString("firstName"));
- empl.setJobTitle(rs.getString("jobTitle"));
- empl.setLastName(rs.getString("lastName"));
- // reports to.
- int reportsTo = rs.getInt("reportsTo");
- if (reportsTo != 0) {
- empl.setReportsTo(getEmployee(reportsTo));
- }
- Office office = new OfficeDAOImpl().getOffice(rs.getString("officeCode"));
- empl.setOffice(office);
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- return empl;
- }
- @Override
- public boolean createEmployee(Employee employee) {
- String sql = "INSERT INTO employees (employeeNumber, lastName, firstName, extension, email, officeCode, reportsTo, jobTitle) "
- + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
- try (Connection conn = DBUtils.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
- pstmt.setInt(1, employee.getEmployeeNumber());
- pstmt.setString(2, employee.getLastName());
- pstmt.setString(3, employee.getFirstName());
- pstmt.setString(4, employee.getExtension());
- pstmt.setString(5, employee.getEmail());
- pstmt.setString(6, employee.getOffice().getOfficeCode());
- pstmt.setInt(7, employee.getReportsTo().getEmployeeNumber());
- pstmt.setString(8, employee.getJobTitle());
- if (pstmt.executeUpdate() == 1) {
- System.out.println("Employee with EmployeeNumber : " + employee.getEmployeeNumber() + " has been inserted.");
- return true;
- }
- } catch (SQLException e) {
- e.printStackTrace();
- }
- return false;
- }
- @Override
- public boolean removeEmployee(int employeeNumber) {
- String sql= "DELETE FROM employees WHERE employeeNumber = ?";
- try(Connection conn = DBUtils.getConnection();
- PreparedStatement pstmt = conn.prepareStatement(sql)) {
- pstmt.setInt(1, employeeNumber);
- if (pstmt.executeUpdate() == 1) {
- System.out.println("Employee with EmployeeNumber : "
- + employeeNumber + " has been deleted.");
- return true;
- }
- } catch (SQLException e) {
- e.printStackTrace();
- }
- return false;
- }
- @Override
- public boolean updateEmployee(Employee employee) {
- String sql = "UPDATE employees SET "
- + "employeeNumber = ?, "
- + "lastName = ?, "
- + "firstName = ?, "
- + "extension = ?, "
- + "email = ?, "
- + "officeCode = ?, "
- + "reportsTo = ?, "
- + "jobTitle = ? WHERE employees.employeeNumber = ?";
- try (Connection conn = DBUtils.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
- pstmt.setInt(1, employee.getEmployeeNumber());
- pstmt.setString(2, employee.getLastName());
- pstmt.setString(3, employee.getFirstName());
- pstmt.setString(4, employee.getExtension());
- pstmt.setString(5, employee.getEmail());
- pstmt.setString(6, employee.getOffice().getOfficeCode());
- pstmt.setInt(7, employee.getReportsTo().getEmployeeNumber());
- pstmt.setString(8, employee.getJobTitle());
- if (pstmt.executeUpdate() == 1) {
- System.out.println("Employee with EmployeeNumber : " + employee.getEmployeeNumber() + " has been updated.");
- return true;
- }
- } catch (SQLException e) {
- e.printStackTrace();
- }
- return false;
- }
- @Override
- public List<Employee> getEmployeesByOfficeCode(String officeCode) {
- return null; // !
- // Employee empl = null;
- // String sql = "SELECT * FROM employees WHERE officeCode = ?";
- // try (Connection conn = DBUtils.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql);) {
- // pstmt.setInt(1, officeCode);
- // ResultSet rs = pstmt.executeQuery();
- // while (rs.next()) {
- // empl = new Employee();
- // empl.setEmployeeNumber(employeeNumber);
- // empl.setEmail(rs.getString("email"));
- // empl.setExtension(rs.getString("extension"));
- // empl.setFirstName(rs.getString("firstName"));
- // empl.setJobTitle(rs.getString("jobTitle"));
- // empl.setLastName(rs.getString("lastName"));
- //
- // // reports to.
- // int reportsTo = rs.getInt("reportsTo");
- // if (reportsTo != 0) {
- // empl.setReportsTo(getEmployee(reportsTo));
- // }
- //
- // Office office = new OfficeDAOImpl().getOffice(rs.getString("officeCode"));
- // empl.setOffice(office);
- // }
- //
- // } catch (Exception e) {
- // e.printStackTrace();
- // }
- // return empl;
- }
- }
Add Comment
Please, Sign In to add comment