mssqlpagesqlhelper.java

文件上传用例,纯sql分页源码,有用的着地方请下载 试用

package org.derrick.jdbc.page.sql;
/**
 * 
 * @author 刘冬宝Oct 26, 2006
   版权申明：所有人员均可自由修改源码并再发布，但必须保留此相关信息
 *
 */
public class MssqlPageSqlHelper extends PageSqlHelper {
	private static final String SORT_DESC = "desc";

	private static final String SORT_ASC = "asc";

	private String lastOrderByField = null;

	private String lastOrderByFieldSort = null;

	private String reverseLastOrderByFieldSort = null;

	public MssqlPageSqlHelper() {
		super();
	}

	public MssqlPageSqlHelper(int recordnumPerPage, String fieldClause,
			String conditionClause) {
		super(recordnumPerPage, fieldClause, conditionClause);
	}

	/**
	 * 返回用于获取第几页的sql，页号从一开始记数
	 */
	protected String buildPageSql(int pageNo) {
		parseOrderByClause();
		StringBuffer sqlBuffer = new StringBuffer("select top ");
		sqlBuffer.append(this.getRecordnumPerPage());
		sqlBuffer.append(" * from (select top ");
		sqlBuffer.append(this.getRecordTotalnum() - (pageNo - 1)
				* this.getRecordnumPerPage());
		sqlBuffer.append(" ");
		sqlBuffer.append(this.getFieldClause());
		sqlBuffer.append(" from ");
		sqlBuffer.append(this.getConditionClause());
		sqlBuffer.append(" order by ");
		sqlBuffer.append(reverseOrderByClause());
		sqlBuffer.append(") t order by t.");
		sqlBuffer.append(this.lastOrderByField);
		sqlBuffer.append(" ");
		sqlBuffer.append(this.lastOrderByFieldSort);

		if (logger.isDebugEnabled()) {
			logger.debug("getPageSql(" + pageNo + "):" + sqlBuffer.toString());
		}
		return sqlBuffer.toString();
	}

	private String reverseOrderByClause() {
		return this.getOrderByClause().replaceAll(lastOrderByFieldSort,
				reverseLastOrderByFieldSort);
	}

	private void parseOrderByClause() {
		if (this.lastOrderByField != null && this.lastOrderByFieldSort != null
				&& this.reverseLastOrderByFieldSort != null)
			return;
		String tmp = this.getOrderByClause().trim();
		lastOrderByFieldSort = SORT_ASC;
		reverseLastOrderByFieldSort = SORT_DESC;
		int sortIndex = tmp.lastIndexOf(SORT_DESC);
		if (sortIndex > 0) {
			lastOrderByFieldSort = SORT_DESC;
			reverseLastOrderByFieldSort = SORT_ASC;
		} else {
			sortIndex = tmp.lastIndexOf(SORT_ASC);
		}
		lastOrderByField = tmp.substring(tmp.lastIndexOf(".") + 1, sortIndex);
		if (logger.isDebugEnabled()) {
			logger.debug("lastOrderByField:" + lastOrderByField);
			logger.debug("sort:" + lastOrderByFieldSort);
		}
	}

}