From e978119c2ea7624d05b80080a94b3ba1fcc7f040 Mon Sep 17 00:00:00 2001
From: Martin Weick <e1627760@student.tuwien.ac.at>
Date: Tue, 15 May 2018 15:40:23 +0200
Subject: Change in Try with Resources in update and add Vehicle #25948
---
.../einsatzverwaltung/dao/VehicleDatabaseDao.java | 196 +++++++++------------
1 file changed, 83 insertions(+), 113 deletions(-)
(limited to 'src')
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;
}
--
cgit v1.2.3-70-g09d2