pagerutil.java

本文论述了一个前台笔记本销售系统的开发过程

/*
 * Created on 2006-2-18
 */
package com.set.utils;

/**
 * <p>
 * Title:
 * </p>
 * <p>
 * Description:
 * </p>
 * <p>
 * Copyright: Copyright (c) 2006
 * </p>
 * <p>
 * Company:
 * </p>
 * 
 * @author tommy.zeng
 * @version 1.0
 */
public class PagerUtil {

	public static String getPageSql(final String sql, int pageNo, int pageSize) {
		if (pageSize <= 0) {
			return sql;
		}
		StringBuffer querySql = new StringBuffer(sql);
		querySql.append(" limit ").append((pageNo - 1) * pageSize).append(",")
				.append(pageSize);
		return querySql.toString();
	}

	/**
	 * 组合记数的SQL 1)添换select * 为 select count(*) as totalcnt 2)去掉order by(如果存在的话)
	 * 3)如果有group by ,则采用select count(*) as totalcnt from(sql)a 方式
	 * 
	 * @param sql
	 * @return
	 */
	public static String getQueryCountSql(final String sql) {
		String newSql1 = sql.toUpperCase();
		int index = newSql1.indexOf("FROM");
		int index2 = newSql1.lastIndexOf(" ORDER BY ");
		int index3 = newSql1.lastIndexOf("GROUP BY");
		String newSql = "select count(*) as totalcnt ";
		if (index3 == -1) {
			if (index2 == -1) {
				newSql += sql.substring(index);
			} else {
				newSql += sql.substring(index, index2);
			}
		} else {
			newSql += "from(@@@)a";
			newSql = newSql.replaceAll("@@@", sql);
		}
		return newSql;
		// String newSql = new String("select count(*) as totalcnt from(@@@)a");
		//
		// return newSql.replaceAll("@@@", sql);

	}
}