1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
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();
}
}
|
