关于Hibernate分页类和jdbc的sql分页完美融合

1.先来看这两个核心兄弟方法。

/**
	 * 分页查询函数
	 * 
	 * @param hql
	 * @param pageNo
	 *            页号,从1开始
	 * @param pageSize
	 *            每页尺寸
	 * @param values
	 * @return 总记录数和当前页数据的Page对象。
	 */
	@SuppressWarnings("unchecked")
	public Page superPagedQuery(String hql, int pageNo, int pageSize,
			Object... values) {
		Page page = null;
		try {
			Session session = this.getSession(false);
			// Count查询
			String countQueryString = " select count (*) "
					+ removeSelect(removeOrders(hql));
			List countlist = this.find(session, countQueryString, values);
			int totalCount = 0;
			Object obj = countlist.get(0);
			if (obj instanceof String) {
				totalCount = Integer.parseInt((String) obj);
			}
			if (obj instanceof Integer) {
				totalCount = (Integer) obj;
			}
			if (obj instanceof Long) {
				totalCount = Integer.parseInt("" + obj);
			}

			if (totalCount < 1)
				return new Page();
			// 实际查询返回分页对象
			int startIndex = Page.getStartOfPage(pageNo, pageSize);
			Query query = createQuery(session, hql, values);
			// 新加入设置查询缓存
			query.setCacheable(false);
			List list = null;

			list = query.setFirstResult(startIndex).setMaxResults(pageSize)
					.list();

			page = new Page(startIndex, totalCount, pageSize, list);

		} catch (Exception e) {
			e.printStackTrace();
		}

		return page;
	}

	/**
	 * 分页查询函数(Oracle Sql)
	 * 
	 * @param hql
	 * @param pageNo
	 *            页号,从1开始
	 * @param pageSize
	 *            每页尺寸
	 * @param values
	 * @return 总记录数和当前页数据的Page对象。
	 */
	@SuppressWarnings("unchecked")
	public Page superSqlPagedQuery(String sql, int pageNo, int pageSize,
			Object... values) {
		if (log.isDebugEnabled()) {
			log.debug("superSqlPagedQuery() start...");
		}
		Page page = null;
		try {
			// Count查询
			String countQueryString = " select count(0) as cnt "
					+ removeSelect(removeOrders(sql));
			if(log.isInfoEnabled()){
				log.info("countQueryString:"+countQueryString);
			}
			List countlist = this.jdbcTemplate.queryForList(countQueryString,
					values);
			int totalCount = 0;
			Object obj = countlist.get(0);
			if (obj instanceof String) {
				totalCount = Integer.parseInt((String) obj);
			}
			if (obj instanceof Integer) {
				totalCount = (Integer) obj;
			}
			if (obj instanceof Long) {
				totalCount = Integer.parseInt("" + obj);
			}
			if (obj instanceof Map) {
				totalCount = Integer.parseInt("" + ((Map)obj).get("cnt"));
			}

			if (totalCount < 1)
				return new Page();
			// 实际查询返回分页对象
			int startIndex = Page.getStartOfPage(pageNo, pageSize);
			String sqlQueryString = createSqlQuery("ora", sql, startIndex,
					startIndex + pageSize);
			if(log.isInfoEnabled()){
				log.info("sqlQueryString:"+sqlQueryString);
			}
			List list = this.jdbcTemplate.queryForList(sqlQueryString, values);
			page = new Page(startIndex, totalCount, pageSize, list);

		} catch (Exception e) {
			e.printStackTrace();
		}

		if (log.isDebugEnabled()) {
			log.debug("superSqlPagedQuery() end");
		}
		return page;
	}

2.再看他们的辅助方法。

/**
	 * 创建查询脚本
	 * 
	 * @param session
	 * @param hql
	 * @param values
	 * @return
	 */
	public Query createQuery(Session session, String hql, Object... values) {
		Query query = session.createQuery(hql);
		for (int i = 0; i < values.length; i++) {
			query.setParameter(i, values[i]);
		}
		return query;
	}

	/**
	 * 创建查询脚本(Sql)
	 * 
	 * 
	 * @param type
	 *            ora: oracle <br/>
	 *            mss: microsoft sql server <br/>
	 *            mys: my sql <br/>
	 *            db2: ibm db2<br/>
	 * @param sql
	 * @param pageStart
	 * @param pageEnd
	 * @return
	 */
	public String createSqlQuery(String type, String sql, int pageStart,
			int pageEnd) {

		if ("ora".equals(type)) {
			sql = "select * from (select pTable_.*, rownum rNum_ from (" + sql
					+ ") pTable_ where rownum<=" + pageEnd + ") where rNum_>="
					+ pageStart;
		} else if ("mss".equals(type)) {

		} else if ("mys".equals(type)) {

		} else if ("db2".equals(type)) {
			sql = "select * from (select *, rownumber() ROW_NEXT from (" + sql
					+ ") where ROW_NEXT between " + pageStart + " and "
					+ pageEnd;
		}
		return sql;
	}

3.奉献两个共同方法

/**
	 * 去除hql中的order by语句
	 * 
	 * @param hql
	 * @return
	 */
	private static String removeOrders(String hql) {
		Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*",
				Pattern.CASE_INSENSITIVE);
		Matcher m = p.matcher(hql);
		StringBuffer sb = new StringBuffer();
		while (m.find()) {
			m.appendReplacement(sb, "");
		}
		m.appendTail(sb);
		return sb.toString();
	}

	private static String removeSelect(final String hql) {
		final int beginPos = hql.toLowerCase().indexOf("from");
		return hql.substring(beginPos);
	}

4.以上方法都包装在了SuperHibernateEntityDao.java中，可看附件。

5.关于Page对象类可查看附件Page.java。
当然，里面没用的业务处理对象及方法都可以删掉。