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

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.
}