SQL 组合查询 分页（hibernate+webwork+spring）

hibernate 支持分页，由于组合查询在DAO层分页，业务逻辑比较混乱
这里用SQ在业务层分页处理

1.Action 层
获取Web端的参数，这里用webwork机制获取

/**
	 * 查询问题列表
	 */
	public String queryQuestion()
	{
		System.out.println("********************查询问题列表 queryQuestion.action************************");
//		String expertName = ServletActionContext.getRequest().getParameter("expertName");
//		String sortId = ServletActionContext.getRequest().getParameter("questionSort");
		String expertName = expertQuery.getQuestionExpert();
		String sortId = expertQuery.getQuestionSort();
		System.out.println("expertName = "+expertName +" --- sortId = "+sortId);
		
		List<ExpertQuestion> queryQuestionCount = new ArrayList<ExpertQuestion>();
		List<ExpertQuestion> queryQuestion = new ArrayList<ExpertQuestion>();
		try {
			queryQuestionCount = mavinService.queryQuestionCount(expertName,sortId);
			/**
			 * ***** 分页 ***** 
			 */
			PageMavin page = new PageMavin();
			page.setCurPageTwo(pagesTwo);
			page.setPageSizeTwo(pageSizeTwo);
			page.setTotalRowTwo(queryQuestionCount.size());
			Mypagebar=page.getToolsMenuTwo();
			queryQuestion = mavinService.queryQuestionPage(expertName,sortId,page.getCurPageTwo(),page.getPageSizeTwo());
			ServletActionContext.getRequest().setAttribute("QUERY_QUESTION",queryQuestion);
		} catch (BOException e) {
			e.printStackTrace();
		}
		
		return SUCCESS;
	}

2.业务层 处理SQL

/**
	 * 组合查询 问题列表
	 * 组建 SQL
	 */
	public List<ExpertQuestion> queryQuestionCount(String expertName,String sortId)throws BOException;
	public List<ExpertQuestion> queryQuestionPage(String expertName,String sortId,int currPage,int pageSize)throws BOException;

public List<ExpertQuestion> queryQuestionPage(String expertName,String sortId,int currPage,int pageSize)throws BOException
	{
		System.out.println("currPage = "+currPage+" - pageSize = "+pageSize);
		List<ExpertQuestion> queryQuestion = new ArrayList<ExpertQuestion>();
		String SQL = "";
		//所有专家 AND 所有分类
		if(expertName.equals("allExpert") && sortId.equals("allSort"))
		{
			System.out.println("所有专家 AND 所有分类");
			SQL = " select q.question_id,q.question_title,q.question_infor,q.question_sort,q.question_expert,q.question_state,q.question_author,q.question_time,q.question_access,q.question_breviary,q.question_delete from expert_question q where q.question_delete='1' and question_Access='0'";
		}
//		根据专家名 AND 分类ID 查询
		if(!expertName.equals("allExpert") && !sortId.equals("allSort"))
		{
			System.out.println("根据专家名 AND 分类ID 查询");
			SQL = " select q.question_id,q.question_title,q.question_infor,q.question_sort,q.question_expert,q.question_state,q.question_author,q.question_time,q.question_access,q.question_breviary,q.question_delete from expert_question q where q.question_delete='1' and question_Access='0' and q.question_expert = '"+expertName+"'"+" and q.question_sort ='"+sortId+"'";
		}
//		所有专家 + 具体分类
		if(expertName.equals("allExpert") && !sortId.equals("allSort"))
		{
			System.out.println("所有专家 + 具体分类");
			SQL = " select q.question_id,q.question_title,q.question_infor,q.question_sort,q.question_expert,q.question_state,q.question_author,q.question_time,q.question_access,q.question_breviary,q.question_delete from expert_question q where q.question_delete='1' and question_Access='0' and q.question_sort ='"+sortId+"'";
		}
//		具体专家 + 所有分类
		if(!expertName.equals("allExpert") && sortId.equals("allSort"))
		{
			System.out.println("具体专家 + 所有分类");
			SQL = " select q.question_id,q.question_title,q.question_infor,q.question_sort,q.question_expert,q.question_state,q.question_author,q.question_time,q.question_access,q.question_breviary,q.question_delete from expert_question q where q.question_delete='1' and question_Access='0' and q.question_expert = '"+expertName+"'";
		}
		SQL = "select * from ( select t1.*,rownum rownum_ from("+SQL+" ) t1 where rownum <= "+ currPage * pageSize +" ) where rownum_ > "+  ((currPage-1) * pageSize);
		System.out.println("SQL PAGE = "+ SQL );
		try {
			queryQuestion = mavinDAO.queryQuestion(SQL);
		} catch (DAOException e) {
			e.printStackTrace();
		}
		return queryQuestion;
	}

3.DAOImpl

/**
	 * 获取问题列表 expertName 、sortId 
	 * 组合查询 SQL 
	 * 可参照 查询专家列表 41行
	 * public List<TbExpert> searchMavinCount ... 写
	 * 但是 解耦性 不强、业务层和DAOImpl 换乱、可维护性降低
	 * 用 SQL 业务层 控制参数 DAOImpl不变 解耦性增强 
	 */
	@SuppressWarnings("unchecked")
	public List<ExpertQuestion> queryQuestion(final String SQL) throws DAOException
	{
		List<ExpertQuestion> queryQuestion = this.getMyHibernateTemplate().executeFind(new HibernateCallback()
		{
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query q = session.createSQLQuery(SQL).addEntity(ExpertQuestion.class);
				return q.list();
			}
		});
		System.out.println("-query question-"+queryQuestion.size());
		return queryQuestion;
	}

4.分页源码 【直接用这个类就可以了，我的分页是继承了重写，里面有其他需求，一般用这个就哦了】

package com.linkage.util.page;

/**
 * @{#} PageUtilsTabOne.java Create on 2009-9-30 上午09:27:09    
 *    
 * Copyright (c) 2009 by linkaged. 
 *     
 * @author wenge
 *
 * @version 1.0  
 * 
 * @Desc: 前台选项卡一分页BEAN
 */

public class PageUtilsTabTwo {
	 protected int curPageTwo = 0; //当前页
	 protected int pageSizeTwo = 0; //每页多少行
	 protected int endSizeTwo ; //用于not in(select top endSize id)不在多少行内
	 protected int totalRowTwo ; //共多少行
	 protected int totalPageTwo ; //共多少页
	 
	 public int getStartTwo(){
		 if(curPageTwo>1)
	     	  return (curPageTwo-1)*pageSizeTwo;
		 else
			  return 0;
	 }
	 

	 public int getEndTwo(){
		 return pageSizeTwo;
	 }
	 public int getCurPageTwo() {
		 return curPageTwo;
	 }
	 public void setCurPageTwo(int curPageTwo) {
	  
	  int tempTwo = pageSizeTwo * (curPageTwo-1);
	  this.setEndSizeTwo(tempTwo);
	  this.curPageTwo = curPageTwo;
	 }
	 public int getEndSizeTwo() {
	  return endSizeTwo;
	 }
	 public void setEndSizeTwo(int endSizeTwo) {
	  this.endSizeTwo = endSizeTwo;
	 }
	 public int getPageSizeTwo() {
	  return pageSizeTwo;
	 }
	 public void setPageSizeTwo(int pageSizeTwo) {
	  this.pageSizeTwo = pageSizeTwo;
	 }
	 public int getTotalRowTwo() {
	  return totalRowTwo;
	 }
	 public void setTotalRowTwo(int totalRowTwo) {
	  
	  totalPageTwo = totalRowTwo/pageSizeTwo;
	  if(totalRowTwo%pageSizeTwo > 0)
	   totalPageTwo = totalPageTwo + 1;
	  
	  this.totalRowTwo = totalRowTwo;
	 }
	 public int getTotalPageTwo(){
	  
	  return this.totalPageTwo;
	 }
	 
	 public String getToolsMenuTwo() {
	        StringBuffer strTwo = new StringBuffer("");
	        int nextTwo, prevTwo;
	        prevTwo = curPageTwo - 1;
	        nextTwo = curPageTwo + 1;

	        if (curPageTwo > 1) {
	            strTwo.append(
	                "<a href=\"#\" onclick=\"document.forms(0).pagesTwo.value=1;document.forms(0).submit();\">首页</a>&nbsp;");
	        } else {
	            strTwo.append("<a href=\"#\">首页</a>&nbsp;");
	        }
	        if (curPageTwo > 1) {
	            strTwo.append(
	                "<a href=\"#\" onclick='document.forms(0).pagesTwo.value=" +prevTwo + ";document.forms(0).submit();'>上页</a>&nbsp;");
	        } else {
	            strTwo.append("<a href=\"#\">上页</a>&nbsp;");
	        }
	        if (curPageTwo < totalPageTwo) {
	            strTwo.append(
	                "<a href=\"#\" onclick='document.forms(0).pagesTwo.value=" +nextTwo + ";document.forms(0).submit();'>下页</a>&nbsp;");
	        } else {
	            strTwo.append("<a href=\"#\" >下页</a>&nbsp;");
	        }
	        if (totalPageTwo > 1 && curPageTwo != totalPageTwo) {
	            strTwo.append(
	                "<a href=\"#\"  onclick='document.forms(0).pagesTwo.value=" +totalPageTwo + ";document.forms(0).submit();'>尾页</a>&nbsp;&nbsp;");
	        } else {
	            strTwo.append("<a href=\"#\" >尾页</a>&nbsp;&nbsp;");
	        }
	        strTwo.append(" 共 <font color=\"red\" >" + totalRowTwo + "</font> 条记录/当前第"+curPageTwo+"页");
	        
	        strTwo.append("   转到");
	        strTwo.append("<SELECT style='width: 50px;' size=1 name=PagelistTwo onchange='this.form.pagesTwo.value=this.value;this.form.submit();'>");
	        for (int i = 1; i < totalPageTwo + 1; i++) {
	            if (i == curPageTwo) {
	                strTwo.append("<OPTION value=" + i + " selected>" + i +
	                           "</OPTION>");
	            } else {
	                strTwo.append("<OPTION value=" + i + ">" + i + "</OPTION>");
	            }
	        }
	        strTwo.append("</SELECT>页");
	        strTwo.append("<INPUT type=hidden  value=" + curPageTwo + " name=\"pagesTwo\" > ");
	        strTwo.append("<INPUT type=hidden  value=" + pageSizeTwo+" name=\"pageSizeTwo\"> ");
	        System.out.println(strTwo);
	        return strTwo.toString();
	    }

}

 <!--  分页  -->
	<table>
	   <tr>
	    <td></td>
	    <td height="25">&nbsp;</td>
	    <td height="25">
	   		<div align="center" class="text_title03">
	   			<ww:property value="Mypagebar" escape="false"/>
	   		</div></td>
	    <td height="25">&nbsp;</td>
	   </tr>
	</table>