使用jdbc分页取出oracle数据

首先是分页工具类

如下代码:

 

public class PageKit {
    /**
     * 当前第几页
     */
    private int page;
    /**
     * 每页条数
     */
    private int pageSize;
    /**
     * 总页数
     */
    private int totalPage;
    /**
     * 总条数
     */
    private int total;
    /**
     * 当前启始页
     */
    private int offer;

    /**
     * oracle 分页 开始页码
     */
    private int startPage;
    /**
     * oracle 分页 结束页码
     */
    private int endPage;

    public int getStartPage() {
        return startPage;
    }

    public void setStartPage(int startPage) {
        this.startPage = startPage;
    }

    public int getEndPage() {
        return endPage;
    }

    public void setEndPage(int endPage) {
        this.endPage = endPage;
    }

    public int getPage() {
        return page;
    }

    public void setPage(int page) {
        this.page = page;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public int getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }

    public int getTotal() {
        return total;
    }

    public void setTotal(int total) {
        this.total = total;
    }

    public int getOffer() {
        return offer;
    }

    public void setOffer(int offer) {
        this.offer = offer;
    }

    public PageKit(int page, int pageSize) {
        this.page = page;
        this.pageSize = pageSize;
        resetOffer();
    }

    public PageKit(int page, int pageSize, int total) {
        this.page = page;
        this.pageSize = pageSize;
        this.total = total;
        resetOffer();
    }

    public void exec(PageHandler handler){
        do {
            String str = " curr page : %s ,total page : %s, curr page num: %s,page size: %s" ;
            log.info(String.format(str, page,totalPage,offer,pageSize));
            handler.exec(this);
            page++;
            resetOffer();
        } while (totalPage  > page);
    }

    public void execOracle(PageOracleHandler handler){
        boolean exec;
        do {
            String str = " curr page : %s ,total page : %s, start page: %s,end page: %s" ;
            log.info(String.format(str, page,totalPage,startPage,endPage));
            // list条数小于每页个数说明已到最后一页，返回false
            exec = handler.exec(this);
            page++;
            resetOffer();
        } while (exec);
    }

    private void resetOffer() {
        int i = page * pageSize;
        this.offer = page == 0 ? 0 : i;
        //mysql 使用的分类参数
        if (pageSize == 0) {
            totalPage = 0;
        } else {
            totalPage = total % pageSize == 0 ? total / pageSize : total / pageSize + 1;
        }

        //oracle 使用的分类参数 分页从1开始，
        this.startPage = offer + 1;
        this.endPage = pageSize + offer;
    }
    @FunctionalInterface
    public  interface PageOracleHandler{
        /**
         * oracle 分页处理器
         * @param pageKit 分页
         */
        boolean exec(PageKit pageKit);
    }

    @FunctionalInterface
    public  interface PageHandler{
        /**
         * mysql
         * @param pageKit 分页
         */
        void exec(PageKit pageKit);
    }
}

2. 下面是直接分页逻辑代码 

 

void jdbc() {
        ResultSet rs = null;
        PreparedStatement stmt = null;
        Connection conn = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String dbURL = "jdbc:oracle:thin:@localhost:1521:system";
            conn = DriverManager.getConnection(dbURL, "wang01", "wang01");
            System.out.println("连接成功");

            PageKit kit = new PageKit(0,3);
            Connection finalConn = conn;
            kit.execOracle(pageKit -> {
                PreparedStatement preparedStatement = null;
                ResultSet resultSet = null;
                List<String> list = new ArrayList<>();
                try {
                    String sql = "select \"ju.phone\" as phone from (select rownum as rn,a.* from my_view_phone a )  where rn >= ? and rn <=? ";
                    preparedStatement = finalConn.prepareStatement(sql);
                    preparedStatement.setInt(1,pageKit.getStartPage());
                    preparedStatement.setInt(2,pageKit.getEndPage());
                    resultSet = preparedStatement.executeQuery();
                    while (resultSet.next()) {
                        String name = resultSet.getString("phone");
                        list.add(name);
                    }
                    System.out.println(list);
                    return list.size() >= pageKit.getPageSize();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }finally {
                    try {
                        if (resultSet != null) {
                            resultSet.close();
                            resultSet = null;
                        }
                        if (preparedStatement != null) {
                            preparedStatement.close();
                            preparedStatement = null;
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                return false;
            });
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (stmt != null) {
                    stmt.close();
                    stmt = null;
                }
                if (conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

3，总共8条数据，每页3条。

15:49:53.237 [main] INFO PageKit - curr page : 0 ,total page : 0, start page: 1,end page: 3
[13811125522, 13811125523, 13811125524]
15:49:53.370 [main] INFO PageKit - curr page : 1 ,total page : 0, start page: 4,end page: 6
[13811125525, 13811125526, 13811125527]
15:49:53.428 [main] INFO PageKit - curr page : 2 ,total page : 0, start page: 7,end page: 9
[13811125528, 222]