Mybatis踩过得坑collection 一对多问题

昨天在使用分页查询一对多时出现了一个很严重的问题，绑定多少个数据分页总数就会增加多少条这样肯定是不对的

首先看一下原来写的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.jjckj.boot.mapper.UsersMapper">

    <resultMap id="userVOResultMap" type="com.jjckj.boot.base.vo.UserVO">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="email" property="email"/>
        <result column="mobile" property="mobile"/>
        <result column="avatar" property="avatar"/>
        <result column="qq" property="qq"/>
        <result column="wechat" property="wechat"/>
        <result column="weibo" property="weibo"/>
        <result column="qq_openid" property="qqOpenid"/>
        <result column="wechat_openid" property="wechatOpenid"/>
        <result column="weibo_openid" property="weiboOpenid"/>
        <result column="creator_id" property="creatorId"/>
        <result column="creator" property="creator"/>
        <result column="create_time" property="createTime"/>
        <result column="modifier_id" property="modifierId"/>
        <result column="modifier" property="modifier"/>
        <result column="modify_time" property="modifyTime"/>
        <result column="del_flag" property="delFlag"/>
        <result column="last_login_ip" property="lastLoginIp"/>
        <result column="last_login_location" property="lastLoginLocation"/>
        <result column="last_login_time" property="lastLoginTime"/>

        <collection property="roleVoList" ofType="com.jjckj.boot.base.vo.RoleVO">
            <id column="role_id" property="id"/>
            <result column="role_code" property="roleCode"/>
            <result column="role_name" property="roleName"/>
            <result column="rcreator_id" property="creatorId"/>
            <result column="rcreator" property="creator"/>
            <result column="rcreate_time" property="createTime"/>
            <result column="rmodifier_id" property="modifierId"/>
            <result column="rmodifier" property="modifier"/>
            <result column="rmodify_time" property="modifyTime"/>
            <result column="rdel_flag" property="delFlag"/>
        </collection>

        <collection property="groupVoList" ofType="com.jjckj.boot.base.vo.GroupVO">
            <id column="group_id" property="id"/>
            <result column="group_name" property="name"/>
            <result column="parent_id" property="parentId"/>
            <result column="group_code" property="code" />
            <result column="group_type" property="type" />
            <result column="leader" property="leader" />
            <result column="address" property="address" />
            <result column="gmobile" property="mobile" />
            <result column="gemail" property="email" />
            <result column="gstatus" property="status" />
            <result column="gsort" property="sort" />
            <result column="gcreator_id" property="creatorId" />
            <result column="gcreate_time" property="createTime" />
            <result column="gmodifier" property="modifier" />
            <result column="gmodify_time" property="modifyTime" />
        </collection>
    </resultMap>

    <select id="pageBootUserVoByQuery" resultMap="userVOResultMap"
            parameterType="com.jjckj.boot.model.query.UserVoQuery">
        select
          u.id,
          u.username,
          u.email,
          u.mobile,
          u.avatar,
          u.qq,
          u.wechat,
          u.weibo,
          u.qq_openid,
          u.wechat_openid,
          u.weibo_openid,
          u.user_source,
          u.last_login_ip,
          u.last_login_location,
          u.last_login_time,
          u.creator_id,
          u.create_time,
          u.modifier_id,
          u.modify_time,
          u.del_flag,
          u.remark,
          r.id as role_id,
          r.role_code,
          r.role_name,
          r.creator_id as rcreator_id,
          r.create_time as rcreate_time,
          r.modifier_id as rmodifier_id,
          r.modify_time as rmodify_time,
          r.del_flag as rdel_flag,
          g.id as group_id,
          g.name as group_name,
          g.parent_id,
          g.code as group_code,
          g.type as group_type,
          g.leader,
          g.address,
          g.mobile as gmobile,
          g.email as gemail,
          g.status as gstatus,
          g.sort as gsort,
          g.creator_id as gcreator_id,
          g.create_time as gcreate_time,
          g.modifier_id as gmodifier_id,
          g.modify_time as gmodify_time
        from
          users as u
          left join role_user as ru on ru.user_id = u.id
          left join role as r on r.id = ru.role_id
          left join group_user gu on gu.user_id = u.id
          left join  groups as g on g.id = gu.group_id
        <where>
            <if test="username != null and username != ''">
                u.username like concat('%', #{username}, '%')
            </if>
        </where>
    </select>
</mapper>

按理说应该是没有问题的是吧，但是使用分页就会先统计总数而不是先查询

所以我就对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.jjckj.boot.mapper.UsersMapper">
	<resultMap id="userVOResultMap" type="com.jjckj.boot.base.vo.UserVO">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="email" property="email"/>
        <result column="mobile" property="mobile"/>
        <result column="avatar" property="avatar"/>
        <result column="qq" property="qq"/>
        <result column="wechat" property="wechat"/>
        <result column="weibo" property="weibo"/>
        <result column="qq_openid" property="qqOpenid"/>
        <result column="wechat_openid" property="wechatOpenid"/>
        <result column="weibo_openid" property="weiboOpenid"/>
        <result column="creator_id" property="creatorId"/>
        <result column="creator" property="creator"/>
        <result column="create_time" property="createTime"/>
        <result column="modifier_id" property="modifierId"/>
        <result column="modifier" property="modifier"/>
        <result column="modify_time" property="modifyTime"/>
        <result column="del_flag" property="delFlag"/>
        <result column="last_login_ip" property="lastLoginIp"/>
        <result column="last_login_location" property="lastLoginLocation"/>
        <result column="last_login_time" property="lastLoginTime"/>

        <collection property="roleVoList" column="id" ofType="com.jjckj.boot.base.vo.RoleVO" select="getRoleVoList">
            <id column="id" property="id"/>
            <result column="role_code" property="roleCode"/>
            <result column="role_name" property="roleName"/>
            <result column="creator_id" property="creatorId"/>
            <result column="create_time" property="createTime"/>
            <result column="modifier_id" property="modifierId"/>
            <result column="modify_time" property="modifyTime"/>
            <result column="del_flag" property="delFlag"/>
        </collection>
        <collection property="groupVoList" column="id" ofType="com.jjckj.boot.base.vo.GroupVO" select="getGroupVoList">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
            <result column="parent_id" property="parentId"/>
            <result column="code" property="code" />
            <result column="type" property="type" />
            <result column="leader" property="leader" />
            <result column="address" property="address" />
            <result column="mobile" property="mobile" />
            <result column="email" property="email" />
            <result column="status" property="status" />
            <result column="sort" property="sort" />
            <result column="creator_id" property="creatorId" />
            <result column="create_time" property="createTime" />
            <result column="modifier_id" property="modifierId" />
            <result column="modify_time" property="modifyTime" />
        </collection>
    </resultMap>

    <!--查询用户绑定角色-->
    <select id="getRoleVoList" resultType="com.jjckj.boot.base.vo.RoleVO">
        select
            r.id ,
            r.role_code,
            r.role_name,
            r.creator_id,
            r.create_time,
            r.modifier_id,
            r.modify_time,
            r.del_flag
        from
            role as r
            left join role_user as ru on ru.role_id = r.id
        where ru.user_id = #{id}
    </select>

    <!--查询用户 绑定组织-->
    <select id="getGroupVoList" resultType="com.jjckj.boot.base.vo.GroupVO">
        select
            g.id,
            g.name,
            g.parent_id,
            g.code,
            g.type,
            g.leader,
            g.address,
            g.mobile,
            g.email,
            g.status,
            g.sort,
            g.creator_id,
            g.create_time,
            g.modifier_id,
            g.modify_time
        from
            groups as g
            left join group_user as gu on gu.group_id = g.id
        where gu.user_id = #{id}
    </select>

    <select id="pageBootUserVoByQuery" resultMap="userVOResultMap"
            parameterType="com.jjckj.boot.model.query.UserVoQuery">
        select
            u.id,
            u.username,
            u.email,
            u.mobile,
            u.avatar,
            u.qq,
            u.wechat,
            u.weibo,
            u.qq_openid,
            u.wechat_openid,
            u.weibo_openid,
            u.user_source,
            u.last_login_ip,
            u.last_login_location,
            u.last_login_time,
            u.creator_id,
            u.create_time,
            u.modifier_id,
            u.modify_time,
            u.del_flag,
            u.remark
        from
            users as u
        <where>
            <if test="username != null and username != ''">
                u.username like concat('%', #{username}, '%')
            </if>
        </where>
    </select>
</mapper>

现在的总数就是对的了