记录mybatis使用resultMap+collection映射实体类，在使用PageHelper分页时总条数不正确问题

记录mybatis使用resultMap+collection映射实体类，在使用PageHelper分页时总条数不正确问题

实体类：实体中包含集合属性

public class LineVO{

    private Integer id;

    private String name;

    private String shortName;

    private String number;

    private String lineCode;

    private Byte status;

    private Byte sort;

    private String remark;

    private Integer createUser;

    private Date createTime;

    private Integer updateUser;

    private Date updateTime;
    /**
     * 項目关联停车场
     * */
    private List<? extends LineParking> parkingList;
    /**
     * 項目关联站点
     * */
    private List<? extends LineDepot> depotList;
    /**
     * 項目关联编组
     * */
    private List<? extends LineMarshalling> marshallingList;

}

出现问题的写法

mapper文件中配置

<resultMap id="lineVOMap" type="com.zm.line.vo.LineVO">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="number" column="number"/>
        <result property="lineCode" column="line_code"/>
        <result property="status" column="status"/>
        <result property="statusName" column="statusName"/>
        <result property="sort" column="sort"/>
        <result property="remark" column="remark"/>
        <result property="createUser" column="create_user"/>
        <result property="createTime" column="create_time"/>
        <result property="updateUser" column="update_user"/>
        <result property="updateTime" column="update_time"/>
        <result property="lineCodeName" column="lineCode"/>
        <collection property="parkingList" ofType="com.zm.line.vo.LineParkingVO" >
            <result property="lineId" jdbcType="INTEGER" column="line_id" />
            <result property="parking" jdbcType="VARCHAR" column="parking" />
            <result property="parkingName" jdbcType="VARCHAR" column="parkingName" />
        </collection>
        <collection property="depotList" ofType="com.zm.line.vo.LineDepotVO" >
            <result property="lineId" jdbcType="INTEGER" column="line_id" />
            <result property="depot" jdbcType="VARCHAR" column="depot" />
            <result property="depotName" jdbcType="VARCHAR" column="depotName" />
        </collection>
        <collection property="marshallingList" ofType="com.zm.line.vo.LineMarshallingVO" >
            <result property="lineId" jdbcType="INTEGER" column="line_id" />
            <result property="marshalling" jdbcType="VARCHAR" column="marshalling" />
            <result property="marshallingName" jdbcType="VARCHAR" column="marshallingName" />
        </collection>
    </resultMap>



<select id="selectLineVOList" resultMap="lineVOMap">
        select l.id, l.name, l.number,
               l.line_code, l.sort, l.remark, l.update_time,
               l.create_user, l.create_time, l.update_user, l.status,
               case l.status
               when 0 then '编辑中'
               when 1 then '启用' end statusName,
               sd.name lineCodeName,
							 lp.line_id, lp.parking, lm.line_id, lm.marshalling,
							 ld.line_id, ld.depot
        from line l
				left join line_marshalling lm on l.id = lm.line_id
				left join line_parking lp on l.id = lp.line_id
				left join line_depot ld on l.id = ld.line_id
                left join sys_dict sd on l.line_code = sd.code
        <where>
            l.status != 9
            <if test="model.name != null and model.name != ''">
                and locate(#{model.name}, v.name)
            </if>
            <if test="model.lineCode != null and model.lineCode != ''">
                and l.line_code = #{model.lineCode}
            </if>
            <if test="model.status != null">
                and l.status = #{model.status}
            </if>
        </where>
    </select>

问题原因：mybatis中的这种写法是只进行一次sql查询，然后在进行集合实体映射，一次查询出的数据是没有进行集合映射之前的数据，例如：一个line中包含3个parking，那么查询出来的数据就是三条，然后在根据resultMap配置进行映射进对应的集合，最后成一条line数据。但是PageHelper插件分页中的总条数读取是查询出来的总条数及3，实际应该是1，分页显示3问题就出现了。

 

解决方法：更改mapper文件中实体配置的映射方式，通过多次查询映射数据

（区别在于<collection property="depotList" ofType="com.zm.line.vo.LineDepotVO" select="selectDepotList" column="id">配置中的  select="selectDepotList" column="id" ,通过多次查询进行映射，这样使用PageHelper分页时读取的总条数就是查询出的line的条数，因为实体和实体中的collection数据是分开查询，所以不会出现问题）

<resultMap id="lineVOMap" type="com.zm.line.vo.LineVO">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="number" column="number"/>
        <result property="lineCode" column="line_code"/>
        <result property="status" column="status"/>
        <result property="statusName" column="statusName"/>
        <result property="sort" column="sort"/>
        <result property="remark" column="remark"/>
        <result property="createUser" column="create_user"/>
        <result property="createTime" column="create_time"/>
        <result property="updateUser" column="update_user"/>
        <result property="updateTime" column="update_time"/>
        <result property="lineCodeName" column="lineCode"/>
        <collection property="parkingList" ofType="com.zm.line.vo.LineParkingVO" select="selectParkingList" column="id">
            <result property="lineId" jdbcType="INTEGER" column="line_id" />
            <result property="parking" jdbcType="VARCHAR" column="parking" />
            <result property="parkingName" jdbcType="VARCHAR" column="parkingName" />
        </collection>
        <collection property="depotList" ofType="com.zm.line.vo.LineDepotVO" select="selectDepotList" column="id">
            <result property="lineId" jdbcType="INTEGER" column="line_id" />
            <result property="depot" jdbcType="VARCHAR" column="depot" />
            <result property="depotName" jdbcType="VARCHAR" column="depotName" />
        </collection>
        <collection property="marshallingList" ofType="com.zm.line.vo.LineMarshallingVO" select="selectMarshallingList" column="id">
            <result property="lineId" jdbcType="INTEGER" column="line_id" />
            <result property="marshalling" jdbcType="VARCHAR" column="marshalling" />
            <result property="marshallingName" jdbcType="VARCHAR" column="marshallingName" />
        </collection>
    </resultMap>

    <select id="selectDepotList" resultType="com.zm.line.vo.LineDepotVO">
        select ld.line_id, ld.depot, sd.name depotName
        from line_depot ld
        left join sys_dict sd on ld.depot = sd.code
        where ld.line_id = #{id}
    </select>

    <select id="selectParkingList" resultType="com.zm.line.vo.LineParkingVO">
        select lp.line_id, lp.parking, sd.name parkingName
        from line_parking lp
        left join sys_dict sd on lp.parking = sd.code
        where lp.line_id = #{id}
    </select>

    <select id="selectMarshallingList" resultType="com.zm.line.vo.LineMarshallingVO">
        select lm.line_id, lm.marshalling, sd.name marshallingName
        from line_marshalling lm
        left join sys_dict sd on lm.marshalling = sd.code
        where lm.line_id = #{id}
    </select>
    
    
    <select id="selectLineVOList" resultMap="lineVOMap">
        select l.id, l.name, l.number,
               l.line_code, l.sort, l.remark, l.update_time,
               l.create_user, l.create_time, l.update_user, l.status,
               case l.status
               when 0 then '编辑中'
               when 1 then '启用' end statusName,
               sd.name lineCodeName
        from line l
        left join sys_dict sd on l.line_code = sd.code
        <where>
            l.status != 9
            <if test="model.name != null and model.name != ''">
                and locate(#{model.name}, v.name)
            </if>
            <if test="model.lineCode != null and model.lineCode != ''">
                and l.line_code = #{model.lineCode}
            </if>
            <if test="model.status != null">
                and l.status = #{model.status}
            </if>
        </where>
    </select>