这是存储过程的代码：
CREATE PROCEDURE d_pageShow
@tableName varchar(255), -- 表名
@getFields varchar(1000) = '*', -- 需要返回的列
@orderByFields varchar(255)='', -- 排序的字段名
@pageSize int = 10, -- 页尺寸
@pageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@orderType bit = 0, -- 设置排序类型, 非 0 值则降序
@where varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @doCount != 0
begin
if @where !=''
set @strSQL = "select count(*) as Total from [" + @tableName + "] where "+@where
else
set @strSQL = "select count(*) as Total from [" + @tableName + "]"
end
--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况
else
begin
if @orderType != 0
begin
set @strTmp = "<(select min"
set @strOrder = " order by [" + @orderByFields +"] desc"
--如果@orderType不是0，就执行降序，这句很重要！
end
else
begin
set @strTmp = ">(select max"
set @strOrder = " order by [" + @orderByFields +"] asc"
end
if @pageIndex = 1
begin
if @where != ''
set @strSQL = "select top " + str(@pageSize) +" "+@getFields+ " from [" + @tableName + "] where " + @where + " " + @strOrder
else
set @strSQL = "select top " + str(@pageSize) +" "+@getFields+ " from ["+ @tableName + "] "+ @strOrder
--如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = "select top " + str(@pageSize) +" "+@getFields+ " from ["
+ @tableName + "] where [" + @orderByFields + "]" + @strTmp + "(["+ @orderByFields + "]) from (select top " + str((@pageIndex-1)*@pageSize) + " ["+ @orderByFields + "] from [" + @tableName + "]" + @strOrder + ") as tblTmp)"+ @strOrder
if @where != ''
set @strSQL = "select top " + str(@pageSize) +" "+@getFields+ " from ["
+ @tableName + "] where [" + @orderByFields + "]" + @strTmp + "(["
+ @orderByFields + "]) from (select top " + str((@pageIndex-1)*@pageSize) + " ["
+ @orderByFields + "] from [" + @tableName + "] where " + @where + " "
+ @strOrder + ") as tblTmp) and " + @where + " " + @strOrder
end
end
exec (@strSQL)
GO
====================================================================
这是调用的代码（***.action）:
public class InfoTitlePageAction extends Action {
public ActionForward execute(
ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
InfoPageForm infoPageForm = (InfoPageForm)form;
InfoTitlePage infoTitlePage = new InfoTitlePage();
infoTitlePage.copyInfoPageForm(infoPageForm);
request.setAttribute("infoTitlePage",infoTitlePage);
InfoTitlePageDao dao = InfoTitlePageDaoFactory.create();
DataSource datasource=this.getDataSource(request,"infoPromulgate");
Connection con = null;
try {
con = datasource.getConnection();
InfoTitle[] infoTitles = new InfoTitle[dao.getInfoTitles(infoTitlePage,con).length] ;
infoTitles = dao.getInfoTitles(infoTitlePage,con) ;
request.setAttribute("infoTitles",infoTitles);
} catch (SQLException e) {
e.printStackTrace();
return mapping.findForward("failure");
}finally {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return mapping.findForward("success");
}
}
=========================================================
这是那个infoTitles = dao.getInfoTitles(infoTitlePage,con) ;的具体代码：
/*
* 得到相应的分页InfoTitles
*/
public InfoTitle[] getInfoTitles(InfoTitlePage infoTitlePage,Connection con) {
Connection conn = null;
CallableStatement cstmt = null;
ResultSet rs = null;
ArrayList arrInfoTitle = new ArrayList();
InfoTitle[] infoTitles = null;
//到首页
if(infoTitlePage.getStrMethod().trim().equals("first")){
infoTitlePage.setIntPageIndex("1");
infoTitlePage.setStrMethod("view");
log.info("到首页");
}
//到上一页
if(infoTitlePage.getStrMethod().trim().equals("up")){
if(infoTitlePage.getIntPageIndex() > 1){
infoTitlePage.setIntPageIndex("" + (infoTitlePage.getIntPageIndex()-1) );
}else{
infoTitlePage.setIntPageIndex("1");
}
infoTitlePage.setStrMethod("view");
log.info("到上一页");
}
//到下一页
if(infoTitlePage.getStrMethod().trim().equals("down")){
if(infoTitlePage.getIntPageIndex() < infoTitlePage.getIntPageMax()){
infoTitlePage.setIntPageIndex("" + (infoTitlePage.getIntPageIndex() + 1) );
}else{
infoTitlePage.setIntPageIndex("" + infoTitlePage.getIntPageMax());
}
infoTitlePage.setStrMethod("view");
log.info("到下一页");
}
//到末页
if(infoTitlePage.getStrMethod().trim().equals("last")){
infoTitlePage.setIntPageIndex("" + infoTitlePage.getIntPageMax());
infoTitlePage.setStrMethod("view");
log.info("到末页");
}
//得到要显示的分页信息
if(infoTitlePage.getStrMethod().trim().equals("view")){
if( infoTitlePage.getIntPageIndex() > infoTitlePage.getIntPageMax() ){
infoTitlePage.setIntPageIndex( infoTitlePage.getIntPageMax() + "" );
}
try {
conn = con;
cstmt = conn.prepareCall(SQLinfoTitlePage);
cstmt.setString( COLUMN_tableName , infoTitlePage.getStrTableName() );
cstmt.setString( COLUMN_getFields , infoTitlePage.getStrGetFields() );
cstmt.setString( COLUMN_orderByFields , infoTitlePage.getStrOrderByFields() );
cstmt.setString( COLUMN_pageSize , infoTitlePage.getIntPageSize() + "" );
cstmt.setString( COLUMN_pageIndex , infoTitlePage.getIntPageIndex() + "" );
cstmt.setString( COLUMN_doCount , infoTitlePage.getIntDoCount() + "" );
cstmt.setString( COLUMN_orderType , infoTitlePage.getIntOrderType() + "" );
cstmt.setString( COLUMN_where , infoTitlePage.getStrWhere() );
//log.info("得到具体infoTitles的存储过程：");
log.info("SQLinfoTitlePage: " + cstmt.toString());
rs = cstmt.executeQuery();
while (rs.next()) {
InfoTitle infoTitle = new InfoTitle();
infoTitle.setIntId(rs.getString("id"));
infoTitle.setStrInfoTitle(rs.getString("infoTitle"));
infoTitle.setStrInfoType(rs.getString("infoType"));
infoTitle.setStrPromulgateTime(rs.getDate("promulgateTime").toLocaleString());
arrInfoTitle.add(infoTitle);
}
infoTitles = new InfoTitle[arrInfoTitle.size()];
arrInfoTitle.toArray(infoTitles);
return infoTitles;
} catch (SQLException e) {
log.error("SQLException: " + e.getMessage() , e );
e.printStackTrace();
return null;
}finally {
ResourceManager.close(rs);
ResourceManager.close(cstmt);
//ResourceManager.close(conn);
}
}
return infoTitles;
}
=================================================================
这是页面form初始化的代码：
/*
* infoTitlePage对象的初始化
*/
public InfoTitlePage getInfoTitlePage(String infoType,Connection con) {
Connection conn = null;
CallableStatement cstmt = null;
ResultSet rs = null;
InfoTitlePage infoTitlePage = new InfoTitlePage() ;
infoTitlePage.setStrWhere(infoType);
//得到记录总值intCount
// 如果没有记录总值 计算记录总值并把doCount设置为0
if (infoTitlePage.getIntCount() == -1) {
try {
conn = con;
cstmt = conn.prepareCall(SQLinfoTitlePage);
cstmt.setString( COLUMN_tableName , infoTitlePage.getStrTableName() );
cstmt.setString( COLUMN_getFields , infoTitlePage.getStrGetFields() );
cstmt.setString( COLUMN_orderByFields , infoTitlePage.getStrOrderByFields() );
cstmt.setString( COLUMN_pageSize , infoTitlePage.getIntPageSize() + "" );
cstmt.setString( COLUMN_pageIndex , infoTitlePage.getIntPageIndex() + "" );
cstmt.setString( COLUMN_doCount , infoTitlePage.getIntDoCount() + "" );
cstmt.setString( COLUMN_orderType , infoTitlePage.getIntOrderType() + "" );
cstmt.setString( COLUMN_where , infoTitlePage.getStrWhere() );
//log.info("infoTitlePage初始化的存储过程：");
log.info(SQLinfoTitlePage);
rs = cstmt.executeQuery();
if(rs.next()){
infoTitlePage.setIntCount(rs.getInt("Total") + "");
}
infoTitlePage.setIntDoCount("0");
} catch (SQLException e) {
log.error("SQLException: " + e.getMessage(),e);
e.printStackTrace();
return null;
}finally {
ResourceManager.close(rs);
ResourceManager.close(cstmt);
//ResourceManager.close(conn);
}
}
==================================================================
其它相关属性：
private String SQLinfoTitlePage = "{ call d_pageShow(?,?,?,?,?,?,?,?) }";
private static final int COLUMN_tableName = 1;
private static final int COLUMN_getFields = 2;
private static final int COLUMN_orderByFields = 3;
private static final int COLUMN_pageSize = 4;
private static final int COLUMN_pageIndex = 5;
private static final int COLUMN_doCount = 6;
private static final int COLUMN_orderType = 7;
private static final int COLUMN_where = 8;
private Logger log=Logger.getLogger(this.getClass().getName());
忘了初始化的（***.action）:
public class MoreInfoAction extends Action {
// --------------------------------------------------------- Instance Variables
// --------------------------------------------------------- Methods
public ActionForward execute(
ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
String infoType = request.getParameter("infoType");
InfoTitlePageDao dao = InfoTitlePageDaoFactory.create() ;
DataSource datasource=this.getDataSource(request,"infoPromulgate");
Connection con = null;
try {
con = datasource.getConnection();
InfoTitlePage infoTitlePage = dao.getInfoTitlePage(" infoType='" + infoType + "'",con) ;
InfoTitle[] infoTitles = new InfoTitle[dao.getInfoTitles(infoTitlePage,con).length] ;
infoTitles = dao.getInfoTitles(infoTitlePage,con) ;
request.setAttribute("infoTitles",infoTitles);
request.setAttribute("infoTitlePage",infoTitlePage);
} catch (SQLException e) {
e.printStackTrace();
return mapping.findForward("failure");
}finally {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return mapping.findForward("success");
}
}
速度还行，还可以优化的，呵呵。