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.AgeLimit;
import by.bsuir.hypermarket.entity.Category;
import by.bsuir.hypermarket.entity.Country;
import by.bsuir.hypermarket.entity.Department;
import by.bsuir.hypermarket.entity.Goods;
import by.bsuir.hypermarket.entity.GoodsCondition;
import by.bsuir.hypermarket.entity.Producer;
public class GoodsDao extends AbstractDao<Goods> {
private final Logger log = Logger.getLogger(getClass().getSimpleName());
private static final String SQL_FIND_ALL =
"SELECT `g_uid`, `g_bar_code`, `g_name`, `g_price`, `g_category_uid`," +
"`g_quantity`, `g_d_uid`, `g_made_date`, `g_discount`," +
"`g_al_uid`, `g_gc_uid`, `g_shelf_life`, `g_p_uid`, `g_country_uid`," +
"`p_uid`, `p_name`, `p_phone`, `p_email`," +
"`gc_uid`, `gc_name`, `gc_description`," +
"`d_uid`, `d_name`, `d_phone_num`," +
"`country_uid`, `country_name` AS `goods_from`," +
"`c_uid`, `c_name`, `c_description`, `c_department_uid`," +
"`al_uid`, `al_name`, `al_min_age`, `al_description`" +
"FROM `goods`" +
"LEFT JOIN `producer` ON `p_uid` = `g_p_uid`" +
"LEFT JOIN `goods_condition` ON `gc_uid` = `g_gc_uid`" +
"LEFT JOIN `department` ON `d_uid` = `g_d_uid`" +
"LEFT JOIN `country` ON `g_country_uid` = `country_uid`" +
"LEFT JOIN `category` ON `g_category_uid` = `c_uid`" +
"LEFT JOIN `age_limit` ON `g_al_uid` = `al_uid`";
private static final String SQL_FIND_BY_ID = SQL_FIND_ALL + " WHERE `g_uid` = ?";
private static final String SQL_FIND_BY_DEPARTMENT = SQL_FIND_ALL + "WHERE `g_d_uid` = ?";
private static final String SQL_FIND_BY_CATEGORY = SQL_FIND_ALL + "WHERE `g_category_uid` = ?";
private static final String SQL_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<Goods> findAll() throws DaoException {
List<Goods> goodsList = new ArrayList<Goods>();
try (DbConnection connection = DbConnectionPool.INSTANCE.getConnection();
PreparedStatement statement = connection.prepareStatement(SQL_FIND_ALL);
PreparedStatement producerStatement = connection.prepareStatement(SQL_FIND_PRODUCER_BY_ID)) {
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
Goods goods = new Goods();
fillUpGoodsEntity(resultSet, goods);
producerStatement.setInt(1, goods.getProducer().getUid());
ResultSet producerResultSet = producerStatement.executeQuery();
setProducerAddresses(producerResultSet, goods);
goodsList.add(goods);
}
} catch (SQLException e) {
log.error("Error during searching all goods entities", e);
throw new DaoException(e);
} catch (ConnectionPoolException e) {
log.error("Error during getting connection from the pool", e);
throw new DaoException(e);
}
return goodsList;
}
@Override
public Goods findEntityById(int id) throws DaoException {
Goods goods = new Goods();
try (DbConnection connection = DbConnectionPool.INSTANCE.getConnection();
PreparedStatement idStatement = connection.prepareStatement(SQL_FIND_BY_ID);
PreparedStatement producerStatement = connection.prepareStatement(SQL_FIND_PRODUCER_BY_ID)) {
idStatement.setInt(1, id);
ResultSet resultSet = idStatement.executeQuery();
if (resultSet.next()) {
fillUpGoodsEntity(resultSet, goods);
producerStatement.setInt(1, goods.getProducer().getUid());
resultSet = producerStatement.executeQuery();
setProducerAddresses(resultSet, goods);
}
} catch (SQLException e) {
log.error("Error during goods searching by id", e);
throw new DaoException(e);
} catch (ConnectionPoolException e) {
log.error("Error during getting connection from the pool", e);
throw new DaoException(e);
}
return goods;
}
/**
* Find goods list in specified department
* @param id - department id
* @return goods list
* @throws DaoException
*/
public List<Goods> findEntitiesByDepartment (int id) throws DaoException {
List<Goods> goodsList = new ArrayList<Goods>();
try (DbConnection connection = DbConnectionPool.INSTANCE.getConnection();
PreparedStatement idStatement = connection.prepareStatement(SQL_FIND_BY_DEPARTMENT);
PreparedStatement producerStatement = connection.prepareStatement(SQL_FIND_PRODUCER_BY_ID)) {
idStatement.setInt(1, id);
ResultSet resultSet = idStatement.executeQuery();
while (resultSet.next()) {
Goods goods = new Goods();
fillUpGoodsEntity(resultSet, goods);
producerStatement.setInt(1, goods.getProducer().getUid());
ResultSet producerAddresses = producerStatement.executeQuery();
setProducerAddresses(producerAddresses, goods);
goodsList.add(goods);
}
} catch (SQLException e) {
log.error("Error during searching goods entities by department", e);
throw new DaoException(e);
} catch (ConnectionPoolException e) {
log.error("Error during getting connection from the pool", e);
throw new DaoException(e);
}
return goodsList;
}
/**
* Find goods list according to the specified {@link Category}
* @param id - category id
* @return goods list
* @throws DaoException
*/
public List<Goods> findEntitiesByCategory (int id) throws DaoException {
List<Goods> goodsList = new ArrayList<Goods>();
try (DbConnection connection = DbConnectionPool.INSTANCE.getConnection();
PreparedStatement idStatement = connection.prepareStatement(SQL_FIND_BY_CATEGORY);
PreparedStatement producerStatement = connection.prepareStatement(SQL_FIND_PRODUCER_BY_ID)) {
idStatement.setInt(1, id);
ResultSet resultSet = idStatement.executeQuery();
while (resultSet.next()) {
Goods goods = new Goods();
fillUpGoodsEntity(resultSet, goods);
producerStatement.setInt(1, goods.getProducer().getUid());
ResultSet producerAddresses = producerStatement.executeQuery();
setProducerAddresses(producerAddresses, goods);
goodsList.add(goods);
}
} catch (SQLException e) {
log.error("Error during searching by category", e);
throw new DaoException(e);
} catch (ConnectionPoolException e) {
log.error("Error during getting connection from the pool", e);
throw new DaoException(e);
}
return goodsList;
}
private Goods fillUpGoodsEntity(ResultSet resultSet, Goods goods) throws SQLException {
goods.setUid(resultSet.getInt("g_uid"));
goods.setBarCode(resultSet.getString("g_bar_code"));
goods.setDiscount(resultSet.getInt("g_discount"));
goods.setName(resultSet.getString("g_name"));
goods.setPrice(resultSet.getBigDecimal("g_price"));
goods.setQuantity(resultSet.getInt("g_quantity"));
goods.setShelfLife(resultSet.getInt("g_shelf_life"));
goods.setMadeDate(resultSet.getDate("g_made_date"));
AgeLimit ageLimit = new AgeLimit();
ageLimit.setUid(resultSet.getInt("al_uid"));
ageLimit.setName(resultSet.getString("al_name"));
ageLimit.setMinAge(resultSet.getInt("al_min_age"));
ageLimit.setDescription(resultSet.getString("al_description"));
goods.setAgeLimit(ageLimit);
GoodsCondition goodsCondition = new GoodsCondition();
goodsCondition.setUid(resultSet.getInt("gc_uid"));
goodsCondition.setName(resultSet.getString("gc_name"));
goodsCondition.setDescription(resultSet.getString("gc_description"));
goods.setGoodsCondition(goodsCondition);
Country country = new Country();
country.setUid(resultSet.getInt("country_uid"));
country.setName(resultSet.getString("goods_from"));
goods.setCountry(country);
Department department = new Department();
department.setUid(resultSet.getInt("d_uid"));
department.setName(resultSet.getString("d_name"));
department.setPhoneNumber(resultSet.getString("d_phone_num"));
goods.setDepartment(department);
Category category = new Category();
category.setUid(resultSet.getInt("c_uid"));
category.setName(resultSet.getString("c_name"));
category.setDescription(resultSet.getString("c_description"));
goods.setCategory(category);
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"));
goods.setProducer(producer);
return goods;
}
private void setProducerAddresses(ResultSet resultSet, Goods goods) 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);
}
goods.getProducer().setAddresses(addresses);
}
}