From cc362865daee16d234f772d237c020973689e5eb Mon Sep 17 00:00:00 2001 From: Dominic Rogetzer Date: Sat, 5 May 2018 11:26:47 +0200 Subject: Implement EmployeeDatabaseDao.list --- .../einsatzverwaltung/dao/EmployeeDatabaseDao.java | 36 ++++++++++++++++++++-- 1 file changed, 34 insertions(+), 2 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..bd59dd1 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 list() throws PersistenceException { - throw new UnsupportedOperationException(); + + try { + ResultSet rs = listEmployee.executeQuery(); + + List 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 -- cgit v1.2.3-70-g09d2 From 3d28d350883dce1efebf84f88674b65363b75b80 Mon Sep 17 00:00:00 2001 From: Dominic Rogetzer Date: Sat, 5 May 2018 11:27:03 +0200 Subject: Implement EmployeeServiceImpl.list --- .../groupphase/einsatzverwaltung/service/EmployeeServiceImpl.java | 7 ++++++- 1 file changed, 6 insertions(+), 1 deletion(-) 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..112ddf3 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 list() throws ServiceException { - return null; + + try { + return employeePersistence.list(); + } catch (PersistenceException e) { + throw new ServiceException(e); + } } @Override -- cgit v1.2.3-70-g09d2 From 301f1613c58f619fc65c3c87db5c558f463500d5 Mon Sep 17 00:00:00 2001 From: Dominic Rogetzer Date: Sat, 5 May 2018 15:35:43 +0200 Subject: Add dbunit as dependency --- pom.xml | 8 +++++++- 1 file changed, 7 insertions(+), 1 deletion(-) diff --git a/pom.xml b/pom.xml index 42f24e7..094ded3 100644 --- a/pom.xml +++ b/pom.xml @@ -26,6 +26,7 @@ 2.18.0 1.5 4.0.13-alpha + 2.5.1 2.21.0 3.1.1 @@ -71,7 +72,7 @@ com.h2database h2 ${h2.version} - runtime + compile ch.qos.logback @@ -115,6 +116,11 @@ ${testfx.version} test + + org.dbunit + dbunit + ${dbunit.version} + -- cgit v1.2.3-70-g09d2 From 81e0e800cc3d07e636d035939cf71b454e981a81 Mon Sep 17 00:00:00 2001 From: Dominic Rogetzer Date: Sat, 5 May 2018 15:40:48 +0200 Subject: Change JDBCConnectionManager to allow different connection-url --- .../assignment/groupphase/util/JDBCConnectionManager.java | 11 ++++++++--- 1 file changed, 8 insertions(+), 3 deletions(-) diff --git a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/util/JDBCConnectionManager.java b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/util/JDBCConnectionManager.java index 5494471..6eb15ec 100644 --- a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/util/JDBCConnectionManager.java +++ b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/util/JDBCConnectionManager.java @@ -12,12 +12,17 @@ import org.springframework.stereotype.Component; public class JDBCConnectionManager { private static final Logger LOG = LoggerFactory.getLogger(MethodHandles.lookup().lookupClass()); - private static final String CONNECTION_URL = + private static final String DEFAULT_CONNECTION_URL = "jdbc:h2:~/sepm;INIT=RUNSCRIPT FROM 'classpath:sql/database.sql'"; - + private String connectionUrl; private Connection connection; public JDBCConnectionManager() { + this(DEFAULT_CONNECTION_URL); + } + + public JDBCConnectionManager(String connectionUrl) { + this.connectionUrl = connectionUrl; try { Class.forName("org.h2.Driver"); } catch (ClassNotFoundException e) { @@ -27,7 +32,7 @@ public class JDBCConnectionManager { } public Connection getConnection() throws SQLException { - if (connection == null) connection = DriverManager.getConnection(CONNECTION_URL); + if (connection == null) connection = DriverManager.getConnection(connectionUrl); return connection; } -- cgit v1.2.3-70-g09d2 From b029241d53fa17deab6159344d4f2296553ddcb3 Mon Sep 17 00:00:00 2001 From: Dominic Rogetzer Date: Sat, 5 May 2018 15:41:26 +0200 Subject: Format EmployeeDatabaseDao and EmployeeServiceImpl --- .../einsatzverwaltung/dao/EmployeeDatabaseDao.java | 22 +++++++++++----------- .../service/EmployeeServiceImpl.java | 2 +- 2 files changed, 12 insertions(+), 12 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 bd59dd1..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 @@ -26,9 +26,9 @@ public class EmployeeDatabaseDao implements EmployeeDAO { + "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"; + "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, listEmployee; @@ -95,17 +95,17 @@ public class EmployeeDatabaseDao implements EmployeeDAO { ResultSet rs = listEmployee.executeQuery(); List employees = new ArrayList<>(); - while(rs.next()) { + 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(); + .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); } 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 112ddf3..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 @@ -38,7 +38,7 @@ public class EmployeeServiceImpl implements EmployeeService { public List list() throws ServiceException { try { - return employeePersistence.list(); + return employeePersistence.list(); } catch (PersistenceException e) { throw new ServiceException(e); } -- cgit v1.2.3-70-g09d2 From ad44dc581b063485eede5c2b19b1b5ab0753ce72 Mon Sep 17 00:00:00 2001 From: Dominic Rogetzer Date: Sat, 5 May 2018 15:45:15 +0200 Subject: Change enum types to varchar with check constraint to support DBUnit --- src/main/resources/sql/database.sql | 17 +++++++++++------ 1 file changed, 11 insertions(+), 6 deletions(-) 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 ( -- cgit v1.2.3-70-g09d2 From f14c6eaa7173753cdca8cc44ae798e5700b99de3 Mon Sep 17 00:00:00 2001 From: Dominic Rogetzer Date: Sat, 5 May 2018 15:46:09 +0200 Subject: Add test-data in xml format for EmployeePersistenceTest --- src/test/resources/employeeServiceTestData.xml | 12 ++++++++++++ 1 file changed, 12 insertions(+) create mode 100644 src/test/resources/employeeServiceTestData.xml 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 @@ + + + + + + + + + \ No newline at end of file -- cgit v1.2.3-70-g09d2 From 783aad0bd343a0c5a008ed1433d9958ea8e5e7a2 Mon Sep 17 00:00:00 2001 From: Dominic Rogetzer Date: Sat, 5 May 2018 15:49:41 +0200 Subject: Implement EmployeePersistenceTest with DBUnit --- .../employee/EmployeePersistenceTest.java | 155 +++++++++++++++++++++ 1 file changed, 155 insertions(+) create mode 100644 src/test/java/at/ac/tuwien/sepm/assignment/groupphase/employee/EmployeePersistenceTest.java 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 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 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(); + } + } +} -- cgit v1.2.3-70-g09d2