diff options
Diffstat (limited to 'src/main')
3 files changed, 51 insertions, 9 deletions
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 900fd0e..3e4ba12 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 @@ -1,6 +1,7 @@ package at.ac.tuwien.sepm.assignment.groupphase.einsatzverwaltung.dao; import at.ac.tuwien.sepm.assignment.groupphase.einsatzverwaltung.dto.Employee; +import at.ac.tuwien.sepm.assignment.groupphase.einsatzverwaltung.dto.Employee.EducationLevel; 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.util.JDBCConnectionManager; @@ -10,6 +11,7 @@ import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; +import java.util.ArrayList; import java.util.List; import org.slf4j.Logger; import org.slf4j.LoggerFactory; @@ -23,8 +25,12 @@ public class EmployeeDatabaseDao implements EmployeeDAO { "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 final PreparedStatement insertEmployeeVersion, insertEmployee; + private final PreparedStatement insertEmployeeVersion, insertEmployee, listEmployee; public EmployeeDatabaseDao(JDBCConnectionManager connectionManager) throws PersistenceException { @@ -38,6 +44,8 @@ public class EmployeeDatabaseDao implements EmployeeDAO { insertEmployee = connection.prepareStatement(INSERT_EMPLOYEE, Statement.RETURN_GENERATED_KEYS); + listEmployee = connection.prepareStatement(LIST_EMPLOYEE); + } catch (SQLException e) { throw new PersistenceException(e); } @@ -82,7 +90,31 @@ public class EmployeeDatabaseDao implements EmployeeDAO { @Override public List<Employee> list() throws PersistenceException { - throw new UnsupportedOperationException(); + + try { + ResultSet rs = listEmployee.executeQuery(); + + List<Employee> employees = new ArrayList<>(); + 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); + } + + return employees; + + } catch (SQLException e) { + throw new PersistenceException(e); + } } @Override diff --git a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/service/EmployeeServiceImpl.java b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/service/EmployeeServiceImpl.java index 144ccc6..ed0fb1c 100644 --- a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/service/EmployeeServiceImpl.java +++ b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/service/EmployeeServiceImpl.java @@ -36,7 +36,12 @@ public class EmployeeServiceImpl implements EmployeeService { @Override public List<Employee> list() throws ServiceException { - return null; + + try { + return employeePersistence.list(); + } catch (PersistenceException e) { + throw new ServiceException(e); + } } @Override diff --git a/src/main/resources/sql/database.sql b/src/main/resources/sql/database.sql index bb23c91..e775550 100644 --- a/src/main/resources/sql/database.sql +++ b/src/main/resources/sql/database.sql @@ -1,26 +1,30 @@ CREATE TABLE IF NOT EXISTS VehicleVersion ( id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, - constructionType ENUM('Normal', 'Hochdach', 'Mittelhochdach') NOT NULL, - type ENUM('BKTW', 'KTW-B', 'KTW', 'RTW', 'NEF', 'NAH') NOT NULL, + constructionType VARCHAR NOT NULL, + type VARCHAR NOT NULL, hasNef BOOLEAN NOT NULL, + CHECK constructionType IN ('Normal', 'Hochdach', 'Mittelhochdach'), + CHECK type IN ('BKTW', 'KTW-B', 'KTW', 'RTW', 'NEF', 'NAH') ); CREATE TABLE IF NOT EXISTS Vehicle ( id BIGINT AUTO_INCREMENT PRIMARY KEY, version BIGINT NOT NULL, - status ENUM('abgemeldet', 'frei_wache', 'zum_berufungsort', 'am_berufungsort', 'zum_zielort', - 'am_zielort', 'frei_funk', 'deleted') NOT NULL, + status VARCHAR NOT NULL, FOREIGN KEY (version) REFERENCES VehicleVersion(id), + CHECK status IN ('abgemeldet', 'frei_wache', 'zum_berufungsort', 'am_berufungsort', 'zum_zielort', + 'am_zielort', 'frei_funk', 'deleted') ); CREATE TABLE IF NOT EXISTS EmployeeVersion ( id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, birthday DATE NOT NULL, - educationLevel ENUM('RS', 'NFS', 'NKV', 'NKA', 'NKI', 'NA') NOT NULL, + educationLevel VARCHAR NOT NULL, isDriver BOOLEAN NOT NULL, isPilot BOOLEAN NOT NULL, + CHECK educationLevel IN ('RS', 'NFS', 'NKV', 'NKA', 'NKI', 'NA') ); CREATE TABLE IF NOT EXISTS Employee ( @@ -43,10 +47,11 @@ CREATE TABLE IF NOT EXISTS Registration ( CREATE TABLE IF NOT EXISTS Operation ( id BIGINT AUTO_INCREMENT PRIMARY KEY, opCode VARCHAR(20) NOT NULL, - severity ENUM('A', 'B', 'C', 'D', 'E', 'O') NOT NULL, + severity VARCHAR NOT NULL, created TIMESTAMP NOT NULL, destination VARCHAR(100) NOT NULL, additionalInfo VARCHAR(100), + CHECK severity IN ('A', 'B', 'C', 'D', 'E', 'O') ); CREATE TABLE IF NOT EXISTS VehicleOperation ( |