jsp分页类---统一使用

2010-11-29souphp3l 的分享   加三联MM小编微信好友:sanlian2018

一、建立数据库分页的类

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="#" onclick="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/>");
}%>

 

TA发布的帖子

881

收藏

605