空間站

北極心空

:: 管理

15 Posts :: 393 Stories :: 160 Comments :: 0 Trackbacks

這是存儲過程的代碼：
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");
????????????????
????????}

}

速度還行，還可以優化的，呵呵。

posted on 2006-12-20 20:35 蘆葦閱讀(276) 評論(0) 編輯收藏所屬分類: Struts

新用戶注冊刷新評論列表


只有注冊用戶登錄后才能發表評論。




網站導航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關文章: 擴展Struts2--自定義String和XML格式的Result[收藏] struts2亂碼與json插件[轉] Struts 2 Portlet Tutorial - Creating a simple Bookmark Portlet 深入Struts2 正確優雅的解決用戶退出問題——JSP和Struts解決方案當JsonPlugin遇到CGLib [Translation]JSON與struts2[轉] ［譯］Struts Menu開發向導 [轉] Strus 2的新表單標志的使用 [轉] 轉換器（Converter）——Struts 2.0中的魔術師(Struts2中數據批量保存)[轉別人的]

空間站

公告

常用鏈接

留言簿(15)

我參與的團隊

隨筆分類

隨筆檔案

文章分類

文章檔案

收藏夾

blog收藏

搜索

最新評論

閱讀排行榜

評論排行榜