根据sql查询实现zk Listbox翻页显示数据

     最近一直在研究如何封装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" border="normal" 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);
	}
}