View Javadoc

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