mybatis中用leftjoin导致子表数据缺失问题

二.问题背景

在mybatis中用下面的selectUserVo脚本查询数据分页时，会导致关联的sys_role子表的数据缺失

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sta.system.mapper.SysUserMapper">

    <resultMap type="com.sta.common.system.api.domain.SysUser" id="SysUserResult">
        <result property="id" column="id"/>
        <result property="parentId" column="parent_id"/>
        <result property="ancestors" column="ancestors"/>
        <result property="employeeNo" column="employee_no"/>
        <result property="userName" column="user_name"/>
        <result property="deptId" column="dept_id"/>
        <result property="deptName" column="dept_name"/>
        <result property="email" column="email"/>
        <result property="phonenumber" column="phonenumber"/>
        <result property="sex" column="sex"/>
        <result property="password" column="password"/>
        <result property="bu" column="bu"/>
        <result property="site" column="site"/>
        <result property="status" column="status"/>
        <result property="delFlag" column="del_flag"/>
        <result property="loginIp" column="login_ip" />
        <result property="loginDate" column="login_date"/>
        <result property="note" column="note"/>
        <result property="createBy" column="create_by" />
        <result property="createTime" column="create_time" />
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <collection property="roles" javaType="java.util.List" resultMap="RoleResult"/>
    </resultMap>

    <resultMap id="RoleResult" type="com.sta.common.system.api.domain.SysRole">
        <id property="id" column="id"/>
        <result property="roleName" column="role_name"/>
        <result property="roleKey"  column="role_key" />
        <result property="roleSort" column="role_sort"/>
        <result property="dataScope" column="data_scope"/>
        <result property="status" column="role_status"/>
    </resultMap>

    <sql id="selectUserVo">
        select
        u.id,
        u.parent_id,
        u.ancestors,
        u.employee_no,
        u.user_name,
        u.dept_id,
        u.dept_name,
        u.email,
        u.phonenumber,
        u.sex,
        u.password,
        u.bu,
        u.site,
        u.status,
        u.del_flag,
        u.login_ip,
        u.login_date,
        u.create_by,
        u.create_time,
        u.update_by,
        u.update_time,
        u.note,
        r.id,
        r.role_name,
        r.role_key,
        r.role_sort,
        r.data_scope,
        r.status as role_status
        from
        sys_user u
        left join sys_user_role ur on u.id = ur.user_id
        left join sys_role r on r.id = ur.role_id
    </sql>
	
	</mapper> 

二.解决方法

修改脚本为子查询即可。

在sysuer的resultmap中添加关联角色的子查询。

下面中的property="roles"的名字对应的是Sysuser对象中包含的SysRole对象的属性名。

下面<collection 中的column="id"代表的是sysuser中的和sysrole的关联的字段

下面中的select="com.sta.system.mapper.SysRoleMapper.getRoleListByUserID">这个方法可以自己创建

<collection property="roles"   ofType="com.sta.common.system.api.domain.SysRole"
            column="id" select="com.sta.system.mapper.SysRoleMapper.getRoleListByUserID">
    <result property="id" column="role_id"/>
    <result property="roleName" column="role_name"/>
    <result property="roleKey"  column="role_key" />
    <result property="roleSort" column="role_sort"/>
    <result property="dataScope" column="data_scope"/>
    <result property="status" column="role_status"/>
</collection>

SysRoleMapper中的getRoleListByUserID 脚本如下，因为sys_user_role表中存储的是用户和角色的关联关系，所以需要拿到用户id先去查sys_user_role关联表，再去查询角色表

	<select id="getRoleListByUserID" parameterType="Long" resultMap="SysRoleResult">
		SELECT
		r.id ,
		r.role_name,
		r.role_key,
		r.role_sort,
		r.data_scope,
		r.status
		FROM sys_role r
		INNER JOIN sys_user_role ur ON ur.role_id = r.id
		WHERE ur.user_id = #{id}
	</select>

三。完整代码

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sta.system.mapper.SysUserMapper">

    <resultMap type="com.sta.common.system.api.domain.SysUser" id="SysUserResult">
        <result property="id" column="id"/>
        <result property="employeeNo" column="employee_no"/>
        <result property="managerId" column="manager_id"/>
        <result property="managerName" column="manager_name"/>
        <result property="ancestors" column="ancestors"/>
        <result property="userName" column="user_name"/>
        <result property="deptId" column="dept_id"/>
        <result property="deptName" column="dept_name"/>
        <result property="email" column="email"/>
        <result property="phonenumber" column="phonenumber"/>
        <result property="sex" column="sex"/>
        <result property="password" column="password"/>
        <result property="bu" column="bu"/>
        <result property="site" column="site"/>
        <result property="status" column="status"/>
        <result property="delFlag" column="del_flag"/>
        <result property="loginIp" column="login_ip" />
        <result property="loginDate" column="login_date"/>
        <result property="note" column="note"/>
        <result property="createBy" column="create_by" />
        <result property="createTime" column="create_time" />
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
            <collection property="roles"   ofType="com.sta.common.system.api.domain.SysRole"
                        column="id" select="com.sta.system.mapper.SysRoleMapper.getRoleListByUserID">
                <result property="id" column="role_id"/>
                <result property="roleName" column="role_name"/>
                <result property="roleKey"  column="role_key" />
                <result property="roleSort" column="role_sort"/>
                <result property="dataScope" column="data_scope"/>
                <result property="status" column="role_status"/>
            </collection>

    </resultMap>

    <resultMap id="RoleResult" type="com.sta.common.system.api.domain.SysRole">
        <id property="id" column="role_id"/>
        <result property="roleName" column="role_name"/>
        <result property="roleKey"  column="role_key" />
        <result property="roleSort" column="role_sort"/>
        <result property="dataScope" column="data_scope"/>
        <result property="status" column="role_status"/>
    </resultMap>

    <sql id="selectUserVo">
        select
        u.id,
        u.employee_no,
        u.manager_id,
        u.manager_name,
        u.ancestors,
        u.user_name,
        u.dept_id,
        u.dept_name,
        u.email,
        u.phonenumber,
        u.sex,
        u.password,
        u.bu,
        u.site,
        u.status,
        u.del_flag,
        u.login_ip,
        u.login_date,
        u.create_by,
        u.create_time,
        u.update_by,
        u.update_time,
        u.note
        from
        sys_user u

    </sql>
<mapper>