最近一个项目用到了mybatis plus collection 多层嵌套查询,而且不止一层。。。。导致查询分页出来的数据有问题
<resultMap id="BaseResultMap" type="UnHandleCaseDO">
<id column="pid" jdbcType="BIGINT" property="id" />
<result column="product_id" jdbcType="INTEGER" property="productId" />
<result column="product_name" jdbcType="VARCHAR" property="productName" />
<!-- 此处省略部分字段-->
<association property="judicialOrg" column="judicial_org_id" javaType="JudicialOrgDO"
resultMap="BaseResultMap"/>
<collection property="agents" ofType="CaseRefAgentDO">
<id property="id" column="raid"/>
<association property="agent" column="agent_id" javaType="CaseAgentDO">
<id property="id" column="aid"/>
<result property="name" column="aname"/>
</association>
</collection>
<collection property="caseDefendants" ofType="CaseRefDefendantDO">
<id property="id" column="rdid"/>
<association property="defendant" column="defendant_id" javaType="CaseDefendantDO">
<id property="id" column="did"/>
<!-- 此处省略部分字段-->
</association>
</collection>
</resultMap>
返回的数据,反正,长这样,一共三层。
原先的查询是这样写的(部分字段省略)
<select id="selectListBy" resultMap="BaseResultMap">
SELECT uc.id pid,
cd.id rdid,d.id did,d.type dtype,d.company
FROM uc
LEFT JOIN jo on jo.id = uc.jo_id
LEFT JOIN cd on cd.sys_case_no = uc.sys_case_no
LEFT JOIN d on cd.defendant_id = d.id
LEFT JOIN cra on cra.sys_case_no = uc.sys_case_no
LEFT JOIN ca ON cra.agent_id = ca.id
WHERE uc.deleted = 0 AND uc.status = 1 AND uc.partner_id = #{partnerId}
<if test="query.productName != null and query.productName != ''">
AND uc.product_name like concat("%",#{query.productName},"%")
</if>
<if test="query.company != null and query.company != ''">
AND d.company like concat("%",#{query.company},"%")
</if>
<if test="query.name != null and query.name != ''">
AND d.`name` like concat("%",#{query.name},"%")
</if>
<if test="query.idCard != null and query.idCard != ''">
AND d.id_card like concat("%",#{query.idCard},"%")
</if>
<if test="query.creditCode != null and query.creditCode != ''">
AND d.credit_code like concat("%",#{query.creditCode},"%")
</if>
<if test="query.beginDate != null and query.beginDate != ''">
AND uc.created_time >= #{query.beginDate}
</if>
<if test="query.endDate != null and query.endDate != ''">
AND uc.created_time <![CDATA[ <= ]]>#{query.endDate}
</if>
ORDER BY uc.updated_time DESC
</select>
这样查询的总条数是对的,但是mybatis-plus分页处理过后就有问题,比如总条数是12条,分页查询结果会只有3条
其实把这个sql执行一下,由于多表关联了,12条结果会变为n条结果,而mybatis-plus的分页,在执行时,先count计算总条数,在用limit加在sql后面来查询对应页的条数,但是问题就出在limit这一步
有打日志时就可以看到,limit的是有重复数据的sql,但是执行完后,主表id相同的数据又会变为一条数据
这个问题,以前没遇到过,想了一下要不分开查询好了
但是表又有点多,而且涉及子表的条件查询,分开又有点麻烦
于是我想到要不GROUP BY 一下好了
因为这个问题在于分页那一步,分错对象了,如果分页那一步的数据是无重复的数据,那就行了
所以,原sql的基础上根据主表id进行GROUP BY查询,这一步需要分页,取到的分页列表的id,再去进行正常的查询,这样可以把子表的所有数据都查到。
也不能只进行一步GROUP BY,因为那样取不到子类列表的对象,多个子类的时候,列表项会只有一个对象数据
修改过后如下,group by 查询时可以只查id出来,这样sql执行更快
<select id="selectPageBy" resultMap="BaseResultMap">
SELECT uc.id pid
FROM uc
LEFT JOIN jo on jo.id = uc.jo_id
LEFT JOIN cd on cd.sys_case_no = uc.sys_case_no
LEFT JOIN d on cd.defendant_id = d.id
LEFT JOIN cra on cra.sys_case_no = uc.sys_case_no
LEFT JOIN ca ON cra.agent_id = ca.id
WHERE uc.deleted = 0 AND uc.status = 1 AND uc.partner_id = #{partnerId}
<if test="query.productName != null and query.productName != ''">
AND uc.product_name like concat("%",#{query.productName},"%")
</if>
<if test="query.company != null and query.company != ''">
AND (d.company like concat("%",#{query.company},"%")
OR d.name like concat("%",#{query.name},"%"))
</if>
<if test="query.idCard != null and query.idCard != ''">
AND (d.id_card like concat("%",#{query.idCard},"%")
OR d.credit_code like concat("%",#{query.creditCode},"%"))
</if>
<if test="query.beginDate != null and query.beginDate != ''">
AND uc.created_time >= #{query.beginDate}
</if>
<if test="query.endDate != null and query.endDate != ''">
AND uc.created_time <![CDATA[ <= ]]>#{query.endDate}
</if>
GROUP BY uc.id
ORDER BY uc.updated_time DESC
</select>
<select id="selectListBy" resultMap="BaseResultMap">
SELECT uc.id pid,jo.need_relate_info
FROM uc
LEFT JOIN jo on jo.id = uc.jo
LEFT JOIN cd on cd.sys_case_no = uc.sys_case_no
LEFT JOIN d on cd.defendant_id = d.id
LEFT JOIN cra on cra.sys_case_no = uc.sys_case_no
LEFT JOIN ca ON cra.agent_id = ca.id
WHERE 1=1
<if test="array != null and array.length != 0">
AND uc.id IN
<foreach collection="array" item="item" index="index"
open="(" close=")" separator=",">
#{item}
</foreach>
</if>
ORDER BY uc.updated_time DESC
</select>
java调用
@Override
public Page<vo> list(Integer partnerId, DTO dto) {
List<Long> caseIds = new ArrayList<>();
// dto 对象继承page
Page<vo> mainPage = unHandleCaseDOMapper.selectPageBy(partnerId, dto);
if (mainPage.getTotal() == 0) {
return packageResultForList(mainPage);
}
mainPage.getRecords().forEach(item -> {
caseIds.add(item.getId());
});
// id 集合
Long[] ids = new Long[caseIds.size()];
caseIds.toArray(ids);
List<vo> list = Mapper.selectListBy(ids);
mainPage.setRecords(list);
return packageResultForList(mainPage);
}
这样既保证了条件查询,返回的数据也正常,但是目前数据量比较小,不知道以后会不会有查询性能问题
还有一种方法,可以把查询拆开,分开查
<collection property="agents" column="{id=id}" select="getAgents">
<!--子类查询列-->
</collection>
<select id="getAgents" resultMap="agents">
SELECT * FROM cra
LEFT JOIN ca ON cra.agent_id = ca.id
WHERE 1=1 AND
ca.id=#{id}
</select>
emmmmm数据量大的时候应该是第二种方法效率更高一点,不过不管怎样,都得拆成两步,目前我还没有找到一步到位的办法
个人觉得group by 方法改动较小,而且这次的情况是sql涉及的表比较多,查询也比较复杂,所以选择了第一种方式