diff options
Diffstat (limited to 'src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao')
8 files changed, 971 insertions, 0 deletions
diff --git a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/EmployeeDAO.java b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/EmployeeDAO.java new file mode 100644 index 0000000..675e951 --- /dev/null +++ b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/EmployeeDAO.java @@ -0,0 +1,44 @@ +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 java.util.Set; + +public interface EmployeeDAO { + + /** + * Persist the given employee. + * + * @param employee that should be stored + * @return the id that was assigned + * @throws PersistenceException if the employee could not be persisted + */ + long add(Employee employee) throws PersistenceException; + + /** + * Update the given employee. + * + * @param employee that should be updated + * @throws ElementNotFoundException if no employee with the given id exists + * @throws PersistenceException if the employee could not be updated + */ + void update(Employee employee) throws ElementNotFoundException, PersistenceException; + + /** + * Get all stored employees. + * + * @return list containing all stored employees + * @throws PersistenceException if loading the stored employees failed + */ + Set<Employee> list() throws PersistenceException; + + /** + * Remove employee with the given id from the store. + * + * @param id of the employee that should be removed + * @throws ElementNotFoundException if no employee with the given id exists + * @throws PersistenceException if the employee could not be removed + */ + void remove(long id) throws ElementNotFoundException, PersistenceException; +} diff --git a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/EmployeeDatabaseDAO.java b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/EmployeeDatabaseDAO.java new file mode 100644 index 0000000..32dd6d2 --- /dev/null +++ b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/EmployeeDatabaseDAO.java @@ -0,0 +1,144 @@ +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.util.JDBCConnectionManager; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.time.LocalDate; +import java.util.HashSet; +import java.util.Set; +import org.springframework.stereotype.Repository; + +@Repository +public class EmployeeDatabaseDAO implements EmployeeDAO { + + private JDBCConnectionManager jdbcConnectionManager; + + public EmployeeDatabaseDAO(JDBCConnectionManager jdbcConnectionManager) { + this.jdbcConnectionManager = jdbcConnectionManager; + } + + private long createEmployeeVersion(Connection con, Employee e) + throws PersistenceException, SQLException { + String sql = + "INSERT INTO EmployeeVersion(name, birthday, educationLevel, isDriver, isPilot) " + + "VALUES(?, ?, ?, ?, ?)"; + + try (PreparedStatement pstmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) { + pstmt.setString(1, e.name()); + pstmt.setObject(2, e.birthday()); + pstmt.setInt(3, e.educationLevel().ordinal()); + pstmt.setBoolean(4, e.isDriver()); + pstmt.setBoolean(5, e.isPilot()); + pstmt.executeUpdate(); + + try (ResultSet rs = pstmt.getGeneratedKeys()) { + if (!rs.next()) throw new PersistenceException("Failed to insert EmployeeVersion"); + + return rs.getLong(1); + } + } + } + + @Override + public long add(Employee employee) throws PersistenceException { + String sql = "INSERT INTO Employee(version) VALUES(?)"; + + try { + Connection con = jdbcConnectionManager.getConnection(); + con.setAutoCommit(false); + + long versionId = createEmployeeVersion(con, employee); + + try (PreparedStatement pstmt = + con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) { + pstmt.setLong(1, versionId); + pstmt.executeUpdate(); + + try (ResultSet rs = pstmt.getGeneratedKeys()) { + if (!rs.next()) { + con.rollback(); + throw new PersistenceException("Failed to insert Employee"); + } + + con.commit(); + return rs.getLong(1); + } + } + } catch (SQLException e) { + jdbcConnectionManager.rollbackConnection(); + throw new PersistenceException(e); + } + } + + @Override + public void update(Employee employee) throws ElementNotFoundException, PersistenceException { + String sql = "UPDATE Employee SET version = ? WHERE id = ?"; + + try { + Connection con = jdbcConnectionManager.getConnection(); + con.setAutoCommit(false); + + long versionId = createEmployeeVersion(con, employee); + + try (PreparedStatement pstmt = con.prepareStatement(sql)) { + pstmt.setLong(1, versionId); + pstmt.setLong(2, employee.id()); + + if (pstmt.executeUpdate() != 1) { + con.rollback(); + throw new ElementNotFoundException("No such employeeId exists"); + } + } + + con.commit(); + } catch (SQLException e) { + jdbcConnectionManager.rollbackConnection(); + throw new PersistenceException(e); + } + } + + @Override + public Set<Employee> list() throws PersistenceException { + String sql = + "SELECT emp.id, v.name, v.birthday, v.educationLevel, v.isDriver, v.isPilot " + + "FROM employee emp " + + "JOIN EmployeeVersion v ON v.id = emp.version"; + + try { + Connection con = jdbcConnectionManager.getConnection(); + Set<Employee> employees = new HashSet<>(); + + try (PreparedStatement pstmt = con.prepareStatement(sql)) { + try (ResultSet rs = pstmt.executeQuery()) { + while (rs.next()) { + employees.add( + Employee.builder() + .id(rs.getLong(1)) + .name(rs.getString(2)) + .birthday(rs.getObject(3, LocalDate.class)) + .educationLevel(EducationLevel.valueOf(rs.getString(4))) + .isDriver(rs.getBoolean(5)) + .isPilot(rs.getBoolean(6)) + .build()); + } + } + } + + return employees; + } catch (SQLException e) { + throw new PersistenceException(e); + } + } + + @Override + public void remove(long id) throws ElementNotFoundException, PersistenceException { + throw new UnsupportedOperationException(); + } +} diff --git a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/OperationDAO.java b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/OperationDAO.java new file mode 100644 index 0000000..e496898 --- /dev/null +++ b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/OperationDAO.java @@ -0,0 +1,48 @@ +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.Operation; +import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Operation.Status; +import java.util.EnumSet; +import java.util.Set; + +public interface OperationDAO { + + /** + * Persist the given operation. + * + * @param operation that should be stored + * @return the id that was assigned + * @throws PersistenceException if the operation could not be persisted + */ + long add(Operation operation) throws PersistenceException; + + /** + * Update the given operation. + * + * @param operation that should be updated + * @throws ElementNotFoundException if no operation with the given id exists + * @throws PersistenceException if the operation could not be updated + */ + void update(Operation operation) throws ElementNotFoundException, PersistenceException; + + /** + * Returns the operation with the given id. + * + * @param operationId id of the operation that should be returned + * @return operation with the given id + * @throws ElementNotFoundException if no operation with the given id exists + * @throws PersistenceException if the operation could not be loaded + */ + Operation get(long operationId) throws ElementNotFoundException, PersistenceException; + + /** + * Get all stored operations with matching status. + * + * @param statuses set containing all statuses that should be matched + * @return list containing all matched operations + * @throws PersistenceException if loading the stored operations failed + */ + Set<Operation> list(EnumSet<Status> statuses) throws PersistenceException; +} diff --git a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/OperationDatabaseDAO.java b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/OperationDatabaseDAO.java new file mode 100644 index 0000000..238a2a8 --- /dev/null +++ b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/OperationDatabaseDAO.java @@ -0,0 +1,253 @@ +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.Operation; +import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Operation.Severity; +import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Operation.Status; +import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Vehicle; +import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Vehicle.ConstructionType; +import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Vehicle.VehicleType; +import at.ac.tuwien.sepm.assignment.groupphase.util.JDBCConnectionManager; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.time.OffsetDateTime; +import java.time.ZoneId; +import java.util.EnumSet; +import java.util.HashSet; +import java.util.Set; +import java.util.stream.Collectors; +import org.springframework.lang.NonNull; +import org.springframework.stereotype.Repository; + +@Repository +public class OperationDatabaseDAO implements OperationDAO { + + private JDBCConnectionManager jdbcConnectionManager; + private VehicleDAO vehicleDAO; + private RegistrationDatabaseDAO registrationDAO; + + public OperationDatabaseDAO( + JDBCConnectionManager jdbcConnectionManager, + VehicleDAO vehicleDAO, + RegistrationDatabaseDAO registrationDAO) { + this.jdbcConnectionManager = jdbcConnectionManager; + this.vehicleDAO = vehicleDAO; + this.registrationDAO = registrationDAO; + } + + @Override + public long add(@NonNull Operation o) throws PersistenceException { + String sql = + "INSERT INTO Operation(opCode, severity, created, destination, additionalInfo," + + " status) VALUES (?, ?, ?, ?, ?, ?)"; + long operationId; + + try { + Connection con = jdbcConnectionManager.getConnection(); + con.setAutoCommit(false); + try (PreparedStatement pstmt = con.prepareStatement(sql)) { + pstmt.setString(1, o.opCode()); + pstmt.setInt(2, o.severity().ordinal()); + pstmt.setObject(3, OffsetDateTime.ofInstant(o.created(), ZoneId.systemDefault())); + pstmt.setString(4, o.destination()); + pstmt.setString(5, o.additionalInfo()); + pstmt.setInt(6, o.status().ordinal()); + pstmt.executeUpdate(); + + try (ResultSet rs = pstmt.getGeneratedKeys()) { + if (!rs.next()) throw new PersistenceException("Failed to persist operation"); + + operationId = rs.getLong(1); + } + } + + createVehicleOperation(con, operationId, o.vehicles()); + con.commit(); + return operationId; + } catch (SQLException e) { + jdbcConnectionManager.rollbackConnection(); + throw new PersistenceException(e); + } + } + + @Override + public void update(@NonNull Operation o) throws ElementNotFoundException, PersistenceException { + // Note this will, by design, not update created + String sql = + "UPDATE Operation SET opCode = ?, severity = ?, destination = ?," + + " additionalInfo = ?, status = ? WHERE id = ?"; + String sql2 = "DELETE FROM VehicleOperation WHERE operationId = ?"; + + try { + Connection con = jdbcConnectionManager.getConnection(); + con.setAutoCommit(false); + try (PreparedStatement pstmt = con.prepareStatement(sql)) { + pstmt.setString(1, o.opCode()); + pstmt.setInt(2, o.severity().ordinal()); + pstmt.setString(3, o.destination()); + pstmt.setString(4, o.additionalInfo()); + pstmt.setInt(5, o.status().ordinal()); + pstmt.setLong(6, o.id()); + + if (pstmt.executeUpdate() != 1) + throw new ElementNotFoundException("No such operationId exists"); + } + + try (PreparedStatement pstmt = con.prepareStatement(sql2)) { + pstmt.setLong(1, o.id()); + pstmt.executeUpdate(); + } + + createVehicleOperation(con, o.id(), o.vehicles()); + con.commit(); + } catch (SQLException e) { + jdbcConnectionManager.rollbackConnection(); + throw new PersistenceException(e); + } + } + + private void createVehicleOperation(Connection con, long operationId, Set<Vehicle> vehicles) + throws SQLException { + String sql = + "INSERT INTO VehicleOperation(vehicleId, operationId)" + + " SELECT version, ? FROM Vehicle WHERE id = ?"; + String sqlUpdateVehicleStatus = + "UPDATE Vehicle SET status = 'ZUM_BERUFUNGSORT' WHERE id = ?"; + + try (PreparedStatement pstmt = con.prepareStatement(sql); + PreparedStatement stmtUpdateVehicleStatus = + con.prepareStatement(sqlUpdateVehicleStatus)) { + pstmt.setLong(1, operationId); + + for (long id : (Iterable<Long>) vehicles.stream().map(Vehicle::id)::iterator) { + pstmt.setLong(2, id); + stmtUpdateVehicleStatus.setLong(1, id); + pstmt.addBatch(); + stmtUpdateVehicleStatus.addBatch(); + } + + pstmt.executeBatch(); + stmtUpdateVehicleStatus.executeBatch(); + } + } + + @Override + public Operation get(long operationId) throws ElementNotFoundException, PersistenceException { + String sql = "Select * from operation where id = ?"; + + try { + Connection con = jdbcConnectionManager.getConnection(); + try (PreparedStatement pstmt = con.prepareStatement(sql)) { + pstmt.setLong(1, operationId); + pstmt.execute(); + + try (ResultSet rs = pstmt.getResultSet()) { + if (!rs.next()) + throw new ElementNotFoundException("No such element could be found"); + + return operationFromRS(rs); + } + } + } catch (SQLException e) { + throw new PersistenceException(e); + } + } + + @Override + public Set<Operation> list(EnumSet<Status> statuses) throws PersistenceException { + // This hack exists because H2 currently has a bug that prevents IN (?) with an array of + // ids, i.e. pstmt.setArray(1, con.createArrayOf("INT", intarray) from working. See + // commented code below. + + // SELECT * FROM Operation WHERE status IN ('COMPLETED', 'CANCELLED') is BUGGED on H2! + // for this reason we use the ordinal values instead + String str = + statuses.stream() + .map(e -> Integer.toString(e.ordinal())) + .collect(Collectors.joining(",")); + + String sql = "SELECT * FROM Operation WHERE status IN (" + str + ")"; + Set<Operation> operations = new HashSet<>(); + + try { + Connection con = jdbcConnectionManager.getConnection(); + + try (PreparedStatement pstmt = con.prepareStatement(sql)) { + // Object[] arr = statuses.stream().map(Enum::ordinal).toArray(); + // pstmt.setArray(1, con.createArrayOf("INT", arr)); + + try (ResultSet rs = pstmt.executeQuery()) { + while (rs.next()) operations.add(operationFromRS(rs)); + } + } + + return operations; + } catch (SQLException e) { + throw new PersistenceException(e); + } + } + + private Operation operationFromRS(ResultSet rs) throws PersistenceException, SQLException { + Long operationId = rs.getLong("id"); + + return Operation.builder() + .id(operationId) + .opCode(rs.getString("opCode")) + .severity(Severity.valueOf(rs.getString("severity"))) + .status(Status.valueOf(rs.getString("status"))) + .vehicles(getVehiclesFromOperationId(operationId)) + .created((rs.getObject("created", OffsetDateTime.class)).toInstant()) + .destination(rs.getString("destination")) + .additionalInfo(rs.getString("additionalInfo")) + .build(); + } + + private Set<Vehicle> getVehiclesFromOperationId(long operationId) throws PersistenceException { + /*String sql = + "SELECT id FROM Vehicle WHERE version IN" + + " (SELECT vehicleId FROM VehicleOperation WHERE operationId = ?)";*/ + String sql = + "SELECT vv.* FROM VehicleOperation vo JOIN VehicleVersion vv ON vv.id = vo.vehicleId WHERE operationId = ?"; + + Set<Vehicle> vehicles = new HashSet<>(); + + try { + Connection con = jdbcConnectionManager.getConnection(); + try (PreparedStatement pstmt = con.prepareStatement(sql)) { + pstmt.setLong(1, operationId); + pstmt.execute(); + + try (ResultSet rs = pstmt.getResultSet()) { + while (rs.next()) { + vehicles.add(vehicleFromRS(rs)); + } + } + } + } catch (SQLException e) { + throw new PersistenceException(e); + } catch (ElementNotFoundException e) { + throw new PersistenceException("VehicleOperation contained nonexistent vehicle", e); + } + + return vehicles; + } + + private Vehicle vehicleFromRS(ResultSet rs) + throws SQLException, PersistenceException, ElementNotFoundException { + String name = rs.getString("VehicleVersion.name"); + long vehicleId = Long.parseLong(name.split("-")[1]); + return Vehicle.builder() + .id(vehicleId) + .name(rs.getString("VehicleVersion.name")) + .constructionType( + ConstructionType.values()[rs.getInt("VehicleVersion.constructionType")]) + .type(VehicleType.valueOf(rs.getString("VehicleVersion.type"))) + .status(vehicleDAO.get(vehicleId).status()) + .hasNef(rs.getBoolean("VehicleVersion.hasNef")) + .registrations(registrationDAO.list(rs.getLong("VehicleVersion.id"))) + .build(); + } +} diff --git a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/RegistrationDAO.java b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/RegistrationDAO.java new file mode 100644 index 0000000..4a35f86 --- /dev/null +++ b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/RegistrationDAO.java @@ -0,0 +1,28 @@ +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.Registration; +import java.util.Set; + +public interface RegistrationDAO { + + /** + * Persist the given registration. + * + * @param vehicleId the id of the target vehicle + * @param registrations that should be stored + * @return a list of the ids that were assigned + * @throws PersistenceException if the registration could not be persisted + */ + Set<Long> add(long vehicleId, Set<Registration> registrations) throws PersistenceException; + + /** + * Make registration with the given id inactive. + * + * @param id of the registration that should be made inactive + * @throws ElementNotFoundException if no registration with the given id exists + * @throws PersistenceException if the registration could not be made inactive + */ + void remove(long id) throws ElementNotFoundException, PersistenceException; +} 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 new file mode 100644 index 0000000..b624056 --- /dev/null +++ b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/RegistrationDatabaseDAO.java @@ -0,0 +1,189 @@ +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; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.time.Instant; +import java.time.LocalDate; +import java.time.OffsetDateTime; +import java.time.ZoneId; +import java.util.ArrayList; +import java.util.HashSet; +import java.util.List; +import java.util.Set; +import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.stereotype.Repository; + +@Repository +public class RegistrationDatabaseDAO implements RegistrationDAO { + + private JDBCConnectionManager jdbcConnectionManager; + private EmployeeDAO employeePersistence; + + @Autowired + public RegistrationDatabaseDAO( + JDBCConnectionManager jdbcConnectionManager, EmployeeDAO employeePersistence) { + this.jdbcConnectionManager = jdbcConnectionManager; + 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); + try (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); + try (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 { + String sql = + "INSERT INTO Registration (vehicleId, employeeId, start, end, active) VALUES (?,?,?,?,?)"; + String sql2 = "UPDATE Vehicle SET status = 'FREI_WACHE' WHERE id = ?;"; + + Set<Long> vehicleIds = new HashSet<>(); + + try { + Connection con = jdbcConnectionManager.getConnection(); + con.setAutoCommit(false); + + try (PreparedStatement pstmt = + con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) { + + // 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, 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); + pstmt.addBatch(); + } + + pstmt.executeBatch(); + + try (ResultSet rs = pstmt.getGeneratedKeys()) { + while (rs.next()) vehicleIds.add(rs.getLong(1)); + } + } + + try (PreparedStatement pstmt = con.prepareStatement(sql2)) { + pstmt.setLong(1, vehicleId); + if (pstmt.executeUpdate() != 1) { + con.rollback(); + throw new PersistenceException("Failed to persist registration"); + } + } + + con.commit(); + return vehicleIds; + } catch (SQLException e) { + jdbcConnectionManager.rollbackConnection(); + throw new PersistenceException(e); + } + } + + @Override + public void remove(long id) throws ElementNotFoundException, PersistenceException { + String sql = "UPDATE Registration SET active = 0, end = ? WHERE id = ?"; + + try { + Connection con = jdbcConnectionManager.getConnection(); + + try (PreparedStatement pstmt = con.prepareStatement(sql)) { + pstmt.setObject(1, OffsetDateTime.ofInstant(Instant.now(), ZoneId.systemDefault())); + pstmt.setLong(2, id); + + if (pstmt.executeUpdate() != 1) + throw new ElementNotFoundException("No such registrationId exists"); + } + + } catch (SQLException e) { + throw new PersistenceException(e); + } + } + + protected List<Registration> list(long vehicleId) throws PersistenceException { + + 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, vehicleId); // is vehicle version id! + 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; + } catch (SQLException e) { + throw new PersistenceException(e); + } + } +} diff --git a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/VehicleDAO.java b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/VehicleDAO.java new file mode 100644 index 0000000..46d1853 --- /dev/null +++ b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/VehicleDAO.java @@ -0,0 +1,54 @@ +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.Vehicle; +import java.util.Set; + +public interface VehicleDAO { + + /** + * Persist the given vehicle. + * + * @param vehicle that should be stored + * @return the id that was assigned + * @throws PersistenceException if the vehicle could not be persisted + */ + long add(Vehicle vehicle) throws PersistenceException; + + /** + * Update the given vehicle. + * + * @param vehicle that should be updated + * @throws ElementNotFoundException if no vehicle with the given id exists + * @throws PersistenceException if the vehicle could not be updated + */ + void update(Vehicle vehicle) throws ElementNotFoundException, PersistenceException; + + /** + * Get all stored vehicles. + * + * @return list containing all stored vehicles + * @throws PersistenceException if loading the stored vehicles failed + */ + Set<Vehicle> list() throws PersistenceException; + + /** + * Returns the vehicle with the given id. + * + * @param vehicleId id of the vehicle that should be returned + * @return vehicle with the given id + * @throws ElementNotFoundException if no vehicle with the given id exists + * @throws PersistenceException if the vehicle could not be loaded + */ + Vehicle get(long vehicleId) throws ElementNotFoundException, PersistenceException; + + /** + * Remove vehicle with the given id from the store. + * + * @param id of the vehicle that should be removed + * @throws ElementNotFoundException if no vehicle with the given id exists + * @throws PersistenceException if the vehicle could not be removed + */ + void remove(long id) throws ElementNotFoundException, PersistenceException; +} 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 new file mode 100644 index 0000000..8cef65e --- /dev/null +++ b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/missioncontrol/dao/VehicleDatabaseDAO.java @@ -0,0 +1,211 @@ +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.Vehicle; +import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Vehicle.ConstructionType; +import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Vehicle.Status; +import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Vehicle.VehicleType; +import at.ac.tuwien.sepm.assignment.groupphase.util.JDBCConnectionManager; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.HashSet; +import java.util.Set; +import org.springframework.stereotype.Repository; + +@Repository +public class VehicleDatabaseDAO implements VehicleDAO { + + private final JDBCConnectionManager jdbcConnectionManager; + private RegistrationDatabaseDAO registrationDatabaseDao; + + public VehicleDatabaseDAO( + JDBCConnectionManager j, RegistrationDatabaseDAO registrationDatabaseDao) { + jdbcConnectionManager = j; + this.registrationDatabaseDao = registrationDatabaseDao; + } + + @Override + public long add(Vehicle v) throws PersistenceException { + String sql = + "INSERT INTO VehicleVersion (name,hasNef,constructionType,type) VALUES (?,?,?,?)"; + String sql2 = "INSERT INTO Vehicle (version,status) VALUES (?,?)"; + String sql3 = "UPDATE VehicleVersion SET name=? WHERE id=?"; + + try { + Connection con = jdbcConnectionManager.getConnection(); + con.setAutoCommit(false); + String name = ""; + long version, id; + + try (PreparedStatement pstmt = + con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) { + pstmt.setString(1, name); + pstmt.setBoolean(2, v.hasNef()); + pstmt.setInt(3, v.constructionType().ordinal()); + pstmt.setString(4, v.type().name()); + pstmt.executeUpdate(); + + try (ResultSet rs = pstmt.getGeneratedKeys()) { + if (!rs.next()) + throw new PersistenceException("Failed to insert into VehicleVersion"); + + version = rs.getLong(1); + } + } + + try (PreparedStatement pstmt = + con.prepareStatement(sql2, Statement.RETURN_GENERATED_KEYS)) { + pstmt.setLong(1, version); + pstmt.setInt(2, Status.ABGEMELDET.ordinal()); + pstmt.executeUpdate(); + + try (ResultSet rs = pstmt.getGeneratedKeys()) { + if (!rs.next()) { + con.rollback(); + throw new PersistenceException("Failed to insert into Vehicle"); + } + + id = rs.getLong(1); + } + + name = v.type().name() + "-" + id; + } + + try (PreparedStatement pstmt = con.prepareStatement(sql3)) { + pstmt.setString(1, name); + pstmt.setLong(2, version); + + if (pstmt.executeUpdate() != 1) { + con.rollback(); + throw new PersistenceException("Failed to update VehicleVersion"); + } + } + + con.commit(); + return id; + } catch (SQLException e) { + jdbcConnectionManager.rollbackConnection(); + throw new PersistenceException(e); + } + } + + @Override + public void update(Vehicle v) throws ElementNotFoundException, PersistenceException { + String sql = "SELECT version FROM Vehicle WHERE id = ?"; + String sql2 = + "MERGE INTO VehicleVersion(name, constructionType, type, hasNef)" + + " KEY(name, constructionType, type, hasNef) VALUES(?, ?, ?, ?)"; + String sql3 = "UPDATE Vehicle SET version = ?, status = ? WHERE id = ?"; + + long versionId; + + try { + Connection con = jdbcConnectionManager.getConnection(); + con.setAutoCommit(false); + try (PreparedStatement pstmt = con.prepareStatement(sql)) { + pstmt.setLong(1, v.id()); + + try (ResultSet rs = pstmt.executeQuery()) { + if (!rs.next()) throw new ElementNotFoundException("No such vehicleId exists"); + + versionId = rs.getLong(1); + } + } + + try (PreparedStatement pstmt = + con.prepareStatement(sql2, Statement.RETURN_GENERATED_KEYS)) { + pstmt.setString(1, v.type().name() + "-" + v.id()); + pstmt.setString(2, v.constructionType().name()); + pstmt.setString(3, v.type().name()); + pstmt.setBoolean(4, v.hasNef()); + pstmt.executeUpdate(); + + try (ResultSet rs = pstmt.getGeneratedKeys()) { + if (rs.next()) { + // version changed, update it + versionId = rs.getLong(1); + } + } + } + + try (PreparedStatement pstmt = con.prepareStatement(sql3)) { + pstmt.setLong(1, versionId); + pstmt.setString(2, v.status().name()); + pstmt.setLong(3, v.id()); + pstmt.executeUpdate(); + } + + con.commit(); + } catch (SQLException e) { + jdbcConnectionManager.rollbackConnection(); + throw new PersistenceException(e); + } + } + + @Override + public Set<Vehicle> list() throws PersistenceException { + Set<Vehicle> result = new HashSet<>(); + + String sql = + "Select * from VehicleVersion, Vehicle where VehicleVersion.id=Vehicle.version"; + + try (PreparedStatement pstmt = + jdbcConnectionManager.getConnection().prepareStatement(sql)) { + pstmt.executeQuery(); + try (ResultSet rs = pstmt.getResultSet()) { + while (rs.next()) { + result.add(vehicleFromRS(rs)); + } + } + } catch (SQLException e) { + throw new PersistenceException(e); + } + return result; + } + + @Override + public Vehicle get(long id) throws ElementNotFoundException, PersistenceException { + String sql = + "SELECT *" + + " FROM Vehicle a" + + " INNER JOIN VehicleVersion b" + + " ON version = b.id" + + " WHERE a.id = ?"; + + try { + Connection con = jdbcConnectionManager.getConnection(); + try (PreparedStatement pstmt = con.prepareStatement(sql)) { + pstmt.setLong(1, id); + + try (ResultSet rs = pstmt.executeQuery()) { + if (!rs.first()) throw new ElementNotFoundException("No such vehicle exists"); + + return vehicleFromRS(rs); + } + } + } catch (SQLException e) { + throw new PersistenceException(e); + } + } + + @Override + public void remove(long id) throws ElementNotFoundException, PersistenceException { + throw new UnsupportedOperationException(); + } + + private Vehicle vehicleFromRS(ResultSet rs) throws SQLException, PersistenceException { + return Vehicle.builder() + .id(rs.getLong("Vehicle.id")) + .name(rs.getString("name")) + .constructionType(ConstructionType.values()[rs.getInt("constructionType")]) + .type(VehicleType.valueOf(rs.getString("type"))) + .status(Status.values()[rs.getInt("status")]) + .hasNef(rs.getBoolean("hasNef")) + .registrations(registrationDatabaseDao.list(rs.getLong("Vehicle.version"))) + .build(); + } +} |