paginationdao.java

基于SSH (struts+spring+hibernate)框架设计的 CRM客户关系管理系统

package com.csu.crm.common.page;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import com.csu.crm.common.DBConnection;
import com.csu.crm.common.Debug;
import com.csu.crm.common.exception.DAOSystemException;
/**
* Classname    :PaginationDAO
* Description  :用于进行分页显示的类
* Date         :2007-07-10
* Author       :li.haibo
* Version      : 1.0
*/


public class PaginationDAO implements Serializable
{

    // 变量定义区
    private long	nTotal = 0;			/** 总的记录数 */
    private int		nPageSum = 1;		/** 总页数 */
    private int		nPageNo = 1;		/** 第几页,从1开始 */
    private int		nPageRows = -1;		/** 每页几行,如果为-1,则表示不进行分页 */
    private String sql="";
    private DBConnection conn=new DBConnection();
    public long getNTotal() {
        return nTotal;
    }

    public int getNPageNo() {
        return nPageNo;
    }

    public int getNPageSum() {
        return nPageSum;
    }

    public void setNPageRows(int nPageRows) {
        this.nPageRows = nPageRows;
    }

    public void setNTotal(long nTotal) {
        this.nTotal = nTotal;
    }

    public void setNPageNo(int nPageNo) {
        this.nPageNo = nPageNo;
    }

    public void setNPageSum(int nPageSum) {
        this.nPageSum = nPageSum;
    }

    public int getNPageRows() {
        return nPageRows;
    }

    /**
     * 取总的记录数，返回long
     */
    public long getTotalNum()
    {
        return  nTotal;
    }
    /**
    * 取总的记录数，返回String
    */
    public String getTotalNumStr()
    {
        return  Long.toString(nTotal);
    }
    /**
    * 设置总的记录数
    */
    public void setTotalNum(long aValue)
    {
        nTotal = aValue;
    }
    /**
    * 设置总的记录数
    */
    public void setTotalNum(String aValue)
    {
        try {
            nTotal = Long.parseLong(aValue);
        } catch (Exception e) {
            nTotal = 0;
        }
    }

/////////////
    /**
    * 取总页数，返回int
    */
    public int getPageSum()
    {
        return  nPageSum;
    }
    /**
    * 取总页数，返回String
    */
    public String getPageSumStr()
    {
        return  Integer.toString(nPageSum);
    }
    /**
    * 设置总页数
    */
    public void setPageSum(int aValue)
    {
        nPageSum = aValue;
    }
    /**
    * 设置总页数
    */
    public void setPageSum(String aValue)
    {
        try {
            nPageSum = Integer.parseInt(aValue);
        } catch (Exception e) {
            nPageSum = 0;
        }
    }


/////////////
    /**
    * 取第几页，返回int
    */
    public int getPageNo()
    {
        return  nPageNo;
    }
    /**
    * 取第几页，返回String
    */
    public String getPageNoStr()
    {
        return  Integer.toString(nPageNo);
    }
    /**
    * 设置第几页
    */
    public void setPageNo(int aValue)
    {
        nPageNo = aValue;
    }
    /**
    * 设置第几页
    */
    public void setPageNo(String aValue)
    {
        try {
            nPageNo = Integer.parseInt(aValue);
        } catch (Exception e) {
            nPageNo = 0;
        }
    }

/////////////
    /**
    * 取每页几行，返回int
    */
    public int getPageRows()
    {
        return  nPageRows;
    }
    /**
    * 取每页几行，返回String
    */
    public String getPageRowsStr()
    {
        return  Integer.toString(nPageRows);
    }
    /**
    * 设置每页几行
    */
    public void setPageRows(int aValue)
    {
        nPageRows = aValue;
    }
    /**
    * 设置每页几行
    */
    public void setPageRows(String aValue)
    {
        try {
            nPageRows = Integer.parseInt(aValue);
        } catch (Exception e) {
            nPageRows = 0;
        }
    }


    ///////////////////////////////////////////////////////////
    // 内部公用函数
    ///////////////////////////////////////////////////////////


   /**
    * 把输入的SQL语句转成分页显示的查询语句, 不带分页信息
    * 输入:sqlstr
    * 输出:pageSql
    */
       private String convertToPageSQL(String sqlstr) throws Exception
    {
        try {


        String pageSql = " select * from (select rownum as rno, pagesql.* from (" + sqlstr + ") pagesql) ";

        return pageSql;

        }catch(Exception e){

            throw e;
        }

    }



    /**
     * 把输入的SQL语句转成计算总记录数的语句
     * 输入:sqlstr
     * 输出:sumSql
     */
    private String convertToSumSQL(String sqlstr)
    {
        //System.out.println(" select count(*) as TOTAL_RECORD from (" + sqlstr + ") ");
        sql= "select count(*) as TOTAL_RECORD from (" + sqlstr + ") ";
        return " select count(*) as TOTAL_RECORD from (" + sqlstr + ") ";
    }


    ///////////////////////////////////////////////////////////
    // 支持 PreparedStatement 的分页显示
    ///////////////////////////////////////////////////////////


   /**
    * 对分页的SQL语句添加分页信息, , PreparedStatement 版本
    * 输入:pageSql
    * 输出:pageSqlPS
    */
    private String appendPageSQLToPS(String pageSql) throws Exception
    {
        try {

        return pageSql + " where rownum <= ? and rno > ? ";

        }catch(Exception e){

            throw e;
        }
    }

    /**
     * 对输入的SQL语句生成分页处理的 PreparedStatement
     * 输入: conn, sqlstr
     * 输出: psPage
     */
     public PreparedStatement makePagePrepareStatement(Connection conn, String sqlstr) throws Exception
     {
        try {

            if (conn == null) {
                Debug.print("ERROR: Connection is NULL !!!");
                return null;
            }

            String 	pageSql = null;
               if (nPageRows == -1) {	// 不进行分页显示
                   pageSql = sqlstr;
               } else {
                   pageSql = appendPageSQLToPS(convertToPageSQL(sqlstr));
               }

            return conn.prepareStatement(pageSql);

        }catch(Exception e){

            throw e;
        }

     }


     /**
      * 返回所需页数的Resultset, PreparedStatement 版本,
       * 输入:psPage, nPos
       * 输出:rs
       */
     public ResultSet executeQuery(PreparedStatement psPage, int nPos) throws DAOSystemException
     {
        try {

            if (psPage == null) {
                Debug.print("ERROR: PreparedStatement is NULL !!!");
                return null;
            }

            int 	i = nPos;

               if (nPageRows != -1) {	// 进行分页显示
                   if (nPageNo > nPageSum) { nPageNo = nPageSum; }
                psPage.setInt(i ++, nPageRows);
                psPage.setInt(i ++, (nPageNo - 1) * nPageRows);
               }

            return psPage.executeQuery();

        }catch(Exception e){
            e.printStackTrace();	// 打印出错堆栈

            DAOSystemException appe = new DAOSystemException("进行PreparedStatement分页显示时出错！");
            throw appe;
        }
     }

     /**
      * 对总记录也应生成要处理的 PreparedStatement
      * 输入: conn, sqlstr
      * 输出: psSum
      */
     public PreparedStatement makeSumPrepareStatement(Connection conn, String sqlstr) throws Exception
     {
        try {

            if (conn == null) {
                Debug.print("ERROR: Connection is NULL !!!");
                return null;
            }

            return conn.prepareStatement(convertToSumSQL(sqlstr));

        }catch(Exception e){

            throw e;
        }

     }

     /**
      * 得出总记录数, PreparedStatement 版本
      * 输入: psSum
      * 输出: nTotal
      */
     public long getSumNumberBySQL(PreparedStatement psSum) throws DAOSystemException
     {
         ResultSet 	rs=null;
        try {

            if (psSum == null) {
                Debug.print("ERROR: PreparedStatement is NULL !!!");
                return 0;
            }
               rs = psSum.executeQuery();

               if (rs != null) {
                   if (rs.next()) { // 肯定只有一行数据的
                       nTotal = Long.parseLong(rs.getString("TOTAL_RECORD"));
                   }
               }

               if (nPageRows != 0) {
                   nPageSum = (int)(nTotal / nPageRows);
                if (nPageSum == 0 || nTotal % nPageRows != 0)  		// 最后一页，因为没有记录时也要显示1页
                    nPageSum ++;
               } else {
                   nPageSum = 1;
               }

            return nTotal;

        }catch(Exception e){
            e.printStackTrace();	// 打印出错堆栈

            DAOSystemException appe = new DAOSystemException(sql+"PreparedStatement取总记录数时出错！:"+e.toString());
            throw appe;
        }finally{
        	conn.closeResult(rs);
            
        }

     }



    ///////////////////////////////////////////////////////////
    // 支持 Statement 的分页显示
    ///////////////////////////////////////////////////////////

    /**
     * 把SQL语句转成分页显示的语句
     * 输入:sqlstr
     * 输出:pageSql
     */
    public String appendPageSQLToStatement(String sqlstr) throws Exception
    {
        try {

        if (nPageNo > nPageSum) { nPageNo = nPageSum; }
        String pageSql = convertToPageSQL(sqlstr)
                        + " where rownum <= " + nPageRows + " and rno > " + (nPageNo - 1) * nPageRows;

        return pageSql;

        }catch(Exception e){

            throw e;
        }

    }


     /**
      * 返回所需页数的Resultset, Statement 版本,
       * 输入:stmt, sqlstr
       * 输出:rs
       */
     public ResultSet executeQuery(Statement stmt, String sqlstr) throws DAOSystemException
     {
        try {
            ResultSet	rs = null;

            // 首先得出总记录数
            rs = stmt.executeQuery(convertToSumSQL(sqlstr));

               if (rs != null) {
                   if (rs.next()) { // 肯定只有一行数据的
                       nTotal = Long.parseLong(rs.getString("TOTAL_RECORD"));
                   }
               }

               if (nPageRows != 0) {
                   nPageSum = (int)(nTotal / nPageRows);
                if (nPageSum == 0 || nTotal % nPageRows != 0)  		// 最后一页，因为没有记录时也要显示1页
                    nPageSum ++;
               } else {
                   nPageSum = 1;
               }

               // 其次得出当页的ResultSet
               String pageSql = "";
               if (nPageRows == -1) {	// 不进行分页显示
                   pageSql = sqlstr;
               } else {
                   pageSql = appendPageSQLToStatement(sqlstr);
               }
               rs = stmt.executeQuery(pageSql);

               return rs;

        }catch(DAOSystemException appe){
            throw appe;

        }catch(Exception e){
            e.printStackTrace();	// 打印出错堆栈

            DAOSystemException appe = new DAOSystemException("进行Statement分页显示时出错！");
            throw appe;
        }
     }
}