diff options
2 files changed, 79 insertions, 35 deletions
diff --git a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/RegistrationDatabaseDAO.java b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/RegistrationDatabaseDAO.java index 1f11024..2f52349 100644 --- a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/RegistrationDatabaseDAO.java +++ b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/RegistrationDatabaseDAO.java @@ -3,6 +3,7 @@ package at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dao; 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.missioncontrol.dto.Employee; +import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Employee.EducationLevel; import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Registration; import at.ac.tuwien.sepm.assignment.groupphase.util.JDBCConnectionManager; import java.sql.Connection; @@ -10,6 +11,7 @@ import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; +import java.time.LocalDate; import java.time.OffsetDateTime; import java.time.ZoneId; import java.util.ArrayList; @@ -32,6 +34,38 @@ public class RegistrationDatabaseDAO implements RegistrationDAO { this.employeePersistence = employeePersistence; } + private long getVehicleVersionId(long vehicleId) throws PersistenceException { + String sqlGetVehicleVersionId = "SELECT * FROM vehicle WHERE id = ?"; + try (PreparedStatement stmt = + jdbcConnectionManager.getConnection().prepareStatement(sqlGetVehicleVersionId)) { + stmt.setLong(1, vehicleId); + ResultSet rs = stmt.executeQuery(); + if (rs.next()) { + return rs.getLong("version"); + } else { + throw new PersistenceException("vehicle id not found"); + } + } catch (SQLException e) { + throw new PersistenceException(e); + } + } + + private long getEmployeeVersionId(long employeeId) throws PersistenceException { + String sqlGetEmployeeVersionId = "SELECT * FROM employee WHERE id = ?"; + try (PreparedStatement stmt = + jdbcConnectionManager.getConnection().prepareStatement(sqlGetEmployeeVersionId)) { + stmt.setLong(1, employeeId); + ResultSet rs = stmt.executeQuery(); + if (rs.next()) { + return rs.getLong("version"); + } else { + throw new PersistenceException("employee id not found"); + } + } catch (SQLException e) { + throw new PersistenceException(e); + } + } + @Override public Set<Long> add(long vehicleId, Set<Registration> registrations) throws PersistenceException { @@ -47,10 +81,12 @@ public class RegistrationDatabaseDAO implements RegistrationDAO { try (PreparedStatement pstmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) { - pstmt.setLong(1, vehicleId); + + // vehicleId is a Vehicle.id as it comes from GUI => fetch VehicleVersion.id + pstmt.setLong(1, getVehicleVersionId(vehicleId)); for (Registration r : registrations) { - pstmt.setLong(2, r.employee().id()); + pstmt.setLong(2, getEmployeeVersionId(r.employee().id())); pstmt.setObject(3, OffsetDateTime.ofInstant(r.start(), ZoneId.systemDefault())); pstmt.setObject(4, OffsetDateTime.ofInstant(r.end(), ZoneId.systemDefault())); pstmt.setBoolean(5, true); @@ -86,40 +122,48 @@ public class RegistrationDatabaseDAO implements RegistrationDAO { } protected List<Registration> list(long vehicleId) throws PersistenceException { - String sql = "SELECT * FROM Registration WHERE vehicleId = ?"; - - List<Registration> registrationList = new ArrayList<>(); - try { - Connection con = jdbcConnectionManager.getConnection(); - try (PreparedStatement pstmt = con.prepareStatement(sql)) { - pstmt.setLong(1, vehicleId); + // vehicleId is a Vehicle.id as it comes from GUI => fetch VehicleVersion.id + long vehicleVersionId = getVehicleVersionId(vehicleId); - try (ResultSet rs = pstmt.executeQuery()) { - while (rs.next()) { - long employeeId = rs.getLong("employeeId"); - // TODO: replace the following with employeePersistence.get once implemented - Employee emp = - employeePersistence - .list() - .stream() - .filter(employee -> employee.id() == employeeId) - .findAny() - .orElse(null); - Registration registration = - Registration.builder() - .id(rs.getLong("id")) - .start( - (rs.getObject("start", OffsetDateTime.class)) - .toInstant()) - .end( - (rs.getObject("end", OffsetDateTime.class)) - .toInstant()) - .employee(emp) - .build(); - registrationList.add(registration); - } - } + String sql = + "SELECT * FROM Registration r " + + "JOIN EmployeeVersion ev ON ev.id = r.employeeId " + + "JOIN VehicleVersion vv ON vv.id = r.vehicleId " + + "WHERE r.vehicleId = ?"; + + try (PreparedStatement stmt = jdbcConnectionManager.getConnection().prepareStatement(sql)) { + + List<Registration> registrationList = new ArrayList<>(); + stmt.setLong(1, vehicleVersionId); + ResultSet rs = stmt.executeQuery(); + while (rs.next()) { + + Employee employee = + Employee.builder() + .id(rs.getLong("EmployeeVersion.id")) + .name(rs.getString("EmployeeVersion.name")) + .birthday(rs.getObject("EmployeeVersion.birthday", LocalDate.class)) + .educationLevel( + EducationLevel.valueOf( + rs.getString("EmployeeVersion.educationLevel"))) + .isDriver(rs.getBoolean("EmployeeVersion.isDriver")) + .isPilot(rs.getBoolean("EmployeeVersion.isPilot")) + .build(); + + Registration registration = + Registration.builder() + .id(rs.getLong("Registration.id")) + .start( + (rs.getObject("Registration.start", OffsetDateTime.class)) + .toInstant()) + .end( + (rs.getObject("Registration.end", OffsetDateTime.class)) + .toInstant()) + .employee(employee) + .build(); + + registrationList.add(registration); } return registrationList; diff --git a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/VehicleDatabaseDAO.java b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/VehicleDatabaseDAO.java index 9bd7ec7..eb3cc61 100644 --- a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/VehicleDatabaseDAO.java +++ b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/VehicleDatabaseDAO.java @@ -205,7 +205,7 @@ public class VehicleDatabaseDAO implements VehicleDAO { .type(VehicleType.valueOf(rs.getString("type"))) .status(Status.values()[rs.getInt("status")]) .hasNef(rs.getBoolean("hasNef")) - .registrations(registrationDatabaseDao.list(rs.getLong("id"))) + .registrations(registrationDatabaseDao.list(rs.getLong("Vehicle.id"))) .build(); } } |