package by.bsuir.hypermarket.dao.concrete;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
import by.bsuir.hypermarket.connection.DbConnection;
import by.bsuir.hypermarket.connection.DbConnectionPool;
import by.bsuir.hypermarket.connection.exception.ConnectionPoolException;
import by.bsuir.hypermarket.dao.AbstractDao;
import by.bsuir.hypermarket.dao.exception.DaoException;
import by.bsuir.hypermarket.entity.Address;
import by.bsuir.hypermarket.entity.Country;
import by.bsuir.hypermarket.entity.Producer;
public class ProducerDao extends AbstractDao<Producer> {
private final Logger log = Logger.getLogger(getClass().getSimpleName());
private static final String FIND_ALL = "SELECT `p_uid`, `p_name`, `p_phone`, `p_email`, "
+ "`country_name` FROM `producer`"
+ "LEFT JOIN `address` ON `a_producer_uid` = `p_uid` "
+ "LEFT JOIN `country` ON `country_uid` = `a_c_uid`";
private static final String FIND_BY_ID = "SELECT `p_name`, `p_phone`, `p_email` FROM `producer` WHERE `p_uid` = ?";
private static final String FIND_PRODUCER_BY_ID = "SELECT `a_uid`, `a_c_uid`, `a_region`, `a_district`, "
+ "`a_street`, `a_zip_code`, "
+ "`a_building`, `a_appartment`, `a_house_num`, "
+ " `country_name`"
+ "FROM `address`"
+ "LEFT JOIN `country` ON `country_uid` = `a_c_uid`"
+ "WHERE `a_producer_uid` = ?";
@Override
public List<Producer> findAll() throws DaoException {
List<Producer> producers = new ArrayList<Producer>();
try (DbConnection connection = DbConnectionPool.INSTANCE.getConnection();
PreparedStatement statement = connection.prepareStatement(FIND_ALL);
PreparedStatement producerStatement = connection.prepareStatement(FIND_PRODUCER_BY_ID)) {
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
Producer producer = new Producer();
producer.setUid(resultSet.getInt("p_uid"));
producer.setName(resultSet.getString("p_name"));
producer.setPhoneNumber(resultSet.getString("p_phone"));
producer.setEmail(resultSet.getString("p_email"));
producerStatement.setInt(1, producer.getUid());
ResultSet producerResultSet = producerStatement.executeQuery();
setProducerAddresses(producerResultSet, producer);
producers.add(producer);
}
} catch (SQLException e) {
log.error("Error during statement creation", e);
throw new DaoException(e);
} catch (ConnectionPoolException e) {
log.error("Error during getting connection from the pool", e);
throw new DaoException(e);
}
return producers;
}
@Override
public Producer findEntityById(int id) throws DaoException {
Producer producer = new Producer();
try (DbConnection connection = DbConnectionPool.INSTANCE.getConnection();
PreparedStatement idStatement = connection.prepareStatement(FIND_BY_ID);
PreparedStatement producerStatement = connection.prepareStatement(FIND_PRODUCER_BY_ID)) {
idStatement.setInt(1, id);
ResultSet resultSet = idStatement.executeQuery();
if (resultSet.next()) {
producer.setUid(id);
producer.setName(resultSet.getString("p_name"));
producer.setPhoneNumber(resultSet.getString("p_phone"));
producer.setEmail(resultSet.getString("p_email"));
producerStatement.setInt(1, producer.getUid());
ResultSet producerResultSet = producerStatement.executeQuery();
setProducerAddresses(producerResultSet, producer);
}
} catch (SQLException e) {
log.error("Error during statement creation", e);
throw new DaoException(e);
} catch (ConnectionPoolException e) {
log.error("Error during getting connection from the pool", e);
throw new DaoException(e);
}
return producer;
}
private void setProducerAddresses(ResultSet resultSet, Producer producer) throws SQLException {
List<Address> addresses = new ArrayList<Address>();
while (resultSet.next()) {
Address address = new Address();
address.setAppartment(resultSet.getInt("a_appartment"));
address.setBuilding(resultSet.getInt("a_building"));
Country country = new Country();
country.setName(resultSet.getString("country_name"));
country.setUid(resultSet.getInt("a_c_uid"));
address.setCountry(country);
address.setDistrict(resultSet.getString("a_district"));
address.setHouseNumber(resultSet.getInt("a_house_num"));
address.setRegion(resultSet.getString("a_region"));
address.setStreet(resultSet.getString("a_street"));
address.setUid(resultSet.getInt("a_uid"));
address.setZipCode(resultSet.getInt("a_zip_code"));
addresses.add(address);
}
producer.setAddresses(addresses);
}
}