/***
* **write by zzq 2011-04-14
* @param sql 查询语句 字段数量和len对应
* @param pageNo 当前页
* @param pageSize 总的页数
* @param item 需要对应的页面显示字段
* @param len 总的查询语句内要显示的字段的数量
* @return
*/
public Page pagedSQLQuery(String sql, int pageNo, int pageSize,
String[] item, int len) {
Assert.hasText(sql);
Assert.isTrue(pageNo >= 1, "pageNo should be eg 1");
// Count查询
String countQueryString = "select count (*) "
+ removeSelect(removeOrders(sql));
Integer totalCount = this.getSQLCount(countQueryString);
if (totalCount < 1) {
return new Page();
}
// 实际查询返回分页对象
Query query = createSQLQuery(sql);
int start = (pageNo - 1) * pageSize;
List<Object[]> listTemp = query.setFirstResult(start).setMaxResults(
pageSize).list();
List<Map<String, String>> result = new ArrayList<Map<String, String>>();
int i=0;
for(Object[] o:listTemp)
{
Map<String, String> map = new HashMap<String, String>();
for (i = 0; i < len; i++) {
map.put(item[i],null2String(o[i]));
}
result.add(map);
}
return new Page(result, totalCount);
}
//返回list对象
public List<Object[]> ListSQLQuery(String sql) {
Query query = createSQLQuery(sql);
return query.list();
}
/**
* 去除hql的select 子句,未考虑union的情况,用于pagedSQLQuery.
*
* @param hql
* HQL字符串
* @return 删除select语句后的字符串
* @see #pagedQuery(String,int,int,Object[])
*/
private static String removeSelect(String hql) {
Assert.hasText(hql);
int beginPos = hql.toLowerCase(Locale.CHINA).indexOf("from");
Assert.isTrue(beginPos != -1, " hql : " + hql
+ " must has a keyword 'from'");
return hql.substring(beginPos);
}
/**
* 去除hql的order by 子句,用于pagedQuery.
*
* @param hql
* HQL字符串
* @return 删除排序语句后的字符串
* @see #pagedQuery(String,int,int,Object[])
*/
private static String removeOrders(String hql) {
Assert.hasText(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();
}
//获得总数
public Integer getSQLCount(String sql) {
Object result = createSQLQuery(sql).uniqueResult();
return ((Number) result).intValue();
}
public Query createSQLQuery(String sql) {
Query query = getSession().createSQLQuery(sql);
return query;
}
分页函数还可以优化,里面存在重复调用语句