View Javadoc

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