分頁和多條件查詢功能，分頁條件功能

分頁和多條件查詢功能，分頁條件功能

/** * 輔助拼接HQL語句的工具類 * @author G-Xia * */public class QueryHelper {private String fromClause; // From子句private String whereClause = ""; // Where子句private String orderByClause = ""; // OrderBy子句private List<Object> parameters = new ArrayList<Object>(); // 參數列表/** * 產生From子句 *  * @param clazz * @param alias *            別名 */public QueryHelper(Class clazz, String alias) {fromClause = "FROM " + clazz.getSimpleName() + " " + alias;}/** * 拼接Where子句 *  * @param condition * @param args */public QueryHelper addWhereCondition(String condition, Object... args) {// 拼接if (whereClause.length() == 0) {whereClause = " WHERE " + condition;} else {whereClause += " AND " + condition;}// 處理參數if (args != null && args.length > 0) {for (Object arg : args) {parameters.add(arg);}}return this;}/** * 如果第一個參數的值為true，就拼接Where子句 *  * @param append * @param condition * @param args */public QueryHelper addWhereCondition(boolean append, String condition, Object... args) {if (append) {addWhereCondition(condition, args);}return this;}/** * 拼接OrderBy子句 *  * @param propertyName * @param asc *            true表示升序，false表示降序 */public QueryHelper addOrderByProperty(String propertyName, boolean asc) {if (orderByClause.length() == 0) {orderByClause = " ORDER BY " + propertyName + (asc ? " ASC" : " DESC");} else {orderByClause += ", " + propertyName + (asc ? " ASC" : " DESC");}return this;}/** * 如果第一個參數的值為true，就拼接OrderBy子句 *  * @param append * @param propertyName * @param asc */public QueryHelper addOrderByProperty(boolean append, String propertyName, boolean asc) {if (append) {addOrderByProperty(propertyName, asc);}return this;}/** * 擷取查詢資料列表的HQL語句 *  * @return */public String getQueryListHql() {return fromClause + whereClause + orderByClause;}/** * 擷取查詢總記錄數的HQL語句（沒有OrderBy子句） *  * @return */public String getQueryCountHql() {return "SELECT COUNT(*) " + fromClause + whereClause;}/** * 擷取參數列表 *  * @return */public List<Object> getParameters() {return parameters;}/** * 準備PageBean對象到Struts2的棧頂 * @param service * @param pageNum */public void preparePageBean(DaoSupport<?> service, int pageNum){PageBean pageBean = service.getPageBean(pageNum, this);ActionContext.getContext().getValueStack().push(pageBean);}}

/** * 公用的查詢分頁資訊的方法（最終版） *  * @param pageNum * @param queryHelper *            查詢語句 + 參數列表 * @return */public PageBean getPageBean(int pageNum, QueryHelper queryHelper) {System.out.println("------------> DaoSupportImpl.getPageBean( int pageNum, QueryHelper queryHelper )");// 擷取pageSize等資訊int pageSize = Configuration.getPageSize();List<Object> parameters = queryHelper.getParameters();// 查詢一頁的資料列表Query query = getSession().createQuery(queryHelper.getQueryListHql());if (parameters != null && parameters.size() > 0) { // 設定參數for (int i = 0; i < parameters.size(); i++) {query.setParameter(i, parameters.get(i));}}query.setFirstResult((pageNum - 1) * pageSize);query.setMaxResults(pageSize);List list = query.list(); // 查詢// 查詢總記錄數query = getSession().createQuery(queryHelper.getQueryCountHql()); // 注意空格！if (parameters != null && parameters.size() > 0) { // 設定參數for (int i = 0; i < parameters.size(); i++) {query.setParameter(i, parameters.get(i));}}Long count = (Long) query.uniqueResult(); // 查詢return new PageBean(pageNum, pageSize, count.intValue(), list);}

// 準備分頁的資料 -- 使用QueryHelpernew QueryHelper(Topic.class, "t")//.addWhereCondition("t.forum=?", forum)//.addWhereCondition((viewType == 1), "t.type=?", Topic.TYPE_BEST) // 1 表示只看精華帖.addOrderByProperty((orderBy == 1), "t.lastUpdateTime", asc) // 1 表示只按最後更新時間排序.addOrderByProperty((orderBy == 2), "t.postTime", asc) // 表示只按主題發表時間排序.addOrderByProperty((orderBy == 3), "t.replyCount", asc) // 表示只按回複數量排序.addOrderByProperty((orderBy == 0), "(CASE t.type WHEN 2 THEN 2 ELSE 0 END)", false)//.addOrderByProperty((orderBy == 0), "t.lastUpdateTime", false) // 0 表示預設排序(所有置頂帖在前面，並按最後更新時間降序排列).preparePageBean(topicService, pageNum);

JSP中頁面資訊

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%@ taglib prefix="s" uri="/struts-tags" %><%-- 分頁資訊 --%><div id=PageSelectorBar><div id=PageSelectorMemo>頁次：${currentPage}/${pageCount }頁  每頁顯示：${pageSize }條  總記錄數：${recordCount }條</div><div id=PageSelectorSelectorArea><a href="javascript: gotoPage(1)" title="首頁" style="cursor: hand;"><img src="${pageContext.request.contextPath}/style/blue/images/pageSelector/firstPage.png"/></a><%-- 頁碼列表 --%><s:iterator begin="%{beginPageIndex}" end="%{endPageIndex}" var="num"><s:if test=" #num != currentPage "><%-- 非當前頁，有連結 --%><span class="PageSelectorNum" style="cursor: hand;" onClick="gotoPage(${num});">${num}</span></s:if><s:else><%-- 當前頁，沒有連結 --%><span class="PageSelectorNum PageSelectorSelected">${num}</span></s:else></s:iterator><a href="javascript: gotoPage(${pageCount})" title="尾頁" style="cursor: hand;"><img src="${pageContext.request.contextPath}/style/blue/images/pageSelector/lastPage.png"/></a>轉到：<select id="pn" onchange="gotoPage( this.value )"><s:iterator begin="1" end="%{pageCount}" var="num"><option value="${num}">${num}</option></s:iterator></select><%-- 讓select預設選中當前頁 --%><script type="text/javascript">$("#pn").val( "${currentPage}" );</script></div></div><script type="text/javascript">/** * 轉到指定的頁碼 * @param {Object} pageNum */function gotoPage( pageNum ){// 方式一： // window.location.href = "forum_show.do?id=${id}&pageNum=" + pageNum;// alert("請實現gotoPage()方法！");// 方式二：$("#pageForm").append("<input type='hidden' name='pageNum' value='" + pageNum + "'>"); // 添加pageNum表單欄位$("#pageForm").submit(); // 提交表單}</script>

sql 分頁多條件查詢

查詢可以用if else 去做。
例如：
sql="select * from ........."
``````````````
xh=request("型號")
······
if xh<>"" then sql=sql+" and 型號='"&xh&"'"

分頁可以用
<%TurnPage(rs,20,"型號="&xh&"&其他6個參數·······")%>

<%
Sub TurnPage(ByRef Rs_tmp,PageSize,canshu) 'Rs_tmp 記錄集 PageSize 每頁顯示的記錄條數;
Dim TotalPage '總頁數
Dim PageNo '當前顯示的是第幾頁
Dim RecordCount '總記錄條數
Rs_tmp.PageSize = PageSize
RecordCount = Rs_tmp.RecordCount
TotalPage = INT(RecordCount / PageSize * -1)*-1
PageNo = Request.QueryString ("PageNo")
'直接輸入頁數跳轉；
If Request.Form("PageNo")<>"" Then PageNo = Request.Form("PageNo")
'如果沒有選擇第幾頁，則預設顯示第一頁；
If PageNo = "" then PageNo = 1
If RecordCount <> 0 then
Rs_tmp.AbsolutePage = PageNo
End If
'擷取當前檔案名稱，使得每次翻頁都在當前頁面進行；
Dim fileName,postion
fileName = Request.ServerVariables("script_name")
postion = InstrRev(fileName,"/")+1
'取得當前的檔案名稱，使翻頁的連結指向當前檔案；
fileName = Mid(fileName,postion)
response.write "<table border=0 width='100%'><tr> "
If RecordCount = 0 or TotalPage = 1 Then
Response.Write ""
Else
response.write "<td align=left style='font-size:12px'> 總頁數：<font color=#ff3333>"&TotalPage&"</font>頁"
response.write "當前第<font color=#ff3333>"&PageNo&"</font>頁 <&#47......餘下全文>>
 
GridView多條件查詢分頁問題!!!

首先綁定資料不必要有兩個函數.只需要判斷是哪種情況.然後讀取不同的DataTable即可.
其次.在查詢按鈕事件中.記得把頁碼還原到0.
最後.需要判斷一下是哪個事件觸發了綁定.從而決定如何讀取DataTable.即判斷是否點擊了"查詢"按鈕.

public partial class Default3 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Label1.Text = Session["name"] as string;
ViewState["search"]=0;//設定初始狀態為非查詢
BinData();
}
}
protected void BinData()
{
UserDB db = new UserDB();
//判斷是否點擊了查詢按鈕且文字框中有值
DataTable table = TextBox1.Text.Trim().Length>0&ViewState["search"].ToString().Equals("1")?db.InsertN(TextBox1.Text.Trim()):db.InsertUsern();
PagedDataSource pds = new PagedDataSource();
pds.DataSource = table.DefaultView;
pds.AllowPaging = true;
AspNetPager1.RecordCount = pds.DataSourceCount;
pds.CurrentPageIndex = AspNetPager1.CurrentPageIndex - 1;
pds.PageSize = AspNetPager1.PageSize;
this.GridView1.DataSource = pds;
this.GridView1.DataBind();
}
protected void AspNetPager1_PageChanging(object src, Wuqi.Webdiyer.PageChangingEventArgs e)
{
AspNetPager1.CurrentPageIndex = e.NewPageIndex;
ViewState["search"] =0;//還原初始狀態
BinData();
}
//查詢按鈕
protected void Button1_Click(object sender, EventArgs e)
{
AspNetPager1.CurrentPageIndex = 0;
ViewState[&......餘下全文>>