生成分页SQL的方法，支持MSSQLSRVER2000和MYSQL DB2 ORACLE
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 >= 21if(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;}