CREATE TABLE [dbo].[recordsList] (
    [nId] [int] IDENTITY (1, 1) NOT NULL ,
    [strPoNo] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [strGrNo] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
    [nItem] [int] NOT NULL ,
    [strMaterialDesc] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
    [strMaterialNo] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [strMaterialName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [strApplicant] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
    [fltUnitPrice] [float] NULL ,
    [nCount] [int] NOT NULL ,
    [strDate] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [strDeptName] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [strSignDate] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
    [strMaterialSort] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL  
) ON [PRIMARY]

CREATE PROC P_viewPage
/*
no_mIss 分页存储过程 2007.2.20 QQ:706716259
适用于单一主键或存在唯一值列的表或视图  
*/
@TableName VARCHAR(200), --表名
@FieldList VARCHAR(2000), --显示列名
@PrimaryKey VARCHAR(100), --单一主键或唯一值键
@Where VARCHAR(1000), --查询条件 不含'where'字符
@Order VARCHAR(1000), --排序 不含'order by'字符，如id asc,userid desc，当@SortType=3时生效
@SortType INT, --排序规则 1:正序asc 2:倒序desc 3:多列排序
@RecorderCount INT, --记录总数 0:会返回总记录
@PageSize INT, --每页输出的记录数
@PageIndex INT, --当前页数
@TotalCount INTEGER OUTPUT, --返回记录总数
@TotalPageCount INTEGER OUTPUT --返回总页数

AS
SET NOCOUNT ON
IF ISNULL(@TableName,'') = '' OR ISNULL(@FieldList,'') = ''  
OR ISNULL(@PrimaryKey,'') = ''
OR @SortType < 1 OR @SortType >3
OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0
BEGIN  
    RETURN
END

DECLARE @new_where1 VARCHAR(1000)
DECLARE @new_where2 VARCHAR(1000)
DECLARE @new_order VARCHAR(1000)  
DECLARE @Sql VARCHAR(8000)
DECLARE @SqlCount NVARCHAR(4000)

IF ISNULL(@where,'') = ''
    BEGIN
  SET @new_where1 = ' '
  SET @new_where2 = ' WHERE '
    END
ELSE
    BEGIN
  SET @new_where1 = ' WHERE ' + @where  
  SET @new_where2 = ' WHERE ' + @where + ' AND '
    END
IF ISNULL(@order,'') = '' OR @SortType = 1 OR @SortType = 2  
    BEGIN
  IF @SortType = 1 SET @new_order = ' ORDER BY ' + @PrimaryKey + ' ASC'
  IF @SortType = 2 SET @new_order = ' ORDER BY ' + @PrimaryKey + ' DESC'
    END
ELSE
    BEGIN
  SET @new_order = ' ORDER BY ' + @Order
    END
SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'
+ CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName + @new_where1
IF @RecorderCount = 0
    BEGIN
  EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',
  @TotalCount OUTPUT,@TotalPageCount OUTPUT
    END
ELSE
    BEGIN
  SELECT @TotalCount = @RecorderCount  
    END
IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)
    BEGIN
  SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)
  --这里加一行测试
  --print '当前页PageIndex为'+str(@PageIndex)
    END
IF @PageIndex = 0 or @PageIndex = 1  
--IF @PageIndex = 1    --原始行
    BEGIN
  SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '  
  + @TableName + @new_where1 + @new_order
    END
ELSE
    BEGIN
  IF @SortType = 1
      BEGIN
    SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '  
    + @TableName + @new_where2 + @PrimaryKey + ' > '
    + '(SELECT max(' + @PrimaryKey + ') FROM (SELECT TOP '
    + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey  
    + ' FROM ' + @TableName
    + @new_where1 + @new_order +' ) AS TMP) '+ @new_order
      END
  IF @SortType = 2
      BEGIN
    SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '  
    + @TableName + @new_where2 + @PrimaryKey + ' < '
    + '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP '
    + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey  
    +' FROM '+ @TableName
    + @new_where1 + @new_order + ') AS TMP) '+ @new_order  
      END  
  IF @SortType = 3
      BEGIN
      IF CHARINDEX(',',@Order) = 0 BEGIN RETURN END
      SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
      + @TableName + @new_where2 + @PrimaryKey + ' NOT IN (SELECT TOP '
      + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey
      + ' FROM ' + @TableName + @new_where1 + @new_order + ')'
      + @new_order
      END
    END
EXEC(@Sql)
--print @Sql

package sqlDBconnect;

import java.util.Vector;

/**
* Title:        分页类
* Description:
* Copyright:    Copyright (c) 2007
* Company:      USI
* @author:   LJ
* @version 1.0
*/

public class Pagess {
    protected Vector list;  //物品向量数组
    protected int page=1;  //当前页数
    protected int recordCount=0;  //记录总数 0:会返回总记录
    protected int pageCount=1;      //返回总页数
    protected int pageSize=15;  //每页输出的记录数
    protected int recode=0;  
    protected int pageMaxCount=0;
    protected int listCount=0;
    protected boolean showAll=false;
  
    public Pagess() throws Exception{

    }
  
    public void setPage(int newPage) {
        this.page=newPage;
    }

    public int getPage() {
    if (page<=0)
     return 1;
    else
     return page;  
  
    }
  
    public void setPageSize(int newPageSize) {
    pageSize=newPageSize;
    }
  
    public void setShowAll() {
    showAll=true;
    }

    public int getPageSize() {
    return pageSize;
    }
    
    public int getPageCount() {
  return pageCount;
    }
    public int getRecordCount() {
  return recordCount;
    }
    public int getRecode() {
  return recode;
    }
  
    public void setListCount(int listCount){
  this.listCount=listCount;
    }
  
    public int getListCount() {
  return list.size();
    }
  
    
    public Vector getLists() {
  return list;
    }
  

    public void setShowPage(int newCount) throws Exception {
      listCount=newCount;
      list = new Vector();//PageMaxCount+1);
      list.clear();        
      recode=(page-1)*pageSize+1;
  
    }

    /**
  * @param pageCount 要设置的 pageCount
  */
    public void setPageCount(int pageCount) {
  this.pageCount = pageCount;
    }

    /**
  * @param recordCount 要设置的 recordCount
  */
    public void setRecordCount(int recordCount) {
  this.recordCount = recordCount;
    }
};

package sqlDBconnect;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
/**
* Title:        物品列表
* Description:
* Copyright:    Copyright (c) 2007
* Company:      USI
* @author:   LJ
* @version 1.0
*/

public class Listss extends Pagess {
    private String[]           columnNames = {};
    private ResultSetMetaData  metaData;
    int                        row=0;
    int                       columnCount=0;  
    String                    strSql="";  
    int        execute=0;
    String        sql="";
    private String     poolName;
  
    private String      tableName="";
    private String      fieldList="";
    private String      primaryKey="";
    private String      where="";
    private String      order="";
    private int      sortType=1;
    /**
  * @return fieldList
  */
    public String getFieldList() {
  return fieldList;
    }

    /**
  * @param fieldList 要设置的 fieldList
  */
    public void setFieldList(String fieldList) {
  this.fieldList = fieldList;
    }

    /**
  * @return order
  */
    public String getOrder() {
  return order;
    }

    /**
  * @param order 要设置的 order
  */
    public void setOrder(String order) {
  this.order = order;
    }

    /**
  * @return primaryKey
  */
    public String getPrimaryKey() {
  return primaryKey;
    }

    /**
  * @param primaryKey 要设置的 primaryKey
  */
    public void setPrimaryKey(String primaryKey) {
  this.primaryKey = primaryKey;
    }

    /**
  * @return sortType
  */
    public int getSortType() {
  return sortType;
    }

    /**
  * @param sortType 要设置的 sortType
  */
    public void setSortType(int sortType) {
  this.sortType = sortType;
  //System.out.println(this.sortType);
    }

    /**
  * @return tableName
  */
    public String getTableName() {
  return tableName;
    }

    /**
  * @param tableName 要设置的 tableName
  */
    public void setTableName(String tableName) {
  this.tableName = tableName;
    }

    /**
  * @return where
  */
    public String getWhere() {
  return where;
    }

    /**
  * @param where 要设置的 where
  */
    public void setWhere(String where) {
  this.where = where;
    }

    public Listss() throws Exception{
  super();
    }
  
    public String getColumnName(int column) {
        if (column>=columnCount)  
       column=columnCount-1;
        
        if (column<0)  
       column=0;  
              
        if (columnNames[column] != null) {
            return columnNames[column];
        } else  
            return "";
        
    }
    
    public int getColumnIndex(String name) {
      int ok=0;  
      if (name!=null) {
          for (int i=0;i<columnCount;i++) {
          String temp=columnNames[i].toLowerCase();
          name=name.toLowerCase();  
         if (name.equals(temp))
          {    
          ok= i;
          break;
        }

       }
   }
     return ok;  
   }
    
    public void setRow(int i) {
        row=i;
    }
    
    public int getRow(){
        return this.row;
    }
    
    public int getColumnCount() {
        return columnNames.length;
    }
    
     public Class getColumnClass(int column) {
        int type;
        try {
            type = metaData.getColumnType(column+1);
        }
        catch (SQLException e) {
            return null;
        }

        switch(type) {
        case Types.CHAR:
        case Types.VARCHAR:
        case Types.LONGVARCHAR:
            return String.class;

        case Types.BIT:
            return Boolean.class;

        case Types.TINYINT:
        case Types.SMALLINT:
        case Types.INTEGER:
            return Integer.class;

        case Types.BIGINT:
            return Long.class;

        case Types.FLOAT:
        case Types.DOUBLE:
            return Double.class;

        case Types.DATE:
            return java.sql.Date.class;

        default:
            return Object.class;
        }
    }
        
    public String getString(int aColumn) {
        String[] rowStr = (String[])list.elementAt(row);
        return rowStr[aColumn];
    //    Vector row = (Vector)list.elementAt(FRow);
    //    return row.elementAt(aColumn).toString();
        }
      
    public String getString(String aColumnName) {
        String[] rowStr = (String[])list.elementAt(row);
        return rowStr[getColumnIndex(aColumnName)];      
    //    Vector row = (Vector)list.elementAt(FRow);
    //    return row.elementAt(getColumnIndex(aColumnName)).toString();
        }
    
    public void setSql(String tableName) {
  String Str="";
  Str="select  * from "+tableName+" order by id desc";
  strSql=Str;
    }

    public void setStrSql(String Str) {
  strSql=Str;
    }
  
    public String getStrSql(){
  return this.strSql;
    }
  
    public String getSql() {
  return strSql;
    }

    public int getExecute() throws Exception{
  if(excute()) return 1;
  else return 0;
    }

    public boolean excute() throws Exception {
  int id = 0;
  try {
      DBConnect dbc  = new DBConnect(this.poolName,"CallableStatement");
      CallableStatement   cstmt   =   dbc.getConnection().prepareCall("{call P_viewPage(?,?,?,?,?,?,?,?,?,?,?)}",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
      cstmt.setString(1,this.tableName);
      cstmt.setString(2,this.fieldList);
      cstmt.setString(3,this.primaryKey);
      cstmt.setString(4,this.where);
      cstmt.setString(5,this.order);
      cstmt.setInt(6,this.sortType);
      cstmt.setInt(7,super.getRecordCount());
      cstmt.setInt(8,super.getPageSize());
      cstmt.setInt(9,super.getPage());
      cstmt.registerOutParameter(10,java.sql.Types.INTEGER);    
      cstmt.registerOutParameter(11,java.sql.Types.INTEGER);  
    
      //System.out.print(this.tableName+"  "+this.fieldList+"  "+this.primaryKey+"  "    +this.where+"  "+this.order+"  "+this.sortType+"  "+super.getRecordCount()+"  "+super.getPageSize()+"  "+super.getPage());    
      cstmt.executeUpdate();
      super.setRecordCount(cstmt.getInt(10));
      super.setPageCount(cstmt.getInt(11));
    
      //System.out.println(cstmt.getInt(10));
      //System.out.println(cstmt.getInt(11));
    
      ResultSet rs=cstmt.executeQuery();

      //设置当前页
      if(super.getPage()>=super.getPageCount())    super.setPage(super.getPageCount());
    
      metaData = rs.getMetaData();
      columnCount =  metaData.getColumnCount();
      columnNames = new String[columnCount];
            // Get the column names and cache them.
            // Then we can close the connection.
        for(int column = 0; column < columnCount; column++) {
            columnNames[column] = metaData.getColumnLabel(column+1);
          }
     //rs.last();
        setShowPage(rs.getRow());
        //rs.absolute(getRecode());
  
        if(rs.next()){
      for (int i=0;i<getPageSize();i++){
         String[]            rsRow = {};
         rsRow = new String[columnCount];  
               for(int column = 0; column < columnCount; column++) {
                 rsRow[column] = rs.getString(column+1);
               }
                 list.addElement(rsRow);    
          //    list.addElement(rs.getObject(i));
                 if (!rs.next())
                     break;
          }          
        }
        
        
    
      rs.close();
      cstmt.close();
      dbc.close();
      return true;
  }
  catch (SQLException sqle){
      sqle.printStackTrace();
      return false;
  }
    }  
  

    /**
  * @return poolName
  */
    public String getPoolName() {
  return poolName;
    }

    /**
  * @param poolName 要设置的 poolName
  */
    public void setPoolName(String poolName) {
  this.poolName = poolName;
    }
}

<%@page contentType="text/html; charset=gb2312" language="java"  errorPage=""%>
<%@ taglib uri="/WEB-INF/struts-bean.tld" prefix="bean" %>
<%@ taglib uri="/WEB-INF/struts-html.tld" prefix="html" %>
<%@ taglib uri="/WEB-INF/struts-logic.tld" prefix="logic" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core" %>

<form method="post"  action='<c:out value="${selfweb}"/>.jsp' name="PageForm">
<tr>  
    <td class="search4" width="100%" height="100%">
    <img src="images/y1.gif" width="5" height="7">
    <a href='<c:out value="${selfweb}"/>.jsp?' target="_self">第一页</a>
    <c:if test="${ShowPage==1}">  
  <img src="images/y2.gif" width="4" height="7">前一页  
    </c:if>
    <c:if test="${ShowPage!=1}">  
  <img src="images/y2.gif" width="4" height="7">
  <%request.setAttribute("prev",String.valueOf(Integer.parseInt(request.getAttribute("ShowPage").toString())-1));%>
  <a href='<c:out value="${selfweb}"/>.jsp?page=<c:out value="${prev}"/>' target="_self">前一页</a>  
    </c:if>
    <c:if test="${ShowPage==PageCount}"> <img src="images/y3.gif" width="4" height="7">下一页  
    </c:if>
    <c:if test="${ShowPage!=PageCount}"> <img src="images/y3.gif" width="4" height="7">  
  <%request.setAttribute("next",String.valueOf(Integer.parseInt(request.getAttribute("ShowPage").toString())+1));%>
  <a href='<c:out value="${selfweb}"/>.jsp?page=<c:out value="${next}"/>' target="_self">下一页</a>  
    </c:if>
    <img src="images/y4.gif" width="5" height="7"><a href='<c:out value="${selfweb}"/>.jsp?page=<c:out value="${list1.pageCount}"/>' target="_self">最后一页</a>
    记录总数<c:out value="${list1.recordCount}"/>
    每页<c:out value="${PageSize}"/>
    当前页[<c:out value="${ShowPage}"/>/<c:out value="${list1.pageCount}"/>页]  
    页数
    <select name="page" onchange="Jumping()" style="width:50px">
  <c:if test="${ShowPage!=0}">
      <option value='<c:out value="${ShowPage}"/>'><c:out value="${ShowPage}"/></option>
  </c:if>
  <c:if test="${list1.pageCount>=1}">
      <c:forEach var="i" begin="1" end="${list1.pageCount}" step="1">
    <option value='<c:out value="${i}"/>'><c:out value="${i}"/></option>
      </c:forEach>
  </c:if>
    </select>　　
    </td>
</tr>
</form>