PageHelper複雜分頁

weixin_41238665發表於2020-12-23

三張表關聯，查詢出滿足條件的記錄，分頁顯示出來

封裝的物件

public class RefundInfoVo implements Serializable {
    private BigDecimal refundInfoId;

    private String refundsLocation;

    private Date refundsDate;

    private String receiptorName;

    private String payeesPhone;

    private String agentsName;

    private String agentsPhone;

    private String accountsName;

    private String accountsNo;

    private String banksName;

    private BigDecimal refundAmtLowercase;

    private String refundAmtLowercaseCurrency;

    private String refundAmtUppercase;

    private String refundAmtUppercaseCurrency;

    private String refundReason;

    private String refundReasonDsc;

    private String operatorRefundName;

    private Date createRefundDtm;

    private Date updateRefundDtm;

    private String createpersonId;

    private String updatepersonId;

    private String operatorId;

    private BigDecimal status;

    private Date approvalDate;

    private String approvalRemark;

    private static final long serialVersionUID = 1L;

    private List<TBfuRefundPassengerInfo> refundPassengerInfos;

    private List<TBfuAttachment> attachmentList;
}

業務層實現


public interface AfterSaleService {
public PageInfo<RefundInfoVo> getAllRefundInfoAsPageInfo(Integer pageNum, String refundTktNo, String refundPassagerName);

/**
     * 分頁顯示所有滿足條件的退票單資訊
     * @return
     */
    @Override
    public PageInfo<RefundInfoVo> getAllRefundInfoAsPageInfo(Integer pageNum, String refundTktNo, String refundPassagerName) {
        List<RefundInfoVo> refundInfoVos=new ArrayList<RefundInfoVo>();
        PageInfo<RefundInfoVo> info=null;
        String refundTktNo01="";
        String refundPassagerName01="";
        try {
            Page<RefundInfoVo> page = PageHelper.startPage(pageNum, 10);
            System.out.println("==============getAllRefundInfoAsPageInfo=========================");
           if(!StringUtil.isEmpty(refundTktNo)){
               refundTktNo01="%"+refundTktNo+"%";
           }
           if(!StringUtil.isEmpty(refundPassagerName)){
               refundPassagerName01="%"+refundPassagerName+"%";
           }
            List<BigDecimal> refundMainInfoIds = afterSaleDao.getAllRefundInfoAsPageInfo(refundTktNo01, refundPassagerName01);
            info= new PageInfo<>(page.getResult());
            for (BigDecimal refundMainInfoId : refundMainInfoIds) {
                RefundInfoVo refundInfoVo = tBfuRefundMaininfoMapperExtend.selectByPrimaryKey(refundMainInfoId);
                List<TBfuRefundPassengerInfo> tBfuRefundPassengerInfos = tBfuRefundPassengerInfoMapperExtend.selectByRefundMainInfoId(refundMainInfoId);
                List<TBfuAttachment> tBfuAttachments = tBfuAttachmentMapperExtend.selectByRefundMainInfoId(refundMainInfoId);
                refundInfoVo.setRefundPassengerInfos(tBfuRefundPassengerInfos);
                refundInfoVo.setAttachmentList(tBfuAttachments);
                refundInfoVos.add(refundInfoVo);
            }

            info.setList(refundInfoVos);
            log.info("退票所有資訊查詢返回的集合為:{}",info);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("分頁展示所有的退票資訊發生異常，{}",e);
        }

        return info;
    }

dao層介面

public interface AfterSaleDao{
List<BigDecimal> getAllRefundInfoAsPageInfo(@Param("refundTktNo") String refundTktNo01,@Param("refundPassagerName") String refundPassagerName01);
}

mybatis.xml的配置

<select id="getAllRefundInfoAsPageInfo" resultType="bigDecimal">
        select  REFUND_INFO_ID from  (select distinct  m.REFUND_INFO_ID as REFUND_INFO_ID ,m.CREATE_REFUND_DTM from t_bfu_refund_maininfo m left join T_BFU_REFUND_PASSENGER_INFO p on m.refund_info_id=p.refund_maininfo_id left join t_bfu_attachment a
        on m.refund_info_id=a.key_1
        where 1=1
        <if test="refundTktNo!=null and refundTktNo!=''">
            AND p.REFUND_TICKET like #{refundTktNo}
        </if>
        <if test="refundPassagerName!=null and refundPassagerName!=''">
            AND p.REFUND_PASSENGER_NAME like #{refundPassagerName}
        </if>
         order by m.CREATE_REFUND_DTM desc)
	</select>

測試

在這裡插入圖片描述

{
    "total": 24,
    "list": [
        {
            "refundInfoId": 1042,
            "refundsLocation": "555",
            "refundsDate": "2020-12-01T16:00:00.000+0000",
            "receiptorName": "55",
            "payeesPhone": "555",
            "agentsName": "5555",
            "agentsPhone": "555",
            "accountsName": "55",
            "accountsNo": "5555",
            "banksName": "55",
            "refundAmtLowercase": 555,
            "refundAmtLowercaseCurrency": "CNY",
            "refundAmtUppercase": "伍佰伍拾伍元整",
            "refundAmtUppercaseCurrency": "CNY",
            "refundReason": "自願退票",
            "refundReasonDsc": "5555",
            "operatorRefundName": "55",
            "createRefundDtm": "2020-12-01T16:00:00.000+0000",
            "updateRefundDtm": "2020-12-01T16:00:00.000+0000",
            "createpersonId": "yc_sha_00",
            "updatepersonId": null,
            "operatorId": "55",
            "status": 2,
            "approvalDate": "2020-12-01T16:00:00.000+0000",
            "approvalRemark": null,
            "refundPassengerInfos": [
                {
                    "ticketNo": "555-",
                    "amountPaid": 55,
                    "completeRouting": "555",
                    "usedAmount": 55,
                    "usedSector": "55",
                    "fareRefunded": 555,
                    "refundTicket": "555",
                    "taxRefunded": 55,
                    "issueDate": "2020-12-01T16:00:00.000+0000",
                    "serviceCharge": 55,
                    "issuePlace": "555",
                    "amountRefunded": 555,
                    "refundPassengerId": 1043,
                    "refundMaininfoId": 1042,
                    "refundPassengerName": "111"
                }
            ],
            "attachmentList": []
        },
        {
            "refundInfoId": 1043,
            "refundsLocation": "6666",
            "refundsDate": "2020-12-01T16:00:00.000+0000",
            "receiptorName": "66",
            "payeesPhone": "6666",
            "agentsName": "6",
            "agentsPhone": "6666",
            "accountsName": "6",
            "accountsNo": "66",
            "banksName": "66",
            "refundAmtLowercase": 666,
            "refundAmtLowercaseCurrency": "CNY",
            "refundAmtUppercase": "陸佰陸拾陸元整",
            "refundAmtUppercaseCurrency": "CNY",
            "refundReason": "自願退票",
            "refundReasonDsc": "666",
            "operatorRefundName": "6666",
            "createRefundDtm": "2020-12-01T16:00:00.000+0000",
            "updateRefundDtm": "2020-12-01T16:00:00.000+0000",
            "createpersonId": "yc_sha_00",
            "updatepersonId": null,
            "operatorId": "6666",
            "status": 2,
            "approvalDate": "2020-12-01T16:00:00.000+0000",
            "approvalRemark": null,
            "refundPassengerInfos": [
                {
                    "ticketNo": "666-",
                    "amountPaid": 6,
                    "completeRouting": "666",
                    "usedAmount": 66,
                    "usedSector": "66",
                    "fareRefunded": 66,
                    "refundTicket": "666",
                    "taxRefunded": 66,
                    "issueDate": "2020-12-07T16:00:00.000+0000",
                    "serviceCharge": 66,
                    "issuePlace": "66",
                    "amountRefunded": 66,
                    "refundPassengerId": 1044,
                    "refundMaininfoId": 1043,
                    "refundPassengerName": "111"
                }
            ],
            "attachmentList": []
        },
        {
            "refundInfoId": 1044,
            "refundsLocation": "777777",
            "refundsDate": "2020-12-01T16:00:00.000+0000",
            "receiptorName": "7777",
            "payeesPhone": "7777",
            "agentsName": "7777",
            "agentsPhone": "7777",
            "accountsName": "7777",
            "accountsNo": "7777",
            "banksName": "7777",
            "refundAmtLowercase": 7777,
            "refundAmtLowercaseCurrency": "CNY",
            "refundAmtUppercase": "柒仟柒佰柒拾柒元整",
            "refundAmtUppercaseCurrency": "CNY",
            "refundReason": "自願退票",
            "refundReasonDsc": "7777",
            "operatorRefundName": "7777",
            "createRefundDtm": "2020-12-01T16:00:00.000+0000",
            "updateRefundDtm": "2020-12-01T16:00:00.000+0000",
            "createpersonId": "yc_sha_00",
            "updatepersonId": "yc_sha_00",
            "operatorId": "7777",
            "status": 10,
            "approvalDate": "2020-12-01T16:00:00.000+0000",
            "approvalRemark": null,
            "refundPassengerInfos": [
                {
                    "ticketNo": "7777",
                    "amountPaid": 7777,
                    "completeRouting": "7777",
                    "usedAmount": 7777,
                    "usedSector": "7777",
                    "fareRefunded": 7777,
                    "refundTicket": "7777",
                    "taxRefunded": 7777,
                    "issueDate": "2020-12-01T16:00:00.000+0000",
                    "serviceCharge": 7777,
                    "issuePlace": "7777",
                    "amountRefunded": 7777,
                    "refundPassengerId": 1045,
                    "refundMaininfoId": 1044,
                    "refundPassengerName": "111"
                }
            ],
            "attachmentList": []
        },
        {
            "refundInfoId": 3343,
            "refundsLocation": "333",
            "refundsDate": "2020-12-01T16:00:00.000+0000",
            "receiptorName": "33",
            "payeesPhone": "33",
            "agentsName": "3",
            "agentsPhone": "3",
            "accountsName": "33",
            "accountsNo": "3",
            "banksName": "333",
            "refundAmtLowercase": 3333,
            "refundAmtLowercaseCurrency": "CNY",
            "refundAmtUppercase": "叄仟叄佰叄拾叄元整",
            "refundAmtUppercaseCurrency": "CNY",
            "refundReason": "自願退票",
            "refundReasonDsc": "33333",
            "operatorRefundName": "333",
            "createRefundDtm": "2020-12-01T16:00:00.000+0000",
            "updateRefundDtm": "2020-12-01T16:00:00.000+0000",
            "createpersonId": "yc_sha_00",
            "updatepersonId": null,
            "operatorId": "yc_sha_00",
            "status": 2,
            "approvalDate": "2020-12-01T16:00:00.000+0000",
            "approvalRemark": null,
            "refundPassengerInfos": [],
            "attachmentList": []
        }
    ],
    "pageNum": 3,
    "pageSize": 10,
    "size": 4,
    "startRow": 21,
    "endRow": 24,
    "pages": 3,
    "prePage": 2,
    "nextPage": 0,
    "isFirstPage": false,
    "isLastPage": true,
    "hasPreviousPage": true,
    "hasNextPage": false,
    "navigatePages": 8,
    "navigatepageNums": [
        1,
        2,
        3
    ],
    "navigateFirstPage": 1,
    "navigateLastPage": 3
}

相關文章