BigBadDev BigBadDev - 2 months ago 18
SQL Question

Spring jdbcTemplate data access and java algorithms

I have one method which must return data in DAO.

Department model:

public class Department implements Serializable {
private Long id;
private String departmentName;
private List<Employees> employeesInThisDepartment;
// getters and setters...
}


Employees model:

public class Employees implements Serializable {

private Long id;
private String department;
private String fullName;
private Date birthday;
private int salary;
// getters and setters...
}


And method in DAO:

@Override
public Department findByDepartmentNameWithEmployees(String departmentName) {
String sql = "select d.id, d.departmentName, e.id, e.fullName, e.department, e.birthday" +
", e.salary from department as d left join employees as e on d.departmentName = e.department " +
"where lower(d.departmentName) = lower(:departmentName)";
Map<String, Object> map = new HashMap<>();
map.put("departmentName", departmentName);
return jdbcTemplate.queryForObject(sql, map, (rs, rowNum) -> {
Department department = new Department();
department.setId(rs.getLong("department.id"));
department.setDepartmentName(rs.getString("department.departmentName"));
department.setEmployeesInThisDepartment(new ArrayList<>());

while (rs.next()){
Employees employees = new Employees();
employees.setId(rs.getLong("employees.id"));
employees.setFullName(rs.getString("employees.fullName"));
employees.setDepartment(rs.getString("employees.department"));
employees.setBirthday(rs.getDate("employees.birthday"));
employees.setSalary(rs.getInt("employees.salary"));
department.getEmployeesInThisDepartment().add(employees);
}
return department;
});
}


This method must return one department with list of all employees who works in this department, but it misses the first employee in the list.
Why is this happening? (SQL part is working correctly, I think that the problem is with the loop?)

Answer

Right, problem in the loop. According to docs:

Implementations must implement this method to map each row of data in the ResultSet.

So you do not need to invoke rs.next(), just remove this loop wrapper and move block

    Department department = new Department();

    department.setEmployeesInThisDepartment(new ArrayList<>());

outside return jdbcTemplate.queryForObject(sql, map, (rs, rowNum) -> { ... }. I.e.:

Department department = new Department();
department.setEmployeesInThisDepartment(new ArrayList<>());

jdbcTemplate.queryForObject(sql, map, (rs, rowNum) -> {
        department.setId(rs.getLong("department.id"));
        department.setDepartmentName(rs.getString("department.departmentName"));

        Employees employees = new Employees();
        employees.setId(rs.getLong("employees.id"));
        employees.setFullName(rs.getString("employees.fullName"));
        employees.setDepartment(rs.getString("employees.department"));
        employees.setBirthday(rs.getDate("employees.birthday"));
        employees.setSalary(rs.getInt("employees.salary"));
        department.getEmployeesInThisDepartment().add(employees);
});
return department;
Comments