|
mybatisPlus+多表联查+多条件分页查询
为什么会写这篇文章？自定义mapper自定义mapper映射文件定义接口service定义service接口实现类
为什么会写这篇文章？
分页插件在分页时处理一对多的结构的情况时，总条数与实际条数不匹配。这时候需要自定义sql分页查询语句来实现一对多分页条件查询。希望能帮到你！
自定义mapper
package com.minglei.hotnews.mapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderDetailDto;
import com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderLoadDto;
import com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderLoadParam;
import javafx.scene.control.Pagination;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface ClientuserCustomMapper {
/**
* 根据工单ID获取详情
* @param workOrderId
* @return
*/
WorkOrderDetailDto loadWorkOrderDetailDto(Long workOrderId);
// List loadWorkOrderDetails(WorkOrderLoadParam para);
/**
* 多条件分页显示工单
* @param para
* @return
*/
List loadWorkOrderDetails(WorkOrderLoadParam para);
/**
* 多条件分页显示工单的工单数量
* @param param
* @return
*/
Integer loadWorkOrderDetailsCount(WorkOrderLoadParam param);
/**
* 多条件分页显示工单
*
* @param page
* @param param
* @return
*/
List loadWorkOrder(Page page, @Param("param") WorkOrderLoadParam param);
}
自定义mapper映射文件
select
w.id as id,
w.addtime as addtime,
w.ordernum as ordernum,
w.shop_id as shop_Id,
s.id as shopId,
s.shopname as shopname,
s.address as address,
wlog.Id as logId,
wlog.memo as memo,
wlog.addtime as logaddtime from workorder as w
left JOIN shop as s ON w.shop_id=s.id
LEFT JOIN workoderlog as wlog ON w.id=wlog.workorder_id
WHERE w.id=#{workOrderId,jdbcType=BIGINT}
select
w.id as id,
w.addtime as addtime,
w.ordernum as ordernum,
w.shop_id as shop_Id,
s.id as shopId,
s.shopname as shopname,
s.address as address,
wlog.Id as logId,
wlog.memo as memo,
wlog.addtime as logaddtime from workorder as w
left JOIN shop as s ON w.shop_id=s.id
LEFT JOIN workoderlog as wlog ON w.id=wlog.workorder_id
and w.ordernum like concat('%',#{OrderNum},'%')
and s.shopname like concat('%',#{ShopName},'%')
and w.id in(select temp.id from( select distinct childw.id id from workorder childw LEFT JOIN shop childs ON childw.shop_id=childs.id
and childw.ordernum like concat('%',#{OrderNum},'%')
and childs.shopname like concat('%',#{ShopName},'%')
limit #{PageNum},#{PageSize}) as temp)
select count(temp.id) from( select distinct childw.id id from workorder childw LEFT JOIN shop childs ON childw.shop_id=childs.id
and childw.ordernum like concat('%',#{OrderNum},'%')
and childs.shopname like concat('%',#{ShopName},'%')
) as temp
select
w.id as id,
w.addtime as addtime,
w.ordernum as ordernum,
w.shop_id as shop_Id,
s.id as shopId,
s.shopname as shopname,
s.address as address
from workorder as w
left JOIN shop as s ON w.shop_id=s.id
and w.ordernum like concat('%',#{param.OrderNum},'%')
and s.shopname like concat('%',#{param.ShopName},'%')
ORDER BY w.id
定义接口service
package com.minglei.hotnews.service.WorkOrderService;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderDetailDto;
import com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderLoadDto;
import com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderLoadParam;
import java.util.List;
import java.util.Map;
/**
* 工单service
*/
public interface IWorkOrderLoadService {
/**
* 多条件分页显示工单
* @param param
* @return
*/
// List loadWorkOrder(WorkOrderLoadParam param);
Map loadWorkOrder(WorkOrderLoadParam param);
/**
* 使用mp自带分页查询工单
* @param param
* @return
*/
Page loadWorkOrderCustom(WorkOrderLoadParam param);
}
定义service接口实现类
package com.minglei.hotnews.service.WorkOrderService.Impl;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderDetailDto;
import com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderLoadDto;
import com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderLoadParam;
import com.minglei.hotnews.mapper.ClientuserCustomMapper;
import com.minglei.hotnews.service.WorkOrderService.IWorkOrderLoadService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Component
public class WorkOrderLoadService implements IWorkOrderLoadService {
@Autowired
ClientuserCustomMapper clientuserCustomMapper;
/**
* 多条件分页显示工单
*
* @param param
* @return
*/
@Override
public Map loadWorkOrder(WorkOrderLoadParam param) {
Map workOrderRes=new HashMap();
Integer aLong = clientuserCustomMapper.loadWorkOrderDetailsCount(param);
workOrderRes.put("total",aLong);
List workOrderDetailDtos = clientuserCustomMapper.loadWorkOrderDetails( param);
workOrderRes.put("list",workOrderDetailDtos);
return workOrderRes;
}
/**
* 使用mp自带分页查询工单
*
* @param param
* @return
*/
@Override
public Page loadWorkOrderCustom(WorkOrderLoadParam param) {
Page page=new Page(param.PageNum,param.PageSize);
List workOrderLoadDtos = clientuserCustomMapper.loadWorkOrder(page, param);
return page.setRecords(workOrderLoadDtos);
}
}
|