通用sql分页

        /**
	 * @通用分页生成sql语句
	 * @param sql			要查询的sql语句
	 * @param pageNum		查询的页码(第几页)
	 * @param pageSize		每页显示条数
	 * @param orderKey		排序字段名(此项必须有要么程序默认为ID排序)
	 * @param order			排序类型（desc,asc）
	 * @return 生成后分页的sql
	 */
	public static String fomartPageSql(String sql,int pageNum,int pageSize,String orderKey[],String order[]){
		String desc = " DESC";
		String asc = " ASC";
		String orderR[] = null;
		if(order!=null){
			orderR = new String[order.length];
			for(int i = 0;i<order.length;i++){
				if("DESC".equals(order[i].toUpperCase().trim())){
					orderR[i] = "ASC";
				}else if("ASC".equals(order[i].toUpperCase().trim())){
					orderR[i] = "DESC";
				}
			}
			
		}
		StringBuffer orderByB = new StringBuffer();
		StringBuffer orderByAD = new StringBuffer();
		//判断是否有排序字段
		if(orderKey!=null){
			//有排序字段
			if(order!=null&&orderR!=null&&orderKey.length==order.length){
				for(int i=0;i<orderKey.length;i++){
					if(i==0){
						orderByB.append(orderKey[i]).append(" ").append(orderR[i]);
						orderByAD.append(orderKey[i]).append(" ").append(order[i]);
					}else{
						orderByB.append(",").append(orderKey[i]).append(" ").append(orderR[i]);
						orderByAD.append(",").append(orderKey[i]).append(" ").append(order[i]);
					}
					
				}
			}else{
				//没有排序类型或与排序字段长度不对应
				//默认升序排列
				for(int i=0;i<orderKey.length;i++){
					if(i==0){
						orderByB.append(orderKey[i]).append(desc);
						orderByAD.append(orderKey[i]).append(asc);
					}else{
						orderByB.append(",").append(orderKey[i]).append(desc);
						orderByAD.append(",").append(orderKey[i]).append(asc);
					}
				}
			}
		}else{
			//默认为ID排序
			orderByB.append("ID").append(desc);
			orderByAD.append("ID").append(asc);
		}
		
		StringBuffer resultSql = new StringBuffer();
		resultSql.append("select * from( select");
		resultSql.append(" top (case when (").append(pageNum*pageSize);
		resultSql.append(" )>(select count(*) from (");
		resultSql.append(sql);
		resultSql.append(" ) as conts) then (");
		resultSql.append(" case when (");
		//----------------此处防止top为负数-------------
		resultSql.append("(select count(*) from (");
		resultSql.append(sql);
		resultSql.append(" ) as conts)-");
		resultSql.append((pageNum-1)*pageSize);
		resultSql.append(")>0 then (");
		resultSql.append("(select count(*) from (");
		resultSql.append(sql);
		resultSql.append(" ) as conts)-");
		resultSql.append((pageNum-1)*pageSize);
		resultSql.append(" ) else 0 end ");
		//-------------------------------------
		resultSql.append(" ) else ");
		resultSql.append(pageSize);
		resultSql.append(" end) * from (");
		resultSql.append("select top(");
		resultSql.append(pageNum*pageSize);
		resultSql.append(")* from (");
		resultSql.append(" select top(");
		resultSql.append(pageNum*pageSize);
		resultSql.append(" )* from ( ");
		resultSql.append(sql);
		resultSql.append(" ) as pageTableA");
		if(!"".equals(orderByAD.toString().trim())){
			resultSql.append(" order by ").append(orderByAD);
		}
		resultSql.append(" ) as pageTableB");
		if(!"".equals(orderByB.toString().trim())){
			resultSql.append(" order by ").append(orderByB);
		}
		resultSql.append(" ) as pageTableC");
		resultSql.append(" ) as pageTableD");
		if(!"".equals(orderByAD.toString().trim())){
			resultSql.append(" order by ").append(orderByAD);
		}
		return resultSql.toString();
	}/**

	 * @测试代码
	 */
	public static void main(String[] args) {
		String sql = "分页的sql语句";
		int pageNum = 页码;
		int pageSize = 显示条数;
		String orderKey[] = {"排序字段"};
		String order[] = {"排序类型"};//如果有排序类型应该与排序字段一一对应。如果没有程序默认升序
		
		System.out.println(fomartPageSql(sql, pageNum, pageSize, orderKey, null));
	}

 

注意：如果不写字段，表里要有ID字段。在程序中默认认为不写排序字段将按ID排序。