出家如初,成佛有余

在jmesa中使用hsql支持复杂的hsql查询

Posted in Uncategorized by chuanliang on 2008/05/06

在目前的框架中,使用的是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

 

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s

%d 博主赞过: