jsp分页类－－－统一使用

一建立数据库分页的类

package newsbean;
import javasql*;

public class DBConnection{
//这里使用ms jdbc
String sDBDriver = "commicrosoftjdbcsqlserverSQLServerDriver";
//指定数据库名/url
private final String url = "jdbc:microsoft:sqlserver://";
private final String serverName= "localsqlserver";
private final String portNumber = "";
private final String databaseName= "MyBusiDB";
//String sConnStr = "jdbc:microsoft:sqlserver://localsqlserver:;DatabaseName=ourcompany";
private final String dbUserName = "sa";
private final String dbPassword = "";
// Informs the driver to use server a sidecursor
// 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 = ; //返回总行数
int iPageCount = ; //返回总页数
int iPage = ;

public DBConnection(){
try
{
ClassforName(sDBDriver);
}
catch(ClassNotFoundException e)
{
Systemerrprintln("DBConnection():" + egetMessage());
}
}

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

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

//内部设置总页数
private void setPageCount(int ipagecount)
{
thisiPageCount = ipagecount;
}
//返回内部设置的总页数
public int getPageCount()
{
return thisiPageCount;
}

//内部设置当前页
private void setPage(int ipage)
{
thisiPage = ipage;
}
//返回内部设置的总页数
public int getPage()
{
return thisiPage;
}
//显示翻页信息
//参数总页数总行数当前页
//应该考虑加一个查询参数列表进入本方法
public String showChangePage()
{
return thisiPage + "/" + thisiPageCount + ">>>";
}

//考虑了翻页的选择查询
public ResultSet execQuery(String sqlint iPageSizeint iPage)
{//======iRowCount== iPageCount==iPageSize===iPage================
try
{
conn = DriverManagergetConnection(getConnectionUrl()dbUserNamedbPassword);
stmt = conncreateStatement(ResultSetTYPE_SCROLL_SENSITIVEResultSetCONCUR_UPDATABLE);
rs = stmtexecuteQuery(sql);
rslast();

//获取总行数 移动到最后检索当前行编号
int iRowCount= rsgetRow();
//计算总页数 总行 每页行===〉总页数= 页==> 页
int iPageCount = (iRowCount + iPageSize) / iPageSize;
if(iPage>iPageCount) iPage = iPageCount;
if(iPage <= ) iPage = ;
if(iPageCount>){
//在方法体中已经到了指定行
rsabsolute((iPage) * iPageSize +);
}
//自己设置iPageCount和iRowCountiPage避免混淆？！
setPageCount(iPageCount);
setRowCount(iRowCount);
setPage(iPage);
//stmtclose();???
}//end try
catch(SQLException ex)
{
Systemerrprintln("DBConnectionexecQuery():" + exgetMessage());
}//end catch

return rs;

}//end execQuery

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

//Systemoutprintln("DBConnectioncloseDB( here!)" );
if(rs!=null)
{
rsclose();
rs = null;
}
else
{
Systemoutprintln("rs closed!");
}//?????

if(stmt!=null)
{
stmtclose();
stmt = null;
}
else
{
Systemoutprintln("stmt closed!");
}
if(conn!=null)
{
connclose();
conn = null;
}
else
{
Systemoutprintln("conn closed!");
}
}
catch(Exception ex)
{
//Systemerrprintln("DBConnectioncloseDB()" + exgetMessage());
Systemoutprintln("DBConnectioncloseDB()" + exgetMessage());
}
}

}//end Class

二jsp程序中使用本类过程
<jsp:useBean id="conn" class="newsbeanDBConnection" />
处理查询参数iPage txtSearchKeyword等
文章列表

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

//取得resultset
rs = connexecQuery(sqliPageSizeiPage);

//获取记录总数
iRowCount = conngetRowCount();
//获取总页数
iPageCount = conngetPageCount();

//循环
do{
%>
<tr>
<td>[<%=rsgetRow()%>]</td>
<td><%=rsgetString("sms_no")%></td>
<td><%=rsgetString("company_card_name")%></td>
<td><%=rsgetDate("Reg_Date")%></td>
<td><%=rsgetString("province")%></td>
<td><%=rsgetString("City")%></td>
<td><a href="#" onclick="return domodify(<%=rsgetInt("number")%>)">修改</a></td>
</tr>
<%
}while(++i<iPageSize && rsnext());
}catch(Exception e){
outprint("rs Err:" + egetMessage());
outprint(sql + "<br/>");
}%>