package com.clevergang.dbtests;
import com.clevergang.dbtests.repository.api.DataRepository;
import com.clevergang.dbtests.repository.api.data.*;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.RandomStringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import static java.util.stream.Collectors.toList;
/**
* Implementation of the scenarios. Note that the scenarios are always the same, what changes is the
* DB API implementation. To make things little bit easier for us we do not autowire the
* DB API implementation, but we pass it to the constructor of the Scenarios class instead - this
* isn't typical pattern we use in production code.
*
* @author Bretislav Wajtr
*/
@SuppressWarnings("WeakerAccess")
public class Scenarios {
private static final Logger logger = LoggerFactory.getLogger(Scenarios.class);
private final DataRepository repository;
public Scenarios(DataRepository repository) {
this.repository = repository;
}
/**
* 1. Fetch single entity based on primary key
* <br/>
* This is the case when pid comes from outside (typically from UI) and we need to fetch complete record from database.
*
* @param companyPid Primary key of the record coming from outside
*/
public void fetchSingleEntityScenario(Integer companyPid) {
Company company = repository.findCompany(companyPid);
// check some post conditions
assert company != null;
assert company.getPid().equals(companyPid);
assert company.getName().equals("CleverGang");
logger.info("Fetched result: {}", company);
}
/**
* 2. Fetch list of entities based on condition
* <br/>
* This is a case when we want to get records from database using some kind of filter. Filter values typically come from UI.
* @param employeeMinSalary Example of external filter value
*/
public void fetchListOfEntitiesScenario(Integer employeeMinSalary) {
List<Employee> employees = repository.employeesWithSalaryGreaterThan(employeeMinSalary);
// check some post conditions
assert employees != null;
assert employees.size() == 3;
assert employees.get(0).getSalary().compareTo(new BigDecimal(employeeMinSalary)) > 0;
assert employees.get(1).getSalary().compareTo(new BigDecimal(employeeMinSalary)) > 0;
assert employees.get(2).getSalary().compareTo(new BigDecimal(employeeMinSalary)) > 0;
logger.info("Fetched result: {}", employees);
}
/**
* 3. Save new single entity and return primary key
*/
public void saveNewEntityScenario() {
Project project = new Project();
project.setName("TestProject");
project.setDate(LocalDate.now());
// SCENARIO CODE STARTS HERE
Integer newPid = repository.insertProject(project);
// check some post conditions
logger.info("Scenario three, pid of inserted entity: {}", newPid);
assert newPid != null;
assert newPid > 2;
Project storedProject = repository.findProject(newPid);
logger.info("Scenario three, stored project: {}", storedProject);
assert storedProject != null;
assert newPid.equals(storedProject.getPid());
assert project.getName().equals(storedProject.getName());
assert project.getDate().equals(storedProject.getDate());
}
/**
* 4. Batch insert multiple entities of same type and return generated keys
* <br/>
* This scenario represents a situation, when business method, as a result of it's execution, wants to store
* multiple records of same type into database effectively.
* </br>
* In our scenario method, we create 1000 products first and then we want to store them into the database
* as fast as we can - through batch insert functionality.
*/
public void batchInsertMultipleEntitiesScenario() {
// create a list of thousand products
List<Project> projects = new ArrayList<>();
for (int i = 0; i < 1000; i++) {
Project project = new Project();
project.setName(RandomStringUtils.randomAlphabetic(10));
project.setDate(LocalDate.now());
projects.add(project);
}
// SCENARIO CODE STARTS HERE
long start = System.nanoTime();
List<Integer> newPids = repository.insertProjects(projects);
// check some post conditions
Integer projectsCount = repository.getProjectsCount();
long end = System.nanoTime();
assert projectsCount == 1002;
logger.info("Scenario 4. output {}", newPids);
logger.info("Rough time needed for execution (without commit): {} ms", (end - start) / 1000000d);
}
/**
* 5. Update single existing entity - update all fields of entity at once
* <br/>
* This scenario covers typical situation in information systems where a detail of a record is displayed in UI, user
* has possibility to modify any field of the record and then he/she presses Save button -> complete record data are sent
* back to server and the record should be updated in database.
*/
public void updateCompleteEntityScenario() {
// Imagine that this object comes from UI edit dialog, which is typical scenario
Employee employeeToUpdate = performSomeEmployeeRecordModificationsInUI(1);
// SCENARIO CODE STARTS HERE
repository.updateEmployee(employeeToUpdate);
// check some post conditions
Employee updatedEmployee = repository.findEmployee(1);
assert employeeToUpdate.getPid().equals(updatedEmployee.getPid());
assert employeeToUpdate.getDepartmentPid().equals(updatedEmployee.getDepartmentPid());
assert employeeToUpdate.getName().equals(updatedEmployee.getName());
assert employeeToUpdate.getSurname().equals(updatedEmployee.getSurname());
assert employeeToUpdate.getEmail().equals(updatedEmployee.getEmail());
assert employeeToUpdate.getSalary().equals(updatedEmployee.getSalary());
}
private Employee performSomeEmployeeRecordModificationsInUI(Integer employeePid) {
Employee employeeToUpdate = new Employee();
employeeToUpdate.setPid(employeePid);
employeeToUpdate.setDepartmentPid(6);
employeeToUpdate.setName("Curt1");
employeeToUpdate.setSurname("Odegaard1");
employeeToUpdate.setEmail("[email protected]"); // <-- this is updated value
employeeToUpdate.setSalary(new BigDecimal("15000.00")); // <-- this is updated value
return employeeToUpdate;
}
/**
* 6. Fetch many-to-one relation (Company for Department)
*/
public void fetchManyToOneRelationScenario() {
Department softwareDevelopmentDepartment = repository.findDepartment(3);
// SCENARIO CODE STARTS HERE
// Getting Company for Department (many-to-one relation) in JPA is quite easy. You typically have
// @ManyToOne relation defined in the Department entity class, so once you have instance of Department,
// you just call department.getCompany() and JPA does the magic for you (typically one or more lazy selects
// are executed). We don't have any such magical call here, but non-JPA approach is quite straightforward
// too: we have company_pid, so just ask DataRepository for the record:
Company company = repository.findCompany(softwareDevelopmentDepartment.getCompanyPid());
// check some post conditions
assert company.getName().equals("CleverGang");
assert company.getPid().equals(1);
logger.info("Department {} is in the {} company", softwareDevelopmentDepartment.getName(), company.getName());
}
/**
* 7. Fetch one-to-many relation (Departments for Company)
*/
public void fetchOneToManyRelationScenario() {
Company company = repository.findCompany(1);
// SCENARIO CODE STARTS HERE
// For one-to-many relations the situation is quite similar to many-to-one relations (scenario six). In JPA this
// is "easy" - you define @OneToMany relation in the Company entity and then you just call getDepartments() method ->
// a lazy select is issued and Departments are fetched from DB. However you can also use EAGER FetchType strategy which
// causes the relation to load along with the primary entity - this behavior is (by our opinion) the source of all evil
// in JPA... So, in non-JPA approach, we don't have any "eager" loads, just explicit calls for data:
List<Department> departments = repository.findDepartmentsOfCompany(company);
// check some post conditions
assert departments.size() == 4;
logger.info("There are {} departments in {} company", departments.size(), company.getName());
}
/**
* 8. Update entities one-to-many relation (Departments in Company) - add two items, update two items and delete one item - all at once
* <br/>
* This scenario covers situation where we have no idea what operations were performed by the user. We only
* have new list of Departments and we have to efficiently update DB so it exactly reflects new Departments list.
* <br/>
* At the same time, we DON'T want to take the tempting path and do it by "removing all the existing departments of the company from
* database and then inserting new values". This is actually not what the user did nor it's what we should do -> what if the departments
* which were only updated have some additional relations in the database? -> if we delete them, we will delete also those relations ->
* always ask yourself if this is something you want (or you want risk).
*/
public void updateCompleteOneToManyRelationScenario() {
Company company = repository.findCompany(1); // Clevergang company
// this call simulates what typically happens in UI - the user chooses new list of departments for company
// (adds new ones, updates some other or removes some departments). The new list is transferred from
// UI to business service a List<Department> with no information about what departments were deleted or which
// ones were updated - the business service has to determine these changes - which is what rest of the code in this method does
List<Department> newDepartments = createNewDepartmentsList(company);
// SCENARIO CODE STARTS HERE - update departments in DB
updateDepartments(company, newDepartments);
// check some post conditions
List<Department> departmentsForCompany = repository.findDepartmentsOfCompany(company);
assert departmentsForCompany.size() == 5;
assert departmentsForCompany.get(0).getName().equals("Back office");
assert departmentsForCompany.get(1).getName().equals("IT Department Updated");
assert departmentsForCompany.get(2).getName().equals("Software Development");
assert departmentsForCompany.get(3).getName().equals("New department 1");
assert departmentsForCompany.get(4).getName().equals("New department 2");
logger.info("State of database at the end of scenario eight: {}", departmentsForCompany);
}
private void updateDepartments(Company company, List<Department> newDepartments) {
// --------------------------------------------------------------------------------------------------------------------------------------
// The pattern for one-to-many relation update begins here - but BEWARE, it'll work nicely only for few items in the one to many relation
// --------------------------------------------------------------------------------------------------------------------------------------
// first get current departments
List<Department> currentDepartments = repository.findDepartmentsOfCompany(company);
logger.info("Company {} current departments {}", company.getName(), currentDepartments);
// now determine which departments were deleted
Collection<Integer> newPIDs = CollectionUtils.collect(newDepartments, Department::getPid);
List<Department> deletedDepartments = currentDepartments.stream()
.filter(department -> !newPIDs.contains(department.getPid()))
.collect(toList());
// ... and which were added or updated
Map<Boolean, List<Department>> addedOrUpdatedDepartments = newDepartments.stream()
.filter(department -> !currentDepartments.contains(department)) // filtering out not changed items, assumes that equals and hashCode is properly coded in Department class
.collect(Collectors.partitioningBy(d -> d.getPid() == null)); // split (partition) by new or updated (pid is either null or not)
List<Department> addedDepartments = addedOrUpdatedDepartments.get(Boolean.TRUE);
List<Department> updatedDepartments = addedOrUpdatedDepartments.get(Boolean.FALSE);
// now perform relevant operations on each list:
repository.deleteDepartments(deletedDepartments);
repository.insertDepartments(addedDepartments);
repository.updateDepartments(updatedDepartments);
}
private List<Department> createNewDepartmentsList(Company company) {
List<Department> departments = repository.findDepartmentsOfCompany(company);
// delete one item
departments.removeIf(department -> department.getName().equals("Lazy Department"));
// add two items (notice they don't have their own pid yet)
departments.add(new Department(company.getPid(), "New department 1"));
departments.add(new Department(company.getPid(), "New department 2"));
// update one item
departments.stream()
.filter(it -> it.getName().equals("IT Department"))
.findFirst()
.ifPresent(it -> it.setName("IT Department Updated"));
return departments;
}
/**
* 9. Complex select - construct select where conditions based on some boolean conditions + throw in some joins
* <br/>
* In our case we are executing following query:<br/>
* Query: get all projects, where the total cost of the project per month is greater than 70000. In the same result set
* get all companies participating on such project along with cost of the project for the company.
*/
public void executeComplexSelectScenario() {
List<ProjectsWithCostsGreaterThanOutput> projectsWithCostsGreaterThan = repository.getProjectsWithCostsGreaterThan(70000);
// check some post conditions
assert projectsWithCostsGreaterThan != null;
assert projectsWithCostsGreaterThan.size() == 2;
assert projectsWithCostsGreaterThan.get(0).getCompanyName().equals("CleverGang");
assert projectsWithCostsGreaterThan.get(0).getCompanyCost().equals(new BigDecimal("72000.00"));
assert projectsWithCostsGreaterThan.get(1).getCompanyName().equals("Supersoft");
assert projectsWithCostsGreaterThan.get(1).getCompanyCost().equals(new BigDecimal("13000.00"));
logger.info("executeComplexSelectScenario output: {}", projectsWithCostsGreaterThan);
}
/**
* 10. Call stored procedure/function and process results
*/
public void callStoredProcedureScenario() {
RegisterEmployeeOutput output = repository.callRegisterEmployee("Bretislav", "Wajtr", "[email protected]", new BigDecimal(40000), "MyDepartment", "MyCompany");
// check some post conditions
assert output != null;
assert output.getEmployeePid() != null;
assert output.getEmployeePid() > 10;
assert output.getDepartmentPid() != null;
assert output.getDepartmentPid() > 7;
assert output.getCompanyPid() != null;
assert output.getCompanyPid() > 3;
logger.info("callStoredProcedureScenario output: {}", output);
}
/**
* 11. Execute query using JDBC simple Statement (not PreparedStatement)
* <br/>
* Motivation why we need the "static statement" feature: In 96% of the cases, you’re better off writing
* a PreparedStatement rather than a static statement - it's safer (sql injection),
* easier (complex data types like dates) and sometimes faster (prepared statements reuse). However, there are
* edge cases for complex queries and lot of data where it's actually faster to use simple statement query, because
* your database’s cost-based optimiser or planner obtains some heads-up about what kind of data is really going to
* be affected by the query and can therefore execute the query faster.
* <p>
* Good SQL API framework should offer way how to execute simple static statements.
*/
public void executeSimpleStaticStatementScenario() {
Company output = repository.findCompanyUsingSimpleStaticStatement(1);
// check some post conditions
assert output != null;
assert output.getName().equals("CleverGang");
logger.info("Output of scenario 11: {}", output);
}
/**
* 12. Delete one record by PID
* <br/>
* Just one of the CRUD operations.
*/
public void removeSingleEntityScenario() {
repository.removeProject(2);
// check some post conditions
Integer projectCount = repository.getProjectsCount();
assert projectCount == 1;
}
}
