View Javadoc

    1   package org.example.customerdao;
    2
    3   /*
    4    * This is free and unencumbered software released into the public domain.
    5    * Anyone is free to copy, modify, publish, use, compile, sell, or distribute this software,
    6    * either in source code form or as a compiled binary, for any purpose, commercial or
    7    * non-commercial, and by any means.
    8    *
    9    * In jurisdictions that recognize copyright laws, the author or authors of this
    10   * software dedicate any and all copyright interest in the software to the public domain.
    11   * We make this dedication for the benefit of the public at large and to the detriment of
    12   * our heirs and successors. We intend this dedication to be an overt act of relinquishment in
    13   * perpetuity of all present and future rights to this software under copyright law.
    14   *
    15   * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED,
    16   * INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
    17   * PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES
    18   * OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
    19   * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
    20   *
    21   * For more information, please refer to: https://unlicense.org/
    22  */
    23
    24  import java.sql.Connection;
    25  import java.sql.PreparedStatement;
    26  import java.sql.ResultSet;
    27  import java.sql.SQLException;
    28  import java.util.ArrayList;
    29  import java.util.List;
    30
    31  import javax.sql.DataSource;
    32
    33  import org.apache.commons.dbutils.DbUtils;
    34  import org.apache.logging.log4j.LogManager;
    35  import org.apache.logging.log4j.Logger;
    36  import org.example.customer.Customer;
    37  import org.example.customer.utility.CustomerEntity;
    38  import org.example.customer.utility.Location;
    39  import org.example.customer.utility.Phone;
    40  import org.example.customerdao.utility.ErrorFormatter;
    41  import org.example.customerdao.utility.NonDeleteableRecordException;
    42  import org.example.websecurity.UserCredentials;
    43
    44  /**
    45   * This is the CustomerDAO Implementation for the Customer DAO component of the Customer Web Application.
    46   * This will be the primary Customer database exposure for the Customer Web Layer.
    47   *
    48   * @author Jonathan Earl
    49   * @since 1.0
    50   *
    51   */
    52  public final class CustomerDAOImpl
    53      implements CustomerDAO
    54  {
    55      private static final Logger LOG = LogManager.getLogger();
    56
    57      private DataSource myReadOnlyDS = null;
    58      private DataSource myReadWriteDS = null;
    59
    60      private static final String FIND_ALL_SQL = "Select * from Customer";
    61      private static final String FIND_BY_ID_SQL = "Select * from Customer where ID = ?";
    62      private static final String FIND_BY_NAME_SQL = "Select * from Customer where FIRST_NAME = ? AND LAST_NAME = ?";
    63      private static final String ADD_SQL = "Insert into Customer(FIRST_NAME,LAST_NAME,CITY,COUNTRY,PHONE)"
    64              + " VALUES(?,?,?,?,?)";
    65      private static final String UPDATE_SQL = "Update Customer set FIRST_NAME = ?, LAST_NAME = ?, "
    66              + "CITY = ?, COUNTRY = ?, PHONE = ?"
    67              + " WHERE ID = ?";
    68      private static final String IS_DELETEABLE_CUSTOMER_SQL = "Select COUNT(*) from Orders where CUSTOMER_ID = ?";
    69      private static final String DELETE_SQL = "Delete from Customer where id = ?";
    70
    71      /**
    72       * {@inheritDoc}
    73       */
    74      @Override
    75      public void setReadOnlyDS(final DataSource readOnlyDS)
    76      {
    77          LOG.debug("Setting the ReadOnly DataSource");
    78          this.myReadOnlyDS = readOnlyDS;
    79      }
    80
    81      /**
    82       * {@inheritDoc}
    83       */
    84      @Override
    85      public void setReadWriteDS(final DataSource readWriteDS)
    86      {
    87          LOG.debug("Setting the ReadWrite DataSource");
    88          this.myReadWriteDS = readWriteDS;
    89      }
    90
    91      /**
    92       * {@inheritDoc}
    93       */
    94      @Override
    95      public List<Customer> findAllCustomers(final UserCredentials credentials)
    96      {
    97          LOG.debug("findAllCustomers");
    98
    99          if (credentials == null || !credentials.hasRole("worker"))
    100         {
    101             LOG.error("findAllCustomers - Permission refused");
    102             throw new IllegalArgumentException("Permission refused for this operation");
    103         }
    104
    105         PreparedStatement findAll = null;
    106         Connection conn = null;
    107         ResultSet results = null;
    108         List<Customer> data = new ArrayList<Customer>();
    109         try
    110         {
    111             conn = myReadOnlyDS.getConnection();
    112             findAll = conn.prepareStatement(FIND_ALL_SQL);
    113
    114             results = findAll.executeQuery();
    115
    116             while (results.next())
    117             {
    118                 data.add(buildCustomer(results));
    119             }
    120         }
    121         catch (SQLException sqle)
    122         {
    123             LOG.error("Error finding all Customers");
    124             LOG.error(ErrorFormatter.extractError(sqle));
    125             throw new IllegalArgumentException(sqle);
    126         }
    127         finally
    128         {
    129             DbUtils.closeQuietly(conn, findAll, results);
    130         }
    131         return data;
    132     }
    133
    134     /**
    135      * {@inheritDoc}
    136      */
    137     @Override
    138     public Customer findCustomerById(final UserCredentials credentials, final int id)
    139     {
    140         LOG.debug("findCustomerById");
    141
    142         if (credentials == null || !credentials.hasRole("worker"))
    143         {
    144             LOG.error("findCustomerById - Permission refused");
    145             throw new IllegalArgumentException("Permission refused for this operation");
    146         }
    147
    148         PreparedStatement findById = null;
    149         Connection conn = null;
    150         ResultSet results = null;
    151         Customer customer = null;
    152         try
    153         {
    154             conn = myReadOnlyDS.getConnection();
    155             findById = conn.prepareStatement(FIND_BY_ID_SQL);
    156
    157             findById.setInt(1, id);
    158             results = findById.executeQuery();
    159
    160             if (results.next())
    161             {
    162                 customer = buildCustomer(results);
    163             }
    164         }
    165         catch (SQLException sqle)
    166         {
    167             LOG.error("Error finding Customer by id");
    168             LOG.error(ErrorFormatter.extractError(sqle));
    169             throw new IllegalArgumentException(sqle);
    170         }
    171         finally
    172         {
    173             DbUtils.closeQuietly(conn, findById, results);
    174         }
    175         return customer;
    176     }
    177
    178     /**
    179      * {@inheritDoc}
    180      */
    181     @Override
    182     public List<Customer> findCustomersByName(final UserCredentials credentials,
    183             final String firstName, final String lastName)
    184     {
    185         LOG.debug("findCustomersByName");
    186
    187         if (credentials == null || !credentials.hasRole("worker"))
    188         {
    189             LOG.error("findCustomersByName - Permission refused");
    190             throw new IllegalArgumentException("Permission refused for this operation");
    191         }
    192
    193         if (firstName == null || lastName == null)
    194         {
    195             LOG.error("findCustomersByName - names are required");
    196             throw new IllegalArgumentException("A first name and a last name are required");
    197         }
    198
    199         PreparedStatement findByName = null;
    200         Connection conn = null;
    201         ResultSet results = null;
    202         List<Customer> data = new ArrayList<Customer>();
    203         try
    204         {
    205             conn = myReadOnlyDS.getConnection();
    206             findByName = conn.prepareStatement(FIND_BY_NAME_SQL);
    207
    208             findByName.setString(1, firstName);
    209             findByName.setString(2, lastName);
    210
    211             results = findByName.executeQuery();
    212
    213             while (results.next())
    214             {
    215                 data.add(buildCustomer(results));
    216             }
    217         }
    218         catch (SQLException sqle)
    219         {
    220             LOG.error("Error finding Customer by name");
    221             LOG.error(ErrorFormatter.extractError(sqle));
    222             throw new IllegalArgumentException(sqle);
    223         }
    224         finally
    225         {
    226             DbUtils.closeQuietly(conn, findByName, results);
    227         }
    228         return data;
    229     }
    230
    231     /**
    232      * {@inheritDoc}
    233      */
    234     @Override
    235     public int addCustomer(final UserCredentials credentials, final Customer customer)
    236     {
    237         LOG.debug("addCustomer");
    238
    239         if (credentials == null || !credentials.hasRole("manager"))
    240         {
    241             LOG.error("addCustomer - Permission refused");
    242             throw new IllegalArgumentException("Permission refused for this operation");
    243         }
    244
    245         if (customer == null)
    246         {
    247             LOG.error("addCustomer - null Customer was provided");
    248             throw new IllegalArgumentException("A Customer must be provided");
    249         }
    250
    251         PreparedStatement addCustomer = null;
    252         Connection conn = null;
    253         ResultSet results = null;
    254         int generatedId = 0;
    255         try
    256         {
    257             conn = myReadWriteDS.getConnection();
    258             addCustomer = conn.prepareStatement(ADD_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
    259
    260             String firstName = customer.getFirstName();
    261             String lastName = customer.getLastName();
    262             Location location = customer.getLocation();
    263             String city = location.getCity();
    264             String country = location.getCountry();
    265             Phone phone = customer.getPhone();
    266             String number = phone.getNumber();
    267
    268             addCustomer.setString(1, firstName);
    269             addCustomer.setString(2, lastName);
    270             addCustomer.setString(3, city);
    271             addCustomer.setString(4, country);
    272             addCustomer.setString(5, number);
    273
    274             addCustomer.executeUpdate();
    275             results = addCustomer.getGeneratedKeys();
    276             if (results.next())
    277             {
    278                 generatedId = results.getInt(1);
    279             }
    280         }
    281         catch (SQLException sqle)
    282         {
    283             LOG.error("Error adding Customer");
    284             LOG.error(ErrorFormatter.extractError(sqle));
    285             throw new IllegalArgumentException(sqle);
    286         }
    287         finally
    288         {
    289             DbUtils.closeQuietly(conn, addCustomer, results);
    290         }
    291         return generatedId;
    292     }
    293
    294     /**
    295      * {@inheritDoc}
    296      */
    297     @Override
    298     public void updateCustomer(final UserCredentials credentials, final Customer customer)
    299     {
    300         LOG.debug("updateCustomer");
    301
    302         if (credentials == null || !credentials.hasRole("manager"))
    303         {
    304             LOG.error("updateCustomer - Permission refused");
    305             throw new IllegalArgumentException("Permission refused for this operation");
    306         }
    307
    308         if (customer == null)
    309         {
    310             LOG.error("updateCustomer - null Customer was provided");
    311             throw new IllegalArgumentException("A Customer must be provided");
    312         }
    313
    314         PreparedStatement updateCustomer = null;
    315         Connection conn = null;
    316         try
    317         {
    318             conn = myReadWriteDS.getConnection();
    319             updateCustomer = conn.prepareStatement(UPDATE_SQL);
    320
    321             int id = customer.getId();
    322             String firstName = customer.getFirstName();
    323             String lastName = customer.getLastName();
    324             Location location = customer.getLocation();
    325             String city = location.getCity();
    326             String country = location.getCountry();
    327             String number = null;
    328             Phone phone = customer.getPhone();
    329             if (phone != null)
    330             {
    331                 number = phone.getNumber();
    332             }
    333
    334             updateCustomer.setString(1, firstName);
    335             updateCustomer.setString(2, lastName);
    336             updateCustomer.setString(3, city);
    337             updateCustomer.setString(4, country);
    338             updateCustomer.setString(5, number);
    339             updateCustomer.setInt(6, id);
    340
    341             updateCustomer.executeUpdate();
    342         }
    343         catch (SQLException sqle)
    344         {
    345             LOG.error("Error updating Customer");
    346             LOG.error(ErrorFormatter.extractError(sqle));
    347             throw new IllegalArgumentException(sqle);
    348         }
    349         finally
    350         {
    351             DbUtils.closeQuietly(updateCustomer);
    352             DbUtils.closeQuietly(conn);
    353         }
    354     }
    355
    356     /**
    357      * {@inheritDoc}
    358      * <p>
    359      * Customers with Orders are not deleteable.
    360      */
    361     @Override
    362     public boolean isDeleteable(final UserCredentials credentials, final int id)
    363     {
    364         LOG.debug("isDeleteable");
    365
    366         if (credentials == null || !credentials.hasRole("worker"))
    367         {
    368             LOG.error("isDeleteable - Permission refused");
    369             throw new IllegalArgumentException("Permission refused for this operation");
    370         }
    371
    372         PreparedStatement isDeleteable = null;
    373         Connection conn = null;
    374         ResultSet results = null;
    375         boolean isDeleteableResult = false;
    376         try
    377         {
    378             conn = myReadOnlyDS.getConnection();
    379             isDeleteable = conn.prepareStatement(IS_DELETEABLE_CUSTOMER_SQL);
    380
    381             isDeleteable.setInt(1, id);
    382             results = isDeleteable.executeQuery();
    383
    384             if (results.next())
    385             {
    386                 int count = results.getInt(1);
    387                 if (count == 0)
    388                 {
    389                     isDeleteableResult = true;
    390                 }
    391             }
    392         }
    393         catch (SQLException sqle)
    394         {
    395             LOG.error("Error finding Customer by Order");
    396             LOG.error(ErrorFormatter.extractError(sqle));
    397             throw new IllegalArgumentException(sqle);
    398         }
    399         finally
    400         {
    401             DbUtils.closeQuietly(conn, isDeleteable, results);
    402         }
    403         return isDeleteableResult;
    404     }
    405
    406     /**
    407      * {@inheritDoc}
    408      * <p>
    409      * Customers with Orders are not deleteable.
    410      */
    411     @Override
    412     public boolean isDeleteable(final UserCredentials credentials, final CustomerEntity entity)
    413     {
    414         LOG.debug("isDeleteable");
    415
    416         if (credentials == null || entity == null)
    417         {
    418             LOG.error("isDeleteable - null Customer was provided");
    419             throw new IllegalArgumentException("A Customer must be provided");
    420         }
    421         if (!(entity instanceof Customer))
    422         {
    423             LOG.error("isDeleteable - invalid CustomerEntity was provided");
    424             throw new IllegalArgumentException("A Customer must be provided");
    425         }
    426        return isDeleteable(credentials, entity.getId());
    427     }
    428
    429     /**
    430      * {@inheritDoc}
    431      * <p>
    432      * Customers with Orders are not deleteable.
    433      */
    434     @Override
    435     public void deleteEntity(final UserCredentials credentials, final CustomerEntity entity)
    436     {
    437         LOG.debug("deleteCustomer");
    438
    439         if (entity == null)
    440         {
    441             LOG.error("deleteCustomer - null Customer was provided");
    442             throw new IllegalArgumentException("A Customer must be provided");
    443         }
    444         if (!(entity instanceof Customer))
    445         {
    446             LOG.error("deleteCustomer - invalid CustomerEntity was provided");
    447             throw new IllegalArgumentException("A Customer must be provided");
    448         }
    449         deleteEntity(credentials, entity.getId());
    450     }
    451
    452     /**
    453      * {@inheritDoc}
    454      * <p>
    455      * Customers with Orders are not deleteable.
    456      */
    457     @Override
    458     public void deleteEntity(final UserCredentials credentials, final int id)
    459     {
    460         LOG.debug("deleteCustomer");
    461
    462         if (credentials == null || !credentials.hasRole("manager"))
    463         {
    464             LOG.error("deleteCustomer - Permission refused");
    465             throw new IllegalArgumentException("Permission refused for this operation");
    466         }
    467
    468         if (!isDeleteable(credentials, id))
    469         {
    470             LOG.error("Customer not deleteable");
    471             throw new NonDeleteableRecordException("This Customer is not deletable");
    472         }
    473
    474         PreparedStatement deleteCustomer = null;
    475         Connection conn = null;
    476         try
    477         {
    478             conn = myReadWriteDS.getConnection();
    479             deleteCustomer = conn.prepareStatement(DELETE_SQL);
    480
    481             deleteCustomer.setInt(1, id);
    482
    483             deleteCustomer.executeUpdate();
    484         }
    485         catch (SQLException sqle)
    486         {
    487             LOG.error("Error updating Customer");
    488             LOG.error(ErrorFormatter.extractError(sqle));
    489             throw new IllegalArgumentException(sqle);
    490         }
    491         finally
    492         {
    493             DbUtils.closeQuietly(deleteCustomer);
    494             DbUtils.closeQuietly(conn);
    495         }
    496
    497     }
    498
    499     private Customer buildCustomer(final ResultSet results) throws SQLException
    500     {
    501         Customer current = new Customer();
    502
    503         current.setId(results.getInt("ID"));
    504         current.setFirstName(results.getString("FIRST_NAME").trim());
    505         current.setLastName(results.getString("LAST_NAME").trim());
    506         Location location = new Location();
    507         location.setCity(results.getString("CITY").trim());
    508         location.setCountry(results.getString("COUNTRY").trim());
    509         current.setLocation(location);
    510         Phone phone = new Phone();
    511         phone.setNumber(results.getString("Phone").trim());
    512         current.setPhone(phone);
    513
    514         LOG.trace("Found Customer: " + current.toString());
    515
    516         return current;
    517     }
    518 }