# CustomerDAOImpl.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.Customer;
    import org.example.customer.utility.CustomerEntity;
    import org.example.customer.utility.Location;
    import org.example.customer.utility.Phone;
    import org.example.customerdao.utility.ErrorFormatter;
    import org.example.customerdao.utility.NonDeleteableRecordException;
    import org.example.websecurity.UserCredentials;

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

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

        private static final String FIND_ALL_SQL = "Select * from Customer";
        private static final String FIND_BY_ID_SQL = "Select * from Customer where ID = ?";
        private static final String FIND_BY_NAME_SQL = "Select * from Customer where FIRST_NAME = ? AND LAST_NAME = ?";
        private static final String ADD_SQL = "Insert into Customer(FIRST_NAME,LAST_NAME,CITY,COUNTRY,PHONE)"
                + " VALUES(?,?,?,?,?)";
        private static final String UPDATE_SQL = "Update Customer set FIRST_NAME = ?, LAST_NAME = ?, "
                + "CITY = ?, COUNTRY = ?, PHONE = ?"
                + " WHERE ID = ?";
        private static final String IS_DELETEABLE_CUSTOMER_SQL = "Select COUNT(*) from Orders where CUSTOMER_ID = ?";
        private static final String DELETE_SQL = "Delete from Customer 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<Customer> findAllCustomers(final UserCredentials credentials)
        {
            LOG.debug("findAllCustomers");

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

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

                results = findAll.executeQuery();

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

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

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

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

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

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

        /**
         * {@inheritDoc}
         */
        @Override
        public List<Customer> findCustomersByName(final UserCredentials credentials,
                final String firstName, final String lastName)
        {
            LOG.debug("findCustomersByName");

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

            if (firstName == null || lastName == null)
            {
                LOG.error("findCustomersByName - names are required");
                throw new IllegalArgumentException("A first name and a last name are required");
            }

            PreparedStatement findByName = null;
            Connection conn = null;
            ResultSet results = null;
            List<Customer> data = new ArrayList<Customer>();
            try
            {
                conn = myReadOnlyDS.getConnection();
                findByName = conn.prepareStatement(FIND_BY_NAME_SQL);

                findByName.setString(1, firstName);
                findByName.setString(2, lastName);

                results = findByName.executeQuery();

                while (results.next())
                {
                    data.add(buildCustomer(results));
                }
            }
            catch (SQLException sqle)
            {
                LOG.error("Error finding Customer by name");
                LOG.error(ErrorFormatter.extractError(sqle));
                throw new IllegalArgumentException(sqle);
            }
            finally
            {
                DbUtils.closeQuietly(conn, findByName, results);
            }
            return data;
        }

        /**
         * {@inheritDoc}
         */
        @Override
        public int addCustomer(final UserCredentials credentials, final Customer customer)
        {
            LOG.debug("addCustomer");

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

            if (customer == null)
            {
                LOG.error("addCustomer - null Customer was provided");
                throw new IllegalArgumentException("A Customer must be provided");
            }

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

                String firstName = customer.getFirstName();
                String lastName = customer.getLastName();
                Location location = customer.getLocation();
                String city = location.getCity();
                String country = location.getCountry();
                Phone phone = customer.getPhone();
                String number = phone.getNumber();

                addCustomer.setString(1, firstName);
                addCustomer.setString(2, lastName);
                addCustomer.setString(3, city);
                addCustomer.setString(4, country);
                addCustomer.setString(5, number);

                addCustomer.executeUpdate();
                results = addCustomer.getGeneratedKeys();
                if (results.next())
                {
                    generatedId = results.getInt(1);
                }
            }
            catch (SQLException sqle)
            {
                LOG.error("Error adding Customer");
                LOG.error(ErrorFormatter.extractError(sqle));
                throw new IllegalArgumentException(sqle);
            }
            finally
            {
                DbUtils.closeQuietly(conn, addCustomer, results);
            }
            return generatedId;
        }

        /**
         * {@inheritDoc}
         */
        @Override
        public void updateCustomer(final UserCredentials credentials, final Customer customer)
        {
            LOG.debug("updateCustomer");

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

            if (customer == null)
            {
                LOG.error("updateCustomer - null Customer was provided");
                throw new IllegalArgumentException("A Customer must be provided");
            }

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

                int id = customer.getId();
                String firstName = customer.getFirstName();
                String lastName = customer.getLastName();
                Location location = customer.getLocation();
                String city = location.getCity();
                String country = location.getCountry();
                String number = null;
                Phone phone = customer.getPhone();
                if (phone != null)
                {
                    number = phone.getNumber();
                }

                updateCustomer.setString(1, firstName);
                updateCustomer.setString(2, lastName);
                updateCustomer.setString(3, city);
                updateCustomer.setString(4, country);
                updateCustomer.setString(5, number);
                updateCustomer.setInt(6, id);

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

        /**
         * {@inheritDoc}
         * <p>
         * Customers 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_CUSTOMER_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 Customer by Order");
                LOG.error(ErrorFormatter.extractError(sqle));
                throw new IllegalArgumentException(sqle);
            }
            finally
            {
                DbUtils.closeQuietly(conn, isDeleteable, results);
            }
            return isDeleteableResult;
        }

        /**
         * {@inheritDoc}
         * <p>
         * Customers 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 Customer was provided");
                throw new IllegalArgumentException("A Customer must be provided");
            }
            if (!(entity instanceof Customer))
            {
                LOG.error("isDeleteable - invalid CustomerEntity was provided");
                throw new IllegalArgumentException("A Customer must be provided");
            }
           return isDeleteable(credentials, entity.getId());
        }

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

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

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

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

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

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

                deleteCustomer.setInt(1, id);

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

        }

        private Customer buildCustomer(final ResultSet results) throws SQLException
        {
            Customer current = new Customer();

            current.setId(results.getInt("ID"));
            current.setFirstName(results.getString("FIRST_NAME").trim());
            current.setLastName(results.getString("LAST_NAME").trim());
            Location location = new Location();
            location.setCity(results.getString("CITY").trim());
            location.setCountry(results.getString("COUNTRY").trim());
            current.setLocation(location);
            Phone phone = new Phone();
            phone.setNumber(results.getString("Phone").trim());
            current.setPhone(phone);

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

            return current;
        }
    }