在目前的框架中，使用的是Hibernate Criteria来实现复杂的分页、排序、过滤等操作，基本上能够完成大部分复杂的查询分页、排序处理，但对于统计分析这样的复杂查询，Criteria实现还是有点力不从心，比较麻烦，尤其是在多表查询的情况下。
由于对于整型字段在目前在列表的输入框中输入过滤条件，会由于HttpServletRequest为String类型，而Hibernate为整型字段，导致对于在数据库中非String类型的字段过滤报错，因此暂时去除对过滤的支持功能，后续有空再研究解决方案，方法如下：
HtmlRow row = table.getRow();
row.setFilterable(false);
这样处理后，意味着我们在jmesa中实际上只需要处理分页、排序操作（导出为txt、excel、pdf等与此关系不大），因此对jmesa增加对hsql语句查询的支持，而不采用Criteria的方式，方法如下：
1. 对排序类HibernateSort
package com.mobilesoft.esales.dao.hibernate;
import java.util.ArrayList;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.criterion.Order;
public class HibernateSort implements CriteriaCommand {
List<Sort> sorts = new ArrayList<Sort>();
public void addSort(String property, String order) {
sorts.add(new Sort(property, order));
}
public Criteria execute(Criteria criteria) {
for (Sort sort : sorts) {
buildCriteria(criteria, sort.getProperty(), sort.getOrder());
}
return criteria;
}
public List getSortList(){
return this.sorts;
}
private void buildCriteria(Criteria criteria, String property, String order) {
if (order.equals(Sort.ASC)) {
criteria.addOrder(Order.asc(property));
} else if (order.equals(Sort.DESC)) {
criteria.addOrder(Order.desc(property));
}
}
public static class Sort {
public final static String ASC = “asc”;
public final static String DESC = “desc”;
private final String property;
private final String order;
public Sort(String property, String order) {
this.property = property;
this.order = order;
}
public String getProperty() {
return property;
}
public String getOrder() {
return order;
}
}
}
2. 对DAO类PersonDAO
增加getPersonWithFilterAndSort2和getPersonCountWithFilter2：
public int getPersonCountWithFilter(final HibernateFilter filter) {
Integer count = (Integer) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
Criteria criteria = session.createCriteria(Person.class);
criteria = filter.execute(criteria);
criteria.setProjection(Projections.rowCount()).uniqueResult();
return criteria.uniqueResult();
}
});
return count.intValue();
}
public int getPersonCountWithFilter2(final HibernateFilter filter) {
Long count = (Long) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
StringBuffer querySql=new StringBuffer(“select count(person) from Person as person “);
Query query = session.createQuery(querySql.toString());
List list = query.list();
return list.get(0);
}
});
return count.intValue();
}
public List<Person> getPersonWithFilterAndSort(final HibernateFilter filter, final HibernateSort sort, final int rowStart, final int rowEnd) {
List applications = (List) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
Criteria criteria = session.createCriteria(Person.class);
criteria = filter.execute(criteria);
criteria = sort.execute(criteria);
criteria.setFirstResult(rowStart);
criteria.setMaxResults(rowEnd – rowStart);
return criteria.list();
}
});
return applications;
}
public List<Person> getPersonWithFilterAndSort2(final HibernateFilter filter, final HibernateSort sort, final int rowStart, final int rowEnd) {
List applications = (List) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
List sorts =sort.getSortList();
Iterator iterator=sorts.itera
tor();
StringBuffer sortSql=new StringBuffer(” “);
int i=1;
while(iterator.hasNext()){
HibernateSort.Sort field=(HibernateSort.Sort)iterator.next();
String property=field.getProperty();
String order=field.getOrder();
if(i>1)
sortSql.append(” , “);
else
sortSql.append(” order by “);
sortSql.append(property);
sortSql.append(” “);
sortSql.append(order);
i++;
}
StringBuffer querySql=new StringBuffer(“select person from Person as person “);
querySql.append(sortSql);
Query query = session.createQuery(querySql.toString());
logger.fatal(“$HibernateCallback.doInHibernate(Session) “+querySql.toString()); //$NON-NLS-1$
query.setFirstResult(rowStart);
query.setMaxResults(rowEnd-rowStart);
List list = query.list();
return list;
}
});
return applications;
}
3. PersonService及PersonServiceImpl
在PersonService中增加接口声明
public int getPersonCountWithFilter2(HibernateFilter filter);
public Collection<Person> getPersonWithFilterAndSort2(HibernateFilter filter, HibernateSort sort, int rowStart, int rowEnd);
在PersonServiceImpl增加实现：
public int getPersonCountWithFilter2(HibernateFilter filter) {
return personDAO.getPersonCountWithFilter2(filter);
}
public Collection<Person> getPersonWithFilterAndSort2(HibernateFilter filter, HibernateSort sort, int rowStart, int rowEnd) {
return personDAO.getPersonWithFilterAndSort2(filter, sort, rowStart, rowEnd);
}
4. PersonAction
将获取数据修改为调用getPersonWithFilterAndSort2，获取总数修改为getPersonCountWithFilter2
if (!limit.isComplete()) {
// deal with Criteria
//int totalRows = personService.getPersonCountWithFilter(hibernateFilter);
// deal with hsql
int totalRows = personService.getPersonCountWithFilter2(hibernateFilter);
tableFacade.setTotalRows(totalRows);
}
HibernateSort hibernateSort = getHibernateSort(limit);
int rowStart = limit.getRowSelect().getRowStart();
int rowEnd = limit.getRowSelect().getRowEnd();
// deal with Criteria
//Collection<Person> items = personService.getPersonWithFilterAndSort(hibernateFilter, hibernateSort, rowStart, rowEnd);
// deal with hsql
Collection<Person> items = personService.getPersonWithFilterAndSort2(hibernateFilter, hibernateSort, rowStart, rowEnd);
tableFacade.setItems(items); // Do not forget to set the items back on the tableFacade.
5、参考
http://code.google.com/p/jmesa/wiki/LimitExample
转载请注明：出家如初,成佛有余 » 在jmesa中使用hsql支持复杂的hsql查询