mybatisplus实现三表联查

MybatisPlus可以通过XML映射文件或者注解的方式实现三表联查。

XML映射文件方式：

1.在mapper.xml文件中编写SQL语句，使用join关键字连接三个表。

<select id="selectUserAndRoleAndPermission" resultMap="userRolePermissionMap">
    select u.*, r.*, p.*
    from user u
    left join user_role ur on u.id=ur.user_id
    left join role r on ur.role_id=r.id
    left join role_permission rp on r.id=rp.role_id
    left join permission p on rp.permission_id=p.id
</select>

2.在对应的实体类中定义对应的属性以及对应的getter和setter方法。

public class User {
    private Long id;
    private String name;
    private List<Role> roles;
    // 省略getter和setter
}

public class Role {
    private Long id;
    private String name;
    private List<Permission> permissions;
    // 省略getter和setter
}

public class Permission {
    private Long id;
    private String name;
    // 省略getter和setter
}

3.定义对应的ResultMap，将查询结果映射到对应的实体类中。

<resultMap id="userRolePermissionMap" type="User">
    <id column="u.id" property="id"/>
    <result column="u.name" property="name"/>
    <collection property="roles" ofType="Role">
        <id column="r.id" property="id"/>
        <result column="r.name" property="name"/>
        <collection property="permissions" ofType="Permission">
            <id column="p.id" property="id"/>
            <result column="p.name" property="name"/>
        </collection>
    </collection>
</resultMap>

4.在对应的Mapper接口中定义对应的方法。

public interface UserMapper extends BaseMapper<User> {
    List<User> selectUserAndRoleAndPermission();
}

注解方式：

1.在对应的Mapper接口中使用@Select注解编写SQL语句，使用join关键字连接三个表。

@Select("select u.*, r.*, p.* " +
        "from user u " +
        "left join user_role ur on u.id=ur.user_id " +
        "left join role r on ur.role_id=r.id " +
        "left join role_permission rp on r.id=rp.role_id " +
        "left join permission p on rp.permission_id=p.id")
@Results({
        @Result(column = "u.id", property = "id"),
        @Result(column = "u.name", property = "name"),
        @Result(column = "r.id", property = "roles.id"),
        @Result(column = "r.name", property = "roles.name"),
        @Result(column = "p.id", property = "roles.permissions.id"),
        @Result(column = "p.name", property = "roles.permissions.name")
})
List<User> selectUserAndRoleAndPermission();

2.在对应的实体类中定义对应的属性以及对应的getter和setter方法。

public class User {
    private Long id;
    private String name;
    private List<Role> roles;
    // 省略getter和setter
}

public class Role {
    private Long id;
    private String name;
    private List<Permission> permissions;
    // 省略getter和setter
}

public class Permission {
    private Long id;
    private String name;
    // 省略getter和setter
}