mc_zefirka

OfficeDAOImpl

Apr 18th, 2021
530
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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.  
  8. import com.itstep.mvn.app.model.jdbc.Office;
  9. import com.itstep.mvn.app.util.DBUtils;
  10.  
  11. public class OfficeDAOImpl implements OfficeDAO{
  12.  
  13.     @Override
  14.     public Office getOffice(String officeCode) {
  15.         Office off = null;
  16.         String sql = "SELECT * FROM offices WHERE officeCode = ?";
  17.         try (Connection conn = DBUtils.getConnection();
  18.                 PreparedStatement pstmt = conn.prepareStatement(sql);) {
  19.             pstmt.setString(1, officeCode);
  20.             ResultSet rs = pstmt.executeQuery();
  21.             while (rs.next()) {
  22.                 off = new Office();
  23.                 off.setOfficeCode(rs.getString("officeCode"));
  24.                 off.setCity(rs.getString("city"));
  25.                 off.setPhone(rs.getString("phone"));
  26.                 off.setAddressLine1(rs.getString("addressLine1"));
  27.                 off.setAddressLine2(rs.getString("addressLine2"));
  28.                 off.setState(rs.getString("state"));
  29.                 off.setCountry(rs.getString("country"));
  30.                 off.setPostalCode(rs.getString("postalCode"));
  31.                 off.setTerritory(rs.getString("territory"));
  32. //                  off.setEmployees(new EmployeeDAOImpl()
  33. //                          .getEmployeesByOfficeCode(officeCode));                
  34.  
  35.             }
  36.  
  37.         } catch (Exception e) {
  38.             e.printStackTrace();
  39.         }
  40.         return off;
  41.     }
  42.  
  43.     @Override
  44.     public boolean createOffice(Office office) {
  45.        
  46.         String sql= "INSERT INTO offices (officeCode, city, phone, addressLine1, addressLine2, state, country, postalCode, territory) "
  47.                 + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
  48.        
  49.         try(Connection conn = DBUtils.getConnection();
  50.             PreparedStatement pstmt = conn.prepareStatement(sql)) {
  51.             pstmt.setString(1, office.getOfficeCode());
  52.             pstmt.setString(2, office.getCity());
  53.             pstmt.setString(3, office.getPhone());
  54.             pstmt.setString(4, office.getAddressLine1());
  55.             pstmt.setString(5, office.getAddressLine2());
  56.             pstmt.setString(6, office.getState());
  57.             pstmt.setString(7, office.getCountry());
  58.             pstmt.setString(8, office.getPostalCode());
  59.             pstmt.setString(9, office.getTerritory());
  60.            
  61.             if (pstmt.executeUpdate() == 1) {
  62.                 System.out.println("Office with ID : "
  63.                         + office.getOfficeCode() + " has been inserted.");
  64.                 return true;
  65.             }
  66.            
  67.         } catch (SQLException e) {
  68.             e.printStackTrace();
  69.         }
  70.         return false;
  71.     }
  72.  
  73.     @Override
  74.     public boolean removeOffice(String officeCode) {
  75.         String sql= "DELETE FROM offices WHERE officeCode = ?";
  76.        
  77.         try(Connection conn = DBUtils.getConnection();
  78.             PreparedStatement pstmt = conn.prepareStatement(sql)) {
  79.             pstmt.setString(1, officeCode);
  80.            
  81.            
  82.             if (pstmt.executeUpdate() == 1) {
  83.                 System.out.println("Office with ID : "
  84.                         + officeCode + " has been deleted.");
  85.                 return true;
  86.             }
  87.            
  88.         } catch (SQLException e) {
  89.             e.printStackTrace();
  90.         }
  91.         return false;
  92.     }
  93.  
  94.     @Override
  95.     public boolean updateOffice(Office office) {
  96.         String sql = "UPDATE offices SET city = ?, phone = ?, addressLine1 = ?, addressLine2 = ?, state = ?, country = ?, postalCode = ?, territory = ? WHERE offices.officeCode = ?";
  97.         try(Connection conn = DBUtils.getConnection();
  98.                 PreparedStatement pstmt = conn.prepareStatement(sql)) {
  99.            
  100.                 pstmt.setString(1, office.getCity());
  101.                 pstmt.setString(2, office.getPhone());
  102.                 pstmt.setString(3, office.getAddressLine1());
  103.                 pstmt.setString(4, office.getAddressLine2());
  104.                 pstmt.setString(5, office.getState());
  105.                 pstmt.setString(6, office.getCountry());
  106.                 pstmt.setString(7, office.getPostalCode());
  107.                 pstmt.setString(8, office.getTerritory());
  108.                
  109.                 pstmt.setString(9, office.getOfficeCode());
  110.                
  111.                 if (pstmt.executeUpdate() == 1) {
  112.                     System.out.println("Office with ID : "
  113.                             + office.getOfficeCode() + " has been updated.");
  114.                     return true;
  115.                 }
  116.                
  117.             } catch (SQLException e) {
  118.                 e.printStackTrace();
  119.             }
  120.             return false;
  121.     }
  122.    
  123.  
  124. }
RAW Paste Data

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×