From b676355ab04479864d9c0e57b8149c567928da26 Mon Sep 17 00:00:00 2001
From: Tharre <tharre3@gmail.com>
Date: Sat, 16 Jun 2018 22:46:08 +0200
Subject: Rework RegistrationDatabaseDAO #27305
---
.../dao/RegistrationDatabaseDAO.java | 148 ++++++++++-----------
src/main/resources/sql/database.sql | 4 +-
.../einsatzverwaltung/dao/RegistrationDAOTest.java | 4 +-
3 files changed, 73 insertions(+), 83 deletions(-)
(limited to 'src')
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);
--
cgit v1.2.3-70-g09d2