jdbc实现分页

 

下面的类是实现分页的接口

 

package com.hit.dao.support;

import java.util.*;
import java.sql.*;

public interface BasicDaoInterface
{
   /**
    * 分页查询
    * @param dto
    * @param pagesize
    * @return
    * @throws Exception
    */	
   public List queryForPage(Map dto,int pagesize)throws Exception;  
   public List queryForPage(Map dto)throws Exception;  
   
   public Map  queryForId(Object id)throws Exception;

   public String getPageInfo(String url);
   public String getPageTable(String url);
   public String getPageTable(String url,boolean type);
   public String getPageOption(String url);
}

 

实现分页接口方法的具体实现

package com.hit.dao.support;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.*;
import java.util.*;


import java.sql.ResultSetMetaData;
import com.hit.system.db.DBUtils;

public abstract class WebPageSupport implements BasicDaoInterface
{
 protected  int defpagesize=10;              //默认情况下,每页长度
 private    int countPage=0;                 //定义总的页码 
 private    int currentPage=0;               //定义当前页码
 private    String contextpath=null;         //工程的上下文路径
 
 private    ResultSet rs = null;             //结果集对象
 private    PreparedStatement pstm = null;   //语句对象
 
 protected  StringBuilder sql = null;        //SQL语句
 protected  List pars = null;                //保存pstm?值的列表
 
 protected final Map  findById(Object id)throws Exception
 {
  try
  {
      this.pstm=DBUtils.prepareStatement(sql.toString());
      pstm.setObject(1, id);
      rs=pstm.executeQuery();
      Map instance=null;
      if(rs.next())
      {
       ResultSetMetaData rsmd=rs.getMetaData();
       int size=rsmd.getColumnCount();
       instance= new HashMap();
       for(int i=1;i<=size;i++)
       {
        instance.put(rsmd.getColumnLabel(i), rs.getString(i));
       }
      }
      return instance;
  }
  finally
  {
   DBUtils.close(rs);
   DBUtils.close(pstm);
  }
 }
    
 private final List queryForList(Map dto)throws Exception
 {
   
  return this.queryForList(dto, this.defpagesize);
 }
 
   /**
    * 
    * @param dto           //页面数据
    * @param pagesize      //每页的行数
    * @return
    * @throws Exception
    */ 
   protected final List queryForList(Map dto,int pagesize)throws Exception
   {
    try
    {
     //将当前工程上下文路径,保存到属性 contextpath
     this.contextpath=dto.get("contextpath").toString(); 
     //编译SQL
     this.parseSql();
     //计算总的页数
     this.getPageCount(pagesize);
     //计算可获取当前页码
     this.getCurrentPno(dto.get("query"));
     //定位当前页码
     this.gotoCurrentPage(pagesize);
     //读取当前页数据
     return this.WriteCurrentPage(pagesize);   
    }
    finally
    {
     DBUtils.close(rs);
     DBUtils.close(pstm);
    }
    
   }
   
   /**
    * 解析SQL语句
    * @throws Exception
    */
   private final void parseSql()throws Exception
   {
        //编译
       this.pstm=DBUtils.preparedStatementConcur(this.sql.toString());
       //赋值
      if(this.pars!=null)         //如果子类创建了该对象
      {
       //循环读取pstm的问号("?")值列表里面的元素,通过循环完成,对问号的赋值
       int size=this.pars.size();
      for(int i=0;i<size;i++)
      {
       /**
        * 由于pstm第一个元素的下标是1,而List第一元素下标是零,
        * 所以i零开始循环,pstm赋值时,下标为i+1
        */
       this.pstm.setObject(i+1, this.pars.get(i));
      } 
      }
      //执行pstm对象生成rs结果集
      this.rs=this.pstm.executeQuery();
    
   }
   /**
    * 计算总的页数
    * @param pagesize
    * @return
    * @throws Exception
    */
   private final void getPageCount(int pagesize)throws Exception
   {
    //计算总行数
    this.rs.last();
    int rowcount=rs.getRow();
    //总页码计算   (总行数+每页长度-1)/每页长度
    this.countPage=(rowcount+pagesize-1)/pagesize;
    //System.out.println("countPage="+countPage);
   }
 
   /**
    * 计算当前页码
    * @param currentPage
    * @param pagesize
    * @throws Exception
    */
   private final void getCurrentPno(Object currentPage)throws Exception
   {
       int onPageNo=1;                                       //默认获取第一页
       if(currentPage!=null && !currentPage.equals(""))
       {
        onPageNo=Integer.parseInt(currentPage.toString());
       }
       
       //如果传入的当前页码比总的页数大,那么获取最后页
       if(onPageNo>this.countPage)
       {
           onPageNo = this.countPage;
       }
       //当前可以获取页码
       this.currentPage= onPageNo;  
       //System.out.println("currentPage="+currentPage);
   }
   
   /**
    * 定位rs结果集到当前页码
    * @param pagesize
    * @throws Exception
    */
   private final void gotoCurrentPage(int pagesize)throws Exception
   {
    //计算当前页码的起始行数[算法:(当前页码-1)*每页行数)]
    int startRow=(this.currentPage-1)*pagesize;
    if(startRow<1)
    {
     rs.beforeFirst();
    }
    else
    {
     rs.absolute(startRow);  
    }     
   // System.out.println("startRow="+startRow);  
   }
   
   private final List WriteCurrentPage(int pagesize)throws Exception
   {
       List rows = new ArrayList(pagesize);
       Map  item = null;

       ResultSetMetaData rsm = rs.getMetaData();
       int size=rsm.getColumnCount();          //每行列数
       for(int i=1;i<=pagesize;i++)
       {
         if(rs.next())
         {
          item = new HashMap();
                for (int j = 1; j <=size; j++)
                {
                 item.put(rsm.getColumnLabel(j), rs.getString(j));
                }
                rows.add(item);          
         } 
         else
         {
          break;
         } 
       }
       return rows;
   }

 public final String getPageInfo(String url)
 {
  StringBuilder pagetable=new StringBuilder()
  .append("<table  width=\"100%\" border=\"0\" >")
  .append("  <tr bgcolor=\"#FFFFFF\">")
  .append("    <td align=\"left\" nowrap=\"nowrap\" width=\"70%\">")
  .append(          this.getPageOption(url))       //加入下拉列表控件 
  .append("    </td>")
  .append("    <td align=\"right\" nowrap=\"nowrap\" width=\"30%\">")
  .append("      第 "+this.currentPage+" 页  共 "+this.countPage+" 页")
  .append("        ")
  .append("      <a href=\"#\" οnclick=\"ConPages('1')\">                          <font

class=\"Pagination\">9</font></a>")
  .append("      <a href=\"#\" οnclick=\"ConPages('"+this.getPreviousPage()+"')\"> <font

class=\"Pagination\">3</font></a>")
  .append("      <a href=\"#\" οnclick=\"ConPages('"+this.getNextPage()+"')\">     <font

class=\"Pagination\">4</font></a>")
  .append("      <a href=\"#\" οnclick=\"ConPages('"+this.countPage+"')\">         <font

class=\"Pagination\">:</font></a>")
        .append("    </td>")
        .append("  </tr>")
        .append("</table> ")
     .append("<script language=\"javascript\">")
     .append("  function ConPages(pno)")
     .append("  {")
     .append("    with(document.forms[0])")
     .append("    {")
     .append("        query.value=pno;")
     .append("        action=\""+this.contextpath+"/"+url+"?next=query\";")
     .append("        submit();")
     .append("    }  ")
     .append("  }")
     .append(" </script>");
        ;
        return pagetable.toString();
  
 }
 
 public final String getPageTable(String url)
 {
  return this.getPageTable(url, true);
 }

 public final String getPageTable(String url,boolean type)
 {
  StringBuilder pagetable=new StringBuilder();
  if(!type)
  {
   pagetable.append("<input type=\"hidden\" name=\"query\">");
  }
  
  pagetable.append("<table  width=\"100%\" border=\"0\" >")
  .append("  <tr bgcolor=\"#FFFFFF\">")
  .append("    <td align=\"left\" nowrap=\"nowrap\" width=\"30%\">")
  .append("       第 "+this.currentPage+" 页  共 "+this.countPage+" 页")
  .append("    </td>")
  .append("    <td align=\"right\" nowrap=\"nowrap\" width=\"30%\">")
  .append("    </td>")
  .append("    <td align=\"right\" nowrap=\"nowrap\" width=\"40%\">")
  .append("      <a href=\"#\" οnclick=\"ConPages('1')\">                         <font

class=\"Pagination\">7</font></a>")
  .append("      <a href=\"#\" οnclick=\"ConPages('"+this.getPreviousPage()+"')\"><font

class=\"Pagination\">3</font></a>")
  .append("      <a href=\"#\" οnclick=\"ConPages('"+this.getNextPage()+"')\">    <font

class=\"Pagination\">4</font></a>")
  .append("      <a href=\"#\" οnclick=\"ConPages('"+this.countPage+"')\">        <font

class=\"Pagination\">8</font></a>")
        .append("    </td>")
        .append("  </tr>")
        .append("</table> ")
     .append("<script language=\"javascript\">")
     .append("  function ConPages(pno)")
     .append("  {")
     .append("    with(document.forms[0])")
     .append("    {")
     .append("        query.value=pno;")
     .append("        action=\""+this.contextpath+"/"+url+"?next=query\";")
     .append("        submit();")
     .append("    }  ")
     .append("  }")
     .append(" </script>");
        ;
        return pagetable.toString();
 }
 
 /**
  * 下拉列表分页器
  * @param url
  * @return
  */
 public final String getPageOption(String url) 
 {
    StringBuilder sel=new StringBuilder()
    .append("<select name=\"query\" οnchange=\"changePageNo()\">")
    ;
    for(int i=1;i<=this.countPage;i++)
    {
     if(i==this.currentPage)
     {
      sel.append(" <option value="+i+" selected=\"selected\">第 "+i+" 页</option>");
     }
     else
     {
      sel.append(" <option value="+i+">第 "+i+" 页</option>");   
     }    
    }
    sel.append("</select>");
    
    sel.append("<script language=\"javascript\">")
    .append("  function changePageNo()")
    .append("  {")
    .append("     with(document.forms[0])")
    .append("     {")
    .append("        action=\""+this.contextpath+"/"+url+"?next=query\";")
    .append("        submit();")
    .append("     }  ")
    .append("  }")
    .append("  document.all[\"query\"].onmousewheel = dealWheel;")
    .append("  function dealWheel()")
    .append("  {")
    .append("     return false;")
    .append("  }")
    .append(" </script>");
    return sel.toString();
 }
 
 /**
  * 下页页码计算
  * @return
  */
 private int getNextPage()
 {
  int nextpage=this.currentPage+1;
  if(nextpage>=this.countPage)
  {
   nextpage=this.countPage;
  }
  //System.out.println("nextpage="+nextpage);
  return nextpage;
 }
 /**
  * 上页页码计算
  * @return
  */
 private int getPreviousPage()
 {
  int previouspage=this.currentPage-1;
  if(previouspage<=1)
  {
   previouspage=1;
  }
  //System.out.println("previouspage="+previouspage);
  return previouspage;
 }

 
 public final List queryForPage(Map dto) throws Exception
 {
  return this.queryForPage(dto, this.defpagesize);
 }
 
 //public abstract List queryForPage(Map dto,int pagesize)throws Exception;  
}

 

 对分页进行测试

package com.hit.dao.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.hit.dao.support.WebPageSupport;

public class St02DaoImpl extends WebPageSupport
{

 public Map queryForId(Object id) throws Exception
 {
  this.sql=new StringBuilder()
  .append("SELECT A.SAA201,A.SAA202,A.SAA203,A.SAA204,A.SAA205")
  .append("  FROM ST02 A")
  .append(" WHERE A.SAA201=?")
  ;
  return this.findById(id);
 }

 public List queryForPage(Map dto, int pagesize) throws Exception
 {
  Object SAA202=dto.get("QSAA202");
  Object SAA205=dto.get("QSAA205");
  
  this.sql=new StringBuilder()
  .append("SELECT A.FVALUE CNSAA205,")
  .append("       B.SAA201,B.SAA202,B.SAA203,B.SAA204")
  .append("  FROM SYSCODE A,ST02 B")
  .append(" WHERE A.FCODE=B.SAA205 AND A.FNAME='SAA205'")
  ;
  this.pars=new ArrayList();
  if(SAA202!=null && !SAA202.equals(""))
  {
   this.sql.append(" AND B.SAA202 LIKE ?");
   this.pars.add("%"+SAA202+"%");
  }
  if(SAA205!=null && !SAA205.equals(""))
  {
   this.sql.append(" AND B.SAA205=?");
   this.pars.add(SAA205);
  }
  this.sql.append("  ORDER BY B.SAA204");
  return this.queryForList(dto, pagesize);
 }

}