最近一直在研究如何封装zk的Listbox,实现简单的数据展现。做了个简单的demo。
我没有使用hibernate,而是自己写sql去查询。直接用spring jdbcTemplate就挺好的。jdbcTemplate.query(sql, RowMapper);方法能够传回Map,所以,封装如下方法:
public List<Object> queryForListExp(String sql, Class<?> entityClass){
if (logger.isDebugEnabled()){
logger.debug("queryForListExp sql =" + sql + ", entityClass="+ entityClass);
}
return super.query(sql, new RowMapperImpl(entityClass)) ;
}
直接返回实体类的List结果集。RowMapperImpl将查询的结果集中的列按照列名,映射到实体类的属性上,实现如下:
protected class RowMapperImpl implements RowMapper {
private final Class<?> clazz;
public RowMapperImpl(Class<?> clazz) {
if (logger.isDebugEnabled()){
logger.debug("Initial RowMap "+clazz);
}
this.clazz = clazz;
}
public Object mapRow(ResultSet rs, int Index) throws SQLException {
Object newEntity = null;
try {
newEntity = clazz.newInstance();
// logger.info("newEntity="+newEntity);
} catch (Exception e1) {
if (logger.isErrorEnabled()){
logger.error("Fetching resultset to Entity"+clazz);
}
throw new RuntimeException("方法映射时出现异常:"+e1.toString(), e1);
}
// 根据map将取出来
///Map<String, Field> map = getColumnMap(clazz);
Map<String, Field> map = getColumnMap(clazz, rs);
Set<String> columns = map.keySet();
int mycolIdx=0;
for (String column : columns) {
Field field = map.get(column);
try {
mycolIdx = rs.findColumn(column);
setFieldValue(newEntity, field, rs.getObject(column));
} catch (SQLException e){
throw new SQLException("结果集合中列 "+ column+ " 不存在,或者有异常", e);
} catch (Exception e) {
if (logger.isErrorEnabled()){
logger.error("Fetching resultset to Entity"+clazz);
}
e.printStackTrace();
throw new RuntimeException("方法映射时出现异常2:"+e.toString(), e);
}
}
return newEntity;
}
}
现在,关心的就是sql如何写的问题了,sql中支持row_number() over()查询出行号,所以,我要实现分页功能,首先就是要实现按照resultset的指定行查询。
select * from (
select row_number() over() as rnum,g.* from T_City as g ) as a
where rnum between 100 and 300
DAO的实现如下:
private static String SQL_COUNT="select count(*) from T_City where 1=1";
private static String SQL_BY_ROW="select * from (";
private static String sql="select row_number() over() as rnum,c.* from T_City as c";
public List<TCity> findByOption(Integer noFrom, Integer noTo) {
String mysql=SQL_BY_ROW+sql+" ) as a where rnum between "+noFrom+" and "+noTo;
return super.queryForList(mysql);
}
public Integer findCount(String option) {
return super.getDtjdbcTemplate().queryForInt(SQL_COUNT);
}
下面,我们看界面实现部分,zul页面很简单,仅仅划了一个div作为容器来装载Listbox:
<zk xmlns="http://www.zkoss.org/2005/zul"
xmlns:h="http://www.w3.org/1999/xhtml"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.zkoss.org/2005/zul http://www.zkoss.org/2005/zul/zul.xsd">
<window title="list" id="win_AutoListbox" use="test.component.WinAutoListbox" width="100%" height="100%">
<vbox>
<div id="div_AutoList" align="left" width="600px" height="300px" />
<hbox>
<button label="Show" id="btn_Show" />
</hbox>
</vbox>
</window>
</zk>
WinAutoListbox.java:(用到AfterCompose)
查询前先执行Integer rowCount=cityservice.findCount("");,获取本次查询resultset的总行数。
public class WinAutoListbox extends Window implements AfterCompose{
static Logger logger = Logger.getLogger(WinAutoListbox.class);
private Button btn_Show;
private Div div_AutoList;
private AutoListbox<TCity> autolistbox;
private CityService cityservice;
@Override
public void afterCompose() {
Components.wireVariables(this, this);
Components.addForwards(this, this);
cityservice = (CityService) BeanHelper.getBean("base_CityServiceImpl");
autolistbox=new AutoListbox(TCity.class,div_AutoList);
}
public void onClick$btn_Show(){
Integer rowCount=cityservice.findCount("");
Method method;
try {
method = cityservice.getClass().getMethod("findByRow",new Class[]{Integer.class, Integer.class});
autolistbox.beginQuery(rowCount,cityservice,method);
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (NoSuchMethodException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
AutoListbox就是我对Listbox的封装,包括一个Listbox、一个Paging,通过捕获Paging的onPaging事件,实现翻页查询,以下只贴出具体实现部分代码:
public class AutoListbox<T> {
private final static String HEADER_TO_COL_SPLIT="_";
private int PAGE_SIZE=10;
/*databean.class*/
private Class<T> _clazz;
/*_clazz.Fields*/
private Map<String, Field> _beanFieldMap;
/* 需要显示的结果集 */
private List<T> _lstrs;
private Listbox _listbox;
private Paging _paging;
private Listhead _listhead;
/*执行查询的类和方法*/
private Object objQueryClazz;
private Method objQueryMethod;
private Integer _totalrows=0;
public AutoListbox(Class<T> clazz, Component comp){
super();
this._clazz=clazz;
this._beanFieldMap=getBeanFields(this._clazz);
this._listbox=new Listbox();
this._listhead=new Listhead();
this._paging=new Paging(); ///new Paging();
this._btnColumnsEditor=new Button();
this._listbox.setId("MyAutoListbox");
this._listbox.setWidth("99%");
this._listbox.setHeight("75%");
this._listbox.setVisible(true);
this._listhead.setSizable(true);
addListheder(null);
this._listhead.setParent(this._listbox);
this._paging.setPageSize(PAGE_SIZE);
this._paging.setTotalSize(this._totalrows);
this._paging.setDetailed(true); //显示记录数
this._paging.setWidth("99%");
this._listbox.setPaginal(this._paging);
this._paging.setDetailed(true);
this._paging.setParent(this._listbox);*/
addPagingListener();
this._btnColumnsEditor.setLabel("ColumnsEditor");
this._btnColumnsEditor.setVisible(true);
this._listbox.setParent(comp);
this._paging.setParent(comp);
}
private void addPagingListener(){
this._paging.addEventListener("onPaging", new EventListener(){
@Override
public void onEvent(Event event) throws Exception {
PagingEvent pevt=(PagingEvent) event;
int pagesize=PAGE_SIZE;
int pgno =pevt.getActivePage();;
int ofs = pgno * pagesize;
logger.debug(" pagesize="+pagesize);
logger.debug(" pgno="+pgno);
logger.debug(" ofs="+ofs);
redraw(ofs+1,ofs+pagesize);
}
});
}
private void redraw(Integer noFrom,Integer onTo){
//int rowCount=cityservice.findCount("");
//List<TCity> list=cityservice.findByOption(1, 20);
//method.invoke(a, new Object[]{"world", new Integer(5)});
clearListbox(this._listbox);
//this._listbox.setWidth("99%");
//this._paging.setWidth("100%");
this._paging.setAutohide(false);
this._paging.setVisible(true);
try {
this._paging.setTotalSize(this._totalrows);
List<T> list = (List<T>) objQueryMethod.invoke(objQueryClazz, new Object[]{noFrom,onTo});
loadList(list);
} catch (Exception e) {
e.printStackTrace();
}
}
private void loadList(List<T> list){
//读每行记录,构造Listitem,构造每列Listcell
}
public void beginQuery(Integer rowCount, Object objQueryClazz,
Method objQueryMethod) {
this._totalrows=rowCount;
this._paging.setTotalSize(this._totalrows);
//this._paging.setTotalSize(rowCount);
this.objQueryClazz=objQueryClazz;
this.objQueryMethod=objQueryMethod;
redraw(0, this.PAGE_SIZE);
}
}