From f80a69e941f0949dbec476755abfd373c26846ce Mon Sep 17 00:00:00 2001
From: Tharre <tharre3@gmail.com>
Date: Sat, 16 Jun 2018 20:18:14 +0200
Subject: Rework EmployeeDao to use pstmts directly #27305
---
.../einsatzverwaltung/dao/EmployeeDatabaseDAO.java | 193 ++++++++-------------
.../einsatzverwaltung/dao/EmployeeDAOTest.java | 3 +-
2 files changed, 71 insertions(+), 125 deletions(-)
(limited to 'src')
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)
--
cgit v1.2.3-70-g09d2