pagesqlhelper.java

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

package org.derrick.jdbc.page.sql;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.derrick.jdbc.page.InvalidPageNoException;

/**
 * 用于提供分页sql的辅助抽象类
 * @author 刘冬宝Oct 26, 2006
   版权申明：所有人员均可自由修改源码并再发布，但必须保留此相关信息
 *
 */
abstract public class PageSqlHelper {

    /**
     * 无效的记数值
     */
    private static final int INVALID_COUNT = 0;

    private boolean checked = false;

    /**
     * sql语句中的条件集合，不包含order by field asc(or desc), select fieldClause from
     * conditionClause order by orderByClause;
     */
    private String conditionClause = null;

    /**
     * 需要返回的字段集合,select fieldClause from conditionClause order by orderByClause;
     */
    private String fieldClause = null;

    protected final Logger logger = LogManager.getLogger(getClass());

    /**
     * sql语句中的order by子句，不包括 order by,且 asc(or desc) 必须指定。
     */

    private String orderByClause = null;

    /**
     * 每页返回记录条数
     */
    private int recordnumPerPage = INVALID_COUNT;

    /**
     * 总记录数
     */
    private int recordTotalnum = INVALID_COUNT;

    public PageSqlHelper() {
    }

    public PageSqlHelper(int recordnumPerPage, String fieldClause, String conditionClause) {
        this.recordnumPerPage = recordnumPerPage;
        this.fieldClause = fieldClause;
        this.conditionClause = conditionClause;
    }

    /**
     * 检查必要的参数是否设置
     * 
     */
    protected void checkInnerFields() {
        if (checked)
            return;
        checkConditionClause();
        checkOrderByClause();
        checkRecordPageCount();
        checkRecordTotalCount();
        checked = true;
    }

    private void checkRecordTotalCount() {
        if (this.recordTotalnum <= INVALID_COUNT)
            throw new IllegalArgumentException(
                    "must set   record total count !");
    }

    private void checkRecordPageCount() {
        if (this.recordnumPerPage <= INVALID_COUNT)
            throw new IllegalArgumentException(
                    "must set  count record per page !");
    }

    /**
     * 检查conditons是否正确设置
     * 
     */
    private void checkConditionClause() {
        if (this.conditionClause == null)
            throw new IllegalArgumentException("must set conditionClause !");
        String tmp = conditionClause.trim();
        if (tmp.indexOf("from") > -1)
            throw new IllegalArgumentException(
                    "conditionClause can't has [from] word !");

        if (tmp.indexOf("order by") > -1)
            throw new IllegalArgumentException(
                    "conditionClause can't has [order by] word !");
    }

    private void checkOrderByClause() {
        int ascIndex = orderByClause.lastIndexOf("asc");
        int descIndex = orderByClause.lastIndexOf("desc");
        int byIndex = orderByClause.lastIndexOf("by");
        if ((ascIndex < byIndex) && (descIndex < byIndex)) {
            throw new IllegalArgumentException(
                    " order by clause must set asc or desc key word !");
        }
    }

    public String getConditionClause() {
        return conditionClause;
    }

    public String getFieldClause() {
        return fieldClause;
    }

    public String getOrderByClause() {
        return orderByClause;
    }

    /**
     * 返回用于获取第几页的sql，页号从一开始记数
     */
    final public String getPageSql(int pageNo) throws InvalidPageNoException {
        checkInnerFields();
        checkPageNo(pageNo);
        return buildPageSql(pageNo);
    }

    /**
     * 检查页号是否有效
     * 
     * @param pageNo
     */
    private void checkPageNo(int pageNo) throws InvalidPageNoException {
        if (pageNo > this.getTotalPage() || pageNo < 1)
            throw new InvalidPageNoException("Invalid pageNo:[" + pageNo + "]");

    }

    abstract protected String buildPageSql(int pageNo);

    public int getRecordnumPerPage() {
        return recordnumPerPage;
    }

    public int getRecordTotalnum() {
        return recordTotalnum;
    }

    /**
     * 返回用于获取记录总数的sql
     */
    public String getRecordTotalnumSql() {
        checkConditionClause();
        StringBuffer sqlBuffer = new StringBuffer("select count(*) from ");
        sqlBuffer.append(this.conditionClause);
        if (logger.isDebugEnabled()) {
            logger.debug("getRecordTotalCountSql:" + sqlBuffer.toString());
        }
        return sqlBuffer.toString();
    }

    public void setConditionClause(String conditions) {
        this.conditionClause = conditions;
    }

    public void setFieldClause(String fields) {
        this.fieldClause = fields;
    }

    public void setOrderByClause(String orderByClause) {
        this.orderByClause = orderByClause;
    }

    public void setRecordnumPerPage(int recordnumPerPage) {
        this.recordnumPerPage = recordnumPerPage;
    }

    public void setRecordTotalnum(int recordTotalnum) {
        this.recordTotalnum = recordTotalnum;
    }

    public int getTotalPage() {
        return (this.recordTotalnum + this.recordnumPerPage - 1)
                / this.recordnumPerPage;
    }

}