1 files changed, 144 insertions, 0 deletions
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();
+    }
+}