和風細雨

世上本無難事，心以為難，斯乃真難。茍不存一難之見于心，則運用之術自出。

統計

隨筆 - 99
文章 - 1
評論 - 89
引用 - 0

常用鏈接

留言簿(9)

隨筆分類

隨筆檔案

文章檔案

2007年2月 (1)

相冊

11

搜索

閱讀排行榜

評論排行榜

JDBC分頁示例（Oracle數據庫）

實例一：
效果圖：

頁面代碼：

<table>
                        <form method=post action="ViewTag">
                        <tr><td colspan=3 align=center>類別查詢<input type="text" name="keyWord"
                                    value="" style="width: 200px; height: 20px" />
                                    <input type="submit"
                                    value="查詢" style="width: 60px; height: 20px"/>
                        </td></tr>
                        </form>
                        <tr>
                            <td align=left>
                            <%
                                // 取得關鍵詞
                                String keyWord=(String)request.getAttribute("keyWord");

                                String prevStart=(String)request.getAttribute("PrevStart");
                                if(prevStart!=null){
                                    out.print("<a href='ViewTag?keyWord="+keyWord+"&&start="+prevStart+"'>上一頁</a>");
                                }
                                else{
                                    out.print("上一頁");
                                }
                            %>
                            </td>
                            <td width=400 align=center><div>資源一覽</div></td>
                            <td align=right>
                            <%
                                String nextStart=(String)request.getAttribute("NextStart");
                                if(nextStart!=null){
                                    out.print("<a href='ViewTag?keyWord="+keyWord+"&&start="+nextStart+"'>下一頁</a>");
                                }
                                else{
                                    out.print("下一頁");
                                }
                            %>
                            </td>
                        </tr>
                    </table>

                    <table id="TbSort" class="Listing" width=100% align=center>
                        <tbody id="todoList">
                            <TR>
                                <TH>類別ID</TH>
                                <TH>分類名稱</TH>
                                <TH>支出/收入</TH>
                                <TH>總和</TH>
                                <TH>刪除</TH>
                            </TR>
                            <%
                                List<Tag> tags=(List<Tag>)request.getAttribute("Tags");

                                if(tags==null || tags.size()<1){
                                    out.print("<tr><td colspan=7>沒有數據</td></tr>");
                                }
                                else{
                                    for(Tag tag:tags){
                                        out.print("<tr>");
                                        out.print("<td>"+tag.getId()+"</td>");
                                        out.print("<td>"+tag.getName()+"</td>");
                                        out.print("<td>"+(tag.isOutput()?"支出":"收入")+"</td>");
                                        out.print("<td>"+tag.getSum()+"</td>");
                                        out.print("<td><a href='DelTag?id="+tag.getId()+"'>刪除</a></td>");
                                        out.print("</tr>");
                                    }
                                }
                            %>
                        </tbody>
                    </table>

Servlet代碼：

package com.sitinspring.action;

import java.util.logging.Logger;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.sitinspring.domain.User;
import com.sitinspring.service.TagService;
import com.sitinspring.util.CommonUtil;

/**
* 用于查看Tag的Servlet
* @author sitinspring
*
* @date 2008-2-11
*/
public class ViewTagServlet extends HttpServlet {
    private static final long serialVersionUID = 54354353L;

    // 日志記錄器
    private static Logger logger = Logger.getLogger(ViewTagServlet.class
            .toString());

    /**
     * 根據輸入的頁面名稱，取得需要的數據再進入對應頁面
     */
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, java.io.IOException {
        request.setCharacterEncoding("UTF-8");

        // 查看用戶是否存在
        User user = (User) request.getSession().getAttribute("User");
        if(user==null){
            // 如果Session中沒有執行修改操作的人,轉到錯誤頁面
            String pageName="login";
            request.setAttribute("Msg","在Session中找不到操作用戶,請重新登錄");
            gotoUrl(CommonUtil.combineUrl(pageName), request, response);
            return;
        }

        final int pageSize=CommonUtil.PageSize;

        TagService service=new TagService();

        // 取得頁起始記錄號
        int start;
        String strStart=(String)request.getParameter("start");
        if(strStart==null){
            start=0;
        }
        else{
            start=Integer.parseInt(strStart);
        }

        // 查詢關鍵詞
        String keyWord=(String)request.getParameter("keyWord");
        if(keyWord==null){
            keyWord="";
        }
        request.setAttribute("keyWord", keyWord);

        // 總數
        int allCount=service.getCountByKeyWord(keyWord,user.getId());

        // 上一頁
        if(start-pageSize>=0){
            request.setAttribute("PrevStart", String.valueOf(start-pageSize));
        }
        // 下一頁
        if(start+pageSize<allCount){
            request.setAttribute("NextStart", String.valueOf(start+pageSize));
        }

        request.setAttribute("Tags", service.fetchPageRecords(start,start+pageSize,keyWord,user.getId()));
        request.setAttribute("Msg", "歡迎進入類別查看頁面.");

        // 設置page參數
        String pageName = "viewTag";
        gotoUrl(CommonUtil.combineUrl(pageName), request, response);
    }

    /**
     * 遷移到相應頁面
     * @param url
     * @param request
     * @param response
     * @throws ServletException
     * @throws java.io.IOException
     */
    private void gotoUrl(String url, HttpServletRequest request,
            HttpServletResponse response) throws ServletException,
            java.io.IOException {
        logger.info("進入頁面:" + url);

        RequestDispatcher dispatcher = null;
        dispatcher = request.getRequestDispatcher(url);
        dispatcher.forward(request, response);
    }

    public void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, java.io.IOException {
        doPost(request, response);
    }
}

Service中兩分頁函數代碼：

package com.sitinspring.service;

import java.util.List;

import com.sitinspring.dao.TagDao;
import com.sitinspring.domain.Tag;
import com.sitinspring.exception.SoloIdMultiRecordException;

/**
* 服務類--為領域對象賬目類別類Tag服務
* @author sitinspring
*
* @date 2008-1-31
*/
public class TagService{

..

    /**
     * 按關鍵字取得分類賬目的數量
     * @param keyWord
     * @return
     */
    public int getCountByKeyWord(String keyWord,String uid){
        StringBuffer sb=new StringBuffer();
        sb.append("                 Select              ");
        sb.append("                        *            ");
        sb.append("                 from                ");
        sb.append("                        Tag    ");
        sb.append("                 where    ");
        sb.append("                        name like   '%"+keyWord+"%' and ");
        sb.append("                        userid='"+uid+"' ");

        String sql=sb.toString();
        return search(sql).size();
    }

    /**
     * 按關鍵字取得分類賬目的分頁記錄
     * @param start
     * @param end
     * @param keyWord
     * @return
     */
    public List<Tag> fetchPageRecords(int start,int end,String keyWord,String uid){
        StringBuffer sb=new StringBuffer();
        sb.append(" Select                              ");
        sb.append("        *                            ");
        sb.append(" from                                ");
        sb.append("        (                            ");
        sb.append("         Select                      ");
        sb.append("                t01.*,               ");
        sb.append("                rownum as newRowNum  ");
        sb.append("         from                        ");
        sb.append("                (                    ");
        sb.append("                 Select              ");
        sb.append("                        *            ");
        sb.append("                 from                ");
        sb.append("                        Tag          ");
        sb.append("                 where    ");
        sb.append("                        name like   '%"+keyWord+"%' and ");
        sb.append("                        userid='"+uid+"' ");
        sb.append("                 order by id         ");
        sb.append("                ) t01                ");
        sb.append("         where                       ");
        sb.append("                rownum<='"+end+"'    ");
        sb.append("        )                            ");
        sb.append(" where                               ");
        sb.append("        newRowNum>'"+start+"'        ");

        String sql=sb.toString();
        return search(sql);
    }
}

建表語句：

create table Tag(
   ID VARCHAR2(255) not null primary key,
   NAME VARCHAR2(255),
   userid VARCHAR2(255),
   isOutput number(1)
)

本來是要放兩個例子的，但第二個怎么也貼不上來，居然說格式不正確，算了。
再試試。

實例二：

頁面代碼：

<table>
                        <form method=post action="ViewAccount">
                        <tr><td colspan=3 align=center>開支下限<input type="text" name="leftLimit"
                                    value="" style="width: 200px; height: 20px" />
                                    開支上限<input type="text" name="rightLimit"
                                    value="" style="width: 200px; height: 20px" />
                                    <input type="submit"
                                    value="開支查詢" style="width: 60px; height: 20px"  onclick="return searchAccount()"/>
                        </td></tr>
                        </form>
                        <tr>
                            <td colspan=3 align=left>分頁:
                            <%
                                String leftLimit=(String)request.getAttribute("leftLimit");
                                String rightLimit=(String)request.getAttribute("rightLimit");

                                int currPage=Integer.parseInt((String)request.getAttribute("currPage"));
                                int pageCount=Integer.parseInt((String)request.getAttribute("pageCount"));

                                for(int i=0;i<pageCount;i++){
                                    String strPageIndex=String.valueOf(i+1);

                                    if(i==currPage){
                                        out.print(strPageIndex+" ");
                                    }
                                    else{
                                        out.print("<a href='ViewAccount?leftLimit="+leftLimit+"&&rightLimit="+rightLimit+"&&currPage="+i+"'>"+strPageIndex+"</a> ");
                                    }
                                }
                            %>
                            </td>
                        </tr>
                    </table>

                    <table id="TbSort" class="Listing" width=100% align=center>
                        <tbody id="todoList">
                            <TR>
                                <TH>ID</TH>
                                <TH>數量</TH>
                                <TH>支出/收入</TH>
                                <TH>類別</TH>
                                <TH>登記時間</TH>
                                <TH>刪除</TH>
                            </TR>
                            <%
                                List<Account> accounts=(List<Account>)request.getAttribute("Accounts");

                                if(accounts==null || accounts.size()<1){
                                    out.print("<tr><td colspan=7>沒有數據</td></tr>");
                                }
                                else{
                                    for(Account account:accounts){
                                        out.print("<tr>");
                                        out.print("<td>"+account.getId()+"</td>");
                                        out.print("<td>"+account.getCount()+"</td>");
                                        out.print("<td>"+(account.getTag().isOutput()?"支出":"收入")+"</td>");
                                        out.print("<td>"+account.getTag().getName()+"</td>");
                                        out.print("<td>"+account.getAddTime()+"</td>");
                                        out.print("<td><a href='DelAccount?id="+account.getId()+"'>刪除</a></td>");
                                        out.print("</tr>");
                                    }
                                }
                            %>
                        </tbody>
                    </table>

Servlet：

package com.sitinspring.action;

import java.util.logging.Logger;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.sitinspring.domain.User;
import com.sitinspring.service.AccountService;
import com.sitinspring.util.CommonUtil;

/**
* 用于查看Account的Servlet
* @author sitinspring
*
* @date 2008-2-11
*/
public class ViewAccountServlet extends HttpServlet {
    private static final long serialVersionUID = 8908908L;

    // 日志記錄器
    private static Logger logger = Logger.getLogger(ViewAccountServlet.class
            .toString());

    /**
     * 根據輸入的頁面名稱，取得需要的數據再進入對應頁面
     */
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, java.io.IOException {
        request.setCharacterEncoding("UTF-8");

        // 查看用戶是否存在
        User user = (User) request.getSession().getAttribute("User");
        if(user==null){
            // 如果Session中沒有執行修改操作的人,轉到錯誤頁面
            String pageName="login";
            request.setAttribute("Msg","在Session中找不到操作用戶,請重新登錄");
            gotoUrl(CommonUtil.combineUrl(pageName), request, response);
            return;
        }

        final int pageSize=CommonUtil.PageSize;

        AccountService service=new AccountService();

        // 取得頁起始記錄號
        int currPage;
        String strCurrPage=(String)request.getParameter("currPage");
        if(strCurrPage==null){
            currPage=0;
        }
        else{
            currPage=Integer.parseInt(strCurrPage);
        }
        request.setAttribute("currPage", String.valueOf(currPage));

        // 查詢關鍵詞
        String leftLimit=(String)request.getParameter("leftLimit");
        if(leftLimit==null || leftLimit.trim().length()<1){
            leftLimit="0";
        }
        request.setAttribute("leftLimit", leftLimit);

        String rightLimit=(String)request.getParameter("rightLimit");
        if(rightLimit==null || rightLimit.trim().length()<1){
            rightLimit="999999";
        }
        request.setAttribute("rightLimit", rightLimit);

        // 總數
        int pageCount=0;
        int allCount=service.getCountByKeyWord(leftLimit,rightLimit,user.getId());
        if((allCount % pageSize)==0){
            pageCount=allCount/pageSize;
        }
        else{
            pageCount=allCount/pageSize+1;
        }
        request.setAttribute("pageCount", String.valueOf(pageCount));

        request.setAttribute("Accounts", service.fetchPageRecords(currPage*pageSize,(currPage+1)*pageSize,leftLimit,rightLimit,user.getId()));
        request.setAttribute("Msg", "歡迎進入開支逐項查看頁面.");

        // 設置page參數
        String pageName = "viewAccount";
        gotoUrl(CommonUtil.combineUrl(pageName), request, response);
    }

    /**
     * 遷移到相應頁面
     * @param url
     * @param request
     * @param response
     * @throws ServletException
     * @throws java.io.IOException
     */
    private void gotoUrl(String url, HttpServletRequest request,
            HttpServletResponse response) throws ServletException,
            java.io.IOException {
        logger.info("進入頁面:" + url);

        RequestDispatcher dispatcher = null;
        dispatcher = request.getRequestDispatcher(url);
        dispatcher.forward(request, response);
    }

    public void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, java.io.IOException {
        doPost(request, response);
    }
}

Service代碼：

package com.sitinspring.service;

import java.math.BigDecimal;
import java.util.List;

import com.sitinspring.dao.AccountDao;
import com.sitinspring.domain.Account;
import com.sitinspring.exception.SoloIdMultiRecordException;

/**
* 服務類--為領域對象賬目類服務
* @author sitinspring
*
* @date 2008-1-31
*/
public class AccountService{
    AccountDao dao;

    /**
     * 無參構造函數
     *
     */
    public AccountService(){
        dao=new AccountDao();
    }

    /**
     * 對賬目進行查詢
     * @param sql
     * @return
     */
    public List<Account> search(String sql){
        return dao.search(sql);
    }


    /**
     * 取得賬目分類的統計值
     * @param tid
     * @return
     */
    public String getSumByTagId(String tid,String uid){
        String sql="select * from Account where tid='"+tid+"' and userid='"+uid+"' ";
        List<Account> accounts=search(sql);

        BigDecimal retval=new BigDecimal("0");

        for(Account account:accounts){
            retval=retval.add(account.getCount());
        }

        return retval.toString();
    }

    /**
     * 按關鍵字取得分類賬目的數量
     * @param leftLimit
     * @param rightLimit
     * @param uid
     * @return
     */
    public int getCountByKeyWord(String leftLimit,String rightLimit,String uid){
        StringBuffer sb=new StringBuffer();
        sb.append("                 Select              ");
        sb.append("                        *            ");
        sb.append("                 from                ");
        sb.append("                        Account      ");
        sb.append("                 where    ");
        sb.append("                        count >='"+leftLimit+"' and ");
        sb.append("                        count <='"+rightLimit+"' and ");
        sb.append("                        userid='"+uid+"' ");

        String sql=sb.toString();
        return search(sql).size();
    }

    /**
     * 按關鍵字取得分類賬目的分頁記錄
     * @param start
     * @param end
     * @param leftLimit
     * @param rightLimit
     * @param uid
     * @return
     */
    public List<Account> fetchPageRecords(int start,int end,String leftLimit,String rightLimit,String uid){
        StringBuffer sb=new StringBuffer();
        sb.append(" Select                              ");
        sb.append("        *                            ");
        sb.append(" from                                ");
        sb.append("        (                            ");
        sb.append("         Select                      ");
        sb.append("                t01.*,               ");
        sb.append("                rownum as newRowNum  ");
        sb.append("         from                        ");
        sb.append("                (                    ");
        sb.append("                 Select              ");
        sb.append("                        *            ");
        sb.append("                 from                ");
        sb.append("                        Account      ");
        sb.append("                 where    ");
        sb.append("                        count >='"+leftLimit+"' and ");
        sb.append("                        count <='"+rightLimit+"' and ");
        sb.append("                        userid='"+uid+"' ");
        sb.append("                 order by addTime    ");
        sb.append("                ) t01                ");
        sb.append("         where                       ");
        sb.append("                rownum<='"+end+"'    ");
        sb.append("        )                            ");
        sb.append(" where                               ");
        sb.append("        newRowNum>'"+start+"'        ");

        String sql=sb.toString();
        return search(sql);
    }
}