MyBatis 多对多关联查询 collection column select方式

方式一：

UserVO.java

package com.gumj.rbac.domain.vo;

import com.gumj.rbac.entity.Organization;
import com.gumj.rbac.entity.Post;
import com.gumj.rbac.entity.Role;
import lombok.Data;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

/**
 * 用户视图对象
 *
 * @author : gumingjun
 * @date : 2020-12-30
 */
@Data
public class UserVO {
    //其它字段省略......

    /**
     * 组织机构
     */
    private List<Organization> organizations;
}

UserMapper.java

/**
 * 分页查询用户列表
 *
 * @param page      分页参数
 * @param userQuery 用户查询参数对象
 * @return 用户集合
 */
IPage<UserVO> listUserByParam(IPage<User> page, @Param("userQuery") UserQuery userQuery);

UserMapper.xml

<resultMap id="UserVOResultMap" type="com.gumj.rbac.domain.vo.UserVO">
    <id column="id" property="id"/>
    <result column="username" property="username"/>
    <result column="name" property="name"/>
    <result column="state" property="state"/>
    <result column="birthday" property="birthday"/>
    <result column="gender" property="gender"/>
    <result column="mobile" property="mobile"/>
    <result column="phone" property="phone"/>
    <result column="sort_no" property="sortNo"/>
    <result column="head_file_id" property="headFileId"/>
    <result column="expiry_time" property="expiryTime"/>
    <result column="gmt_create" property="gmtCreate"/>
    <result column="gmt_modified" property="gmtModified"/>

	<!--如果使用了多层嵌套对象，此处不能使用 fetchType="lazy"否则会报错-->
    <collection property="organizations" column="{userId=id, levelCode=level_Code}"
                select="com.gumj.rbac.mapper.OrganizationMapper.listOrganizationByUserIdAndLevelCode"/>
</resultMap>

<!--增加的参数重点：#{userQuery.levelCode} as level_code-->
<select id="listUserByParam" resultMap="UserVOResultMap">
    select u.id, u.username, u.name, u.state, u.birthday, u.gender, u.mobile, u.phone, u.sort_no, u.head_file_id, u.expiry_time, u.gmt_create, u.gmt_modified, #{userQuery.levelCode} as level_code
    from sys_organization as o
    left join sys_organization_user as ou on o.id=ou.org_id
    left join sys_user as u on ou.user_id=u.id
    <trim prefix="WHERE" prefixOverrides="AND">
        <if test="userQuery.levelCode!=null and userQuery.levelCode!=''">
            <bind name="levelCodeFuzzy" value="userQuery.levelCode+'%'"/>
            and o.level_code like #{levelCodeFuzzy}
        </if>
        <if test="userQuery.username!=null and userQuery.username!=''">
            and u.username=#{userQuery.username}
        </if>
        <if test="userQuery.state!=null">
            and u.state=#{userQuery.state}
        </if>
        <if test="userQuery.startDate!=null and userQuery.endDate!=null">
            <!--解决结束日期，between边界值问题-->
            <bind name="endDate" value="userQuery.endDate.plusDays(1)"/>
            <choose>
                <!--0：创建时间，1：账号到期时间-->
                <when test="userQuery.timeType==1">
                    and u.expiry_time between #{userQuery.startDate} and #{endDate}
                </when>
                <otherwise>
                    and u.gmt_create between #{userQuery.startDate} and #{endDate}
                </otherwise>
            </choose>
        </if>
        <if test="userQuery.name!=null and userQuery.name!=''">
            <bind name="nameFuzzyQuery" value="'%'+userQuery.name+'%'"/>
            and u.name like #{nameFuzzyQuery}
        </if>
        <!--是否删除 0:否,1:是-->
        and u.is_delete=0
    </trim>
    order by u.sort_no
</select>

/**
 * 查询组织机构集合
 *
 * @param userId    用户ID
 * @param levelCode 组织机构层级编码
 * @return 组织机构集合
 */
List<Organization> listOrganizationByUserIdAndLevelCode(@Param("userId") Long userId, @Param("levelCode") String levelCode);

OrganizationMapper.xml

<resultMap id="BaseResultMap" type="com.gumj.rbac.entity.Organization">
    <result column="id" property="id"/>
    <result column="pid" property="pid"/>
    <result column="name" property="name"/>
    <result column="short_name" property="shortName"/>
    <result column="type" property="type"/>
    <result column="level_code" property="levelCode"/>
    <result column="sort_no" property="sortNo"/>
    <result column="state" property="state"/>
    <result column="is_delete" property="delete"/>
    <result column="gmt_create" property="gmtCreate"/>
    <result column="gmt_modified" property="gmtModified"/>
</resultMap>

<!--通过level_code 查询组织机构集合-->
<select id="listOrganizationByUserIdAndLevelCode" resultMap="BaseResultMap">
    select o.id, o.pid, o.name, o.short_name, o.type, o.level_code, o.sort_no, o.state, o.is_delete, o.gmt_create, o.gmt_modified
    from sys_organization_user as ou
    left join sys_organization as o on ou.org_id=o.id
    <bind name="levelCodeFuzzyQuery" value="levelCode+'%'"/>
    where ou.user_id=#{userId} and o.level_code like #{levelCodeFuzzyQuery} and o.is_delete=0 and o.state=0
    order by o.sort_no
</select>

方式二

<!-- 通用查询映射结果 -->
<resultMap id="UserVOResultMap" type="com.gumj.rbac.domain.vo.UserVO">
    <id column="id" property="id"/>
    <result column="username" property="username"/>
    <result column="name" property="name"/>
    <result column="state" property="state"/>
    <result column="birthday" property="birthday"/>
    <result column="gender" property="gender"/>
    <result column="mobile" property="mobile"/>
    <result column="phone" property="phone"/>
    <result column="sort_no" property="sortNo"/>
    <result column="head_file_id" property="headFileId"/>
    <result column="expiry_time" property="expiryTime"/>
    <result column="gmt_create" property="gmtCreate"/>
    <result column="gmt_modified" property="gmtModified"/>
	
	<!--如与上面的result无相同字段可直接引用Mappper中的ResultMap
    <collection property="organizations" ofType="com.gumj.rbac.entity.Organization"
                resultMap="com.gumj.rbac.mapper.OrganizationMapper.BaseResultMap"/>
    -->
	<collection property="organizations" ofType="com.lensyn.rbac.entity.Organization">
        <result column="org_id" property="id"/>
        <result column="org_pid" property="pid"/>
        <result column="org_name" property="name"/>
        <result column="org_short_name" property="shortName"/>
        <result column="org_type" property="type"/>
        <result column="org_level_code" property="levelCode"/>
        <result column="org_is_leaf" property="leaf"/>
        <result column="org_sort_no" property="sortNo"/>
        <result column="org_state" property="state"/>
        <result column="org_is_deleted" property="deleted"/>
        <result column="org_gmt_create" property="gmtCreate"/>
        <result column="org_gmt_modified" property="gmtModified"/>
    </collection>
</resultMap>


<!--查询用户列表-->
<!--重点：o.id as org_id, o.pid as org_pid, o.name as org_name, o.short_name as org_short_name, o.type as org_type, o.level_code as org_level_code, o.is_leaf as org_is_leaf, o.sort_no as org_sort_no, o.state as org_state, o.is_deleted as org_is_deleted, o.gmt_create as org_gmt_create, o.gmt_modified as org_gmt_modified-->
<select id="listUserByParam" resultMap="UserVOResultMap">
    select u.id, u.username, u.name, u.state, u.birthday, u.gender, u.mobile, u.phone, u.sort_no, u.head_file_id, u.expiry_time, u.gmt_create, u.gmt_modified,
    o.id as org_id, o.pid as org_pid, o.name as org_name, o.short_name as org_short_name, o.type as org_type, o.level_code as org_level_code, o.is_leaf as org_is_leaf, o.sort_no as org_sort_no, o.state as org_state, o.is_deleted as org_is_deleted, o.gmt_create as org_gmt_create, o.gmt_modified as org_gmt_modified
    from sys_organization as o
    left join sys_organization_user as ou on o.id=ou.org_id
    left join sys_user as u on ou.user_id=u.id
    <trim prefix="WHERE" prefixOverrides="AND">
        <if test="userQuery.levelCode!=null and userQuery.levelCode!=''">
            <bind name="levelCodeFuzzy" value="userQuery.levelCode+'%'"/>
            and o.level_code like #{levelCodeFuzzy}
        </if>
        <if test="userQuery.username!=null and userQuery.username!=''">
            and u.username=#{userQuery.username}
        </if>
        <if test="userQuery.state!=null">
            and u.state=#{userQuery.state}
        </if>
        <if test="userQuery.startDate!=null and userQuery.endDate!=null">
            <!--解决结束日期，between边界值问题-->
            <bind name="endDate" value="userQuery.endDate.plusDays(1)"/>
            <choose>
                <!--0：创建时间，1：账号到期时间-->
                <when test="userQuery.timeType==1">
                    and u.expiry_time between #{userQuery.startDate} and #{endDate}
                </when>
                <otherwise>
                    and u.gmt_create between #{userQuery.startDate} and #{endDate}
                </otherwise>
            </choose>
        </if>
        <if test="userQuery.name!=null and userQuery.name!=''">
            <bind name="nameFuzzyQuery" value="'%'+userQuery.name+'%'"/>
            and u.name like #{nameFuzzyQuery}
        </if>
        <!--是否删除 0:否,1:是-->
        and u.is_delete=0
    </trim>
    order by u.sort_no
</select>