mc_zefirka

EmployeeDAOImpl

Apr 18th, 2021 (edited)
92
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Java 5.32 KB | None | 0 0
  1. package com.itstep.mvn.app.jdbc.dao;
  2.  
  3. import java.sql.Connection;
  4. import java.sql.PreparedStatement;
  5. import java.sql.ResultSet;
  6. import java.sql.SQLException;
  7. import java.util.ArrayList;
  8. import java.util.List;
  9.  
  10. import com.itstep.mvn.app.model.jdbc.Employee;
  11. import com.itstep.mvn.app.model.jdbc.Office;
  12. import com.itstep.mvn.app.util.DBUtils;
  13.  
  14. public class EmployeeDAOImpl implements EmployeeDAO {
  15.  
  16.     @Override
  17.     public Employee getEmployee(int employeeNumber) {
  18.         Employee empl = null;
  19.         String sql = "SELECT * FROM employees WHERE employeeNumber = ?";
  20.         try (Connection conn = DBUtils.getConnection();
  21.                 PreparedStatement pstmt = conn.prepareStatement(sql);) {
  22.             pstmt.setInt(1, employeeNumber);
  23.             ResultSet rs = pstmt.executeQuery();
  24.             while (rs.next()) {
  25.                 empl = new Employee();
  26.                 empl.setEmployeeNumber(employeeNumber);
  27.                 empl.setEmail(rs.getString("email"));
  28.                 empl.setExtension(rs.getString("extension"));
  29.                 empl.setFirstName(rs.getString("firstName"));
  30.                 empl.setJobTitle(rs.getString("jobTitle"));
  31.                 empl.setLastName(rs.getString("lastName"));
  32.  
  33.                 // reports to.
  34.                 int reportsTo = rs.getInt("reportsTo");
  35.                 if (reportsTo != 0) {
  36.                     empl.setReportsTo(getEmployee(reportsTo));
  37.                 }
  38.  
  39.                 Office office = new OfficeDAOImpl().getOffice(rs.getString("officeCode"));
  40.                 empl.setOffice(office);
  41.             }
  42.  
  43.         } catch (Exception e) {
  44.             e.printStackTrace();
  45.         }
  46.         return empl;
  47.     }
  48.  
  49.     @Override
  50.     public boolean createEmployee(Employee employee) {
  51.  
  52.         String sql = "INSERT INTO employees (employeeNumber, lastName, firstName, extension, email, officeCode, reportsTo, jobTitle) "
  53.                 + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
  54.  
  55.         try (Connection conn = DBUtils.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
  56.             pstmt.setInt(1, employee.getEmployeeNumber());
  57.             pstmt.setString(2, employee.getLastName());
  58.             pstmt.setString(3, employee.getFirstName());
  59.             pstmt.setString(4, employee.getExtension());
  60.             pstmt.setString(5, employee.getEmail());
  61.             pstmt.setString(6, employee.getOffice().getOfficeCode());
  62.             pstmt.setInt(7, employee.getReportsTo().getEmployeeNumber());
  63.             pstmt.setString(8, employee.getJobTitle());
  64.  
  65.             if (pstmt.executeUpdate() == 1) {
  66.                 System.out.println("Employee with EmployeeNumber : " + employee.getEmployeeNumber() + " has been inserted.");
  67.                 return true;
  68.             }
  69.  
  70.         } catch (SQLException e) {
  71.             e.printStackTrace();
  72.         }
  73.  
  74.         return false;
  75.     }
  76.  
  77.     @Override
  78.     public boolean removeEmployee(int employeeNumber) {
  79.        
  80.         String sql= "DELETE FROM employees WHERE employeeNumber = ?";
  81.        
  82.         try(Connection conn = DBUtils.getConnection();
  83.             PreparedStatement pstmt = conn.prepareStatement(sql)) {
  84.             pstmt.setInt(1, employeeNumber);
  85.            
  86.            
  87.             if (pstmt.executeUpdate() == 1) {
  88.                 System.out.println("Employee with EmployeeNumber : "
  89.                         + employeeNumber + " has been deleted.");
  90.                 return true;
  91.             }
  92.            
  93.         } catch (SQLException e) {
  94.             e.printStackTrace();
  95.         }
  96.         return false;
  97.  
  98.     }
  99.  
  100.     @Override
  101.     public boolean updateEmployee(Employee employee) {
  102.  
  103.         String sql = "UPDATE employees SET "
  104.                 + "employeeNumber = ?, "
  105.                 + "lastName = ?, "
  106.                 + "firstName = ?, "
  107.                 + "extension = ?, "
  108.                 + "email = ?, "
  109.                 + "officeCode = ?, "
  110.                 + "reportsTo = ?, "
  111.                 + "jobTitle = ? WHERE employees.employeeNumber = ?";
  112.         try (Connection conn = DBUtils.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
  113.  
  114.             pstmt.setInt(1, employee.getEmployeeNumber());
  115.             pstmt.setString(2, employee.getLastName());
  116.             pstmt.setString(3, employee.getFirstName());
  117.             pstmt.setString(4, employee.getExtension());
  118.             pstmt.setString(5, employee.getEmail());
  119.             pstmt.setString(6, employee.getOffice().getOfficeCode());
  120.             pstmt.setInt(7, employee.getReportsTo().getEmployeeNumber());
  121.             pstmt.setString(8, employee.getJobTitle());
  122.  
  123.             if (pstmt.executeUpdate() == 1) {
  124.                 System.out.println("Employee with EmployeeNumber : " + employee.getEmployeeNumber() + " has been updated.");
  125.                 return true;
  126.             }
  127.  
  128.         } catch (SQLException e) {
  129.             e.printStackTrace();
  130.         }
  131.  
  132.         return false;
  133.     }
  134.  
  135.     @Override
  136.     public List<Employee> getEmployeesByOfficeCode(String officeCode) {
  137.         return null; // !
  138.  
  139. //      Employee empl = null;
  140. //      String sql = "SELECT * FROM employees WHERE officeCode = ?";
  141. //      try (Connection conn = DBUtils.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql);) {
  142. //          pstmt.setInt(1, officeCode);
  143. //          ResultSet rs = pstmt.executeQuery();
  144. //          while (rs.next()) {
  145. //              empl = new Employee();
  146. //              empl.setEmployeeNumber(employeeNumber);
  147. //              empl.setEmail(rs.getString("email"));
  148. //              empl.setExtension(rs.getString("extension"));
  149. //              empl.setFirstName(rs.getString("firstName"));
  150. //              empl.setJobTitle(rs.getString("jobTitle"));
  151. //              empl.setLastName(rs.getString("lastName"));
  152. //
  153. //              // reports to.
  154. //              int reportsTo = rs.getInt("reportsTo");
  155. //              if (reportsTo != 0) {
  156. //                  empl.setReportsTo(getEmployee(reportsTo));
  157. //              }
  158. //
  159. //              Office office = new OfficeDAOImpl().getOffice(rs.getString("officeCode"));
  160. //              empl.setOffice(office);
  161. //          }
  162. //
  163. //      } catch (Exception e) {
  164. //          e.printStackTrace();
  165. //      }
  166. //      return empl;
  167.  
  168.     }
  169.  
  170. }
  171.  
Add Comment
Please, Sign In to add comment