当前使用版本(必填,否则不予处理)
3.4.1
该问题是如何引起的？(确定最新版也有问题再提!!!)
mapper中自己写了一个左连接，对这个左连接进行分页查询。
重现步骤(如果有就写完整)
mapper方法：
<select id="selectStoreroomOutboundRecordList" resultType="com.bkgtsoft.spd.web.entity.vo.StoreroomOutboundRecordVO">
SELECT
a.*
FROM
storeroom_outbound_record a LEFT JOIN storeroom_outbound_record_detail b ON a.id = b.head_id
${ew.customSqlSegment}
</select>
mapper调用： pagenum=1 pagesize=100 其他参数都是空的
@Override
public RespBean<Page<StoreroomOutboundRecordVO>> listStoreroomOutboundRecord(StoreroomOutboundDTO dto) {
QueryWrapper<StoreroomOutboundRecordVO> queryWrapper = Wrappers.<StoreroomOutboundRecordVO>query()
.eq(ObjectUtil.isNotNull(dto.getMaterialId()), "b.material_id", dto.getMaterialId())
.eq(ObjectUtil.isNotNull(dto.getMaterialTypeId()), "b.material_type_id", dto.getMaterialTypeId())
.eq(ObjectUtil.isNotNull(dto.getWarehouseId()), "a.out_warehouse_id", dto.getWarehouseId())
.ge(ObjectUtil.isNotEmpty(dto.getStartTime()), "a.delivery_date", dto.getStartTime())
.le(ObjectUtil.isNotEmpty(dto.getEndTime()), "a.delivery_date", dto.getEndTime())
.like(StrUtil.isNotBlank(dto.getUseDepartment()), "a.use_department", dto.getUseDepartment())
.orderByDesc("a.update_time");
Page<StoreroomOutboundRecordVO> page = baseMapper.selectStoreroomOutboundRecordList(new Page<StoreroomOutboundRecordVO>(dto.getPageNum(), dto.getPageSize()), queryWrapper);
log.info("总数为{},内容为{}", page.getTotal(), page.getRecords().size());
return RespBean.succeed(GeneralRespEnum.EXECUTE_SUCCESS, page);
}
返回结果,log日志：
总数为26,内容为31
完整SQL日志：
`
[spd-web:192.168.124.5:9003] 2021-01-14 21:35:30.032 INFO 1617 [] [PollingServerListUpdater-0] com.netflix.config.ChainedDynamicProperty [checkAndFlip,115] Flipping property: spd-activiti.ribbon.ActiveConnectionsLimit to use NEXT property: niws.loadbalancer.availabilityFilteringRule.activeConnectionsLimit = 2147483647
[spd-web:192.168.124.5:9003] 2021-01-14 21:35:30.212 DEBUG 1617 [] [main] com.bkgtsoft.spd.web.mapper.StoreroomOutboundRecordMapper.selectStoreroomOutboundRecordList_mpCount [debug,137] ==> Preparing: SELECT COUNT() FROM storeroom_outbound_record a
[spd-web:192.168.124.5:9003] 2021-01-14 21:35:30.722 DEBUG 1617 [] [main] com.bkgtsoft.spd.web.mapper.StoreroomOutboundRecordMapper.selectStoreroomOutboundRecordList_mpCount [debug,137] ==> Parameters:
[spd-web:192.168.124.5:9003] 2021-01-14 21:35:30.817 DEBUG 1617 [] [main] com.bkgtsoft.spd.web.mapper.StoreroomOutboundRecordMapper.selectStoreroomOutboundRecordList_mpCount [debug,137] <== Total: 1
[spd-web:192.168.124.5:9003] 2021-01-14 21:35:30.843 DEBUG 1617 [] [main] com.bkgtsoft.spd.web.mapper.StoreroomOutboundRecordMapper.selectStoreroomOutboundRecordList [debug,137] ==> Preparing: SELECT a. FROM storeroom_outbound_record a LEFT JOIN storeroom_outbound_record_detail b ON a.id = b.head_id ORDER BY a.update_time DESC LIMIT ?
[spd-web:192.168.124.5:9003] 2021-01-14 21:35:30.884 DEBUG 1617 [] [main] com.bkgtsoft.spd.web.mapper.StoreroomOutboundRecordMapper.selectStoreroomOutboundRecordList [debug,137] ==> Parameters: 100(Long)
[spd-web:192.168.124.5:9003] 2021-01-14 21:35:30.921 DEBUG 1617 [] [main] com.bkgtsoft.spd.web.mapper.StoreroomOutboundRecordMapper.selectStoreroomOutboundRecordList [debug,137] <== Total: 31
[spd-web:192.168.124.5:9003] 2021-01-14 21:35:30.925 INFO 1617 [] [main] com.bkgtsoft.spd.web.service.impl.StoreroomOutboundRecordServiceImpl [listStoreroomOutboundRecord,44] 总数为26,内容为31
{"code":205,"data":{"current":1,"hitCount":false,"optimizeCountSql":true,"orders":[],"pages":1,"records":[{"outWarehouse":"马保国","totalAmount":6300.00,"useDepartment":"骨科"},{"outWarehouse":"马保国","totalAmount":3600.00,"useDepartment":"骨科"},{"outWarehouse":"信息科仓库","totalAmount":900.00,"useDepartment":"设备科"},{"outWarehouse":"信息科仓库","totalAmount":0.00,"useDepartment":"设备科"},{"outWarehouse":"信息科仓库","totalAmount":1590.00,"useDepartment":"设备科"},{"outWarehouse":"信息科仓库","totalAmount":4504.00,"useDepartment":"设备科"},{"outWarehouse":"信息科仓库","totalAmount":4504.00,"useDepartment":"设备科"},{"outWarehouse":"信息科仓库","totalAmount":4504.00,"useDepartment":"设备科"},{"outWarehouse":"信息科仓库","totalAmount":60.00,"useDepartment":"设备科"},{"outWarehouse":"信息科仓库","totalAmount":210.00,"useDepartment":"设备科"},{"outWarehouse":"信息科仓库","totalAmount":210.00,"useDepartment":"设备科"},{"outWarehouse":"马保国","totalAmount":4444.00,"useDepartment":"1112"},{"outWarehouse":"信息科仓库","totalAmount":30.00,"useDepartment":"1112"},{"outWarehouse":"马保国","totalAmount":8888.00,"useDepartment":"骨科"},{"outWarehouse":"马保国","totalAmount":22220.00,"useDepartment":"骨科"},{"outWarehouse":"信息科仓库","totalAmount":30.00,"useDepartment":"设备科"},{"outWarehouse":"马保国","totalAmount":4444.00,"useDepartment":"设备科"},{"outWarehouse":"信息科仓库","totalAmount":150.00,"useDepartment":"设备科"},{"outWarehouse":"信息科仓库","totalAmount":150.00,"useDepartment":"设备科"},{"outWarehouse":"信息科仓库","totalAmount":150.00,"useDepartment":"设备科"},{"outWarehouse":"信息科仓库","totalAmount":30.00,"useDepartment":"设备科"},{"outWarehouse":"信息科仓库","totalAmount":90.00,"useDepartment":"设备科"},{"outWarehouse":"信息科仓库","totalAmount":30.00,"useDepartment":"设备科"},{"outWarehouse":"信息科仓库","totalAmount":30.00,"useDepartment":"设备科"},{"outWarehouse":"信息科仓库","totalAmount":1020.00,"useDepartment":"设备科"},{"outWarehouse":"信息科仓库","totalAmount":1020.00,"useDepartment":"设备科"},{"outWarehouse":"马保国","totalAmount":22220.00,"useDepartment":"骨科"},{"outWarehouse":"马保国","totalAmount":22220.00,"useDepartment":"骨科"},{"outWarehouse":"马保国","totalAmount":22220.00,"useDepartment":"骨科"},{"outWarehouse":"马保国","totalAmount":22220.00,"useDepartment":"骨科"},{"outWarehouse":"马保国","totalAmount":22220.00,"useDepartment":"骨科"}],"searchCount":true,"size":100,"total":26},"msg":"操作成功","status":true,"time":"2021-01-14T21:35:30.959"}
`
自我分析
发现在查询select count(*)的时候，mapper中的SQL语句的左连接的b表没有left join都没有起作用。
换成pagehelper进行分页，能正常查询出总条数31和记录数据也是31.
已经吓死我了，项目里都是这么写的呀， 那岂不是之前都有问题，而测试不用心，没有测试出来
报错信息
日志已经打印出来了，在上面。 求解决呀，项目里都是这么写的分页，参考你们的文档写的。
Comment From: miemieYaho
用 3.4.1.2-SNAPSHOT 配置插件的 optimizeJoin 属性值为 false
Comment From: zengziqiang
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.1.2-SNAPSHOT</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-extension</artifactId>
<version>3.4.1.2-SNAPSHOT</version>
</dependency>
请问是这两个依赖吗？ maven无法下载。
配置如下： 我加了你说的那个属性，运行后还是会把left join后面的去掉
mybatis-plus:
mapper-locations: classpath:/mapper/*.xml,classpath*:/mapper/common/*.xml
#实体扫描，多个package用逗号或者分号分隔
type-aliases-package: com.bkgtsoft.spd.web.entity.po;com.bkgtsoft.spd.db.po
global-config:
db-config:
logic-delete-value: 1
logic-not-delete-value: 0
type-enums-package: com.bkgtsoft.spd.common.constant.busi;com.bkgtsoft.spd.db.enums
optimizeJoin: false
麻烦能描述清晰一点不
Comment From: miemieYaho
https://github.com/baomidou/mybatis-plus/blob/3.0/mybatis-plus-extension/src/main/java/com/baomidou/mybatisplus/extension/plugins/inner/PaginationInnerInterceptor.java
Comment From: zengziqiang
https://github.com/baomidou/mybatis-plus/blob/3.0/mybatis-plus-extension/src/main/java/com/baomidou/mybatisplus/extension/plugins/inner/PaginationInnerInterceptor.java
3.4.2的版本才有optimizeJoin这个属性，还好我机智，你们官网当前版本是3.4.1，但是maven可以下载3.4.2这个版本，然后设置了一下就可以了，完美解决。不过我感觉你们加这个判断真没有必要，很容易出现数据不一致问题，其他人在用应该也有这个问题，估计是他们没有发现或者不是这个版本，最好你们默认值用false吧。 不过你们反馈还是挺快的
Comment From: miemieYaho
首先你这种一对多分页如果不去掉join只查主表的话,主表数据是会重复的.其次如果是一对一去掉join反而更好,3.4.2是中午才发的