JDBC resultset into java POJO with list member

Question

I have two query and resultsets, in the below code I want to showcase that for a particular userGroupCode I have certain userPreference and employee associated with it. I have written the code below code to display the userGroupCode object:

String query1= "SELECT ug.userGroupCode, ug.userGroupDesc, up.userPreference"
+ "FROM dbo.UserGroup_link ug INNER JOIN dbo.UserPreference up ON  ug.userGroupCode = up.userGroupCode";

userGroupCode	userGroupDesc	userPreference
A100	Finance	Mumbai
A100	Finance	Bangalore
A200	Supply Chain	Chennai
A201	Marketing	Delhi
A201	Marketing	Kolkata
A300	Health	Indore

String query2= "SELECT ug.userGroupCode, ug.userGroupDesc, emp.employee_id,emp.name,emp.role"
+ "FROM dbo.UserGroup ug INNER JOIN dbo.employee emp ON  ug.userGroupCode = emp.userGroupCode";

userGroupCode	userGroupDesc	employee_id	name	role
A100	Finance	101	Foo1	Developer
A100	Finance	101	Foo1	Team Lead
A200	Supply Chain	091	Test1	Manager
A201	Marketing	591	User1	Analyst
A201	Marketing	1001	Boo1	Scrum Master
A300	Health	1001	Boo1	Developer

I have class UserGroupMapping like:

public class UserGroupMapping {
    private String userGroupCode;
    private String userGroupCode;
    private List<String> userPreference;
    private List<Employee> emp;
    
    //getter and setter
}

Another class for Employee is:

public class Employee {
    private String employee_id;
    private String name;
    private List<String> role;
    
    //getter and setter
}

In my stored procedure class I am calling these queries with the help of jdbcTemplate.query();

String userCode = null; 
List<String> userPreferenceList = new ArrayList<>();
List<UserGroupMapping> userGroupMappingList = new ArrayList<>();
List<UserGroupMapping> userGroupMappingList1 = new ArrayList<>();
UserGroupMapping userGroupMapping = new UserGroupMapping();
List<Employee> employeeList = new ArrayList<>();
Employee emp = new Employee();
UserGroupMapping userGroupMapping1 = new UserGroupMapping();

jdbcTemplate.query(query1, (rs)->{

    String user_group_code = rs.getString("userGroupCode");
    String user_group_desc = rs.getString("userGroupDesc");
    String user_preference = rs.getString("userPreference");
    
    if(userCode == null){
        userGroupMapping.setUserGroupCode(user_group_code);
        userGroupMapping.setUserGroupDesc(user_group_desc);
        userPreferenceList.add(userPreference);
        userCode = user_group_code;
    } else if (userCode.equals(user_group_code)) {
        userPreferenceList.add(userPreference);
    } else {
        userGroupMapping.setUserPreference(userPreferenceList);
        userGroupMappingList.add(userGroupMapping);
        userPreferenceList = new ArrayList<>();
        userGroupMapping = new userGroupMapping();
        userGroupMapping.setUserGroupCode(user_group_code);
        userGroupMapping.setUserGroupDesc(user_group_desc);
        userPreferenceList.add(userPreference);
        userCode = user_group_code;
    }});
    userCode = null;
    userGroupMapping.setUserPreference(userPreferenceList);
    userGroupMappingList.add(userGroupMapping);
jdbcTemplate.query(query2, (rs)->{

String user_group_code = rs.getString("userGroupCode");
String user_group_desc = rs.getString("userGroupDesc");
String emp_id = rs.getString("employee_id");
String name = rs.getString("name");

if(userCode == null){
    userGroupMapping1.setUserGroupCode(user_group_code);
    userGroupMapping1.setUserGroupDesc(user_group_desc);
    emp.setId(employeeId);
    emp.setName(name);
    employeeList.add(emp);
    userCode = user_group_code;
} else if (userCode.equals(user_group_code)) {
    Employee emp = new Employee();
    emp.setId(employeeId);
    emp.setName(name);
    employeeList.add(emp);
} else {
    userGroupMapping1.setEmployee(employeeList);
    userGroupMappingList1.add(userGroupMapping1);
    employeeList = new ArrayList<>();
    userGroupMapping1 = new userGroupMapping();
    Employee emp = new Employee();
    userGroupMapping1.setUserGroupCode(user_group_code);
    userGroupMapping1.setUserGroupDesc(user_group_desc);
    emp.setId(employeeId);
    emp.setName(name);
    employeeList.add(emp);
    userCode = user_group_code;
}});
userGroupMapping1.setEmployee(employeeList);
userGroupMappingList1.add(userGroupMapping1);

    List<UserGroupMapping> ugList = Stream.concat(userGroupMappingList.stream, userGroupMappingList1.stream).distinct().collect(Collectors.toList())
return ugList;

The problem is that I want my output to be like :

[
    {
        "userGroupCode" : "A100",
        "userGroupDesc" : "Finance",
        "userPreference": ["Mumbai","Bangalore"],
        "Employee"      : [
                            "employee_id" : "101",
                            "name"        : "Foo1",
                            "role"        : ["Developer","Team Lead"]
                          ]
    }
]

After merging the two list I am getting the below output:

[
    {
        "userGroupCode" : "A100",
        "userGroupDesc" : "Finance",
        "userPreference": ["Mumbai","Bangalore"],
        "Employee"      : []
    },
    {
        "userGroupCode" : "A100",
        "userGroupDesc" : "Finance",
        "userPreference": [],
        "Employee"      : [
                            "employee_id" : "101",
                            "name"        : "Foo1",
                            "role"        : []
                          ]
    }
]

Could anyone please help me with few things:

1. How could I embed the role into the Employee object.
2. How can I merge the table based on userGroupCode and userGroupDesc.
3. I am feeling the code is not that performance optimised, How could I optimised this code.

Thank you in advance.

Answer 1

You basically have 2 solutions,

1. Write a query that returns all results with joins and do filtering in java, quite easy to achieve with 2 maps (one for the UserGroup the other for the Employee.

2. Write a query and aggregate the duplicates using list in the query itself.

SELECT ug.userGroupCode, ug.userGroupDesc, up.userPreference, emp.employee_id,emp.name,emp.role
FROM dbo.UserGroup_link ug
INNER JOIN dbo.UserPreference up ON  ug.userGroupCode = up.userGroupCode
INNER JOIN dbo.employee emp ON  ug.userGroupCode = emp.userGroupCode

Then use a RowCallbackHandler to achieve what you want (instead of a ResultSetExtractor.

Map<String, UserGroup> userGroups = new HashMap<>;
Map<Integer, Employee> employees = new HashMap<>;

jdbc.query(query, (rs) -> {

 String userGroupCode = rs.getString("userGroupCode"); 
 String emp_id = rs.getString("employee_id");
 
 UserGroupMapping ugm userGroups.computeIfAbsent(userGroupCode,  {
    UserGroupMapping ugm1 = new UserGroupMapping();
    ugm1.setUserGroupCode(userGroupCode);
    ugm1.setUserGroupDesc(rs.getString("userGroupDesc");
    ugm1.setUserPreference(new ArrayList<>());
    ugm1.getEmployee(new ArrayList<>());
    return ugm1;
 });
 ugm.getUserPreference().add(rs.getString("userPreference"));

 Employee emp = employees.computeIfAbsent(emp_id, {
   Employee emp1 = new Employee();
   emp1.setName(rs.getString("name"));
   emp1.setRole(new ArrayList<>());
   ugm.getEmployee().add(emp);
   return emp1;
 });

 emp.getRole().add(rs.getString("role"));

});

return userGroups.values();

The above code will get all UserGroupMapping objects from the result including all the Employee instances. The temporary maps are needed to determine if record has already been shown.

Another solution would be to use list in your query and some GROUP BY statement to let the query do part of the aggregation. That way you could make it a bit easier to create an Employee.

Answer 2

Ola,

You can group by using Map taking id as key and value as object ( to be aggregated into) . For example:

if(map.containes(key))
{
    get object from map and do Ops.
}
else
{
    1. Create new object
    2. Do set Ops on Object
    3. Add to map.
}