日期:2014-05-16 浏览次数:20485 次
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; }