/**
* @通用分页生成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排序。