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.Product;
    37  import org.example.customer.Supplier;
    38  import org.example.customer.utility.CustomerEntity;
    39  import org.example.customerdao.utility.ErrorFormatter;
    40  import org.example.customerdao.utility.NonDeleteableRecordException;
    41  import org.example.websecurity.UserCredentials;
    42
    43  /**
    44   * This is the ProductDAO Implementation for the Customer DAO component of the
    45   * Customer Web Application. This will be the primary Product database exposure
    46   * for the Customer Web Layer.
    47   *
    48   * @author Jonathan Earl
    49   * @since 1.0
    50   *
    51   */
    52  public class ProductDAOImpl
    53      implements ProductDAO
    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 Product";
    61      private static final String FIND_ALL_ACTIVE_SQL = "Select * from Product where IS_DISCONTINUED = false ";
    62      private static final String FIND_BY_ID_SQL = "Select * from Product where ID = ?";
    63      private static final String FIND_BY_SUPPLIER_SQL = "Select * from Product where SUPPLIER_ID = ?";
    64      private static final String ADD_SQL = "Insert into Product(PRODUCT_NAME,SUPPLIER_ID,UNIT_PRICE,PACKAGE,IS_DISCONTINUED)"
    65              + " VALUES(?,?,?,?,?)";
    66      private static final String UPDATE_SQL = "Update Product set PRODUCT_NAME = ?, SUPPLIER_ID = ?, "
    67              + "UNIT_PRICE = ?, PACKAGE = ?, IS_DISCONTINUED = ?" + " WHERE ID = ?";
    68      private static final String IS_DELETEABLE_PRODUCT_SQL = "Select COUNT(*) from Order_Item where PRODUCT_ID = ?";
    69      private static final String DELETE_SQL = "Delete from Product 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<Product> findAllProducts(final UserCredentials credentials)
    96      {
    97          LOG.debug("findAllProducts");
    98
    99          if (credentials == null || !credentials.hasRole("worker"))
    100         {
    101             LOG.error("findAllProducts - 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<Product> data = new ArrayList<Product>();
    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(buildProduct(results));
    119             }
    120         }
    121         catch (SQLException sqle)
    122         {
    123             LOG.error("Error finding all Products");
    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 List<Product> findAllActiveProducts(final UserCredentials credentials)
    139     {
    140         LOG.debug("findAllActiveProducts");
    141
    142         if (credentials == null || !credentials.hasRole("worker"))
    143         {
    144             LOG.error("findAllActiveProducts - Permission refused");
    145             throw new IllegalArgumentException("Permission refused for this operation");
    146         }
    147
    148         PreparedStatement findAll = null;
    149         Connection conn = null;
    150         ResultSet results = null;
    151         List<Product> data = new ArrayList<Product>();
    152         try
    153         {
    154             conn = myReadOnlyDS.getConnection();
    155             findAll = conn.prepareStatement(FIND_ALL_ACTIVE_SQL);
    156
    157             results = findAll.executeQuery();
    158
    159             while (results.next())
    160             {
    161                 data.add(buildProduct(results));
    162             }
    163         }
    164         catch (SQLException sqle)
    165         {
    166             LOG.error("Error finding all active Products");
    167             LOG.error(ErrorFormatter.extractError(sqle));
    168             throw new IllegalArgumentException(sqle);
    169         }
    170         finally
    171         {
    172             DbUtils.closeQuietly(conn, findAll, results);
    173         }
    174         return data;
    175     }
    176
    177     /**
    178      * {@inheritDoc}
    179      */
    180     @Override
    181     public Product findProductById(final UserCredentials credentials, final int id)
    182     {
    183         LOG.debug("findProductById");
    184
    185         if (credentials == null || !credentials.hasRole("worker"))
    186         {
    187             LOG.error("findProductById - Permission refused");
    188             throw new IllegalArgumentException("Permission refused for this operation");
    189         }
    190
    191         PreparedStatement findById = null;
    192         Connection conn = null;
    193         ResultSet results = null;
    194         Product product = null;
    195         try
    196         {
    197             conn = myReadOnlyDS.getConnection();
    198             findById = conn.prepareStatement(FIND_BY_ID_SQL);
    199
    200             findById.setInt(1, id);
    201             results = findById.executeQuery();
    202
    203             if (results.next())
    204             {
    205                 product = buildProduct(results);
    206             }
    207         }
    208         catch (SQLException sqle)
    209         {
    210             LOG.error("Error finding Product by id");
    211             LOG.error(ErrorFormatter.extractError(sqle));
    212             throw new IllegalArgumentException(sqle);
    213         }
    214         finally
    215         {
    216             DbUtils.closeQuietly(conn, findById, results);
    217         }
    218         return product;
    219     }
    220
    221     /**
    222      * {@inheritDoc}
    223      */
    224     @Override
    225     public List<Product> findProductsBySupplier(final UserCredentials credentials, final Supplier supplier)
    226     {
    227        LOG.debug("findProductsBySupplier");
    228
    229         if (credentials == null || !credentials.hasRole("worker"))
    230         {
    231             LOG.error("findSuppliersByComapanyName - Permission refused");
    232             throw new IllegalArgumentException("Permission refused for this operation");
    233         }
    234         if (supplier == null)
    235         {
    236             LOG.error("Supplier is required");
    237             throw new IllegalArgumentException("Supplier is required");
    238         }
    239
    240         PreparedStatement findBySupplier = null;
    241         Connection conn = null;
    242         ResultSet results = null;
    243         List<Product> data = new ArrayList<Product>();
    244         try
    245         {
    246             conn = myReadOnlyDS.getConnection();
    247             findBySupplier = conn.prepareStatement(FIND_BY_SUPPLIER_SQL);
    248             findBySupplier.setInt(1, supplier.getId());
    249
    250             results = findBySupplier.executeQuery();
    251
    252             while (results.next())
    253             {
    254                 data.add(buildProduct(results));
    255             }
    256         }
    257         catch (SQLException sqle)
    258         {
    259             LOG.error("Error finding Products by Supplier");
    260             LOG.error(ErrorFormatter.extractError(sqle));
    261             throw new IllegalArgumentException(sqle);
    262         }
    263         finally
    264         {
    265             DbUtils.closeQuietly(conn, findBySupplier, results);
    266         }
    267         return data;
    268     }
    269
    270     /**
    271      * {@inheritDoc}
    272      */
    273     @Override
    274     public int addProduct(final UserCredentials credentials, final Product product)
    275     {
    276         LOG.debug("addProduct");
    277
    278         if (credentials == null || !credentials.hasRole("manager"))
    279         {
    280             LOG.error("addProduct - Permission refused");
    281             throw new IllegalArgumentException("Permission refused for this operation");
    282         }
    283
    284         if (product == null)
    285         {
    286             LOG.error("addProduct - null Product was provided");
    287             throw new IllegalArgumentException("A Product must be provided");
    288         }
    289
    290         PreparedStatement addProduct = null;
    291         Connection conn = null;
    292         ResultSet results = null;
    293         int generatedId = 0;
    294         try
    295         {
    296             conn = myReadWriteDS.getConnection();
    297             addProduct = conn.prepareStatement(ADD_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
    298
    299             String name = product.getProductName();
    300             int supplierId = product.getSupplierId();
    301             double unitPrice = product.getUnitPrice();
    302             String packaging = product.getPackaging();
    303             boolean discontinued = product.isDiscontinued();
    304
    305             addProduct.setString(1, name);
    306             addProduct.setInt(2, supplierId);
    307             addProduct.setDouble(3, unitPrice);
    308             addProduct.setString(4, packaging);
    309             addProduct.setBoolean(5, discontinued);
    310             addProduct.executeUpdate();
    311             results = addProduct.getGeneratedKeys();
    312             if (results.next())
    313             {
    314                 generatedId = results.getInt(1);
    315             }
    316         }
    317         catch (SQLException sqle)
    318         {
    319             LOG.error("Error adding Product");
    320             LOG.error(ErrorFormatter.extractError(sqle));
    321             throw new IllegalArgumentException(sqle);
    322         }
    323         finally
    324         {
    325             DbUtils.closeQuietly(conn, addProduct, results);
    326         }
    327         return generatedId;
    328     }
    329
    330     /**
    331      * {@inheritDoc}
    332      */
    333     @Override
    334     public void updateProduct(final UserCredentials credentials, final Product product)
    335     {
    336       LOG.debug("updateProduct");
    337
    338         if (credentials == null || !credentials.hasRole("manager"))
    339         {
    340             LOG.error("updateProduct - Permission refused");
    341             throw new IllegalArgumentException("Permission refused for this operation");
    342         }
    343
    344         if (product == null)
    345         {
    346             LOG.error("updateProduct - null Product was provided");
    347             throw new IllegalArgumentException("A Product must be provided");
    348         }
    349
    350         PreparedStatement updateProduct = null;
    351         Connection conn = null;
    352         try
    353         {
    354             conn = myReadWriteDS.getConnection();
    355             updateProduct = conn.prepareStatement(UPDATE_SQL);
    356
    357             int id = product.getId();
    358             String name = product.getProductName();
    359             int supplierId = product.getSupplierId();
    360             double unitPrice = product.getUnitPrice();
    361             String packaging = product.getPackaging();
    362             boolean discontinued = product.isDiscontinued();
    363
    364             updateProduct.setString(1, name);
    365             updateProduct.setInt(2, supplierId);
    366             updateProduct.setDouble(3, unitPrice);
    367             updateProduct.setString(4, packaging);
    368             updateProduct.setBoolean(5, discontinued);
    369             updateProduct.setInt(6, id);
    370
    371             updateProduct.executeUpdate();
    372         }
    373         catch (SQLException sqle)
    374         {
    375             LOG.error("Error updating Product");
    376             LOG.error(ErrorFormatter.extractError(sqle));
    377             throw new IllegalArgumentException(sqle);
    378         }
    379         finally
    380         {
    381             DbUtils.closeQuietly(updateProduct);
    382             DbUtils.closeQuietly(conn);
    383         }
    384     }
    385
    386     /**
    387      * {@inheritDoc}
    388      * <p>
    389      * Products with Orders are not deleteable.
    390      */
    391     @Override
    392     public boolean isDeleteable(final UserCredentials credentials, final CustomerEntity entity)
    393     {
    394         LOG.debug("isDeleteable");
    395
    396         if (credentials == null || entity == null)
    397         {
    398             LOG.error("isDeleteable - null Product was provided");
    399             throw new IllegalArgumentException("A Product must be provided");
    400         }
    401        return isDeleteable(credentials, entity.getId());
    402     }
    403
    404     /**
    405      * {@inheritDoc}
    406      * <p>
    407      * Products with Orders are not deleteable.
    408      */
    409     @Override
    410     public boolean isDeleteable(final UserCredentials credentials, final int id)
    411     {
    412        LOG.debug("isDeleteable");
    413
    414         if (credentials == null || !credentials.hasRole("worker"))
    415         {
    416             LOG.error("isDeleteable - Permission refused");
    417             throw new IllegalArgumentException("Permission refused for this operation");
    418         }
    419
    420         PreparedStatement isDeleteable = null;
    421         Connection conn = null;
    422         ResultSet results = null;
    423         boolean isDeleteableResult = false;
    424         try
    425         {
    426             conn = myReadOnlyDS.getConnection();
    427             isDeleteable = conn.prepareStatement(IS_DELETEABLE_PRODUCT_SQL);
    428
    429             isDeleteable.setInt(1, id);
    430             results = isDeleteable.executeQuery();
    431
    432             if (results.next())
    433             {
    434                 int count = results.getInt(1);
    435                 if (count == 0)
    436                 {
    437                     isDeleteableResult = true;
    438                 }
    439             }
    440         }
    441         catch (SQLException sqle)
    442         {
    443             LOG.error("Error finding Orders by Product");
    444             LOG.error(ErrorFormatter.extractError(sqle));
    445             throw new IllegalArgumentException(sqle);
    446         }
    447         finally
    448         {
    449             DbUtils.closeQuietly(conn, isDeleteable, results);
    450         }
    451         return isDeleteableResult;
    452     }
    453
    454     /**
    455      * {@inheritDoc}
    456      * <p>
    457      * Products with Orders are not deleteable.
    458      */
    459     @Override
    460     public void deleteEntity(final UserCredentials credentials, final CustomerEntity entity)
    461             throws NonDeleteableRecordException
    462     {
    463         LOG.debug("deleteProduct");
    464
    465         if (entity == null)
    466         {
    467             LOG.error("deleteProduct - null Product was provided");
    468             throw new IllegalArgumentException("A Product must be provided");
    469         }
    470         if (!(entity instanceof Product))
    471         {
    472             LOG.error("deleteProduct - invalid CustomerEntity was provided");
    473             throw new IllegalArgumentException("A Product must be provided");
    474         }
    475         deleteEntity(credentials, entity.getId());
    476     }
    477
    478     /**
    479      * {@inheritDoc}
    480      * <p>
    481      * Products with Orders are not deleteable.
    482      */
    483     @Override
    484     public void deleteEntity(final UserCredentials credentials, final int id)
    485             throws NonDeleteableRecordException
    486     {
    487         LOG.debug("deleteProduct");
    488
    489         if (credentials == null || !credentials.hasRole("manager"))
    490         {
    491             LOG.error("deleteProduct - Permission refused");
    492             throw new IllegalArgumentException("Permission refused for this operation");
    493         }
    494
    495         if (!isDeleteable(credentials, id))
    496         {
    497             LOG.error("Product not deleteable");
    498             throw new NonDeleteableRecordException("This Product is not deletable");
    499         }
    500
    501         PreparedStatement deleteProduct = null;
    502         Connection conn = null;
    503         try
    504         {
    505             conn = myReadWriteDS.getConnection();
    506             deleteProduct = conn.prepareStatement(DELETE_SQL);
    507
    508             deleteProduct.setInt(1, id);
    509
    510             deleteProduct.executeUpdate();
    511         }
    512         catch (SQLException sqle)
    513         {
    514             LOG.error("Error updating Product");
    515             LOG.error(ErrorFormatter.extractError(sqle));
    516             throw new IllegalArgumentException(sqle);
    517         }
    518         finally
    519         {
    520             DbUtils.closeQuietly(deleteProduct);
    521             DbUtils.closeQuietly(conn);
    522         }
    523     }
    524
    525     private Product buildProduct(final ResultSet results) throws SQLException
    526     {
    527         Product current = new Product();
    528
    529         current.setId(results.getInt("ID"));
    530         current.setProductName(results.getString("PRODUCT_NAME").trim());
    531         current.setSupplierId(results.getInt("SUPPLIER_ID"));
    532         current.setUnitPrice(results.getDouble("UNIT_PRICE"));
    533         current.setPackaging(results.getString("PACKAGE"));
    534         current.setDiscontinued(results.getBoolean("IS_DISCONTINUED"));
    535
    536         LOG.trace("Found Product: " + current.toString());
    537
    538         return current;
    539     }
    540 }