zsquerydataset.java

实现oracle查询数据集分页

package wisesoft;

import com.borland.dx.sql.dataset.QueryDataSet;
import com.borland.dx.dataset.*;
import com.borland.dx.sql.dataset.*;

/**
 * <p>Title: 分页Oracle的querydataset控件</p>
 * <p>Description: 实现数据集分页，按页提取数据显示，支持定位页，总页数，每页行数设置，按关键字取值等</p>
 * <p>Copyright: Copyright (c) 2003</p>
 * <p>Company: </p>
 * @author not attributable
 * @version 1.0
 */

public class ZSQueryDataSet extends QueryDataSet {

  private int iTotalPages=1;
  private int iCurPage=1;
  private int iRowsInPage=10;//每页显示数量
  public String Sqlstr;
  public Database db;

  public ZSQueryDataSet() {
  }

  public int getITotalPages() {
    return iTotalPages;
  }

  public void setITotalPages(int iTotalPages) {
    this.iTotalPages = iTotalPages;
  }

  public int getICurPage() {
    return iCurPage;
  }
  public void setICurPage(int iCurPage) {
    this.iCurPage = iCurPage;
  }
  public void setQueryByPage(int iPage)
  {
    if (!db.isOpen()) {
      db.openConnection();
    }
    close();
    String tmpstr="    select b.* "
           +" from "
           +" (select rownum  as irow,a.* "
           +" from ("+Sqlstr+") a) b "
           +" where irow>"+String.valueOf(iRowsInPage*(iPage-1))
           +" and irow<="+String.valueOf(iRowsInPage*(iPage));
    iCurPage=iPage;
    QueryDescriptor m_QD = new QueryDescriptor(db, tmpstr);
    setQuery(m_QD);

  }
  /**定位某一行
   * 传入关键字名，关键字值，且都转为String型
   * to_char(name)||to_char(callsign)
   * "help"+"帮助"
   * */
  public int getIRowByKey(String colNameString,String colValueString)
  {
    if (!db.isOpen()) {
      db.openConnection();
    }
    close();
    String tmpstr="    select b.irow "
           +" from "
           +" (select rownum  as irow,a.* "
           +" from ("+Sqlstr+") a) b "
           +" where "+colNameString+"='"+colValueString+"'";
  //  ZSCom_Func.Showmessage(tmpstr,"");
    return ZSCom_Func.StringToInt(ZSCom_Func.getSqlValue(tmpstr,db,"irow","2"));
  }

  /**显示所有页面*/
  private void setQueryAllPage()
  {
      if (!db.isOpen()) {
        db.openConnection();
      }
      close();
      String tmpstr=
                " select rownum  as irow,a.* "
                +" from ("+Sqlstr+") a ";
      iCurPage=1;
      iTotalPages=1;
      QueryDescriptor m_QD = new QueryDescriptor(db, tmpstr);
      setQuery(m_QD);
  }
  /**得到总记录数*/
  public int getRecordCount()
  {
    String tmpstr=
                " select count(*)  as icount"
                +" from ("+Sqlstr+") a ";
     return ZSCom_Func.StringToInt(ZSCom_Func.getSqlValue(tmpstr,db,"icount","0"));

  }




  /**初始化数据集，传入Sql语句**/
  public void setQueryByPage(String m_sqlStr,Database m_db)
  {
    db=m_db;
    Sqlstr=m_sqlStr;
    setQueryByPage(1);
    getTotalPages();
  }
  /**下页*/
  public void nextPage()
  {
    if (iCurPage!=iTotalPages)
    {
      iCurPage++;
      setQueryByPage(iCurPage);
    }
  }
  /**所有页*/
  public void allPage()
  {
    setQueryAllPage();
  }

  /**上页*/
  public void priorPage()
  {
    if (iCurPage!=1)
    {
      iCurPage--;
      setQueryByPage( iCurPage);
    }
  }
  /**首页*/
  public void firstPage()
  {
    iCurPage=1;
    setQueryByPage(iCurPage);
  }
  /**末页*/
  public void lastPage()
 {
   iCurPage=getTotalPages();
   setQueryByPage(iCurPage);
 }
  /**得到每页显示行数*/
  public int getIRowsInPage() {
    return iRowsInPage;
  }
  /**设置每页显示行数*/
  public void setIRowsInPage(int iRowsInPage) {
    this.iRowsInPage = iRowsInPage;
  }
  /**得到所有页数*/
  public int getTotalPages()
  {
    String tmpStr=" select trunc(max(b.irow)/"+String.valueOf(iRowsInPage)+")+1 as itotalPage "
                  +" from "
                  +" (select rownum  as irow,a.* "
                  +" from ("+Sqlstr+") a) b ";
    iTotalPages= ZSCom_Func.StringToInt(ZSCom_Func.getSqlValue(tmpStr,db,"itotalPage","1"));
    //始终有一页
    if(iTotalPages==0)
    {
      iTotalPages=1;
    }
    return iTotalPages;
  }
}