jmesa之limit

Jmesa默认的方式是对所有的数据进行排序与过滤，如果数据太多，对性能肯定会造成影响，这是每个分页都必须处理的问题。Jmesa通过Limit来完成从数据库提取部分数据的中转操作。
见下面代码：

private void addItems(TableFacade tableFacade) {
		Limit limit = tableFacade.getLimit();
		int rowCount = FillListData.getRowCount();
//获取分面数据前必须先设置数据总数与数据集合
		tableFacade.setTotalRows(rowCount);
		tableFacade.setItems(FillListData.getData());

		this.getContext().getRequest().setAttribute("limit", limit);
		this.getContext().getRequest().setAttribute("items",
				FillListData.getData());
//代表当前页数据的开始行与结束行位置
		int rowStart = limit.getRowSelect().getRowStart();
		int rowEnd = limit.getRowSelect().getRowEnd();

		System.out.println("rowStart=" + rowStart + " rowEnd=" + rowEnd);
//此处提取过滤的条件
		FilterSet filterSet = limit.getFilterSet();
		Collection<Filter> filters = filterSet.getFilters();
		for (Filter filter : filters) {
            String property = filter.getProperty();
            String value = filter.getValue();
            System.out.println("property="+property+" value="+value);
        }
//此处提取过滤的排序方式，如asc,desc等	
		SortSet sortSet = limit.getSortSet();
        Collection<Sort> sorts = sortSet.getSorts();
        for (Sort sort : sorts) {
            String property = sort.getProperty();
            String order = sort.getOrder().toParam();
            System.out.println("property="+property+" value="+order);
        }

	}

JSP代码据说要加上这个：

<jmesa:tableFacade items="${items}" ..... limit="${limit}" >

好像不加也行，暂时还不知道有什么影响。

有了上面代码提供的条件，就可以从数据库中提取相应部分的数据记录了。官方提供了一个很好的例子，使用的是Hibernate的Criteria查询方式:

....

protected void setDataAndLimitVariables(TableFacade tableFacade) {
        Limit limit = tableFacade.getLimit();

        PresidentFilter presidentFilter = getPresidentFilter(limit);

        /*
         * Because we are using the State feature (via stateAttr) we can do a check to see if we
         * have a complete limit already. See the State feature for more details
         */
        if (!limit.isComplete()) {
            int totalRows = presidentService.getPresidentsCountWithFilter(presidentFilter);
            tableFacade.setTotalRows(totalRows); /*
                                                  * Very important to set the totalRow
                                                  * before trying to get the
                                                  * row start and row end variables.
                                                  */
        }

        PresidentSort presidentSort = getPresidentSort(limit);
        int rowStart = limit.getRowSelect().getRowStart();
        int rowEnd = limit.getRowSelect().getRowEnd();
        Collection<President> items = presidentService.getPresidentsWithFilterAndSort(presidentFilter, presidentSort, rowStart, rowEnd);
        tableFacade.setItems(items); // Do not forget to set the items back on the tableFacade.
    }

    /**
     * A very custom way to filter the items. The PresidentFilter acts as a command for the
     * Hibernate criteria object. There are probably many ways to do this, but this is the most
     * flexible way I have found. The point is you need to somehow take the Limit information and
     * filter the rows.
     * 
     * @param limit The Limit to use.
     */
    protected PresidentFilter getPresidentFilter(Limit limit) {
        PresidentFilter presidentFilter = new PresidentFilter();
        FilterSet filterSet = limit.getFilterSet();
        Collection<Filter> filters = filterSet.getFilters();
        for (Filter filter : filters) {
            String property = filter.getProperty();
            String value = filter.getValue();
            presidentFilter.addFilter(property, value);
        }

        return presidentFilter;
    }

    /**
     * A very custom way to sort the items. The PresidentSort acts as a command for the Hibernate
     * criteria object. There are probably many ways to do this, but this is the most flexible way I
     * have found. The point is you need to somehow take the Limit information and sort the rows.
     * 
     * @param limit The Limit to use.
     */
    protected PresidentSort getPresidentSort(Limit limit) {
        PresidentSort presidentSort = new PresidentSort();
        SortSet sortSet = limit.getSortSet();
        Collection<Sort> sorts = sortSet.getSorts();
        for (Sort sort : sorts) {
            String property = sort.getProperty();
            String order = sort.getOrder().toParam();
            presidentSort.addSort(property, order);
        }

        return presidentSort;
    }

....

PresidentFilter.java

public class PresidentFilter implements CriteriaCommand {
    List<Filter> filters = new ArrayList<Filter>();

    public void addFilter(String property, Object value) {
        filters.add(new Filter(property, value));
    }

    public Criteria execute(Criteria criteria) {
        for (Filter filter : filters) {
            buildCriteria(criteria, filter.getProperty(), filter.getValue());
        }

        return criteria;
    }

    private void buildCriteria(Criteria criteria, String property, Object value) {
        if (value != null) {
            criteria.add(Restrictions.like(property, "%" + value + "%").ignoreCase());
        }
    }

    private static class Filter {
        private final String property;
        private final Object value;

        public Filter(String property, Object value) {
            this.property = property;
            this.value = value;
        }

        public String getProperty() {
            return property;
        }

        public Object getValue() {
            return value;
        }
    }
}

public class PresidentSort 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;
    }

    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));
        }
    }
    
    private 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;
        }
    }
}

public interface CriteriaCommand {
	public Criteria execute(Criteria criteria);
}

service部分的代码：

public Collection<President> getPresidentsWithFilterAndSort(PresidentFilter filter, PresidentSort sort, int rowStart, int rowEnd) {
        return presidentDao.getPresidentsWithFilterAndSort(filter, sort, rowStart, rowEnd);
    }

dao部分的代码:

public List<President> getPresidentsWithFilterAndSort(final PresidentFilter filter, final PresidentSort 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(President.class);
                criteria = filter.execute(criteria);
                criteria = sort.execute(criteria);
                criteria.setFirstResult(rowStart);
                criteria.setMaxResults(rowEnd - rowStart);
                return criteria.list();
            }
        });

        return applications;
    }

这个例子的代码实现思路很清晰，值得借鉴，故把它单独提取出来。到此，数据库分页的问题得到彻底的解决。