Merge branch 'employee_list' into fahrzeug_anmelden

6 files changed, 224 insertions, 9 deletions

diff --git a/pom.xml b/pom.xml
index 95747aa..78dbb4c 100644
--- a/pom.xml
+++ b/pom.xml
@@ -26,6 +26,7 @@
         <mockito.version>2.18.0</mockito.version>
         <google-java-format.version>1.5</google-java-format.version>
         <testfx.version>4.0.13-alpha</testfx.version>
+        <dbunit.version>2.5.1</dbunit.version>
         <!-- plugins -->
         <maven-surefire-plugin.version>2.21.0</maven-surefire-plugin.version>
         <maven-shade-plugin.version>3.1.1</maven-shade-plugin.version>
@@ -115,6 +116,11 @@
             <version>${testfx.version}</version>
             <scope>test</scope>
         </dependency>
+        <dependency>
+            <groupId>org.dbunit</groupId>
+            <artifactId>dbunit</artifactId>
+            <version>${dbunit.version}</version>
+        </dependency>
     </dependencies>
 
     <build>
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 (
diff --git a/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/employee/EmployeePersistenceTest.java b/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/employee/EmployeePersistenceTest.java
new file mode 100644
index 0000000..f8fe0f3
--- /dev/null
+++ b/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/employee/EmployeePersistenceTest.java
@@ -0,0 +1,155 @@
+package at.ac.tuwien.sepm.assignment.groupphase.employee;
+
+import static junit.framework.TestCase.fail;
+
+import at.ac.tuwien.sepm.assignment.groupphase.einsatzverwaltung.dao.EmployeeDAO;
+import at.ac.tuwien.sepm.assignment.groupphase.einsatzverwaltung.dao.EmployeeDatabaseDao;
+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.PersistenceException;
+import at.ac.tuwien.sepm.assignment.groupphase.util.JDBCConnectionManager;
+import java.nio.charset.Charset;
+import java.sql.SQLException;
+import java.time.LocalDate;
+import java.time.format.DateTimeFormatter;
+import java.util.List;
+import org.dbunit.IDatabaseTester;
+import org.dbunit.JdbcDatabaseTester;
+import org.dbunit.dataset.DataSetException;
+import org.dbunit.dataset.IDataSet;
+import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
+import org.dbunit.operation.DatabaseOperation;
+import org.h2.tools.RunScript;
+import org.junit.Assert;
+import org.junit.Before;
+import org.junit.BeforeClass;
+import org.junit.Test;
+
+public class EmployeePersistenceTest {
+
+    private static final String JDBC_DRIVER = org.h2.Driver.class.getName();
+    private static final String JDBC_URL = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1";
+    private static final String USER = "";
+    private static final String PASSWORD = "";
+
+    private EmployeeDAO employeePersistence;
+
+    public EmployeePersistenceTest() throws PersistenceException {
+        employeePersistence = new EmployeeDatabaseDao(new JDBCConnectionManager(JDBC_URL));
+    }
+
+    @BeforeClass
+    public static void createSchema() throws SQLException {
+        RunScript.execute(
+                JDBC_URL,
+                USER,
+                PASSWORD,
+                "classpath:sql/database.sql",
+                Charset.forName("UTF8"),
+                false);
+    }
+
+    @Before
+    public void importDataSet() throws Exception {
+        IDataSet dataSet = readDataSet();
+        cleanlyInsert(dataSet);
+    }
+
+    private IDataSet readDataSet() throws DataSetException {
+        return new FlatXmlDataSetBuilder()
+                .build(
+                        getClass()
+                                .getClassLoader()
+                                .getResourceAsStream("employeeServiceTestData.xml"));
+    }
+
+    private void cleanlyInsert(IDataSet dataSet) throws Exception {
+        IDatabaseTester databaseTester =
+                new JdbcDatabaseTester(JDBC_DRIVER, JDBC_URL, USER, PASSWORD);
+
+        databaseTester.setSetUpOperation(DatabaseOperation.CLEAN_INSERT);
+        databaseTester.setDataSet(dataSet);
+        databaseTester.onSetup();
+    }
+
+    @Test
+    public void testListEmployees() {
+
+        try {
+            List<Employee> employees = employeePersistence.list();
+
+            Employee empOne =
+                    Employee.builder()
+                            .id(1)
+                            .name("Adam")
+                            .birthday(
+                                    LocalDate.parse(
+                                            "10.10.2010",
+                                            DateTimeFormatter.ofPattern("dd.MM.yyyy")))
+                            .educationLevel(EducationLevel.RS)
+                            .isDriver(true)
+                            .isPilot(false)
+                            .build();
+
+            Employee empTwo =
+                    Employee.builder()
+                            .id(2)
+                            .name("Max")
+                            .birthday(
+                                    LocalDate.parse(
+                                            "11.11.1990",
+                                            DateTimeFormatter.ofPattern("dd.MM.yyyy")))
+                            .educationLevel(EducationLevel.NFS)
+                            .isDriver(false)
+                            .isPilot(false)
+                            .build();
+
+            Employee empThree =
+                    Employee.builder()
+                            .id(3)
+                            .name("Lisa")
+                            .birthday(
+                                    LocalDate.parse(
+                                            "16.10.1999",
+                                            DateTimeFormatter.ofPattern("dd.MM.yyyy")))
+                            .educationLevel(EducationLevel.NKI)
+                            .isDriver(true)
+                            .isPilot(false)
+                            .build();
+
+            Assert.assertTrue(employees.contains(empOne));
+            Assert.assertTrue(employees.contains(empTwo));
+            Assert.assertTrue(employees.contains(empThree));
+            Assert.assertEquals(3, employees.size());
+
+        } catch (PersistenceException e) {
+            fail();
+        }
+    }
+
+    @Test
+    public void testEmployeeListNoElement() {
+
+        try {
+            List<Employee> employees = employeePersistence.list();
+
+            Employee empOne =
+                    Employee.builder()
+                            .id(10)
+                            .name("Adam")
+                            .birthday(
+                                    LocalDate.parse(
+                                            "10.10.2010",
+                                            DateTimeFormatter.ofPattern("dd.MM.yyyy")))
+                            .educationLevel(EducationLevel.RS)
+                            .isDriver(true)
+                            .isPilot(false)
+                            .build();
+
+            Assert.assertFalse(employees.contains(empOne));
+
+        } catch (PersistenceException e) {
+            fail();
+        }
+    }
+}
diff --git a/src/test/resources/employeeServiceTestData.xml b/src/test/resources/employeeServiceTestData.xml
new file mode 100644
index 0000000..c21fde6
--- /dev/null
+++ b/src/test/resources/employeeServiceTestData.xml
@@ -0,0 +1,12 @@
+<dataset>
+  <EmployeeVersion id="10" name="Adam" birthday="2010-10-10" educationlevel="RS" isDriver="true"
+    isPilot="false"/>
+  <EmployeeVersion id="20" name="Max" birthday="1990-11-11" educationlevel="NFS" isDriver="false"
+    isPilot="false"/>
+  <EmployeeVersion id="30" name="Lisa" birthday="1999-10-16" educationlevel="NKI" isDriver="true"
+    isPilot="false"/>
+
+  <Employee id="1" version="10" />
+  <Employee id="2" version="20" />
+  <Employee id="3" version="30" />
+</dataset>
\ No newline at end of file