常用数据库分页sql
Oracle 分页
SELECT x.* from (SELECT t.*,rownum rn from table t where rownum <= lastRowNumInPage) x where x.rn >= startRowNum
select * from table limit start, pageNum
select * from (select t.* , rownumber() over( order by c) as rowno from table t ) temp where temp.rowno between 1 and 10
select limit startIndex recordCount * from xtable
SELECT * FROM ( SELECT Top N * FROM (SELECT Top (M + N - 1) * FROM table Order by pk desc) t1 ) t2 Order by pk asc
import java.util.List;import org.springframework.jdbc.core.JdbcTemplate;public class Pagination {public static final int NUMBERS_PER_PAGE = 10;private int totalPages;// 总页数private int page;// 当前页码private List resultList;// 结果集存放Listpublic Pagination(String sql, int currentPage, int numPerPage,JdbcTemplate jTemplate) {if (jTemplate == null) {throw new IllegalArgumentException("com.starhub.sms.util.Pagination.jTemplate is null,please initial it first. ");} else if (sql == null || sql.equals("")) {throw new IllegalArgumentException("com.starhub.sms.util.Pagination.sql is empty,please initial it first. ");}String countSQL = getSQLCount(sql);setPage(currentPage);setTotalPages(numPerPage,jTemplate.queryForInt(countSQL));int startIndex = (currentPage - 1) * numPerPage;//数据读取起始indexStringBuffer paginationSQL = new StringBuffer(" ");paginationSQL.append(sql);paginationSQL.append(" limit "+ startIndex+","+numPerPage);setResultList(jTemplate.queryForList(paginationSQL.toString()));}public String getSQLCount(String sql){String sqlBak = sql.toLowerCase();String searchValue = " from ";String sqlCount = "select count(*) from "+ sql.substring(sqlBak.indexOf(searchValue)+searchValue.length(), sqlBak.length());return sqlCount;}public int getTotalPages() {return totalPages;}public void setTotalPages(int totalPages) {this.totalPages = totalPages;}public int getPage() {return page;}public void setPage(int page) {this.page = page;}public List getResultList() {return resultList;}public void setResultList(List resultList) {this.resultList = resultList;}// 计算总页数public void setTotalPages(int numPerPage,int totalRows) {if (totalRows % numPerPage == 0) {this.totalPages = totalRows / numPerPage;} else {this.totalPages = (totalRows / numPerPage) + 1;}}}