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.Category;
import by.bsuir.hypermarket.entity.Department;
public class DepartmentDao extends AbstractDao<Department> {
private final Logger log = Logger.getLogger(getClass().getSimpleName());
private static final String SQL_FIND_ALL = "SELECT `d_uid`, `d_name`, `d_phone_num` FROM `department`";
private static final String SQL_FIND_BY_ID = SQL_FIND_ALL + " WHERE `d_uid` = ?";
private static final String SQL_CATEGORY_BY_DEPARTMENT = "SELECT `c_uid`, `c_name`, `c_description`, `c_department_uid`"
+ "FROM `category`"
+ "WHERE `c_department_uid` = ?";
@Override
public List<Department> findAll() throws DaoException {
List<Department> departments = new ArrayList<Department>();
try (DbConnection connection = DbConnectionPool.INSTANCE.getConnection();
PreparedStatement categoryStatement = connection.prepareStatement(SQL_CATEGORY_BY_DEPARTMENT);
PreparedStatement departmentStatement = connection.prepareStatement(SQL_FIND_ALL)) {
ResultSet resultSet = departmentStatement.executeQuery();
while (resultSet.next()) {
Department department = new Department();
fillUpDepartment(resultSet, department);
categoryStatement.setInt(1, department.getUid());
ResultSet categoriesResultSet = categoryStatement.executeQuery();
setDepartmentCategories(categoriesResultSet, department);
departments.add(department);
}
} 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 departments;
}
@Override
public Department findEntityById(int id) throws DaoException {
Department department = new Department();
try (DbConnection connection = DbConnectionPool.INSTANCE.getConnection();
PreparedStatement categoryStatement = connection.prepareStatement(SQL_CATEGORY_BY_DEPARTMENT);
PreparedStatement idStatement = connection.prepareStatement(SQL_FIND_BY_ID)) {
idStatement.setInt(1, id);
ResultSet resultSet = idStatement.executeQuery();
if (resultSet.next()) {
fillUpDepartment(resultSet, department);
categoryStatement.setInt(1, department.getUid());
ResultSet categoriesResultSet = categoryStatement.executeQuery();
setDepartmentCategories(categoriesResultSet, department);
}
} 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 department;
}
private void fillUpDepartment(ResultSet resultSet, Department department) throws SQLException {
department.setUid(resultSet.getInt("d_uid"));
department.setName(resultSet.getString("d_name"));
department.setPhoneNumber(resultSet.getString("d_phone_num"));
}
private void setDepartmentCategories(ResultSet resultSet, Department department) throws SQLException {
List<Category> categories = new ArrayList<>();
while (resultSet.next()) {
Category category = new Category();
fillUpCategory(resultSet, category);
categories.add(category);
}
department.setCategories(categories);
}
private void fillUpCategory(ResultSet resultSet, Category category) throws SQLException {
category.setUid(resultSet.getInt("c_uid"));
category.setName(resultSet.getString("c_name"));
category.setDescription(resultSet.getString("c_description"));
}
}