oracle自定义SQL查询分页实现Java

项目用MySQL 要从oracle查询一个视图取数据 jpa框架不 智能  只能自己写

然后 涉及到主从数据库 自定义SQL查询分页 废话不多说看我下面的实现

 

Java 分页实现

package com.icss.page;

/**
 * 分页计算工具类
 *
 * @description: some desc
 * @author: haiming
 * @date: 2021/5/20 上午10:54
 */
public class Pager {

    /**
     * 改变这个，当每页大小超过 MAX_FETCH_SIZE 时，这个将是默认的 fetchSize
     */
    public static int DEFAULT_PAGE_SIZE = 20;

    /**
     * ResultSet 最大的 fetch size
     */
    public static int MAX_FETCH_SIZE = 2000;

    // private static final int FIRST_PAGE_NUMBER = 1;

    private int pageNumber;
    private int pageSize;
    private int pageCount;
    private long recordCount;

    public Pager() {
        this.pageNumber = 1;
        this.pageSize = DEFAULT_PAGE_SIZE;
    }

    public Pager(int pageNumber) {
        if (pageNumber < 1) {
            pageNumber = 1;
        }
        this.pageNumber = pageNumber;
        this.pageSize = DEFAULT_PAGE_SIZE;
    }

    public Pager(int pageNumber, int pageSize) {
        if (pageNumber < 1) {
            pageNumber = 1;
        }
        if (pageSize < 1) {
            pageSize = DEFAULT_PAGE_SIZE;
        }
        this.pageNumber = pageNumber;
        this.pageSize = pageSize;
    }

    public Pager resetPageCount() {
        pageCount = -1;
        return this;
    }

    public int getPageCount() {
        if (pageCount < 0) {
            pageCount = (int) Math.ceil((double) recordCount / pageSize);
        }
        return pageCount;
    }

    public int getPageNumber() {
        return pageNumber;
    }

    public int getPageSize() {
        return pageSize;
    }

    public long getRecordCount() {
        return recordCount;
    }

    public Pager setPageNumber(int pn) {

        pageNumber = pn;
        return this;
    }

    public Pager setPageSize(int pageSize) {
        this.pageSize = (pageSize > 0 ? pageSize : DEFAULT_PAGE_SIZE);
        return resetPageCount();
    }

    public Pager setRecordCount(long recordCount) {
        this.recordCount = recordCount > 0 ? recordCount : 0;
        this.pageCount = (int) Math.ceil((double) recordCount / pageSize);
        return this;
    }
    //oracle用到
    public int getOffset() {
        return pageSize * (pageNumber - 1);
    }

    @Override
    public String toString() {
        return String.format("size: %d, total: %d, page: %d/%d",
                pageSize,
                recordCount,
                pageNumber,
                this.getPageCount());
    }


    public boolean isFirst() {
        return pageNumber == 1;
    }


    public boolean isLast() {
        if (pageCount == 0) {
            return true;
        }
        return pageNumber == pageCount;
    }


    public boolean hasNext() {
        return !isLast();
    }

    public boolean hasPrevious() {
        return !isFirst();
    }
}

jpa自定义SQL 注意 第一个是结束分页 第二个参数是起始页面

    /**
     * 查询记录 orcale分页查询
     * @param pageEnd
     * @param pageStart
     * @return
     */
    @Query( value = "SELECT * " +
            "FROM (SELECT T.*, ROWNUM RN " +
            "      FROM ( " +
            "          -- 实际查询SQL \n" +
            "               SELECT CONTRACTNUMBER, " +
            "                      CREATEDATE, " +
            "                      MODIFYDATE, " +
            "                      ORDERSTATUS, " +
            "                      TOTALITY, " +
            "                      BRANDCODE, " +
            "                      BRANDNAME, " +
            "                      PNUMBER " +
            "               FROM NTI.VIEW_DELIVER_GOODS_BENGBU " +
            "           ) T " +
            "      WHERE ROWNUM <= ?) " +
            "WHERE RN > ?" ,nativeQuery = true)
    List<ViewDeliverGoodsBengbu> selectByPage(int pageEnd,int pageStart);

查方法封装

  /**
     * 自定义SQL查询
     * @param page
     * @param pageSize
     * @return
     */
    @Override
    @DataSource(value = DataSourceType.SLAVE)
    public List<ViewDeliverGoodsBengbu> selectAll(int page, int pageSize){
        Pager pager = new Pager(page,pageSize);
        List<ViewDeliverGoodsBengbu> list = viewDeliverGoodsBengbuRepository.selectByPage(pager.getOffset() + pager.getPageSize(),
                pager.getOffset());
        return list;
    }

分页参考 nutz的dao模块

原文地址

https://blog.csdn.net/yhm_brave/article/details/117125121

详情

https://gitee.com/nutz/nutz/blob/master/src/org/nutz/dao/impl/jdbc/oracle/OracleJdbcExpert.java