Mybatis分组查询分页问题解决记录

利用mybatis分组查询时，比如是一对多的情况下，数据库查出来比如是10条，但是经过分组之后在前端页面展示出来的可能只有7条，这样用mybatis的分页插件显示的分页数量信息就对不上。
如下面这段sql:

<resultMap type="com.ly.mp.project.entities.ecuConfigFile.EcuConfigVO" id="getEcuConfigFileTableGroup">
        <result property="id" column="ecuConfigId"/>
        <result property="ecuConfigId" column="ecuConfigId"/>
        <result property="ecuAddress" column="ecuAddress"/>
        <result property="fileName" column="fileName"/>
        <result property="filePath" column="filePath"/>
        <result property="fileSize" column="fileSize"/>
        <result property="customSettingName" column="customSettingName"/>
        <result property="customSettingMethod" column="customSettingMethod"/>
        <result property="customSettingActionType" column="customSettingActionType"/>
        <result property="customSettingStatus" column="customSettingStatus"/>
        <result property="description" column="description"/>
        <result property="createDate" column="createDate"/>
        <result property="creator" column="creator"/>
        <result property="creatorName" column="creatorName"/>
        <collection property="configTypes" resultMap="getEcuConfigFileTableGroup1"/>
    </resultMap>
    <resultMap type="com.ly.mp.project.entities.ecuConfigFile.ConfigTypeVO" id="getEcuConfigFileTableGroup1">
        <result property="configTypeId" column="configTypeId"/>
        <result property="ecuConfigId" column="ecuConfigId"/>
        <result property="actionType" column="configTypeActionType"/>
        <result property="externalId" column="configTypeExternalId"/>
        <collection property="properties" resultMap="getEcuConfigFileTableGroup2"/>
    </resultMap>
    <resultMap type="com.ly.mp.project.entities.ecuConfigFile.PropertyVO" id="getEcuConfigFileTableGroup2">
        <result property="propertyId" column="propertyId"/>
        <result property="configTypeId" column="configTypeId"/>
        <result property="propertyName" column="propertyName"/>
        <result property="displayName" column="displayName"/>
        <result property="propertyType" column="propertyType"/>
        <result property="propertyOptionsStr" column="propertyOptionsStr"/>
        <result property="description" column="description"/>
        <result property="externalId" column="configPropertyExternalId"/>
    </resultMap>
    <select id="getEcuConfigFileTable" resultMap="getEcuConfigFileTableGroup">
    select f.id ecuConfigId,
        f.ecu_address ecuAddress,
        f.file_name fileName, f.file_path filePath, f.file_size fileSize,
        f.setting_name customSettingName, f.setting_method customSettingMethod, 
        f.action_type customSettingActionType,f.setting_status customSettingStatus,
        f.description,
        date_format(f.create_date, '%Y-%m-%d %H:%i:%s') createDate,
        f.creator, f.creator_name creatorName,
        ft.id configTypeId, 
        ft.ecu_config_id ecuConfigId 
         ft.action_Type configTypeActionType, 
         ft.external_id configTypeExternalId, 
         fp.id propertyId, fp.config_type_id configTypeId, 
         fp.property_name propertyName, fp.display_name displayName, 
         fp.property_type propertyType, fp.property_options propertyOptionsStr,
        fp.description,fp.external_id configPropertyExternalId 
         from t_ecu_config f
         left join t_ecu_config_type ft on f.id = ft.ecu_config_id and f.is_enable = ft.is_enable
        left join t_ecu_config_property fp on ft.id = fp.config_type_id and f.is_enable = fp.is_enable 
        where f.is_enable = 1
        <if test='params.fileName != null and params.fileName != ""'>
        and f.file_name like CONCAT('%',#{params.fileName},'%')
        </if>
        <if test='params.ecuAddress != null and params.ecuAddress != ""'>
        and f.ecu_address = #{params.ecuAddress} 
        </if>
        <if test='params.ids != null and params.ids.size > 0'>
        and f.id in 
            <foreach collection="params.ids" item="id" index="index" open="(" close=")" separator=",">
              #{id}
            </foreach>  
        </if>
        order by f.id desc
    </select>

从上面sql里可以看出，三张表关联 分别是一对多对多的关系，用mybatis的分组查询这样直接查出来是一拖多拖多的json结构，但是因为sql查出来的总条数比如是10条，分组之后，可能是7条，mybatis分页插件依然会认为是10条数据而不是7条数据，该怎么办呢，一度以为是mybatis的分页bug,其实不是。

mybatis针对这种情况，在<collection>标签里包含了可以引入其他子查询语句的select属性和column传递参数属性，下面是改造后的sql:

<resultMap type="com.ly.mp.project.entities.ecuConfigFile.EcuConfigVO" id="getEcuConfigFileTableGroup">
        <result property="id" column="ecuConfigId"/>
        <result property="ecuConfigId" column="ecuConfigId"/>
        <result property="ecuAddress" column="ecuAddress"/>
        <result property="fileName" column="fileName"/>
        <result property="filePath" column="filePath"/>
        <result property="fileSize" column="fileSize"/>
        <result property="customSettingName" column="customSettingName"/>
        <result property="customSettingMethod" column="customSettingMethod"/>
        <result property="customSettingActionType" column="customSettingActionType"/>
        <result property="customSettingStatus" column="customSettingStatus"/>
        <result property="description" column="description"/>
        <result property="createDate" column="createDate"/>
        <result property="creator" column="creator"/>
        <result property="creatorName" column="creatorName"/>
        <collection property="configTypes" select="getEcuConfigTypeByEcuConfigId" column="ecuConfigId"/>
    </resultMap>
    <resultMap type="com.ly.mp.project.entities.ecuConfigFile.ConfigTypeVO" id="getEcuConfigFileTableGroup1">
        <result property="configTypeId" column="configTypeId"/>
        <result property="ecuConfigId" column="ecuConfigId"/>
        <result property="actionType" column="configTypeActionType"/>
        <result property="externalId" column="configTypeExternalId"/>
        <collection property="properties" resultMap="getEcuConfigFileTableGroup2"/>
    </resultMap>
    <resultMap type="com.ly.mp.project.entities.ecuConfigFile.PropertyVO" id="getEcuConfigFileTableGroup2">
        <result property="propertyId" column="propertyId"/>
        <result property="configTypeId" column="configTypeId"/>
        <result property="propertyName" column="propertyName"/>
        <result property="displayName" column="displayName"/>
        <result property="propertyType" column="propertyType"/>
        <result property="propertyOptionsStr" column="propertyOptionsStr"/>
        <result property="description" column="description"/>
        <result property="externalId" column="configPropertyExternalId"/>
    </resultMap>
    <select id="getEcuConfigFileTable" resultMap="getEcuConfigFileTableGroup">
    select f.id ecuConfigId,
        f.ecu_address ecuAddress,
        f.file_name fileName, f.file_path filePath, f.file_size fileSize,
        f.setting_name customSettingName, f.setting_method customSettingMethod, 
        f.action_type customSettingActionType,f.setting_status customSettingStatus,
        f.description,
        date_format(f.create_date, '%Y-%m-%d %H:%i:%s') createDate,
        f.creator, f.creator_name creatorName
        <!-- ft.id configTypeId, -->
        <!-- ft.ecu_config_id ecuConfigId -->
        <!-- ft.action_Type configTypeActionType, -->
        <!-- ft.external_id configTypeExternalId, -->
        <!-- fp.id propertyId, fp.config_type_id configTypeId, -->
        <!-- fp.property_name propertyName, fp.display_name displayName, -->
        <!-- fp.property_type propertyType, fp.property_options propertyOptionsStr,
        fp.description,fp.external_id configPropertyExternalId -->
         from t_ecu_config f
        <!-- left join t_ecu_config_type ft on f.id = ft.ecu_config_id and f.is_enable = ft.is_enable
        left join t_ecu_config_property fp on ft.id = fp.config_type_id and f.is_enable = fp.is_enable -->
        where f.is_enable = 1
        <if test='params.fileName != null and params.fileName != ""'>
        and f.file_name like CONCAT('%',#{params.fileName},'%')
        </if>
        <if test='params.ecuAddress != null and params.ecuAddress != ""'>
        and f.ecu_address = #{params.ecuAddress} 
        </if>
        <if test='params.ids != null and params.ids.size > 0'>
        and f.id in 
            <foreach collection="params.ids" item="id" index="index" open="(" close=")" separator=",">
              #{id}
            </foreach>  
        </if>
        order by f.id desc
    </select>
    
    <select id="getEcuConfigTypeByEcuConfigId" resultMap="getEcuConfigFileTableGroup1">
        select ft.id configTypeId,
        ft.ecu_config_id ecuConfigId,
        ft.action_Type configTypeActionType,
        ft.external_id configTypeExternalId,
        fp.id propertyId, fp.config_type_id configTypeId,
        fp.property_name propertyName, fp.display_name displayName,
        fp.property_type propertyType, fp.property_options propertyOptionsStr,
        fp.description,fp.external_id configPropertyExternalId
        from t_ecu_config_type ft 
        left join t_ecu_config_property fp on ft.id = fp.config_type_id and ft.is_enable = fp.is_enable
        where ft.ecu_config_id = #{ecuConfigId}
        and ft.is_enable = 1
    </select>

可以看到，改造后的sql在第一层resultMap里的<collection>标签里加入了select属性引入子查询语句，column把第一段sql的column属性当参数传入子查询语句，这样出来的json结构和一开始的sql是一致的，但是mybatis分页插件会把它当作7条数据，而不是10条数据，就不会有分页数量信息和实际查询信息对不上的情况发生。