记录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>