java - 如何在spring jdbctemplate中从多个表中选择多行

Question

这里我想从多个表中收集多行，所以我想将值设置为 beans.in user.java 有一个 Set<UserRole>所以我也想为该字段设置。所以我也为此编写了另一个行映射器。因此，为了避免设置这些字段，是否有其他方法来设置它们。

User.java

公共(public)类 User 实现可序列化{

    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() {
    }

角色.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

根据建议，您应该使用 JOIN 查询。但是，仍然存在返回两种不同类型的对象的问题，而一个 RowMapper 只返回一种对象。一种解决方案是使用一个包装器对象来封装两者:

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 */
}

现在你可以像这样构建 RowMapper:

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
});