Rework EmployeeDao to use pstmts directly #27305

2 files changed, 71 insertions, 125 deletions

diff --git a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/EmployeeDatabaseDAO.java b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/EmployeeDatabaseDAO.java
index 74e407f..f384937 100644
--- a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/EmployeeDatabaseDAO.java
+++ b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/EmployeeDatabaseDAO.java
@@ -9,9 +9,8 @@ import java.sql.Connection;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
-import java.sql.Savepoint;
 import java.sql.Statement;
-import java.sql.Timestamp;
+import java.time.LocalDate;
 import java.util.HashSet;
 import java.util.Set;
 import org.springframework.stereotype.Repository;
@@ -19,164 +18,112 @@ import org.springframework.stereotype.Repository;
 @Repository
 public class EmployeeDatabaseDAO implements EmployeeDAO {
 
-    // TODO [optional] either implement own interface or use spring disposable
-    // bean to close prepared statements (feedback)
+    private JDBCConnectionManager jdbcConnectionManager;
 
-    private static final String INSERT_EMPLOYEE_VERSION =
-            "INSERT INTO EmployeeVersion(name, birthday, educationLevel, isDriver, isPilot) "
-                    + "VALUES(?, ?, ?, ?, ?)";
-    private static final String INSERT_EMPLOYEE = "INSERT INTO Employee(version) VALUES(?)";
-    private static final String LIST_EMPLOYEE =
-            "SELECT emp.id, v.name, v.birthday, v.educationLevel, v.isDriver, v.isPilot "
-                    + "FROM employee emp "
-                    + "JOIN EmployeeVersion v ON v.id = emp.version";
-    private static final String UPDATE_EMPLOYEE = "UPDATE Employee SET version = ? WHERE id = ?";
-
-    private final PreparedStatement insertEmployeeVersion,
-            insertEmployee,
-            listEmployee,
-            updateEmployee;
-
-    private final Connection connection;
-
-    public EmployeeDatabaseDAO(JDBCConnectionManager connectionManager)
-            throws PersistenceException {
-
-        try {
+    public EmployeeDatabaseDAO(JDBCConnectionManager jdbcConnectionManager) {
+        this.jdbcConnectionManager = jdbcConnectionManager;
+    }
 
-            connection = connectionManager.getConnection();
-            insertEmployeeVersion =
-                    connection.prepareStatement(
-                            INSERT_EMPLOYEE_VERSION, Statement.RETURN_GENERATED_KEYS);
-            insertEmployee =
-                    connection.prepareStatement(INSERT_EMPLOYEE, Statement.RETURN_GENERATED_KEYS);
+    private long createEmployeeVersion(Connection con, Employee e)
+            throws PersistenceException, SQLException {
+        String sql =
+                "INSERT INTO EmployeeVersion(name, birthday, educationLevel, isDriver, isPilot) "
+                        + "VALUES(?, ?, ?, ?, ?)";
 
-            listEmployee = connection.prepareStatement(LIST_EMPLOYEE);
+        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();
 
-            updateEmployee = connection.prepareStatement(UPDATE_EMPLOYEE);
+            try (ResultSet rs = pstmt.getGeneratedKeys()) {
+                if (!rs.next()) throw new PersistenceException("Failed to insert EmployeeVersion");
 
-        } catch (SQLException e) {
-            throw new PersistenceException(e);
+                return rs.getLong(1);
+            }
         }
     }
 
     @Override
     public long add(Employee employee) throws PersistenceException {
+        String sql = "INSERT INTO Employee(version) VALUES(?)";
 
-        // Assumption: the given employee is already validated (from service)
-        Savepoint savepoint = null;
         try {
-            savepoint = connection.setSavepoint();
-            connection.setAutoCommit(false);
-            insertEmployeeVersion.setString(1, employee.name());
-            insertEmployeeVersion.setTimestamp(
-                    2, Timestamp.valueOf(employee.birthday().atStartOfDay()));
-            insertEmployeeVersion.setString(3, employee.educationLevel().toString());
-            insertEmployeeVersion.setBoolean(4, employee.isDriver());
-            insertEmployeeVersion.setBoolean(5, employee.isPilot());
-            insertEmployeeVersion.executeUpdate();
-            try (ResultSet resultSetEmployeeVersion = insertEmployeeVersion.getGeneratedKeys()) {
-                if (resultSetEmployeeVersion.next()) {
-                    long versionId = resultSetEmployeeVersion.getLong(1);
-
-                    insertEmployee.setLong(1, versionId);
-                    insertEmployee.executeUpdate();
-
-                    try (ResultSet resultSetEmployee = insertEmployee.getGeneratedKeys()) {
-                        if (resultSetEmployee.next()) {
-                            connection.commit();
-                            connection.setAutoCommit(true);
-                            return resultSetEmployee.getLong(1);
-                        }
-                    }
-                }
-            }
+            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();
 
-            throw new PersistenceException("Employee was not updated");
+                try (ResultSet rs = pstmt.getGeneratedKeys()) {
+                    if (!rs.next()) throw new PersistenceException("Failed to insert Employee");
 
+                    con.commit();
+                    con.setAutoCommit(true);
+                    return rs.getLong(1);
+                }
+            }
         } catch (SQLException e) {
-            rollbackAndEnableAutoCommit(savepoint);
             throw new PersistenceException(e);
         }
     }
 
     @Override
     public void update(Employee employee) throws ElementNotFoundException, PersistenceException {
+        String sql = "UPDATE Employee SET version = ? WHERE id = ?";
 
-        Savepoint savepoint = null;
         try {
-            savepoint = connection.setSavepoint();
-            connection.setAutoCommit(false);
-
-            insertEmployeeVersion.setString(1, employee.name());
-            insertEmployeeVersion.setTimestamp(
-                    2, Timestamp.valueOf(employee.birthday().atStartOfDay()));
-            insertEmployeeVersion.setString(3, employee.educationLevel().toString());
-            insertEmployeeVersion.setBoolean(4, employee.isDriver());
-            insertEmployeeVersion.setBoolean(5, employee.isPilot());
-            insertEmployeeVersion.executeUpdate();
-            try (ResultSet resultSetEmployeeVersion = insertEmployeeVersion.getGeneratedKeys()) {
-
-                if (resultSetEmployeeVersion.next()) {
-                    long versionId = resultSetEmployeeVersion.getLong(1);
-
-                    updateEmployee.setLong(1, versionId);
-                    updateEmployee.setLong(2, employee.id());
-                    int affectedRows = updateEmployee.executeUpdate();
-
-                    if (affectedRows == 1) {
-                        connection.commit();
-                        connection.setAutoCommit(true);
-                    } else {
-                        throw new ElementNotFoundException(
-                                "element not found with id: " + employee.id());
-                    }
-                }
-            }
+            Connection con = jdbcConnectionManager.getConnection();
+            con.setAutoCommit(false);
 
-        } catch (SQLException e) {
-            rollbackAndEnableAutoCommit(savepoint);
-            throw new PersistenceException(e);
-        }
-    }
+            long versionId = createEmployeeVersion(con, employee);
 
-    private void rollbackAndEnableAutoCommit(Savepoint savepoint) throws PersistenceException {
-        if (savepoint != null) {
-            try {
-                connection.rollback(savepoint);
-                connection.setAutoCommit(true);
-            } catch (SQLException e) {
-                throw new PersistenceException(e);
+            try (PreparedStatement pstmt = con.prepareStatement(sql)) {
+                pstmt.setLong(1, versionId);
+                pstmt.setLong(2, employee.id());
+
+                if (pstmt.executeUpdate() != 1)
+                    throw new ElementNotFoundException("No such employeeId exists");
             }
+        } catch (SQLException e) {
+            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 {
-            Set<Employee> employees;
-            try (ResultSet rs = listEmployee.executeQuery()) {
-
-                employees = new HashSet<>();
-                while (rs.next()) {
-
-                    Employee employee =
-                            Employee.builder()
-                                    .id(rs.getLong(1))
-                                    .name(rs.getString(2))
-                                    .birthday(rs.getTimestamp(3).toLocalDateTime().toLocalDate())
-                                    .educationLevel(EducationLevel.valueOf(rs.getString(4)))
-                                    .isDriver(rs.getBoolean(5))
-                                    .isPilot(rs.getBoolean(6))
-                                    .build();
-
-                    employees.add(employee);
+            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);
         }
diff --git a/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/EmployeeDAOTest.java b/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/EmployeeDAOTest.java
index 585e5ea..dfee5f0 100644
--- a/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/EmployeeDAOTest.java
+++ b/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/EmployeeDAOTest.java
@@ -23,7 +23,7 @@ public class EmployeeDAOTest extends JdbcTestCase {
 
     private EmployeeDAO employeePersistence;
 
-    public EmployeeDAOTest() throws PersistenceException {
+    public EmployeeDAOTest() {
         employeePersistence = new EmployeeDatabaseDAO(getJdbcConnectionManager());
     }
 
@@ -40,7 +40,6 @@ public class EmployeeDAOTest extends JdbcTestCase {
     public void testListEmployees() throws PersistenceException {
         Set<Employee> employees = employeePersistence.list();
 
-        System.out.println(LocalDate.parse("2010-10-10"));
         Employee empOne =
                 Employee.builder()
                         .id(1)