# ProductDAOImpl.java

    package org.example.customerdao;

    /*
    * This is free and unencumbered software released into the public domain.
    * Anyone is free to copy, modify, publish, use, compile, sell, or distribute this software,
    * either in source code form or as a compiled binary, for any purpose, commercial or
    * non-commercial, and by any means.
    *
    * In jurisdictions that recognize copyright laws, the author or authors of this
    * software dedicate any and all copyright interest in the software to the public domain.
    * We make this dedication for the benefit of the public at large and to the detriment of
    * our heirs and successors. We intend this dedication to be an overt act of relinquishment in
    * perpetuity of all present and future rights to this software under copyright law.
    *
    * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED,
    * INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
    * PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES
    * OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
    * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
    *
    * For more information, please refer to: https://unlicense.org/
    */

    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;

    import javax.sql.DataSource;

    import org.apache.commons.dbutils.DbUtils;
    import org.apache.logging.log4j.LogManager;
    import org.apache.logging.log4j.Logger;
    import org.example.customer.Product;
    import org.example.customer.Supplier;
    import org.example.customer.utility.CustomerEntity;
    import org.example.customerdao.utility.ErrorFormatter;
    import org.example.customerdao.utility.NonDeleteableRecordException;
    import org.example.websecurity.UserCredentials;

    /**
     * This is the ProductDAO Implementation for the Customer DAO component of the
     * Customer Web Application. This will be the primary Product database exposure
     * for the Customer Web Layer.
     *
     * @author Jonathan Earl
     * @since 1.0
     *
     */
    public class ProductDAOImpl
        implements ProductDAO
    {
        private static final Logger LOG = LogManager.getLogger();

        private DataSource myReadOnlyDS = null;
        private DataSource myReadWriteDS = null;

        private static final String FIND_ALL_SQL = "Select * from Product";
        private static final String FIND_ALL_ACTIVE_SQL = "Select * from Product where IS_DISCONTINUED = false ";
        private static final String FIND_BY_ID_SQL = "Select * from Product where ID = ?";
        private static final String FIND_BY_SUPPLIER_SQL = "Select * from Product where SUPPLIER_ID = ?";
        private static final String ADD_SQL = "Insert into Product(PRODUCT_NAME,SUPPLIER_ID,UNIT_PRICE,PACKAGE,IS_DISCONTINUED)"
                + " VALUES(?,?,?,?,?)";
        private static final String UPDATE_SQL = "Update Product set PRODUCT_NAME = ?, SUPPLIER_ID = ?, "
                + "UNIT_PRICE = ?, PACKAGE = ?, IS_DISCONTINUED = ?" + " WHERE ID = ?";
        private static final String IS_DELETEABLE_PRODUCT_SQL = "Select COUNT(*) from Order_Item where PRODUCT_ID = ?";
        private static final String DELETE_SQL = "Delete from Product where id = ?";

        /**
         * {@inheritDoc}
         */
        @Override
        public void setReadOnlyDS(final DataSource readOnlyDS)
        {
            LOG.debug("Setting the ReadOnly DataSource");
            this.myReadOnlyDS = readOnlyDS;
        }

        /**
         * {@inheritDoc}
         */
        @Override
        public void setReadWriteDS(final DataSource readWriteDS)
        {
            LOG.debug("Setting the ReadWrite DataSource");
            this.myReadWriteDS = readWriteDS;
        }

        /**
         * {@inheritDoc}
         */
        @Override
        public List<Product> findAllProducts(final UserCredentials credentials)
        {
            LOG.debug("findAllProducts");

            if (credentials == null || !credentials.hasRole("worker"))
            {
                LOG.error("findAllProducts - Permission refused");
                throw new IllegalArgumentException("Permission refused for this operation");
            }

            PreparedStatement findAll = null;
            Connection conn = null;
            ResultSet results = null;
            List<Product> data = new ArrayList<Product>();
            try
            {
                conn = myReadOnlyDS.getConnection();
                findAll = conn.prepareStatement(FIND_ALL_SQL);

                results = findAll.executeQuery();

                while (results.next())
                {
                    data.add(buildProduct(results));
                }
            }
            catch (SQLException sqle)
            {
                LOG.error("Error finding all Products");
                LOG.error(ErrorFormatter.extractError(sqle));
                throw new IllegalArgumentException(sqle);
            }
            finally
            {
                DbUtils.closeQuietly(conn, findAll, results);
            }
            return data;
        }

        /**
         * {@inheritDoc}
         */
        @Override
        public List<Product> findAllActiveProducts(final UserCredentials credentials)
        {
            LOG.debug("findAllActiveProducts");

            if (credentials == null || !credentials.hasRole("worker"))
            {
                LOG.error("findAllActiveProducts - Permission refused");
                throw new IllegalArgumentException("Permission refused for this operation");
            }

            PreparedStatement findAll = null;
            Connection conn = null;
            ResultSet results = null;
            List<Product> data = new ArrayList<Product>();
            try
            {
                conn = myReadOnlyDS.getConnection();
                findAll = conn.prepareStatement(FIND_ALL_ACTIVE_SQL);

                results = findAll.executeQuery();

                while (results.next())
                {
                    data.add(buildProduct(results));
                }
            }
            catch (SQLException sqle)
            {
                LOG.error("Error finding all active Products");
                LOG.error(ErrorFormatter.extractError(sqle));
                throw new IllegalArgumentException(sqle);
            }
            finally
            {
                DbUtils.closeQuietly(conn, findAll, results);
            }
            return data;
        }

        /**
         * {@inheritDoc}
         */
        @Override
        public Product findProductById(final UserCredentials credentials, final int id)
        {
            LOG.debug("findProductById");

            if (credentials == null || !credentials.hasRole("worker"))
            {
                LOG.error("findProductById - Permission refused");
                throw new IllegalArgumentException("Permission refused for this operation");
            }

            PreparedStatement findById = null;
            Connection conn = null;
            ResultSet results = null;
            Product product = null;
            try
            {
                conn = myReadOnlyDS.getConnection();
                findById = conn.prepareStatement(FIND_BY_ID_SQL);

                findById.setInt(1, id);
                results = findById.executeQuery();

                if (results.next())
                {
                    product = buildProduct(results);
                }
            }
            catch (SQLException sqle)
            {
                LOG.error("Error finding Product by id");
                LOG.error(ErrorFormatter.extractError(sqle));
                throw new IllegalArgumentException(sqle);
            }
            finally
            {
                DbUtils.closeQuietly(conn, findById, results);
            }
            return product;
        }

        /**
         * {@inheritDoc}
         */
        @Override
        public List<Product> findProductsBySupplier(final UserCredentials credentials, final Supplier supplier)
        {
           LOG.debug("findProductsBySupplier");

            if (credentials == null || !credentials.hasRole("worker"))
            {
                LOG.error("findSuppliersByComapanyName - Permission refused");
                throw new IllegalArgumentException("Permission refused for this operation");
            }
            if (supplier == null)
            {
                LOG.error("Supplier is required");
                throw new IllegalArgumentException("Supplier is required");
            }

            PreparedStatement findBySupplier = null;
            Connection conn = null;
            ResultSet results = null;
            List<Product> data = new ArrayList<Product>();
            try
            {
                conn = myReadOnlyDS.getConnection();
                findBySupplier = conn.prepareStatement(FIND_BY_SUPPLIER_SQL);
                findBySupplier.setInt(1, supplier.getId());

                results = findBySupplier.executeQuery();

                while (results.next())
                {
                    data.add(buildProduct(results));
                }
            }
            catch (SQLException sqle)
            {
                LOG.error("Error finding Products by Supplier");
                LOG.error(ErrorFormatter.extractError(sqle));
                throw new IllegalArgumentException(sqle);
            }
            finally
            {
                DbUtils.closeQuietly(conn, findBySupplier, results);
            }
            return data;
        }

        /**
         * {@inheritDoc}
         */
        @Override
        public int addProduct(final UserCredentials credentials, final Product product)
        {
            LOG.debug("addProduct");

            if (credentials == null || !credentials.hasRole("manager"))
            {
                LOG.error("addProduct - Permission refused");
                throw new IllegalArgumentException("Permission refused for this operation");
            }

            if (product == null)
            {
                LOG.error("addProduct - null Product was provided");
                throw new IllegalArgumentException("A Product must be provided");
            }

            PreparedStatement addProduct = null;
            Connection conn = null;
            ResultSet results = null;
            int generatedId = 0;
            try
            {
                conn = myReadWriteDS.getConnection();
                addProduct = conn.prepareStatement(ADD_SQL, PreparedStatement.RETURN_GENERATED_KEYS);

                String name = product.getProductName();
                int supplierId = product.getSupplierId();
                double unitPrice = product.getUnitPrice();
                String packaging = product.getPackaging();
                boolean discontinued = product.isDiscontinued();

                addProduct.setString(1, name);
                addProduct.setInt(2, supplierId);
                addProduct.setDouble(3, unitPrice);
                addProduct.setString(4, packaging);
                addProduct.setBoolean(5, discontinued);
                addProduct.executeUpdate();
                results = addProduct.getGeneratedKeys();
                if (results.next())
                {
                    generatedId = results.getInt(1);
                }
            }
            catch (SQLException sqle)
            {
                LOG.error("Error adding Product");
                LOG.error(ErrorFormatter.extractError(sqle));
                throw new IllegalArgumentException(sqle);
            }
            finally
            {
                DbUtils.closeQuietly(conn, addProduct, results);
            }
            return generatedId;
        }

        /**
         * {@inheritDoc}
         */
        @Override
        public void updateProduct(final UserCredentials credentials, final Product product)
        {
          LOG.debug("updateProduct");

            if (credentials == null || !credentials.hasRole("manager"))
            {
                LOG.error("updateProduct - Permission refused");
                throw new IllegalArgumentException("Permission refused for this operation");
            }

            if (product == null)
            {
                LOG.error("updateProduct - null Product was provided");
                throw new IllegalArgumentException("A Product must be provided");
            }

            PreparedStatement updateProduct = null;
            Connection conn = null;
            try
            {
                conn = myReadWriteDS.getConnection();
                updateProduct = conn.prepareStatement(UPDATE_SQL);

                int id = product.getId();
                String name = product.getProductName();
                int supplierId = product.getSupplierId();
                double unitPrice = product.getUnitPrice();
                String packaging = product.getPackaging();
                boolean discontinued = product.isDiscontinued();

                updateProduct.setString(1, name);
                updateProduct.setInt(2, supplierId);
                updateProduct.setDouble(3, unitPrice);
                updateProduct.setString(4, packaging);
                updateProduct.setBoolean(5, discontinued);
                updateProduct.setInt(6, id);

                updateProduct.executeUpdate();
            }
            catch (SQLException sqle)
            {
                LOG.error("Error updating Product");
                LOG.error(ErrorFormatter.extractError(sqle));
                throw new IllegalArgumentException(sqle);
            }
            finally
            {
                DbUtils.closeQuietly(updateProduct);
                DbUtils.closeQuietly(conn);
            }
        }

        /**
         * {@inheritDoc}
         * <p>
         * Products with Orders are not deleteable.
         */
        @Override
        public boolean isDeleteable(final UserCredentials credentials, final CustomerEntity entity)
        {
            LOG.debug("isDeleteable");

            if (credentials == null || entity == null)
            {
                LOG.error("isDeleteable - null Product was provided");
                throw new IllegalArgumentException("A Product must be provided");
            }
           return isDeleteable(credentials, entity.getId());
        }

        /**
         * {@inheritDoc}
         * <p>
         * Products with Orders are not deleteable.
         */
        @Override
        public boolean isDeleteable(final UserCredentials credentials, final int id)
        {
           LOG.debug("isDeleteable");

            if (credentials == null || !credentials.hasRole("worker"))
            {
                LOG.error("isDeleteable - Permission refused");
                throw new IllegalArgumentException("Permission refused for this operation");
            }

            PreparedStatement isDeleteable = null;
            Connection conn = null;
            ResultSet results = null;
            boolean isDeleteableResult = false;
            try
            {
                conn = myReadOnlyDS.getConnection();
                isDeleteable = conn.prepareStatement(IS_DELETEABLE_PRODUCT_SQL);

                isDeleteable.setInt(1, id);
                results = isDeleteable.executeQuery();

                if (results.next())
                {
                    int count = results.getInt(1);
                    if (count == 0)
                    {
                        isDeleteableResult = true;
                    }
                }
            }
            catch (SQLException sqle)
            {
                LOG.error("Error finding Orders by Product");
                LOG.error(ErrorFormatter.extractError(sqle));
                throw new IllegalArgumentException(sqle);
            }
            finally
            {
                DbUtils.closeQuietly(conn, isDeleteable, results);
            }
            return isDeleteableResult;
        }

        /**
         * {@inheritDoc}
         * <p>
         * Products with Orders are not deleteable.
         */
        @Override
        public void deleteEntity(final UserCredentials credentials, final CustomerEntity entity)
                throws NonDeleteableRecordException
        {
            LOG.debug("deleteProduct");

            if (entity == null)
            {
                LOG.error("deleteProduct - null Product was provided");
                throw new IllegalArgumentException("A Product must be provided");
            }
            if (!(entity instanceof Product))
            {
                LOG.error("deleteProduct - invalid CustomerEntity was provided");
                throw new IllegalArgumentException("A Product must be provided");
            }
            deleteEntity(credentials, entity.getId());
        }

        /**
         * {@inheritDoc}
         * <p>
         * Products with Orders are not deleteable.
         */
        @Override
        public void deleteEntity(final UserCredentials credentials, final int id)
                throws NonDeleteableRecordException
        {
            LOG.debug("deleteProduct");

            if (credentials == null || !credentials.hasRole("manager"))
            {
                LOG.error("deleteProduct - Permission refused");
                throw new IllegalArgumentException("Permission refused for this operation");
            }

            if (!isDeleteable(credentials, id))
            {
                LOG.error("Product not deleteable");
                throw new NonDeleteableRecordException("This Product is not deletable");
            }

            PreparedStatement deleteProduct = null;
            Connection conn = null;
            try
            {
                conn = myReadWriteDS.getConnection();
                deleteProduct = conn.prepareStatement(DELETE_SQL);

                deleteProduct.setInt(1, id);

                deleteProduct.executeUpdate();
            }
            catch (SQLException sqle)
            {
                LOG.error("Error updating Product");
                LOG.error(ErrorFormatter.extractError(sqle));
                throw new IllegalArgumentException(sqle);
            }
            finally
            {
                DbUtils.closeQuietly(deleteProduct);
                DbUtils.closeQuietly(conn);
            }
        }

        private Product buildProduct(final ResultSet results) throws SQLException
        {
            Product current = new Product();

            current.setId(results.getInt("ID"));
            current.setProductName(results.getString("PRODUCT_NAME").trim());
            current.setSupplierId(results.getInt("SUPPLIER_ID"));
            current.setUnitPrice(results.getDouble("UNIT_PRICE"));
            current.setPackaging(results.getString("PACKAGE"));
            current.setDiscontinued(results.getBoolean("IS_DISCONTINUED"));

            LOG.trace("Found Product: " + current.toString());

            return current;
        }
    }