生成分页SQL的方法，支持MSSQLSRVER2000和MYSQL DB2 ORACLE

最新推荐文章于 2024-10-14 22:42:36 发布

wanglu271991027

最新推荐文章于 2024-10-14 22:42:36 发布

阅读量102

点赞数

分类专栏：数据库文章标签：数据库

本文链接：https://blog.csdn.net/wanglu271991027/article/details/84291161

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1.生成分页SQL的方法，支持MSSQLSRVER2000和MYSQL DB2 ORACLE

public static String getQuery(int rownum,int pagesize,int pagenum,String Fromsql,Object[] Orders,Object[] Descs,int DbType){
	String orderby1 = "";
	String orderby2 ="";
	if(Orders!=null&&Descs!=null)
	for (int i = 0; i < Orders.length; i++) {
		String order = Orders[i]==null?null:((String)Orders[i]);
		boolean desc = Descs[i]==null?null:((Boolean)Descs[i]);
		if(StringUtils.isNotBlank(order)){
			if(StringUtils.isNotBlank(orderby1)){
				orderby1 = orderby1+",";
				orderby2 = orderby2+",";
			}
			orderby1 = order+" "+(desc?"desc":"asc")+" ";
			orderby2 = order+" "+(desc?"asc":"desc")+" ";
		}
	}
	log.debug("pagesize="+pagesize+":pagenum="+pagenum);
	//MYSQL:SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 50, 10
	//SQLSERVER:SELECT TOP "+pagesize+" * FROM ( SELECT TOP "+(pagesize*pagenum)+" "+Fromsql+" ORDER BY "+orderby1+" ) as tempTable   ORDER BY "+orderby2
	//DB2: SELECT * FROM ( SELECT B.*, ROWNUMBER() OVER() AS RN FROM (SELECT * FROM <TABLE_NAME>) AS B)AS A WHERE A.RN BETWEEN <START_NUMBER> AND <END_NUMBER>;
	//select * from (select 字段1,字段2,字段3,字段4,字段5,rownumber() over(order by 排序字段 asc ) as rowid  from 表名 )as a where a.rowid >= startPage AND a.rowid <endPage
	//SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM <= 40 )WHERE RN >= 21
	if(DbType==IDb.DB_MYSQL){
		return "SELECT "+Fromsql+" "+(StringUtils.isBlank(orderby1)?"":" ORDER BY "+orderby1)+" LIMIT "+((pagenum-1)*pagesize)+", "+pagesize;
	}else if(DbType==IDb.DB_SQLSERVER){//解决MSSQL2000最后一页显示数据过多的问题
		int topnum = pagesize*pagenum;
		int p = pagesize;
		if(topnum>rownum){
			p = pagesize-(topnum-rownum);
		}
		return "select * from (select top "+p+" * from (SELECT TOP "+pagesize+" * FROM ( SELECT TOP "+(pagesize*pagenum)+" "+Fromsql+" "+(StringUtils.isBlank(orderby1)?"":" ORDER BY "+orderby1)+" ) as tempTable  "+(StringUtils.isBlank(orderby2)?"":" ORDER BY "+orderby2)+") as tmp) as tmp2 "+(StringUtils.isBlank(orderby1)?"":" ORDER BY "+orderby1)+" ";
	}else if(DbType==IDb.DB_DB2){
		return "SELECT * FROM ( SELECT B.*, ROWNUMBER() OVER() AS RN FROM (SELECT "+Fromsql+" "+(StringUtils.isBlank(orderby1)?"":" ORDER BY "+orderby1)+") AS B)AS A WHERE A.RN BETWEEN "+((pagenum-1)*pagesize)+" AND "+pagesize*pagenum;
	}else if(DbType==IDb.DB_ORACLE){
		return "SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT  "+Fromsql+" "+(StringUtils.isBlank(orderby1)?"":" ORDER BY "+orderby1)+") A WHERE ROWNUM <= "+(pagesize*pagenum)+" ) WHERE RN >= "+((pagenum-1)*pagesize);
	}
	return null;
}