這是存儲(chǔ)過程的代碼：
CREATE PROCEDURE d_pageShow
@tableName varchar(255), -- 表名
@getFields varchar(1000) = '*', -- 需要返回的列
@orderByFields varchar(255)='', -- 排序的字段名
@pageSize int = 10, -- 頁尺寸
@pageIndex int = 1, -- 頁碼
@doCount bit = 0, -- 返回記錄總數(shù), 非 0 值則返回
@orderType bit = 0, -- 設(shè)置排序類型, 非 0 值則降序
@where varchar(1500) = '' -- 查詢條件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主語句
declare @strTmp varchar(110) -- 臨時(shí)變量
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，就執(zhí)行總數(shù)統(tǒng)計(jì)。以下的所有代碼都是@doCount為0的情況
else
begin
if @orderType != 0
begin
set @strTmp = "<(select min"
set @strOrder = " order by [" + @orderByFields +"] desc"
--如果@orderType不是0，就執(zhí)行降序，這句很重要！
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
--如果是第一頁就執(zhí)行以上代碼，這樣會(huì)加快執(zhí)行速度
end
else
begin
--以下代碼賦予了@strSQL以真正執(zhí)行的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
====================================================================
這是調(diào)用的代碼（***.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");
????????????????
????????}
}
=========================================================
這是那個(gè)infoTitles = dao.getInfoTitles(infoTitlePage,con) ;的具體代碼：
/*
???????? * 得到相應(yīng)的分頁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的存儲(chǔ)過程：");
????????????????????????????????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
????????????????// 如果沒有記錄總值 計(jì)算記錄總值并把doCount設(shè)置為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初始化的存儲(chǔ)過程：");
????????????????????????????????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);
????????????????????????????????????????
????????????????????????}
????????????????}
==================================================================
其它相關(guān)屬性：
????????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");
????????????????
????????}
}
速度還行，還可以優(yōu)化的，呵呵。