本人用SPRING 框架自己写DAO实现LIMIT功能
DAO代码如下:
package org.bcring.dao.table.hibernate;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import org.apache.commons.beanutils.LazyDynaMap;
import org.bcring.dao.table.TableDAO;
import org.extremecomponents.table.limit.Filter;
import org.extremecomponents.table.limit.FilterSet;
import org.extremecomponents.table.limit.Sort;
import org.springframework.jdbc.core.ResultReader;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
public class TableDAOHibernate extends JdbcDaoSupport implements
TableDAO {
public int getTotalRows(String sql) {
StringBuffer totalSQL = new StringBuffer(" SELECT count(*) FROM ( ");
totalSQL.append(sql);
totalSQL.append(" ) totalTable ");
logger.info("JDBC :" + totalSQL.toString());
int totalNumber = getJdbcTemplate().queryForInt(totalSQL.toString());
return totalNumber;
}
public Collection getCollectionData(String sql, int page, int
pageSize,
Sort sort, FilterSet filterSet) {
// 计算数值??
page = page != 1 ? page - 1 : 0;
int startNumber = (page * pageSize) + 1;
int endNumber = startNumber + pageSize;
// 得到SQL
StringBuffer strSql = new StringBuffer();
strSql.append(" SELECT * FROM (SELECT ROWNUM RN, ECTABLE.* ");
strSql.append(" FROM ( " + sql + " ) ECTABLE ");
strSql.append(" WHERE ROWNUM <" + String.valueOf(endNumber));
strSql.append(" MINUS SELECT ROWNUM RN, ECTABLE.* ");
strSql.append(" FROM (" + sql + ") ECTABLE ");
strSql.append(" WHERE ROWNUM <" + String.valueOf(startNumber));
strSql.append(" ) ECTABLE ");
strSql.append(" WHERE 1 = 1 ");
if (filterSet != null) {
// 过滤器SQL
Filter[] filters = filterSet.getFilters();
for (int i = 0; i < filters.length; i++) {
strSql.append("AND ECTABLE." + filters[i].getProperty()
+ " LIKE '%" + filters[i].getValue() + "%'");
}
}
// 排序SQL
if (sort.getProperty() != null) {
strSql.append(" ORDER BY ECTABLE." + sort.getProperty() + " "
+ sort.getSortOrder());
}
logger.info("JDBC :" + strSql.toString());
return getJdbcTemplate().query(strSql.toString(), new ResultReader()
{
List results = new ArrayList();
public List getResults() {
return results;
}
public void processRow(ResultSet rs) throws SQLException {
ResultSetMetaData rdm = rs.getMetaData();
int conCount = rdm.getColumnCount();
LazyDynaMap dynaBean1 = new LazyDynaMap();
for (int i = 1; i <= conCount; i++) {
dynaBean1.set(rdm.getColumnName(i).toUpperCase(), rs
.getString(i));
}
results.add(dynaBean1);
}
});
}
}
SERIVER
中代码省略,基本就是完成SQL的拼装回传给DAO。返回结果集合。
ACTION中:
public ActionForward init(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response)
throws Exception {
// 初始化Context和Limit
Context context = new HttpServletRequestContext(request);
// 指定页面EC的ID
LimitFactory limitFactory = new TableLimitFactory(context, "ec");
Limit limit = new TableLimit(limitFactory);
// 得到页号
int pageNumer = limit.getPage();
// 得到EC页面上选择的PAGESIZE大小,
//上面指定EC的ID为ec
那么页面的选择页面大小就可以用ecid_+rd来组合
String selectPageSize = request.getParameter("ec_rd");
int pageSize = new Integer(selectPageSize != null ? selectPageSize
: 15 + "").intValue();
//得到分页的Manger
TableManger tableManger = (TableManger) getBean("tableManger");
int totalRows = tableManger.getTotalRows(" SELECT * FROM YH_YH ");
limit.setRowAttributes(totalRows, 10);
//调用方法得到集合
Collection c = tableManger.getCollectionData("", pageNumer, pageSize,
limit.getSort(), limit.getFilterSet());
request.setAttribute("page", c);
request.setAttribute("ec_totalRows", new Integer(totalRows + ""));
return mapping.findForward("login");
}
JSP页面:
<ec:table
items="page"
action="${pageContext.request.contextPath}/login.do?method=init"
imagePath="${pageContext.request.contextPath}/images/table/*.gif"
title="Presidents"
width="100%"
method="POST"
locale="zh_CN"
retrieveRowsCallback="limit"
filterRowsCallback="limit"
sortRowsCallback="limit"
view="limit"
>
<ec:exportXls fileName="presidents.xls" tooltip="Export Excel"/>
<ec:exportPdf fileName="presidents.pdf" tooltip="Export PDF"
headerColor="blue" headerBackgroundColor="red"
headerTitle="Presidents"/>
<ec:exportCsv fileName="presidents.txt" tooltip="Export CSV"
delimiter="|"/>
<ec:row>
<ec:column property="YHID" title="用户ID"/>
<ec:column property="HH" title="户号"/>
<ec:column property="HM" title="户名"/>
<ec:column property="dwdm" title="单位代码"/>
</ec:row>
</ec:table>
WEB-INF :
<filter>
<filter-name>encodingFilter</filter-name>
<filter-class>
org.springframework.web.filter.CharacterEncodingFilter
</filter-class>
<init-param>
<param-name>encoding</param-name>
<param-value>GBK</param-value>
</init-param>
<init-param>
<param-name>forceEncoding</param-name>
<param-value>true</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>encodingFilter</filter-name>
<url-pattern>*.do</url-pattern>
</filter-mapping>
<filter-mapping>
<filter-name>encodingFilter</filter-name>
<url-pattern>*.jsp</url-pattern>
</filter-mapping>
基本实现了限制了取出来记录的条数,选第几页就出第几页的数据。而且可以在当页进行排序和过滤。问题是如果过滤的条件是中文,就会乱码。还有就是如果这样做的话。想要导出全部记录怎么办???现在是显示多少就导出多少。。。。
请各位大侠指点。谢谢哦。。。。。。。。。
源文档 <http://groups.google.com/group/eXtremeComponents_CN/browse_thread/thread/5c6f5167863a5bc8>