用createSQLQuery形式执行sql实现分页

学习笔记，转自：http://lw4135.iteye.com/blog/1743342

	public Page getAllWaitWork(String staffID, String roleIDs, Page page) {
		StringBuilder sb = new StringBuilder(
				"select count( distinct v.processInstanceId) from WaitWorkVO v ");
		sb.append("where (v.actorId = 's_" + staffID + "'");
		if (roleIDs != null && !"".equals(roleIDs)) {
			String roleIDstr[] = roleIDs.split(",");
			for (int i = 0; i < roleIDstr.length; i++) {
				sb.append(" or v.actorId = 'r_" + roleIDstr[i] + "'");
			}
		}
		sb.append(" ) ");
		Query query = this.getSession().createQuery(sb.toString());// todo
		List list = query.list();
		int pageSize = page.getPageSize();
		int curPage = page.getCurrentPage();
		int totalSize = Integer.parseInt(list.get(0).toString());// todo
																	// bug可能超过整形最大？
		int pages = totalSize / pageSize + ((totalSize % pageSize) > 0 ? 1 : 0);
		int endPage = totalSize / pageSize
				+ ((totalSize % pageSize) > 0 ? 1 : 1);
		if (curPage < 1)
			curPage = 1;
		if (curPage > pages)
			curPage = pages;
		if (endPage < 1)
			endPage = 1;
		if (endPage > pages)
			endPage = pages;
		StringBuilder sb2 = new StringBuilder(
				"select distinct v.ID_ ,v.BD_ID,v.PROCESSID,v.PRO_INFO,v.SPONSOR,v.SPONSOR_ID,v.PROCESSOR,"
						+ "v.TITLE,v.INC_CODE,v.PROPOSER_TIME,v.PROPOSER_NAME,v.TASKNAME,v.BEGAIN_DATE,v.END_DATE from v_widget_waitwork_view  v");
		sb2.append(" where (v.actorId = 's_" + staffID + "'");
		if (roleIDs != null && !"".equals(roleIDs)) {
			String roleIDstr[] = roleIDs.split(",");
			for (int i = 0; i < roleIDstr.length; i++) {
				sb2.append(" or v.actorId = 'r_" + roleIDstr[i] + "'");
			}
		}
		sb2.append(" ) order by v.proposer_Time desc ");
		query = this.getSession().createSQLQuery(sb2.toString());
		List data = query.setFirstResult(pageSize * (curPage - 1))
				.setMaxResults((endPage - curPage + 1) * pageSize).list();
		return new Page(curPage, pageSize, pages, data, totalSize);
	}