空間站

          北極心空

            BlogJava :: 首頁 :: 聯系 :: 聚合  :: 管理
            15 Posts :: 393 Stories :: 160 Comments :: 0 Trackbacks

          公告

          本博客主要是在本人收集網上一些精彩技術文章,有時可能因疏忽轉載的時候沒有說明轉載出處和作者,如果您認為哪篇文章侵犯了你的版權,請通知本人: EMAIL:luwei-80@163.com 歡迎您光臨本博客!

          常用鏈接

          留言簿(15)

          我參與的團隊

          搜索

          •  

          最新評論

          閱讀排行榜

          評論排行榜

          這是存儲過程的代碼:
          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 蘆葦 閱讀(277) 評論(0)  編輯  收藏 所屬分類: Struts
          主站蜘蛛池模板: 平定县| 阳高县| 玉门市| 白银市| 安平县| 孟连| 图们市| 遵义县| 阿尔山市| 紫阳县| 麦盖提县| 昌邑市| 马鞍山市| 郑州市| 勐海县| 安多县| 济源市| 清丰县| 海宁市| 徐汇区| 辽阳县| 西畴县| 靖安县| 石狮市| 长阳| 万源市| 郎溪县| 吴旗县| 固始县| 阳曲县| 鄱阳县| 称多县| 常熟市| 修武县| 庄浪县| 霍州市| 大石桥市| 合肥市| 夏津县| 博白县| 色达县|