oracle分页方法及java程序实现

MSSQL SYBASE下面用top 来分页

POSTGRESQL MYSQL就直接用limit更简单了

在oracle 下面有4种方法

oracle分页技术2007年08月13日 星期一 23:08两种分页技术，第二种效率高多了
1. select * from
(select rownum rn,t.* from table t )
where rn <50 and rn>=1

2. select * from
(select rownum rn,t.* from table t rownum<50)
where rn>=1

带排序要求的分页，在9i以上的环境推荐使用分析函数
select * from
(select row_number() over(order by esn) rn,esn,akey from as_esn_info )
where rn <50 and rn>=1
（以上方法验证过）
或者以下

（1）minus差分页 ：select * from table where rownum<=10 minus select * from table where rownum<=5

（2）rownum伪列分页 ：select * from (select rownum tid,t.* from table t where rownum<=10) where tid>5

（3）notin相反分页：select * from table where id not in(select id from table where rownum<=5) and rownum<=5



下面是第二种方法的java实现：

import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
/**
*
* @author renshui
* 对oracle查询进行分页
*
*/
public class Pagination {

private int totalRow; //总记录数
private int totalPage; //总页数
private int currentPage = 1; //当前页码，从1计起
private int numPerPage; //每页记录数
private DataSource dataSource; //数据源
private JdbcTemplate jdbcTemplate; //spring jdbcTemplate
private String sql; //查询语句
public Pagination(String sql,int numPerpage,DataSource dataSource){
this.setSql(sql);
this.setNumPerPage(numPerpage);
jdbcTemplate = new JdbcTemplate(dataSource);
String totalSql = "select count(1) from (" + sql + ")";
this.setTotalRow(jdbcTemplate.queryForInt(totalSql)); //设置总行数
this.setTotalPage(); //计算总页数

}
/**
* 取得下一页内容
* @return 下页内容的列表
*/
public List getNextPage(){

if(currentPage > totalPage)
return null;
int startIndex = (currentPage-1) * numPerPage + 1;
int endIndex = 0;
if(currentPage == totalPage)
endIndex = totalRow;
else
endIndex = currentPage * numPerPage;
StringBuffer paginationSQL = new StringBuffer(" SELECT * FROM ( ");
paginationSQL.append(" SELECT temp.* ,ROWNUM num FROM ( ");
paginationSQL.append(sql);
paginationSQL.append(" ) temp where ROWNUM <= " + endIndex);
paginationSQL.append(" ) WHERE num >= " + startIndex);
System.out.println(paginationSQL.toString());
List list = jdbcTemplate.queryForList(paginationSQL.toString());
currentPage++;
return list;
}
public int getCurrentPage() {
return currentPage;
}

public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}

public DataSource getDataSource() {
return dataSource;
}

public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}

public int getNumPerPage() {
return numPerPage;
}

public void setNumPerPage(int numPerPage) {
this.numPerPage = numPerPage;
}

public int getTotalPage() {
return totalPage;
}

public void setTotalPage() {

if(totalRow%numPerPage == 0)
totalPage = totalRow / numPerPage;
else
totalPage = totalRow / numPerPage + 1;
}

public int getTotalRow() {
return totalRow;
}

public void setTotalRow(int totalRow) {
this.totalRow = totalRow;
}

public String getSql() {
return sql;
}

public void setSql(String sql) {
this.sql = sql;
}

}