Limit数据库端分页

package com.sinovs.saler.webapp.action;

 

import java.util.List;

 

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

 

import org.apache.struts.action.ActionForm;

import org.apache.struts.action.DispatchAction;

import org.apache.struts.action.ActionForward;

import org.apache.struts.action.ActionMapping;

import org.apache.struts.action.ActionMessage;

import org.apache.struts.action.ActionMessages;

import org.extremecomponents.table.context.Context;

import org.extremecomponents.table.context.HttpServletRequestContext;

import org.extremecomponents.table.core.Preferences;

import org.extremecomponents.table.core.PreferencesConstants;

import org.extremecomponents.table.core.TableConstants;

import org.extremecomponents.table.core.TableModelUtils;

import org.extremecomponents.table.core.TableProperties;

import org.extremecomponents.table.limit.Limit;

import org.extremecomponents.table.limit.LimitFactory;

import org.extremecomponents.table.limit.TableLimit;

import org.extremecomponents.table.limit.TableLimitFactory;

 

import com.sinovs.saler.model.Saler;

import com.sinovs.saler.service.SalerManager;

 

 

public final class SalerAction extends DispatchAction {

   

    public ActionForward search(ActionMapping mapping, ActionForm form,

           HttpServletRequest request, HttpServletResponse response)

           throws Exception {

      //从spring中得到的bean

 

       SalerManager mgr = (SalerManager) getBean( "salerManager" );

       /**

        * controller首先需要创建一个Limit

        */

       String tableId = "ec";

       Context context = new HttpServletRequestContext(request);

       LimitFactory limitFactory = new TableLimitFactory(context, tableId);

       Limit limit = new TableLimit(limitFactory);

       /**

        * 得到系统默认的页面显示大小

        */

       Preferences preferences = new TableProperties();

       preferences.init(null, TableModelUtils.getPreferencesLocation(context));

 

       String crdStr = tableId + "_" + TableConstants.CURRENT_ROWS_DISPLAYED;

       /**

        * 注意：此处取值时，如果后台配置文件里有配置，就从里面取，反之取15

        */

       int defaultRowsDisplayed = Integer.parseInt(preferences

              .getPreference(PreferencesConstants.TABLE_ROWS_DISPLAYED));

       if (request.getParameter(crdStr) != null) {

           defaultRowsDisplayed = Integer.parseInt(request

                  .getParameter(crdStr));

       }

 

       /**

        * limit需要得到所有的行来得到行的信息

        */

 

 

       int totalRows = mgr.getTotalSalers(limit);

 

       limit.setRowAttributes(totalRows, defaultRowsDisplayed);

       /**

        * 需要从services得到Collection数据

        */

       List list = mgr.getSalers(limit);

 

       /**

        * 将Collections和totalRow这些信息传送回JSP以便eXtremeTable知道如何显示这些信息

        */

       request.setAttribute("list", list);

       request.setAttribute("totalRows", new Integer(totalRows));

 

       return mapping.findForward("list");

    }

 

}

 SalerManagerImpl

/**

     * controller需要到第一条信息就是总行数。

     */

    private final static String salersQuery = "FROM Saler ";

 

    private final static String totalSalersQuery = "SELECT count(*) FROM Saler ";

 

    public int getTotalSalers(Limit limit) {

       String totalQuery = totalSalersQuery;

       String modTotalQuery = filterQuery(limit.getFilterSet(), totalQuery);

       int totalRows = dao.getTotalSalers(modTotalQuery);

       return totalRows;

    }

 

    public List getSalers(Limit limit) {

       String query = salersQuery;

       String modSalersQuery = filterQuery(limit.getFilterSet(), query);

       modSalersQuery = sortQuery(limit.getSort(), modSalersQuery);

       return dao.getSalers(modSalersQuery, limit);

 

    }

 

    private String filterQuery(FilterSet filterSet, String query) {

       if (!filterSet.isFiltered() || filterSet.isCleared()) {

           return query;

       }

 

       Filter filters[] = filterSet.getFilters();

       for (int i = 0; i < filters.length; i++) {

           Filter filter = filters[i];

           String property = filter.getProperty();

           String value = filter.getValue();

           query = filterQuery(query, property, value);

       }

 

       return query;

    }

 

    public String filterQuery(String query, String property, String value) {

       StringBuffer result = new StringBuffer(query);

 

       if (query.indexOf("WHERE") == -1) {

           result.append(" WHERE 1 = 1 "); // stub WHERE clause so can just

           // append AND clause

       }

 

       result.append(" AND " + property + " like '%" + value + "%'");

 

       return result.toString();

    }

 

    private String sortQuery(Sort sort, String query) {

       if (!sort.isSorted()) {

           return query;

       }

 

       String property = sort.getProperty();

       String sortOrder = sort.getSortOrder();

 

       return sortQuery(query, property, sortOrder);

    }

 

    public String sortQuery(String query, String property, String sortOrder) {

       StringBuffer result = new StringBuffer(query + " ORDER BY ");

 

       result.append(property + " " + sortOrder);

       return result.toString();

    }

 

SalerDaoHibernate

/**

     * 得到数据总行数

     */

    public int getTotalSalers(String query) {

       int totalRows = 0;

       List list = getHibernateTemplate().find(query);

       if (list.size() > 0) {

           totalRows = ((Integer) list.get(0)).intValue();

       }

       return totalRows;

    }

 

    /**

     * 根据HQL语句的到纪录列表

     */

    public List getSalers(String query, Limit limit) {

       Session session = this.getSession();

       Query q = session.createQuery(query);

       q.setFirstResult((limit.getPage() - 1)

              * limit.getCurrentRowsDisplayed());

       q.setMaxResults(limit.getCurrentRowsDisplayed());

       return q.list();

    }

页面  

<ec:table

       items="salerList"

       var="saler"

       action="${pageContext.request.contextPath}/saler.do?method=search"

       imagePath="${pageContext.request.contextPath}/images/table/*.gif"

       title="saler.list.title"

       width="80%"

       retrieveRowsCallback="org.extremecomponents.table.callback.LimitCallback" filterRowsCallback="org.extremecomponents.table.callback.LimitCallback" sortRowsCallback="org.extremecomponents.table.callback.LimitCallback"

       rowsDisplayed="16"

       >

       <ec:exportXls fileName="presidents.xls" tooltip="Export Excel"/>

       <ec:row  highlightRow="true">

           <ec:column property="rowcount" cell="rowCount" sortable="false" filterable="false" title="ID" width="60"/>

           <ec:column property="salerName" title="saler.salername"/>

           <ec:column property="salerPhone" title="saler.salerphone"/>

           <ec:column property="id" title="saler.button.mod" filterable="false" viewsAllowed="html" sortable="false">

           <a href="${pageContext.request.contextPath}/saler.do?method=edit&amp;id=${saler.id}"><bean:message key="saler.button.mod"/></a>

           </ec:column>

           <ec:column property="id" title="saler.button.delete" filterable="false" sortable="false" viewsAllowed="html">

           <a href="${pageContext.request.contextPath}/saler.do?method=delete&amp;id=${saler.id}"><bean:message key="saler.button.delete"/></a>

           </ec:column>

       </ec:row>

    </ec:table>