常用数据库分页sql

[b]Oracle 分页[/b]

SELECT x.* 
from (SELECT t.*,rownum rn from table t where rownum <= lastRowNumInPage) x 
where x.rn >= startRowNum 

[b]Mysql分页[/b]

select * from table limit start, pageNum 

[b]DB2分页[/b]

select * from (
select t.* , rownumber() over( order by c) as rowno  from table t ) temp where temp.rowno between 1 and 10 

[b]HSQL分页[/b]

select limit startIndex recordCount * from xtable

[b]SQLServer分页[/b]

SELECT *
　　FROM ( SELECT Top N *
　　FROM (SELECT Top (M + N - 1) * FROM table Order by pk desc) t1 ) t2
　　Order by pk asc

[b]spring jdbcTemplate + mysql 分页java代码[/b]

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;	// 结果集存放List

	public 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;	//数据读取起始index

		StringBuffer 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;
		}
	}

}