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