I have two classes
class Deptartment{
int deptid,
String deptname;
List<Employee> employees;
}
class Employee{
int empid;
String empname;
int deptid;
}
Table:
Department:
deptid,deptname
Employee
empid,empname,deptid
Query: select * from deptartment d,employee e where d.deptid= e.deptid
Now how can i populate Department object using spring jdbc template?
To help Sean Patrick Floyd, here's his solution with a single query :
final Map<Integer, Department> departments = new HashMap<Integer, Department>();
this.jdbcTemplate.query(
"select d.deptid, d.deptname, e.empid, e.empname from Department d inner join Employee on e.deptid = e.deptid",
new RowMapper<Employee>() {
public Department mapRow(ResultSet rs, int rowNum) throws SQLException {
Integer deptId = rs.getInt("deptid");
Department d = (Department) departments.get(deptId);
if (d == null) {
String deptName = rs.getString("deptname");
d = new Department();
d.setDeptId(deptId);
d.setDeptName(deptName);
departments.put(deptId, d);
}
Employee employee = new Employee();
employee.setEmpId(rs.getInt("empid"));
employee.setEmpName(rs.getString("empname"));
employee.setDeptId(deptId);
d.getEmployees().add(employee);
return employee;
}
});
List<Department> result = new ArrayList<Department>(departments.values());
It happens to be shorter and more efficient.
There is no direct support of this scenario in Spring currently. However, you can use the following MultipleRowMapper class proposed for inclusion into future versions of Spring.
Something like this will do:
Department department = this.jdbcTemplate.queryForObject(
"select deptid, deptname from Department where deptid = :deptid",
new Object[deptid],
new RowMapper<Department>() {
public Department mapRow(ResultSet rs, int rowNum) throws SQLException {
Department department = new Department();
department.setDeptid(rs.getInt("deptid"));
department.setDeptname(rs.getString("deptname"));
return department;
}
});
department.setEmployees(this.jdbcTemplate.query(
"select empid, empname from Employee",
new RowMapper<Employee>() {
public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
Employee employee = new Employee();
employee.setEmpid(rs.getInt("emptid"));
employee.setEmpname(rs.getString("empname"));
return department;
}
});
