mybatis中一对多，pageHelper分页问题的解决方案

搭建一个项目，项目中有三个对象，账号User，角色Role,科室Dept。其中User与Role是一对一，User与Dept是一对多。UserMapper.xml配置如下

<?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.yb.modules.sys.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="com.yb.modules.sys.entity.User">
        <id column="id" property="id" />
        <result column="login_name" property="loginName" />
        <result column="password" property="password" />
        <result column="no" property="no" />
        <result column="name" property="name" />
        <result column="phone" property="phone" />
        <result column="mobile" property="mobile" />
        <result column="photo" property="photo" />
        <result column="login_flag" property="loginFlag" />
        <result column="created_by" property="createdBy" />
        <result column="created_date" property="createdDate" />
        <result column="last_modified_by" property="lastModifiedBy" />
        <result column="last_modified_date" property="lastModifiedDate" />
        <result column="remarks" property="remarks" />

        <collection  property="roleList"  ofType="com.yb.modules.sys.entity.Role">
            <id property="id" column="role_id" />
            <result property="name" column="role_name" />
        </collection>
        <collection  property="deptList"  ofType="com.yb.modules.sys.entity.Dept">
            <id property="id" column="dept_id" />
            <result property="name" column="dept_name" />
            <result property="parentId" column="parent_id" />
        </collection>
    </resultMap>


    <!-- 通用查询结果列-->
    <sql id="Base_Column_List">
        u.id, login_name , no, u.name, phone, mobile, photo, login_flag , u.created_by , u.created_date , u.last_modified_by , u.last_modified_date , u.remarks
    </sql>
    <select id="getUserList" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>,r.id as role_id,r.name as role_name,d.id as dept_id,d.name as dept_name,d.parent_id
        from
        fqc_sys_user u
        left join fqc_sys_user_role ur on u.id=ur.user_id
        left join fqc_sys_role r on r.id= ur.role_id
        left join fqc_sys_user_dept ud on u.id=ud.user_id
        left join fqc_sys_dept d on d.id= ud.dept_id
        <where>
            <if test="loginName != null and loginName != ''">AND u.login_name like  concat('%',#{loginName},'%')</if>
            <if test="name != null and name != ''">AND u.name like concat('%',#{name},'%')</if>
            <if test="roleId != null and roleId > 0">AND r.id = #{roleId}</if>
            <if test="loginFlag != null">AND u.login_flag = #{loginFlag}</if>
            <if test="id != null and id > 0">AND u.id = #{id}</if>
            and u.login_flag != 2
            <if test="roleList != null and roleList.size() > 0">
                and r.id in
                <foreach collection="roleList" item="id" index="index" open="(" close=")" separator=",">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>
</mapper>

service使用PageHelper分页，结果查询返回的记录数与返回记录不符，返回记录数大于返回记录。原因是User与Dept是一对多关系，查询出来的结果包含了重复的User记录，mybatis先进行分页截取，然后将重复的User对象中的Dept属性合并到deptList中，所以造成查询记录数大于返回记录。

解决办法：通过collection标签的 column , select 属性，设置mybatis子查询的方式，同时修改select id="getUserList" 中的sql语句，使其返回结果不存在一对多关系

<?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.yb.modules.sys.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="com.yb.modules.sys.entity.User">
        <id column="id" property="id" />
        <result column="login_name" property="loginName" />
        <result column="password" property="password" />
        <result column="no" property="no" />
        <result column="name" property="name" />
        <result column="phone" property="phone" />
        <result column="mobile" property="mobile" />
        <result column="photo" property="photo" />
        <result column="login_flag" property="loginFlag" />
        <result column="created_by" property="createdBy" />
        <result column="created_date" property="createdDate" />
        <result column="last_modified_by" property="lastModifiedBy" />
        <result column="last_modified_date" property="lastModifiedDate" />
        <result column="remarks" property="remarks" />

        <!--<collection  property="roleList"  ofType="com.yb.modules.sys.entity.Role">
            <id property="id" column="role_id" />
            <result property="name" column="role_name" />
        </collection>
        <collection  property="deptList"  ofType="com.yb.modules.sys.entity.Dept">
            <id property="id" column="dept_id" />
            <result property="name" column="dept_name" />
            <result property="parentId" column="parent_id" />
        </collection>-->

        <collection property="roleList" ofType="com.yb.modules.sys.entity.Role" column="id" select="selectUserRole"/>

        <collection property="deptList" ofType="com.yb.modules.sys.entity.Dept" column="id" select="selectUserDept"/>


    </resultMap>

    <resultMap id="RoleResultMap" type="com.yb.modules.sys.entity.Role">
        <id property="id" column="role_id" />
        <result property="name" column="role_name" />
    </resultMap>

    <resultMap id="DeptResultMap" type="com.yb.modules.sys.entity.Dept">
        <id property="id" column="dept_id" />
        <result property="name" column="dept_name" />
        <result property="parentId" column="parent_id" />
    </resultMap>


    <select id="selectUserRole" resultMap="RoleResultMap">
        select r.id as role_id,
               r.name as role_name
        from fqc_sys_user_role ur
        left join fqc_sys_role r on r.id= ur.role_id
        where ur.user_id=#{id}
    </select>

    <select id="selectUserDept" resultMap="DeptResultMap">
        select d.id as dept_id,
               d.name as dept_name
        from fqc_sys_user_dept ud
                 left join fqc_sys_dept d on d.id= ud.dept_id
        where ud.user_id=#{id}
    </select>

    <!-- 通用查询结果列-->
    <sql id="Base_Column_List">
        u.id, login_name , no, u.name, phone, mobile, photo, login_flag , u.created_by , u.created_date , u.last_modified_by , u.last_modified_date , u.remarks
    </sql>

    
    <select id="getUserList" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from
        fqc_sys_user u
        left join fqc_sys_user_role ur on u.id=ur.user_id
        left join fqc_sys_role r on r.id= ur.role_id
        <where>
            <if test="roleId != null and roleId > 0">AND r.id = #{roleId}</if>
            <if test="loginName != null and loginName != ''">AND u.login_name like  concat('%',#{loginName},'%')</if>
            <if test="name != null and name != ''">AND u.name like concat('%',#{name},'%')</if>
            <if test="loginFlag != null">AND u.login_flag = #{loginFlag}</if>
            <if test="id != null and id > 0">AND u.id = #{id}</if>
            and u.login_flag != 2
            <if test="roleList != null and roleList.size() > 0">
                and r.id in
                <foreach collection="roleList" item="id" index="index" open="(" close=")" separator=",">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>

</mapper>