Oracle 高效分页技术 根据ROWNUM分页

/**

 * 这个分页比较高较，项目中的数据是上千万级的数量级

 * 当然了，我的数据库用了索引，where 条件中根据索引来查询是很快的哦，索引怎么用就不在本章的讨论范围之内了

 * 核心SQL语句是：SELECT * FROM (SELECT SUB_TAB.*, ROWNUM RN FROM (your_sql) SUB_TAB WHERE ROWNUM <= 20) WHERE RN >= 1

 * DataSource.java：获取数据库的链接

 * GlobalConst.java：定义全局变量

 * Page.java：分页的核心java bean

 * PageTest.java：一个测试类

 * jdbc.properties：jdbc的配置文件 

*/

/*
 * @author Sugar.Tan<br>
 */

package common;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

/**
 *  pageSize, pageCount, hasNextPage, hasPreviousPage<br>
 * rule: the value of -1 is last page; 1 is the first page.
 */
public class Page {

    private int pageSize;                //一页显示的记录数
    private int pageCount;               //总页数
    private boolean hasNextPage;         //是否有下一页
    private boolean hasPrePage;          //是否有上一页
    private int curPage = 1;             //待显示的页面
    private int tolCount;                //总记录数量
    @SuppressWarnings("unchecked")
    private List dataList;
    private int startRow = 0;
    private int endRow = 10;
   
    private void calPreNext() {
        //this.hasNextPage = curPage == pageCount ? false : true;
        this.hasNextPage = (pageCount == 0 ||curPage == pageCount) ? false : true;
        this.hasPrePage = (curPage > 1) ? true : false;
    }
    private void calPage() {
        pageCount = (tolCount + pageSize - 1) / pageSize;
    }
    private void calStartEnd () {
        startRow = pageSize * (curPage - 1) + 1; //1
        endRow = pageSize * curPage;         //10
    }
    private void volidate() {
        if (curPage == -1 || curPage > pageCount) curPage = pageCount;
        if (curPage == 0 || curPage < -1) curPage = 1;
    }
   
    public void cal() {
        calPage();
        volidate();
        calStartEnd();
        calPreNext();
    }
   
    public int getTolCount() {
        return tolCount;
    }

    public int getStartRow() {
        return startRow;
    }

    public int getEndRow() {
        return endRow;
    }

    public void setEndRow(int endRow) {
        this.endRow = endRow;
    }

    public void setTolCount(int tolCount) {
        this.tolCount = tolCount;
        this.cal();
    }

    public boolean isHasPrePage() {
        return hasPrePage;
    }

    @SuppressWarnings("unchecked")
    public List getDataList() {
        return dataList;
    }

    @SuppressWarnings("unchecked")
    public void setDataList(List dataList) {
        this.dataList = dataList;
    }

   
    /**
     * 构造方法
     */
    public Page(int curPage) {
        this.pageSize = 10;                       //初始设置一页的记录数
        this.hasNextPage = false;                  //初始
        this.hasPrePage = false;                   //初始
        this.curPage = curPage;
    }
   
    public Page(){}

    public int getPageSize() {
        return pageSize;
    }

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

    public int getPageCount() {
        return pageCount;
    }

    public void setPageCount(int pageCount) {
        this.pageCount = pageCount;
    }

    public boolean isHasNextPage() {
        return hasNextPage;
    }

    public void setHasNextPage(boolean hasNextPage) {
        this.hasNextPage = hasNextPage;
    }
   
    public void setHasPrePage(boolean hasPrePage) {
     this.hasPrePage = hasPrePage;
    }

    public int getCurPage() {
        return curPage;
    }

    public void setCurPage(int curPage) {
        this.curPage = curPage;
    }
   
    public static Page newInstance(String curPage, String pageSize) {
        Page page = null;
        if (curPage == null || curPage.equals("")) {
            page = new Page(1);
        } else {
            page = new Page(Integer.parseInt(curPage));
            page.setPageSize(Integer.parseInt(pageSize));
        }
        return page;
    }
   
    /**
     * set the totalCount of page, then calculate
     * @param page
     * @param hphm
     */
    public static void setTolCount(Connection conn, Page page, String sql) {
        Statement st = null;
        ResultSet rs = null;
        try {
            st = conn.createStatement();
            rs = st.executeQuery(new StringBuffer()
                .append(GlobalConst.STR_COUNT)
                .append(sql).append(")").toString());
            if (rs.next()) {
                page.setTolCount(rs.getInt(1));
                page.cal();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) try {rs.close();} catch (SQLException e) {}
            if (st != null) try {st.close();} catch (SQLException e) {}
        }
    }
   
    public static int getTolCount(Connection conn, String sql) {
        Statement st = null;
        ResultSet rs = null;
        try {
            st = conn.createStatement();
            rs = st.executeQuery(new StringBuffer()
                .append(GlobalConst.STR_COUNT)
                .append(sql).append(")").toString());
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        } finally {
            if (rs != null) try {rs.close();} catch (SQLException e) {}
            if (st != null) try {st.close();} catch (SQLException e) {}
        }
        return 0;
    }
   
/*
    public static void main(String args[]){
        Page page = new Page(0);
        page.setTolCount(101);
        page.cal();
        System.out.println("有" + page.getPageCount() + "页");
        System.out.println("StartRow(): " + page.getStartRow());
        System.out.println("EndRow(): " + page.getEndRow());
        System.out.println("有下一页: " + page.isHasNextPage());
        System.out.println("有上一页: " + page.isHasPrePage());
    }
*/   
}

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

//GlobalConst.java

package common;

/**
 * 定义一些通用常量
 * @author Sugar.Tan
 *
 */
public interface GlobalConst {
    public static final String STR_COUNT = "SELECT COUNT(*) FROM (";
   
    //for page
    public static final String STR_PAGE_START = "SELECT * FROM (SELECT SUB_TAB.*, ROWNUM RN FROM (";
   
    /**
     * param1: EndRow number, param2: startRow number.
     */
    public static final String STR_PAGE_END = ") SUB_TAB WHERE ROWNUM <= ?) WHERE RN >= ?";
}

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

//DataSource.java

package common;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
import common.DataSource;

public class DataSource
{
 public Connection getconnection() {
  Connection conn = null;
  try {
   InputStream in = DataSource.class.getResourceAsStream("/jdbc.properties");
   Properties pro = new Properties();
   pro.load(in);
   String classname = pro.getProperty("classname");
   String url = pro.getProperty("url");
   String userid = pro.getProperty("userid");
   String password = pro.getProperty("password");
   Class.forName(classname);
   conn = DriverManager.getConnection(url,userid,password);
  } catch(Exception e) {
   e.printStackTrace();
  }
  return conn;
 }
}

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

#jdbc.properties, 放在src类路径下

classname=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@192.168.1.125:1521:MYORCL
userid=USERONE
password=123456

 

//////////////////////////////////////////////     测试类     /////////////////////////////////////////////////////////////////////////////////////

//PageTest .java

package common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import config.DataSource;

public class PageTest {

 public static void main(String[] args) {
        StringBuffer sbSql = new StringBuffer();
        //在这里拼SQL
        sbSql.append("SELECT * FROM ITS_TABLE1 W");
        sbSql.append(" ORDER BY W.C1 DESC ");
        //获取数据库链接
        Connection conn = new DataSource().getconnection();
        String curPage = "1";
        String pageSize = "10";
        Page page = Page.newInstance(curPage, pageSize);
        common.Page.setTolCount(conn, page, sbSql.toString());

        // do the lst result
        if (page.getTolCount() > 0) {
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                ps = conn.prepareStatement(new StringBuffer().append(GlobalConst.STR_PAGE_START)
                        .append(sbSql)
                        .append(GlobalConst.STR_PAGE_END).toString());
                ps.setInt(1, page.getEndRow());
                ps.setInt(2, page.getStartRow());
                rs = ps.executeQuery();
               
                while (rs.next()) {
                 System.out.println("读一条记录");

//                    ItsObject itsWf = new ItsWf();
//                    itsWf.setHphm(rs.getString(1));
//                    //.........
//                    itsWf.setClbj(rs.getString(12));
//                    lstItsWf.add(itsWf);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                if (rs != null) try {rs.close();} catch (SQLException e) {}
                if (ps != null) try {ps.close();} catch (SQLException e) {}
                if (conn != null) try {conn.close();} catch(SQLException e) {}
            }
        }
    }
}