Java薪资分配功能中如何自动调整旧薪资到期日期与新薪资生效日期

背景说明 #

• 现有employees和salaries两张数据表，一名员工可对应多条薪资记录
• 单条薪资记录包含字段：关联员工ID、工资金额、fromDate（薪资分配生效日期）、toDate（薪资到期、新薪资生效的日期）
• 预期实现效果：为员工分配新薪资时，旧薪资的toDate自动设置为LocalDate.now()，新薪资的toDate设置为null

现有实现代码 #

public Employee save(Employee employee) {    
    String sql = "Insert into employees(emp_no, birth_date,first_name, last_name, gender, hire_date) values (:empNo,:birthDate, :firstName, :lastName, cast(:gender as gender), :hireDate) " +
            "on conflict (emp_no) do update set birth_date = :birthDate, first_name= :firstName, last_name= :lastName, gender= cast(:gender as gender), hire_date= :hireDate";    
    MapSqlParameterSource paramSource = new MapSqlParameterSource("empNo", employee.getEmpNo())
            .addValue("birthDate", employee.getBirthDate())
            .addValue("firstName", employee.getFirstName())
            .addValue("lastName", employee.getLastName())
            .addValue("gender", employee.getGender().toString())
            .addValue("hireDate", employee.getHireDate());    
    namedParameterJdbcTemplate.update(sql, paramSource);    
    if (!employee.getSalaries().isEmpty()) {        
        for (Salary salary : employee.getSalaries()) {            
            insertSalary(employee.getEmpNo(), salary);            
            salary.setToDate(null);        
        }    
    }    
    return employee;
}

private void insertSalary(Long empNo, Salary salary) {    
    String sql = "insert into salaries (emp_no, salary, from_date, to_date) values (:empNo, :salary, :fromDate, :toDate) on conflict (emp_no, from_date) do update set to_date = :toDate";    
    salary.setToDate(LocalDate.now());    
    MapSqlParameterSource parameterSource = new MapSqlParameterSource("salary", salary.getSalary())
            .addValue("empNo", empNo)
            .addValue("fromDate", salary.getFromDate())
            .addValue("toDate", salary.getToDate());    
    namedParameterJdbcTemplate.update(sql, parameterSource);
} 

@Override
public Employee extractData(ResultSet rs) throws SQLException, DataAccessException {        
    Map<Long, Employee> employeeById = new HashMap<>();        
    Employee employee = new Employee();        
    while (rs.next()) {            
        Long empNo = rs.getLong("emp_no");            
        if (employeeById.isEmpty()) {                
            employee = employeeById.get(empNo);            
        }            
        Long salary = rs.getLong("salary");            
        LocalDate fromDate = rs.getObject("from_date", LocalDate.class);            
        LocalDate toDate= rs.getObject("to_date", LocalDate.class);            
        Salary salary1 = new Salary(empNo, salary, fromDate, toDate);            
        if (employee == null) {                
            employee = new Employee();                
            employeeById.put(employee.getEmpNo(), employee);            
        }            
        employee.assignSalary(salary1);        
    }        
    Collection<Employee> employees = employeeById.values();        
    return employee;    
}

内容的提问来源于stack exchange，提问作者Taulant Fazliu