jsp分页类－－－统一使用

 

一、建立数据库分页的类

package newsbean;
import java.sql.*;

public class DBConnection{
        //这里使用ms jdbc
        String sDBDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
        //指定数据库名/url
        private final String url = "jdbc:microsoft:sqlserver://";
        private final String serverName= "localsqlserver";
        private final String portNumber = "1433";
        private final String databaseName= "MyBusiDB";
        //String sConnStr = "jdbc:microsoft:sqlserver://localsqlserver:1433;DatabaseName=ourcompany";
        private final String dbUserName = "sa";
        private final String dbPassword = "111111";
        // Informs the driver to use server a side-cursor,
        // which permits more than one active statement
        // on a connection.
        private final String selectMethod = "cursor";
   
     Connection conn = null;
        ResultSet rs = null;
        Statement stmt = null;
   
     //这三个参数用于记录翻页
        int iRowCount = 0; //返回总行数
     int iPageCount = 0; //返回总页数
     int iPage = 0;

        public DBConnection(){
          try
            {
              Class.forName(sDBDriver);
            }
          catch(ClassNotFoundException e)
            {
              System.err.println("DBConnection():" + e.getMessage());
            }
        }

        //构造一个连接字符串
       private String getConnectionUrl(){
          return url+serverName+":"+portNumber+";databaseName="+databaseName+";selectMethod="+selectMethod+";";
        }

     //================考虑在类中的分页======================================
     //内部设置总条数..
     private void setRowCount(int irowcount)
     {
       this.iRowCount = irowcount;
     }
    //返回内部设置的总条数
     public int getRowCount()
     {
     return this.iRowCount;
     }
   
        //内部设置总页数..
     private void setPageCount(int ipagecount)
     {
       this.iPageCount = ipagecount;
     }
    //返回内部设置的总页数
     public int getPageCount()
     {
     return this.iPageCount;
     }

      //内部设置当前页..
     private void setPage(int ipage)
     {
       this.iPage = ipage;
     }
    //返回内部设置的总页数
     public int getPage()
     {
     return this.iPage;
     }   
      //显示翻页信息
    //参数：总页数,总行数，当前页
    //应该考虑加一个查询参数列表进入本方法--2007-4-12
public String showChangePage()
{
    return this.iPage + "/" +    this.iPageCount + ">>>";
}
   
//考虑了翻页的选择查询
public ResultSet execQuery(String sql,int iPageSize,int iPage)
        {//======iRowCount== iPageCount==iPageSize===iPage================
          try
            {
              conn = DriverManager.getConnection(getConnectionUrl(),dbUserName,dbPassword);
     stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
              rs = stmt.executeQuery(sql);
     rs.last();  

        //获取总行数 移动到最后，检索当前行编号。
     int iRowCount= rs.getRow();
     //计算总页数 总100行 每页10行===〉总页数= 11.4页==> 12页
     int iPageCount =    (iRowCount + iPageSize-1) / iPageSize;
     if(iPage>iPageCount) iPage = iPageCount;
     if(iPage <= 0 ) iPage = 1;
     if(iPageCount>0){
     //在方法体中已经到了指定行
      rs.absolute((iPage-1) * iPageSize +1);
       }
      //自己设置iPageCount和iRowCount,iPage，避免混淆？！
     setPageCount(iPageCount);
     setRowCount(iRowCount);
     setPage(iPage);
     //stmt.close();???
           }//end try
          catch(SQLException ex)
            {
              System.err.println("DBConnection.execQuery():" + ex.getMessage());
            }//end catch

          return rs;

        }//end execQuery

//======================================================
//关闭数据库
        public void closeDB()
        {
          try
            {

              //System.out.println("DBConnection.closeDB( here!)" );
              if(rs!=null)
                {
                  rs.close();
                  rs = null;
                }
              else
                {
                  System.out.println("rs closed!");
                }//?????

              if(stmt!=null)
                {
                  stmt.close();
                  stmt = null;
                }
              else
                {
                  System.out.println("stmt closed!");
                }
              if(conn!=null)
                {
                  conn.close();
                  conn = null;
                }
              else
                {
                  System.out.println("conn closed!");
                }
            }
          catch(Exception ex)
            {
              //System.err.println("DBConnection.closeDB()" + ex.getMessage());
              System.out.println("DBConnection.closeDB()" + ex.getMessage());
            }
        }

  
    }//end Class
--------------------------------------------------------------------------------------------------------------

二、jsp程序中使用本类过程
1、<jsp:useBean id="conn" class="newsbean.DBConnection" />
2、处理查询参数iPage ,txtSearchKeyword等
3、文章列表：

int iPageSize = 15;//每次读行数，作为参数传入<jsp:setProperty id......没用上！
String sql=null;
ResultSet   rs =null;
int iRowCount = 0;
int iPageCount   = 0;
try{
//===========================
int i = 0;
//l构造sql语句。。。
sql="SELECT top 200 number, sms_no, company_card_name, Reg_Date, Dead_Date, OnUse, province, "
+ "City FROM dbo.Company_Card where 1=1 " ;
if(!( txtSearchCompanyCard.equals("")))sql = sql + " and company_card_name like '%" + txtSearchCompanyCard   + "%'";
if(!( txtSearchCity.equals("")))sql = sql + " and City ='" + txtSearchCity   + "'";
sql = sql + " order by number desc ";

//取得resultset
   rs = conn.execQuery(sql,iPageSize,iPage);

//获取记录总数
iRowCount = conn.getRowCount();
//获取总页数
iPageCount = conn.getPageCount();

//循环
do{
%>    
<tr>
       <td>[<%=rs.getRow()%>]</td>
       <td><%=rs.getString("sms_no")%></td>
       <td><%=rs.getString("company_card_name")%></td>
       <td><%=rs.getDate("Reg_Date")%></td>
       <td><%=rs.getString("province")%></td>
       <td><%=rs.getString("City")%></td>
       <td><a href="#" οnclick="return domodify('<%=rs.getInt("number")%>')">修改</a></td>
</tr>
<%
   }while(++i<iPageSize && rs.next());
}catch(Exception e){
out.print("rs Err:" + e.getMessage());
out.print(sql + "<br/>");
}%>