mybatis 分页查询导致数据缺失问题
问题
分页查询主表数据，需要关联查询出子表数据，子表数据超过一条的情况下，查询出的每页条数小于预期
存在问题的写法
<?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.lyc.admin.dao.db.DbUnitInfoDao">
<resultMap type="com.lyc.common.entity.db.DbUnitInfoEntity" id="DbUnitInfoMap">
<result property="id" column="id"/>
<result property="organizationCode" column="organization_code"/>
<result property="shortName" column="short_name"/>
<result property="address" column="address"/>
<result property="socialCreditCode" column="social_credit_code"/>
<result property="legalRepresentative" column="legal_representative"/>
<result property="statutoryContact" column="statutory_contact"/>
<result property="mainBusinessincome" column="main_businessincome"/>
<result property="industryCode" column="industry_code"/>
<result property="mainRawMaterials" column="main_raw_materials"/>
<result property="mainProduct" column="main_product"/>
<result property="hazardLevel" column="hazard_level"/>
<result property="enRiskLevel" column="en_risk_level"/>
<result property="latitude" column="latitude"/>
<result property="longitude" column="longitude"/>
<result property="produceStatus" column="produce_status"/>
<result property="unifiedCode" column="unified_code"/>
<result property="delFlg" column="del_flg"/>
<result property="createId" column="create_id"/>
<result property="createTime" column="create_time"/>
<result property="updateId" column="update_id"/>
<result property="updateTime" column="update_time"/>
<result property="introducce" column="introducce"/>
<result property="sort" column="sort"/>
<result column="unit_code" property="unitCode"/>
<result column="height" property="height"/>
<result column="park_unit_name" property="parkUnitName"/>
<result column="area" property="area"/>
</resultMap>
<resultMap id="unitInfoMap" type="com.lyc.common.vo.DbUnitVo">
<id column="id" property="id"/>
<result column="organization_code" property="organizationCode"/>
<result column="short_name" property="shortName"/>
<result column="address" property="address"/>
<result column="social_credit_code" property="socialCreditCode"/>
<result column="legal_representative" property="legalRepresentative"/>
<result column="statutory_contact" property="statutoryContact"/>
<result column="main_businessincome" property="mainBusinessincome"/>
<result column="industry_code" property="industryCode"/>
<result column="main_raw_materials" property="mainRawMaterials"/>
<result column="main_product" property="mainProduct"/>
<result column="hazard_level" property="hazardLevel"/>
<result column="en_risk_level" property="enRiskLevel"/>
<result column="latitude" property="latitude"/>
<result column="longitude" property="longitude"/>
<result column="produce_status" property="produceStatus"/>
<result column="unified_code" property="unifiedCode"/>
<result column="introducce" property="introducce"/>
<result column="sort" property="sort"/>
<result column="unit_code" property="unitCode"/>
<result column="height" property="height"/>
<result column="park_unit_name" property="parkUnitName"/>
<result column="area" property="area"/>
<collection property="files" ofType="com.lyc.common.vo.FileVo">
<result column="file_id" property="fileId"/>
<result column="bucket_name" property="bucketName"/>
<result column="file_name" property="fileName"/>
<result column="url" property="url"/>
<result column="original" property="name"/>
</collection>
</resultMap>
<sql id="baseColumn">
du.id,
du.organization_code,
du.short_name,
du.address,
du.social_credit_code,
du.legal_representative,
du.statutory_contact,
du.main_businessincome,
du.industry_code,
du.main_raw_materials,
du.main_product,
du.hazard_level,
du.en_risk_level,
du.latitude,
du.longitude,
du.produce_status,
du.unified_code,
du.del_flg,
du.create_id,
du.create_time,
du.update_id,
du.update_time,
du.introducce,
du.sort,
du.unit_code,
du.height,
du.park_unit_name,
du.area
</sql>
<select id="unitInfoVoPage" resultMap="unitInfoMap">
select
<include refid="baseColumn"/>,
sf.id as file_id,
sf.bucket_name,
sf.file_name,
sf.original,
concat('/', sf.bucket_name, '/', sf.file_name) as url
from db_unit_info du
left join sys_file sf on du.unified_code = sf.unique_code and sf.del_flg = 0
<where>
AND du.del_flg = 0
<if test="dbUnitInfoEntity.shortName != null and dbUnitInfoEntity.shortName != ''">
AND du.short_name LIKE CONCAT('%', #{dbUnitInfoEntity.shortName}, '%')
</if>
<if test="dbUnitInfoEntity.parkUnitName != null and dbUnitInfoEntity.parkUnitName != ''">
AND du.park_unit_name LIKE CONCAT('%', #{dbUnitInfoEntity.parkUnitName}, '%')
</if>
<if test="dbUnitInfoEntity.hazardLevel != null and dbUnitInfoEntity.hazardLevel != ''">
AND du.hazard_level = #{dbUnitInfoEntity.hazardLevel}
</if>
<if test="dbUnitInfoEntity.produceStatus != null and dbUnitInfoEntity.produceStatus != ''">
AND du.produce_status = #{dbUnitInfoEntity.produceStatus}
</if>
<if test="dbUnitInfoEntity.industryCode != null and dbUnitInfoEntity.industryCode != ''">
AND du.industry_code = #{dbUnitInfoEntity.industryCode}
</if>
<if test="dbUnitInfoEntity.unitCode != null and dbUnitInfoEntity.unitCode != ''">
AND du.unit_code = #{dbUnitInfoEntity.unitCode}
</if>
</where>
</select>
</mapper>
修改后的写法
<?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.lyc.admin.dao.db.DbUnitInfoDao">
<resultMap type="com.lyc.common.entity.db.DbUnitInfoEntity" id="DbUnitInfoMap">
<result property="id" column="id"/>
<result property="organizationCode" column="organization_code"/>
<result property="shortName" column="short_name"/>
<result property="address" column="address"/>
<result property="socialCreditCode" column="social_credit_code"/>
<result property="legalRepresentative" column="legal_representative"/>
<result property="statutoryContact" column="statutory_contact"/>
<result property="mainBusinessincome" column="main_businessincome"/>
<result property="industryCode" column="industry_code"/>
<result property="mainRawMaterials" column="main_raw_materials"/>
<result property="mainProduct" column="main_product"/>
<result property="hazardLevel" column="hazard_level"/>
<result property="enRiskLevel" column="en_risk_level"/>
<result property="latitude" column="latitude"/>
<result property="longitude" column="longitude"/>
<result property="produceStatus" column="produce_status"/>
<result property="unifiedCode" column="unified_code"/>
<result property="delFlg" column="del_flg"/>
<result property="createId" column="create_id"/>
<result property="createTime" column="create_time"/>
<result property="updateId" column="update_id"/>
<result property="updateTime" column="update_time"/>
<result property="introducce" column="introducce"/>
<result property="sort" column="sort"/>
<result column="unit_code" property="unitCode"/>
<result column="height" property="height"/>
<result column="park_unit_name" property="parkUnitName"/>
<result column="area" property="area"/>
</resultMap>
<resultMap id="unitInfoMap" type="com.lyc.common.vo.DbUnitVo">
<id column="id" property="id"/>
<result column="organization_code" property="organizationCode"/>
<result column="short_name" property="shortName"/>
<result column="address" property="address"/>
<result column="social_credit_code" property="socialCreditCode"/>
<result column="legal_representative" property="legalRepresentative"/>
<result column="statutory_contact" property="statutoryContact"/>
<result column="main_businessincome" property="mainBusinessincome"/>
<result column="industry_code" property="industryCode"/>
<result column="main_raw_materials" property="mainRawMaterials"/>
<result column="main_product" property="mainProduct"/>
<result column="hazard_level" property="hazardLevel"/>
<result column="en_risk_level" property="enRiskLevel"/>
<result column="latitude" property="latitude"/>
<result column="longitude" property="longitude"/>
<result column="produce_status" property="produceStatus"/>
<result column="unified_code" property="unifiedCode"/>
<result column="introducce" property="introducce"/>
<result column="sort" property="sort"/>
<result column="unit_code" property="unitCode"/>
<result column="height" property="height"/>
<result column="park_unit_name" property="parkUnitName"/>
<result column="area" property="area"/>
<collection property="files" column="unified_code" select="selectFilesInfo"/>
</resultMap>
<sql id="baseColumn">
du.id,
du.organization_code,
du.short_name,
du.address,
du.social_credit_code,
du.legal_representative,
du.statutory_contact,
du.main_businessincome,
du.industry_code,
du.main_raw_materials,
du.main_product,
du.hazard_level,
du.en_risk_level,
du.latitude,
du.longitude,
du.produce_status,
du.unified_code,
du.del_flg,
du.create_id,
du.create_time,
du.update_id,
du.update_time,
du.introducce,
du.sort,
du.unit_code,
du.height,
du.park_unit_name,
du.area
</sql>
<select id="unitInfoVoPage" resultMap="unitInfoMap">
select
<include refid="baseColumn"/>
from db_unit_info du
<where>
AND du.del_flg = 0
<if test="dbUnitInfoEntity.shortName != null and dbUnitInfoEntity.shortName != ''">
AND du.short_name LIKE CONCAT('%', #{dbUnitInfoEntity.shortName}, '%')
</if>
<if test="dbUnitInfoEntity.parkUnitName != null and dbUnitInfoEntity.parkUnitName != ''">
AND du.park_unit_name LIKE CONCAT('%', #{dbUnitInfoEntity.parkUnitName}, '%')
</if>
<if test="dbUnitInfoEntity.hazardLevel != null and dbUnitInfoEntity.hazardLevel != ''">
AND du.hazard_level = #{dbUnitInfoEntity.hazardLevel}
</if>
<if test="dbUnitInfoEntity.produceStatus != null and dbUnitInfoEntity.produceStatus != ''">
AND du.produce_status = #{dbUnitInfoEntity.produceStatus}
</if>
<if test="dbUnitInfoEntity.industryCode != null and dbUnitInfoEntity.industryCode != ''">
AND du.industry_code = #{dbUnitInfoEntity.industryCode}
</if>
<if test="dbUnitInfoEntity.unitCode != null and dbUnitInfoEntity.unitCode != ''">
AND du.unit_code = #{dbUnitInfoEntity.unitCode}
</if>
</where>
order by du.unit_code
</select>
<select id="selectFilesInfo" resultType="com.lyc.common.vo.FileVo">
select sf.id as file_id,
sf.bucket_name,
sf.file_name,
sf.original as name,
concat('/', sf.bucket_name, '/', sf.file_name) as url
from sys_file sf
<where>
AND sf.del_flg = 0
AND sf.unique_code = #{unifiedCode}
</where>
</select>
</mapper>
本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 ALLBS！
支付宝- 微信
- qq
相关推荐
评论