Rework RegistrationDatabaseDAO #27305

3 files changed, 73 insertions, 83 deletions

diff --git a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/RegistrationDatabaseDAO.java b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/RegistrationDatabaseDAO.java
index c3b1227..ed458d6 100644
--- a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/RegistrationDatabaseDAO.java
+++ b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/RegistrationDatabaseDAO.java
@@ -10,88 +10,70 @@ import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
-import java.sql.Timestamp;
+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.slf4j.Logger;
-import org.slf4j.LoggerFactory;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.stereotype.Repository;
 
 @Repository
 public class RegistrationDatabaseDAO implements RegistrationDAO {
 
-    private static final Logger LOG = LoggerFactory.getLogger(RegistrationDatabaseDAO.class);
-
-    private static final String ADD_REGISTRATION =
-            "INSERT INTO Registration (vehicleId, employeeId, start, end, active) VALUES (?,?,?,?,?);";
-    private static final String UPDATE_VEHICLE =
-            "UPDATE Vehicle SET status = 'FREI_WACHE' WHERE id = ?;";
-    private static final String FETCH_REGISTRATIONS =
-            "SELECT * FROM Registration WHERE vehicleId = ?";
-
-    private PreparedStatement addRegistration;
-    private PreparedStatement updateVehicle;
-    private PreparedStatement fetchRegistrations;
-
-    private Connection connection;
+    private JDBCConnectionManager jdbcConnectionManager;
     private EmployeeDAO employeePersistence;
 
     @Autowired
     public RegistrationDatabaseDAO(
-            JDBCConnectionManager connectionManager, EmployeeDAO employeePersistence)
-            throws PersistenceException {
+            JDBCConnectionManager jdbcConnectionManager, EmployeeDAO employeePersistence) {
+        this.jdbcConnectionManager = jdbcConnectionManager;
         this.employeePersistence = employeePersistence;
-        try {
-            connection = connectionManager.getConnection();
-            addRegistration =
-                    connection.prepareStatement(ADD_REGISTRATION, Statement.RETURN_GENERATED_KEYS);
-            updateVehicle = connection.prepareStatement(UPDATE_VEHICLE);
-            fetchRegistrations = connection.prepareStatement(FETCH_REGISTRATIONS);
-        } catch (SQLException e) {
-            throw new PersistenceException(e);
-        }
     }
 
     @Override
     public Set<Long> add(long vehicleId, Set<Registration> registrations)
             throws PersistenceException {
-        Set<Long> returnValues = new HashSet<>();
+        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.setAutoCommit(false);
-            for (Registration registration : registrations) {
-                addRegistration.setLong(1, vehicleId);
-                addRegistration.setLong(2, registration.employee().id());
-                addRegistration.setTimestamp(3, Timestamp.from(registration.start()));
-                addRegistration.setTimestamp(4, Timestamp.from(registration.end()));
-                addRegistration.setBoolean(
-                        5, true); // ASSUMPTION: Registration gets created as active
-                addRegistration.executeUpdate();
-                try (ResultSet rs = addRegistration.getGeneratedKeys()) {
-                    if (rs.next()) {
-                        returnValues.add(rs.getLong(1));
-                    } else {
-                        throw new PersistenceException(
-                                "Anmeldung konnte nicht gespeichert werden.");
-                    }
+            Connection con = jdbcConnectionManager.getConnection();
+            con.setAutoCommit(false);
+
+            try (PreparedStatement pstmt =
+                    con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
+                pstmt.setLong(1, vehicleId);
+
+                for (Registration r : registrations) {
+                    pstmt.setLong(2, 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));
                 }
             }
 
-            updateVehicle.setLong(1, vehicleId);
-            updateVehicle.executeUpdate();
+            try (PreparedStatement pstmt = con.prepareStatement(sql2)) {
+                pstmt.setLong(1, vehicleId);
+                if (pstmt.executeUpdate() != 1)
+                    throw new PersistenceException("Failed to persist registration");
+            }
 
-            connection.commit();
-            connection.setAutoCommit(true);
-            return returnValues;
+            con.commit();
+            return vehicleIds;
         } catch (SQLException e) {
-            try {
-                connection.rollback();
-                connection.setAutoCommit(true);
-            } catch (SQLException e1) {
-                LOG.error("Rollback failed! Error message: {}", e.getMessage());
-            }
+            jdbcConnectionManager.rollbackConnection();
             throw new PersistenceException(e);
         }
     }
@@ -101,34 +83,42 @@ public class RegistrationDatabaseDAO implements RegistrationDAO {
         throw new UnsupportedOperationException();
     }
 
-    public List<Registration> list(long vehicleId) throws PersistenceException {
+    protected List<Registration> list(long vehicleId) throws PersistenceException {
+        String sql = "SELECT * FROM Registration WHERE vehicleId = ?";
+
         List<Registration> registrationList = new ArrayList<>();
         try {
-            fetchRegistrations.setLong(1, vehicleId);
-            ResultSet rs = fetchRegistrations.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.getTimestamp("start").toInstant())
-                                .end(rs.getTimestamp("end").toInstant())
-                                .employee(emp)
-                                .build();
-                registrationList.add(registration);
+            Connection con = jdbcConnectionManager.getConnection();
+
+            try (PreparedStatement pstmt = con.prepareStatement(sql)) {
+                pstmt.setLong(1, 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.getTimestamp("start").toInstant())
+                                        .end(rs.getTimestamp("end").toInstant())
+                                        .employee(emp)
+                                        .build();
+                        registrationList.add(registration);
+                    }
+                }
             }
+
+            return registrationList;
         } catch (SQLException e) {
             throw new PersistenceException(e);
         }
-
-        return registrationList;
     }
 }
diff --git a/src/main/resources/sql/database.sql b/src/main/resources/sql/database.sql
index 3d382d1..c7a99b5 100644
--- a/src/main/resources/sql/database.sql
+++ b/src/main/resources/sql/database.sql
@@ -33,8 +33,8 @@ CREATE TABLE IF NOT EXISTS Registration (
   id BIGINT AUTO_INCREMENT PRIMARY KEY,
   vehicleId BIGINT NOT NULL,
   employeeId BIGINT NOT NULL,
-  start TIMESTAMP NOT NULL,
-  end TIMESTAMP NOT NULL,
+  start TIMESTAMP WITH TIME ZONE NOT NULL,
+  end TIMESTAMP WITH TIME ZONE NOT NULL,
   active BOOLEAN NOT NULL,
   FOREIGN KEY (vehicleId) REFERENCES VehicleVersion(id),
   FOREIGN KEY (employeeId) REFERENCES EmployeeVersion(id),
diff --git a/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/RegistrationDAOTest.java b/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/RegistrationDAOTest.java
index 289a0ae..2fb578b 100644
--- a/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/RegistrationDAOTest.java
+++ b/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/RegistrationDAOTest.java
@@ -105,8 +105,8 @@ public class RegistrationDAOTest extends JdbcTestCase {
                         .build();
         Registration registration =
                 Registration.builder()
-                        .start(Instant.MIN)
-                        .end(Instant.MAX)
+                        .start(Instant.now())
+                        .end(Instant.now())
                         .employee(employee)
                         .build();
         registrations.add(registration);