Mybatis：多对多查询

浅浅的花香味﹌ 2024-03-23 13:10 47阅读 0赞

### 需求 ###

查询⽤户同时查询出该⽤户的所有⻆⾊

### Bean ###

public class User implements Serializable {
    
        private Integer id;
        private String username;
    
        //表示用户关联的角色
        private List<Role> roleList = new ArrayList<>();
    }
    
    
    public class Role {
    
        private Integer id;
        private String roleName;
        private String roleDesc;
    }

### 第一种方式XML ###

#### Mapper ####

public interface IUserMapper {
    
        //查询所有用户、同时查询每个用户关联的角色信息
        public List<User> findAllUserAndRole();
    }

#### XML ####

<resultMap id="userRoleMap" type="com.lagou.pojo.User">
            <result property="id" column="userid"></result>
            <result property="username" column="username"></result>
            <collection property="roleList" ofType="com.lagou.pojo.Role">
                <result property="id" column="roleid"></result>
                <result property="roleName" column="roleName"></result>
                <result property="roleDesc" column="roleDesc"></result>
            </collection>
        </resultMap>
    
        <select id="findAllUserAndRole" resultMap="userRoleMap">
            select * from user u left join sys_user_role ur on u.id = ur.userid
    										 left join sys_role r on r.id = ur.roleid
        </select>

### 第二种方式注解 ###

#### Mapper ####

public interface IUserMapper {
    
        //查询所有用户、同时查询每个用户关联的角色信息
        @Select("select * from user")
        @Results({
                @Result(property = "id",column = "id"),
                @Result(property = "username",column = "username"),
                @Result(property = "roleList",column = "id",javaType = List.class,
                 many = @Many(select = "com.lagou.mapper.IRoleMapper.findRoleByUid"))
        })
        public List<User> findAllUserAndRole();
    }
    
    
    
    public interface IRoleMapper {
    
        @Select("select * from sys_role r,sys_user_role ur where r.id = ur.roleid and ur.userid = #{uid}")
        public List<Role> findRoleByUid(Integer uid);
    }