How to select multiple rows from multiple tables in spring jdbctemplate

Question

Here i want to collect multiple rows from multiple tables so i want to set the values to the beans.in user.java there is one Set<UserRole> so i want to set for that field also.so i writern another row mapper for that also. so in order to avoid setting for those fields is there any other ways to set them.

User.java

public class User implements Serializable{

    private Integer id;
    private String username;
    private String password;
    private Set<UserRole> userRoles = new HashSet<UserRole>(0);

    public User() {
    }

UserRole.java

public class UserRole implements Serializable{
    private Integer id;
    private User user;
    private Role role;

    public UserRole() {
    }

Role.java

public class Role implements Serializable{

    private Integer roleId;
    private String roleName;
    private Set<UserRole> userRoles = new HashSet<UserRole>(0);
    private Set<RoleAction> roleActions = new HashSet<RoleAction>(0);

Action.java

public class Action implements Serializable{

    private Integer id;
    private String name;
    private Set<RoleAction> roleActions = new HashSet<RoleAction>(0);

Dao.java

public User getUser(String username){
        
        List<User> userModelList=new ArrayList<User>();
        
        try{
        
        String query="select ID,USERNAME,PASSWORD from users u where u.username = ?";
        
        userModelList = jdbcTemplate.query(query, new Object[] { username },
                new RowMapper<User>(){
            
                        @Override
                        public User mapRow( ResultSet resultSet,  int rownumber)
                                throws SQLException {
                         User user=new User();
                            
                        user.setId(resultSet.getInt("ID"));
                        user.setUsername(resultSet.getString("USERNAME"));
                        user.setPassword(resultSet.getString("PASSWORD"));
                        
                        int userid=user.getId();
                        
                        Role role=roleasd(userid);
                        
                        Set<UserRole> userRoles=setUserRole(role,user);
                        
                        for(UserRole ur:userRoles){
                        System.out.println("userRole details "+ur.getId()+""+ur.getRole().getRoleActions());
                        }
                        
                        user.setUserRoles(userRoles);
                        
                        System.out.println("the values are "+user.getId()+" " +user.getUsername());
                        
                        return user;
                    }

                        private Set<UserRole> setUserRole(Role role, User user) {

                            Integer id = user.getId();

                            System.out.println("coming to  setuserrole id is "+ id);

                            String query = "select * from user_role where USER_ID=? ";

                            Set<UserRole> userRoles = new HashSet<UserRole>();

                            jdbcTemplate.query(query, new Object[] { id },
                                    new RowMapper<UserRole>() {

                                @Override
                                public UserRole mapRow(ResultSet rs, int arg1)
                                        throws SQLException {
                                    UserRole userRole=new UserRole();
                                    System.out.println("**********"+rs.getInt("ID"));
                                    
                                    userRole.setId(rs.getInt("ID"));
                                    userRole.setRole(role);
                                    userRole.setUser(user);
                                    userRoles.add(userRole);
                                    return userRole;
                                }
                                
                            });
                            
                            System.out.println("Size::::"+userRoles.size());
                            
                            return userRoles;
                        }
                        
                        public  Role roleasd(int userid) {
                            
                            String query="select * from role where ROLE_ID = ?";
                            Role role=new Role();
                            
                            jdbcTemplate.query(query, new Object[] { userid },new RowMapper<Role>(){

                                        @Override
                                        public Role mapRow(ResultSet rs, int arg1)
                                                throws SQLException {
                                            
                                            role.setRoleId(rs.getInt("ROLE_ID"));
                                            role.setRoleName(rs.getString("ROLE_NAME"));
                                            
                                            System.out.println(":::"+rs.getString("ROLE_NAME"));
                                            
                                            return role;
                                        }

                            });
                            
                            return role;
                            
                        }
            
        });
        
        }catch(Exception e){
            System.out.println(" the DB error is "+e.getMessage());
        }
        
        if (userModelList.size()>0) {
            
            System.out.println("the list is "+ userModelList.get(0).getUserRoles());
            
            return userModelList.get(0);
            
        }
            return null;
}
    

Answer 1

As suggested, you should use JOIN queries. But then you still have the problem of returning two different kinds of objects, whereas one RowMapper returns only one kind of object. One solution is to have a wrapper Object that encapsulates both:

public class Result{
    private final User user;
    private final Role role;
    public Result(User user, Role role){ 
        this.role=role; this.user = user;
    }
    /* getters here */
}

end now you build your RowMapper like this:

List<Result> resultList = jdbcQuery.query(/* some sql */,
    (rs, arg1) ->{ // RowMapper as Lambda
        User user = new User();
        // assign user properties from rs
        Role role = new Role();
        // assign role properties from rs;
        return new Result(user, role);
    });

// now process your users and roles
resultList.forEach(r -> {
    User user = result.getUser();
    Role role = result.getRole();
    // do something here with User and Role
});