常用数据库的 sql 转分页sql代码,Java代码库,德仔网

代码语言

代码分类

【Java】常用数据库的 sql 转分页sql

作者:Sub / 发布于2013/9/13/ 944


	// mysql
	public String get_sql_pagelist(Pagelist pagelist, String sql) {
	 if (pagelist.getFirstResult() == 0) {
	 return sql + " limit " + pagelist.getPageSize();
	 } else {
	 return sql + " limit " + pagelist.getFirstResult() + "," + pagelist.getPageSize();
	 }
	}

	// h2
	public String get_sql_pagelist(Pagelist pagelist, String sql) {
	 sql = sql + " limit " + pagelist.getPageSize();
	 if (pagelist.getFirstResult() > 0) {
	 sql = sql + " offset " + pagelist.getFirstResult();
	 }
	 return sql;
	}

	// oracle
	public String get_sql_pagelist(Pagelist pagelist, String sql) {
	 sql = "select * from ("
	 + " select t.*, ROWNUM row from ("
	 + sql
	 + " ) t where ROWNUM <= " + (pagelist.getFirstResult() + pagelist.getPageSize())
	 + ")";
	 if (pagelist.getFirstResult() > 0) {
	 sql = sql + " where row > " + pagelist.getFirstResult();
	 }
	 return sql;
	}

	// sqlserver 2005
	// @return null if create sql error. (no order by statement)
	public String get_sql_pagelist(Pagelist pagelist, String sql) {
	 if (pagelist.getFirstResult() == 0) {
	 sql = "select top " + pagelist.getPageSize() + " * from (" + sql + ") as temp";
	 } else {
	 sql = sql.replaceAll("\\s+", " ");

	 // 从原始 sql 中获取 order by 子句
	 int orderby_pos = sql.toLowerCase().lastIndexOf(" order by ");
	 String sorts = null;
	 if (orderby_pos > 0) {
	 sorts = sql.substring(orderby_pos);
	 if (sorts.indexOf(")") > 0) {
	 sorts = null; // skip the nested order by
	 }
	 }
	 if (sorts == null) {
	 //sorts = "order by id";
	 return null;
	 }

	 int lastNum = pagelist.getFirstResult() + pagelist.getPageSize();
	 sql = "select * from ("
	 + " select top " + lastNum + " row_number() over(" + sorts + ") as row, * from (" + sql + ")"
	 + ") as temp where row > " + pagelist.getFirstResult();
	 }
	 return sql;
	}

试试其它关键字

　sql　　转分页　

同语言下

可能有用的

Sub贡献的其它代码(1)

.常用数据库的 sql 转分页sql