Change in Try with Resources in update and add Vehicle #25948

1 files changed, 83 insertions, 113 deletions

diff --git a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/VehicleDatabaseDao.java b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/VehicleDatabaseDao.java
index b2d253a..c6c03f6 100644
--- a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/VehicleDatabaseDao.java
+++ b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/VehicleDatabaseDao.java
@@ -11,6 +11,7 @@ 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;
@@ -28,167 +29,136 @@ public class VehicleDatabaseDao implements VehicleDAO {
         String query1 =
                 "INSERT INTO VehicleVersion (name,hasNef,constructionType,type) VALUES (?,?,?,?)";
         String query2 = "INSERT INTO Vehicle (version,status) VALUES (?,?)";
-        PreparedStatement p1 = null;
-        PreparedStatement p2 = null;
-        PreparedStatement p3 = null;
+
         String status = "ABGEMELDET";
         String name = "";
         int id = -1;
         try {
-            p1 =
-                    jdbcConnectionManager
-                            .getConnection()
-                            .prepareStatement(query1, PreparedStatement.RETURN_GENERATED_KEYS);
-            p1.setString(1, name);
-            p1.setBoolean(2, vehicle.hasNef());
-            p1.setString(3, vehicle.constructionType().name());
-            if (vehicle.type() == VehicleType.KTW_B) {
-                p1.setString(4, "KTW-B");
-            } else {
+            Connection connection = jdbcConnectionManager.getConnection();
+            connection.setAutoCommit(false);
+            try (PreparedStatement p1 =
+                    connection.prepareStatement(query1, PreparedStatement.RETURN_GENERATED_KEYS)) {
+
+                p1.setString(1, name);
+                p1.setBoolean(2, vehicle.hasNef());
+                p1.setString(3, vehicle.constructionType().name());
+
                 p1.setString(4, vehicle.type().name());
-            }
-            p1.executeUpdate();
 
-            try (ResultSet keyResultSet = p1.getGeneratedKeys()) {
+                p1.executeUpdate();
+
+                try (ResultSet keyResultSet = p1.getGeneratedKeys()) {
 
-                if (keyResultSet.next()) {
-                    id = keyResultSet.getInt(1);
+                    if (keyResultSet.next()) {
+                        id = keyResultSet.getInt(1);
+                    }
                 }
+
+                name = vehicle.type().name() + "-" + id;
             }
 
-            name = vehicle.type().name() + "-" + id;
+            query1 = "UPDATE VehicleVersion SET name=? WHERE id=?";
+            try (PreparedStatement p3 = connection.prepareStatement(query1)) {
+                p3.setString(1, name);
+                p3.setInt(2, id);
+                p3.executeUpdate();
+            }
 
-        } catch (SQLException e) {
-            throw new PersistenceException("SQL Excpetion : " + e.toString());
-        } finally {
-            try {
-                p1.close();
+            try (PreparedStatement p2 = connection.prepareStatement(query2)) {
 
-            } catch (SQLException e) {
-                throw new PersistenceException("SQL Excpetion : " + e.toString());
+                p2.setInt(1, id);
+                p2.setString(2, status);
+                p2.executeUpdate();
             }
-        }
-        try {
-            query1 = "UPDATE VehicleVersion SET name=? WHERE id=?";
-            p3 = jdbcConnectionManager.getConnection().prepareStatement(query1);
-            p3.setString(1, name);
-            p3.setInt(2, id);
-            p3.executeUpdate();
+            connection.commit();
+            connection.setAutoCommit(true);
         } catch (SQLException e) {
-            throw new PersistenceException("SQL Excpetion : " + e.toString());
-        } finally {
-            try {
-                p3.close();
-            } catch (SQLException e) {
-                throw new PersistenceException("SQL Excpetion : " + e.toString());
-            }
-        }
-        try {
-            p2 = jdbcConnectionManager.getConnection().prepareStatement(query2);
-            p2.setInt(1, id);
-            p2.setString(2, status);
-            p2.executeUpdate();
-        } catch (SQLException e) {
-            throw new PersistenceException("SQL Excpetion : " + e.toString());
-        } finally {
-            try {
-                p2.close();
-            } catch (SQLException e) {
-                throw new PersistenceException("SQL Excpetion : " + e.toString());
-            }
+            throw new PersistenceException(e);
         }
         return id;
     }
 
     @Override
     public void update(Vehicle vehicle) throws ElementNotFoundException, PersistenceException {
-        String query="SELECT id FROM vehicle WHERE version=?";
-        PreparedStatement p=null;
-        long vehicleID=-1;
-        long vehicleVersion=-1;
+        String query = "SELECT id FROM vehicle WHERE version=?";
+
+        long vehicleID = -1;
+        long vehicleVersion = -1;
         try {
-            p=jdbcConnectionManager.getConnection().prepareStatement(query);
-            p.setLong(1,vehicle.id());
-            p.executeQuery();
-            try (ResultSet rs = p.getResultSet()) {
-                while (rs.next()) {
-                    vehicleID = rs.getLong(1);
+            Connection connection = jdbcConnectionManager.getConnection();
+            connection.setAutoCommit(false);
+            try (PreparedStatement p =
+                    connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS)) {
+
+                p.setLong(1, vehicle.id());
+                p.executeQuery();
+                try (ResultSet rs = p.getResultSet()) {
+                    while (rs.next()) {
+                        vehicleID = rs.getLong(1);
+                    }
                 }
             }
-            query="INSERT INTO VehicleVersion (name,hasNef,constructionType,type) VALUES (?,?,?,?)";
-            String name="";
-            p=jdbcConnectionManager.getConnection().prepareStatement(query);
-            p.setString(1, name);
-            p.setBoolean(2, vehicle.hasNef());
-            p.setString(3, vehicle.constructionType().name());
-            if (vehicle.type() == VehicleType.KTW_B) {
-                p.setString(4, "KTW-B");
-            } else {
+
+            query =
+                    "INSERT INTO VehicleVersion (name,hasNef,constructionType,type) VALUES (?,?,?,?)";
+            String name = "";
+            try (PreparedStatement p =
+                    connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS)) {
+                p.setString(1, name);
+                p.setBoolean(2, vehicle.hasNef());
+                p.setString(3, vehicle.constructionType().name());
+
                 p.setString(4, vehicle.type().name());
-            }
-            p.executeUpdate();
 
-            try (ResultSet keyResultSet = p.getGeneratedKeys()) {
+                p.executeUpdate();
 
-                if (keyResultSet.next()) {
-                    vehicleVersion = keyResultSet.getInt(1);
+                try (ResultSet keyResultSet = p.getGeneratedKeys()) {
+
+                    if (keyResultSet.next()) {
+                        vehicleVersion = keyResultSet.getInt(1);
+                    }
                 }
             }
-
             name = vehicle.type().name() + "-" + vehicleVersion;
 
             query = "UPDATE VehicleVersion SET name=? WHERE id=?";
-            p = jdbcConnectionManager.getConnection().prepareStatement(query);
-            p.setString(1, name);
-            p.setLong(2, vehicleVersion);
-            p.executeUpdate();
+            try (PreparedStatement p = connection.prepareStatement(query)) {
 
-            query="UPDATE Vehicle SET version=? WHERE id=?";
-            p=jdbcConnectionManager.getConnection().prepareStatement(query);
-            p.setLong(1,vehicleVersion);
-            p.setLong(2,vehicleID);
-            p.executeUpdate();
+                p.setString(1, name);
+                p.setLong(2, vehicleVersion);
+                p.executeUpdate();
+            }
+            query = "UPDATE Vehicle SET version=? WHERE id=?";
+            try (PreparedStatement p = connection.prepareStatement(query)) {
 
-        } catch (SQLException e) {
-            throw new PersistenceException("SQL Excpetion : " + e.toString());
-        } finally {
-            try {
-                p.close();
-            } catch (SQLException e) {
-                throw new PersistenceException("SQL Excpetion : " + e.toString());
+                p.setLong(1, vehicleVersion);
+                p.setLong(2, vehicleID);
+                p.executeUpdate();
             }
+            connection.commit();
+            connection.setAutoCommit(true);
+        } catch (SQLException e) {
+            throw new PersistenceException(e);
         }
     }
 
     @Override
     public Set<Vehicle> list() throws PersistenceException {
-        PreparedStatement pstmt = null;
         Set<Vehicle> result = new HashSet<>();
-        try {
-            pstmt =
-                    jdbcConnectionManager
-                            .getConnection()
-                            .prepareStatement(
-                                    "Select * from VehicleVersion, "
-                                            + "Vehicle where VehicleVersion.id=Vehicle.version");
+
+        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("Die Werte konnten nicht geladen werden.", e);
-        } finally {
-            if (pstmt != null) {
-                try {
-                    pstmt.close();
-                } catch (SQLException e) {
-                    throw new PersistenceException(
-                            "Verbindung zur Datenbank konnte nicht geschlossen werden!", e);
-                }
-            }
         }
         return result;
     }