From fbe6dcc8e7017312f1b05726e52a20c07ff96cb3 Mon Sep 17 00:00:00 2001 From: Viktoria Pundy Date: Wed, 16 May 2018 09:37:49 +0200 Subject: Added methods [#24990] Copied methods from branch Change_Operation_Status to load operations from database --- .../einsatzverwaltung/dao/DBOperationDAO.java | 230 ++++++++++++++++++++- .../service/OperationServiceImpl.java | 13 +- .../userInterface/ArchivOperationController.java | 6 +- 3 files changed, 244 insertions(+), 5 deletions(-) (limited to 'src/main/java/at') diff --git a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/DBOperationDAO.java b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/DBOperationDAO.java index 68185d6..16233e1 100644 --- a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/DBOperationDAO.java +++ b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/DBOperationDAO.java @@ -1,27 +1,65 @@ package at.ac.tuwien.sepm.assignment.groupphase.einsatzverwaltung.dao; +import at.ac.tuwien.sepm.assignment.groupphase.einsatzverwaltung.dto.Employee; +import at.ac.tuwien.sepm.assignment.groupphase.einsatzverwaltung.dto.Employee.EducationLevel; import at.ac.tuwien.sepm.assignment.groupphase.einsatzverwaltung.dto.Operation; +import at.ac.tuwien.sepm.assignment.groupphase.einsatzverwaltung.dto.Operation.Severity; import at.ac.tuwien.sepm.assignment.groupphase.einsatzverwaltung.dto.Operation.Status; +import at.ac.tuwien.sepm.assignment.groupphase.einsatzverwaltung.dto.Registration; import at.ac.tuwien.sepm.assignment.groupphase.einsatzverwaltung.dto.Vehicle; +import at.ac.tuwien.sepm.assignment.groupphase.einsatzverwaltung.dto.Vehicle.ConstructionType; +import at.ac.tuwien.sepm.assignment.groupphase.einsatzverwaltung.dto.Vehicle.VehicleType; import at.ac.tuwien.sepm.assignment.groupphase.exception.ElementNotFoundException; import at.ac.tuwien.sepm.assignment.groupphase.exception.PersistenceException; import at.ac.tuwien.sepm.assignment.groupphase.util.JDBCConnectionManager; +import java.lang.invoke.MethodHandles; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; import java.util.EnumSet; +import java.util.HashSet; +import java.util.LinkedList; +import java.util.List; import java.util.Set; +import org.slf4j.Logger; +import org.slf4j.LoggerFactory; import org.springframework.stereotype.Repository; @Repository public class DBOperationDAO implements OperationDAO { private JDBCConnectionManager jdbcConnectionManager; + private static final Logger LOG = LoggerFactory.getLogger(MethodHandles.lookup().lookupClass()); - public DBOperationDAO(JDBCConnectionManager j) { + public DBOperationDAO(JDBCConnectionManager j) throws PersistenceException { jdbcConnectionManager = j; + try { + + getVehicleId = jdbcConnectionManager.getConnection().prepareStatement(GET_VEHICLE_ID); + + getVehicle = jdbcConnectionManager.getConnection().prepareStatement(GET_VEHICLE); + + getVehicleVersion = + jdbcConnectionManager.getConnection().prepareStatement(GET_VEHICLE_VERSION); + + getRegistrations = + jdbcConnectionManager.getConnection().prepareStatement(GET_REGISTRATIONS); + + getEmployee = jdbcConnectionManager.getConnection().prepareStatement(GET_EMPLOYEE); + + getEmployeeVersion = + jdbcConnectionManager.getConnection().prepareStatement(GET_EMPLOYEE_VERSION); + + } catch (SQLException e) { + + LOG.error( + "SQLException occurred while preparing Statements. Error message: {} ", + e.getMessage()); + + throw new PersistenceException(e); + } } @Override @@ -165,7 +203,195 @@ public class DBOperationDAO implements OperationDAO { @Override public Set list(EnumSet statuses) throws PersistenceException { - return null; + StringBuilder listString = new StringBuilder("SELECT * FROM Operation WHERE status IN ("); + boolean first = true; + for (Status status : statuses) { + if (first) { + first = false; + } else { + listString.append(", "); + } + listString.append("\'" + status.name() + "\'"); + } + listString.append(") ;"); + Set operations = new HashSet<>(); + try { + try (PreparedStatement listStatement = + jdbcConnectionManager.getConnection().prepareStatement(listString.toString())) { + try (ResultSet resultSet = listStatement.executeQuery()) { + while (resultSet.next()) { + long operationId = resultSet.getLong("id"); + operations.add( + Operation.builder() + .id(operationId) + .opCode(resultSet.getString("opCode")) + .severity(Severity.valueOf(resultSet.getString("severity"))) + .status(Status.valueOf(resultSet.getString("status"))) + .vehicles(getVehiclesFromOperationId(operationId)) + .created(resultSet.getTimestamp("created").toInstant()) + .destination(resultSet.getString("destination")) + .additionalInfo(resultSet.getString("additionalInfo")) + .build()); + } + } + } + } catch (SQLException e) { + LOG.error( + "SQLException happened while preparing statement. Error message: {}", + e.getMessage()); + throw new PersistenceException(e); + } + return operations; + } + + private static String GET_VEHICLE_ID = + "SELECT vehicleId FROM VehicleOperation WHERE operationId = ? ;"; + + private final PreparedStatement getVehicleId; + + private Set getVehiclesFromOperationId(long operationId) throws PersistenceException { + Set vehicles = new HashSet<>(); + try { + getVehicleId.setLong(1, operationId); + try (ResultSet resultSet = getVehicleId.executeQuery()) { + while (resultSet.next()) { + vehicles.add(getVehicle(resultSet.getLong("vehicleId"))); + } + } + } catch (SQLException e) { + LOG.error( + "SQLException occurred while getting VehicleId from OperationId. Error message: {}", + e.getMessage()); + throw new PersistenceException(e); + } + return vehicles; + } + + private static String GET_VEHICLE = "SELECT * FROM Vehicle WHERE id = ? ;"; + + private final PreparedStatement getVehicle; + + private Vehicle getVehicle(long vehicleId) throws PersistenceException { + try { + getVehicle.setLong(1, vehicleId); + try (ResultSet resultSet = getVehicle.executeQuery()) { + resultSet.next(); + return getVehicleVersion( + resultSet.getLong("id"), + resultSet.getLong("version"), + resultSet.getString("status")); + } + } catch (SQLException e) { + LOG.error( + "SQLException occurred while getting Vehicle by id. Error message: {}", + e.getMessage()); + throw new PersistenceException(e); + } + } + + private static String GET_VEHICLE_VERSION = "SELECT * FROM VehicleVersion WHERE id = ? ;"; + + private final PreparedStatement getVehicleVersion; + + private Vehicle getVehicleVersion(long vehicleId, long versionId, String status) + throws PersistenceException { + try { + getVehicleVersion.setLong(1, versionId); + try (ResultSet resultSet = getVehicleVersion.executeQuery()) { + resultSet.next(); + return Vehicle.builder() + .id(vehicleId) + .name(resultSet.getString("name")) + .constructionType( + ConstructionType.valueOf(resultSet.getString("constructionType"))) + .type(VehicleType.valueOf(resultSet.getString("type"))) + .status(Vehicle.Status.valueOf(status)) + .hasNef(resultSet.getBoolean("hasNef")) + .registrations(getRegistrations(vehicleId)) + .build(); + } + } catch (SQLException e) { + LOG.error( + "SQLException occurred while getting VehicleVersion. Error message: {}", + e.getMessage()); + throw new PersistenceException(e); + } + } + + private static String GET_REGISTRATIONS = "SELECT * FROM Registration WHERE id = ? ;"; + + private final PreparedStatement getRegistrations; + + private List getRegistrations(long vehicleId) throws PersistenceException { + List registrations = new LinkedList<>(); + try { + getRegistrations.setLong(1, vehicleId); + try (ResultSet resultSet = getRegistrations.executeQuery()) { + while (resultSet.next()) { + long registrationId = resultSet.getLong("id"); + registrations.add( + Registration.builder() + .id(registrationId) + .start(resultSet.getTimestamp("start").toInstant()) + .end(resultSet.getTimestamp("end").toInstant()) + .employee(getEmployee(resultSet.getLong("employeeId"))) + .build()); + } + } + } catch (SQLException e) { + LOG.error( + "SQLException occurred while getting Registration. Error message: {}", + e.getMessage()); + throw new PersistenceException(e); + } + return registrations; + } + + private static String GET_EMPLOYEE = "SELECT version FROM Employee WHERE id = ? ;"; + + private final PreparedStatement getEmployee; + + private Employee getEmployee(long employeeId) throws PersistenceException { + try { + getEmployee.setLong(1, employeeId); + try (ResultSet resultSet = getEmployee.executeQuery()) { + resultSet.next(); + return getEmployeeVersion(employeeId, resultSet.getLong("version")); + } + } catch (SQLException e) { + LOG.error( + "SQLException occurred while getting Employee. Error message: {}", + e.getMessage()); + throw new PersistenceException(e); + } + } + + private static String GET_EMPLOYEE_VERSION = "SELECT * FROM EmployeeVersion WHERE id = ? ;"; + + private final PreparedStatement getEmployeeVersion; + + private Employee getEmployeeVersion(long employeeId, long versionId) + throws PersistenceException { + try { + getEmployeeVersion.setLong(1, versionId); + try (ResultSet resultSet = getEmployeeVersion.executeQuery()) { + resultSet.next(); + return Employee.builder() + .id(employeeId) + .name(resultSet.getString("name")) + .birthday(resultSet.getDate("birthday").toLocalDate()) + .educationLevel( + EducationLevel.valueOf(resultSet.getString("educationLevel"))) + .isDriver(resultSet.getBoolean("isDriver")) + .isPilot(resultSet.getBoolean("isPilot")) + .build(); + } + } catch (SQLException e) { + LOG.error( + "SQLException occurred while getting EmployeeVersion. Error message: {}", + e.getMessage()); + throw new PersistenceException(e); + } } @Override diff --git a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/service/OperationServiceImpl.java b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/service/OperationServiceImpl.java index ba4eb7f..8a9d61c 100644 --- a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/service/OperationServiceImpl.java +++ b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/service/OperationServiceImpl.java @@ -19,12 +19,16 @@ import java.util.SortedSet; import java.util.regex.Matcher; import java.util.regex.Pattern; import org.springframework.stereotype.Service; +import java.lang.invoke.MethodHandles; +import org.slf4j.Logger; +import org.slf4j.LoggerFactory; @Service public class OperationServiceImpl implements OperationService { // TODO: anders? private OperationDAO operationDAO; + private static final Logger LOG = LoggerFactory.getLogger(MethodHandles.lookup().lookupClass()); private final VehicleDAO vehicleDAO; @@ -201,6 +205,13 @@ public class OperationServiceImpl implements OperationService { @Override public Set list(EnumSet statuses) throws ServiceException { - return null; + try { + return operationDAO.list(statuses); + } catch (PersistenceException e) { + LOG.debug( + "Caught PersistenceException. Throwing ServiceException. Message: {}", + e.getMessage()); + throw new ServiceException(e); + } } } diff --git a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/userInterface/ArchivOperationController.java b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/userInterface/ArchivOperationController.java index c0de7cc..57a3e3b 100644 --- a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/userInterface/ArchivOperationController.java +++ b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/userInterface/ArchivOperationController.java @@ -5,8 +5,10 @@ import at.ac.tuwien.sepm.assignment.groupphase.einsatzverwaltung.dto.Operation.S import at.ac.tuwien.sepm.assignment.groupphase.einsatzverwaltung.service.OperationService; import at.ac.tuwien.sepm.assignment.groupphase.exception.ServiceException; import java.util.EnumSet; +import java.util.HashSet; import java.util.LinkedList; import java.util.List; +import java.util.Set; import javafx.fxml.FXML; import javafx.scene.control.Alert; import javafx.scene.control.Alert.AlertType; @@ -51,7 +53,7 @@ public class ArchivOperationController { }*/ List list = new LinkedList<>(); try { - list = operationService.list(EnumSet.of(Status.CANCELLED, Status.COMPLETED)); + list.addAll(operationService.list(EnumSet.of(Status.CANCELLED, Status.COMPLETED))); } catch (ServiceException e) { Alert alert = new Alert(AlertType.ERROR); alert.setTitle("Fehler"); @@ -59,7 +61,7 @@ public class ArchivOperationController { alert.setContentText("Die Einsätze konnten nicht geladen werden!"); alert.showAndWait(); } - for (int i =0;i