`

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

SQLzkMySQLSpringHibernate 
阅读更多

    最近一直在研究如何封装zk的Listbox，实现简单的数据展现。做了个简单的demo。

 

     我没有使用hibernate，而是自己写sql去查询。直接用spring jdbcTemplate就挺好的。jdbcTemplate.query(sql, RowMapper);方法能够传回Map，所以，封装如下方法：

Java代码 复制代码
  1. public List<Object> queryForListExp(String sql, Class<?> entityClass){   
  2.         if (logger.isDebugEnabled()){   
  3.             logger.debug("queryForListExp sql =" + sql + ",  entityClass="+ entityClass);   
  4.         }   
  5.         return super.query(sql, new RowMapperImpl(entityClass)) ;   
  6.     }  
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将查询的结果集中的列按照列名，映射到实体类的属性上，实现如下：

Java代码 复制代码
  1. protected class RowMapperImpl implements RowMapper {   
  2.         private final Class<?> clazz;   
  3.            
  4.         public RowMapperImpl(Class<?> clazz) {   
  5.             if (logger.isDebugEnabled()){   
  6.                 logger.debug("Initial RowMap "+clazz);   
  7.             }   
  8.             this.clazz = clazz;   
  9.         }   
  10.   
  11.         public Object mapRow(ResultSet rs, int Index) throws SQLException {   
  12.             Object newEntity = null;   
  13.             try {   
  14.                 newEntity = clazz.newInstance();   
  15.                 // logger.info("newEntity="+newEntity);   
  16.             } catch (Exception e1) {   
  17.                 if (logger.isErrorEnabled()){   
  18.                     logger.error("Fetching resultset to Entity"+clazz);   
  19.                 }   
  20.                 throw new RuntimeException("方法映射时出现异常:"+e1.toString(), e1);   
  21.             }   
  22.   
  23.             // 根据map将取出来   
  24.             ///Map<String, Field> map = getColumnMap(clazz);   
  25.             Map<String, Field> map = getColumnMap(clazz, rs);   
  26.             Set<String> columns = map.keySet();   
  27.             int mycolIdx=0;   
  28.                
  29.             for (String column : columns) {   
  30.                 Field field = map.get(column);   
  31.                 try {   
  32.                     mycolIdx = rs.findColumn(column);   
  33.                     setFieldValue(newEntity, field, rs.getObject(column));   
  34.                 } catch (SQLException e){   
  35.                     throw new SQLException("结果集合中列 "+ column+ " 不存在，或者有异常", e);   
  36.                 } catch (Exception e) {   
  37.                     if (logger.isErrorEnabled()){   
  38.                         logger.error("Fetching resultset to Entity"+clazz);   
  39.                     }   
  40.                     e.printStackTrace();   
  41.                     throw new RuntimeException("方法映射时出现异常2:"+e.toString(), e);   
  42.                 }   
  43.             }   
  44.             return newEntity;   
  45.         }   
  46.     }  
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的指定行查询。

Sql代码 复制代码
  1. select * from (   
  2. select row_number() over() as rnum,g.* from T_City as g ) as a    
  3. where rnum between 100 and 300   
select * from (
select row_number() over() as rnum,g.* from T_City as g ) as a 
where rnum between 100 and 300

 

DAO的实现如下：  

 

Java代码 复制代码
  1. private static String SQL_COUNT="select count(*) from T_City where 1=1";   
  2.   
  3. private static String SQL_BY_ROW="select * from (";   
  4.   
  5. private static String sql="select row_number() over() as rnum,c.* from T_City as c";   
  6.   
  7.   
  8. public List<TCity> findByOption(Integer noFrom, Integer noTo) {   
  9.     String mysql=SQL_BY_ROW+sql+" ) as a where rnum between "+noFrom+" and "+noTo;   
  10.        
  11.     return super.queryForList(mysql);   
  12. }   
  13.   
  14.   
  15.   
  16. public Integer findCount(String option) {   
  17.     return super.getDtjdbcTemplate().queryForInt(SQL_COUNT);   
  18. }  
	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：

 

Xml代码 复制代码
  1. <zk xmlns="http://www.zkoss.org/2005/zul"  
  2.     xmlns:h="http://www.w3.org/1999/xhtml"  
  3.     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
  4.     xsi:schemaLocation="http://www.zkoss.org/2005/zul http://www.zkoss.org/2005/zul/zul.xsd">  
  5.     <window title="list" border="normal" id="win_AutoListbox" use="test.component.WinAutoListbox" width="100%" height="100%">  
  6.         <vbox>  
  7.             <div id="div_AutoList" align="left" width="600px" height="300px" />  
  8.             <hbox>  
  9.                 <button label="Show" id="btn_Show" />  
  10.             </hbox>  
  11.         </vbox>  
  12.     </window>  
  13. </zk>   
<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的总行数。

Java代码 复制代码
  1. public class WinAutoListbox extends Window implements AfterCompose{   
  2.   
  3.   
  4.     static Logger logger = Logger.getLogger(WinAutoListbox.class);   
  5.   
  6.     private Button btn_Show;   
  7.        
  8.     private Div div_AutoList;   
  9.        
  10.     private AutoListbox<TCity> autolistbox;   
  11.        
  12.     private CityService cityservice;   
  13.   
  14.     @Override  
  15.     public void afterCompose() {   
  16.         Components.wireVariables(this, this);   
  17.         Components.addForwards(this, this);   
  18.         cityservice = (CityService) BeanHelper.getBean("base_CityServiceImpl");   
  19.         autolistbox=new AutoListbox(TCity.class,div_AutoList);   
  20.     }   
  21.        
  22.     public void onClick$btn_Show(){   
  23.         Integer rowCount=cityservice.findCount("");   
  24.         Method method;   
  25.         try {   
  26.             method = cityservice.getClass().getMethod("findByRow",new Class[]{Integer.class, Integer.class});   
  27.             autolistbox.beginQuery(rowCount,cityservice,method);   
  28.         } catch (SecurityException e) {   
  29.             // TODO Auto-generated catch block   
  30.             e.printStackTrace();   
  31.         } catch (NoSuchMethodException e) {   
  32.             // TODO Auto-generated catch block   
  33.             e.printStackTrace();   
  34.         }   
  35.     }   
  36.       
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事件，实现翻页查询，以下只贴出具体实现部分代码：

 

Java代码 复制代码
  1. public class AutoListbox<T> {   
  2.   
  3.     private final static String HEADER_TO_COL_SPLIT="_";   
  4.        
  5.     private int PAGE_SIZE=10;   
  6.        
  7.     /*databean.class*/  
  8.     private Class<T> _clazz;   
  9.        
  10.     /*_clazz.Fields*/  
  11.     private Map<String, Field> _beanFieldMap;   
  12.   
  13.     /* 需要显示的结果集 */  
  14.     private List<T> _lstrs;   
  15.        
  16.     private Listbox _listbox;   
  17.        
  18.     private Paging _paging;   
  19.        
  20.     private Listhead _listhead;   
  21.        
  22.        
  23.        
  24.     /*执行查询的类和方法*/  
  25.     private Object objQueryClazz;   
  26.     private Method objQueryMethod;   
  27.     private Integer _totalrows=0;   
  28.        
  29.        
  30.     public AutoListbox(Class<T> clazz, Component comp){   
  31.         super();   
  32.         this._clazz=clazz;   
  33.         this._beanFieldMap=getBeanFields(this._clazz);   
  34.         this._listbox=new Listbox();   
  35.         this._listhead=new Listhead();   
  36.         this._paging=new Paging();                          ///new Paging();   
  37.         this._btnColumnsEditor=new Button();   
  38.            
  39.         this._listbox.setId("MyAutoListbox");   
  40.         this._listbox.setWidth("99%");   
  41.         this._listbox.setHeight("75%");   
  42.         this._listbox.setVisible(true);   
  43.   
  44.         this._listhead.setSizable(true);   
  45.         addListheder(null);   
  46.         this._listhead.setParent(this._listbox);   
  47.            
  48.         this._paging.setPageSize(PAGE_SIZE);   
  49.         this._paging.setTotalSize(this._totalrows);   
  50.         this._paging.setDetailed(true);                       //显示记录数   
  51.         this._paging.setWidth("99%");   
  52.         this._listbox.setPaginal(this._paging);   
  53.         this._paging.setDetailed(true);   
  54.         this._paging.setParent(this._listbox);*/   
  55.         addPagingListener();   
  56.            
  57.         this._btnColumnsEditor.setLabel("ColumnsEditor");   
  58.         this._btnColumnsEditor.setVisible(true);   
  59.            
  60.         this._listbox.setParent(comp);   
  61.         this._paging.setParent(comp);   
  62.     }   
  63.         
  64.        
  65.   
  66.     private void addPagingListener(){   
  67.            
  68.         this._paging.addEventListener("onPaging", new EventListener(){   
  69.                
  70.             @Override  
  71.             public void onEvent(Event event) throws Exception {   
  72.                 PagingEvent pevt=(PagingEvent) event;   
  73.                 int pagesize=PAGE_SIZE;   
  74.                 int pgno =pevt.getActivePage();;   
  75.                 int ofs = pgno * pagesize;   
  76.                 logger.debug(" pagesize="+pagesize);   
  77.                 logger.debug(" pgno="+pgno);   
  78.                 logger.debug(" ofs="+ofs);   
  79.                 redraw(ofs+1,ofs+pagesize);   
  80.             }   
  81.                    
  82.             });   
  83.     }   
  84.        
  85.        
  86.     private void redraw(Integer noFrom,Integer onTo){   
  87.         //int rowCount=cityservice.findCount("");   
  88.         //List<TCity> list=cityservice.findByOption(1, 20);   
  89.         //method.invoke(a, new Object[]{"world", new Integer(5)});   
  90.         clearListbox(this._listbox);   
  91.         //this._listbox.setWidth("99%");   
  92.         //this._paging.setWidth("100%");   
  93.         this._paging.setAutohide(false);   
  94.         this._paging.setVisible(true);   
  95.         try {   
  96.             this._paging.setTotalSize(this._totalrows);   
  97.             List<T> list = (List<T>) objQueryMethod.invoke(objQueryClazz, new Object[]{noFrom,onTo});   
  98.             loadList(list);   
  99.         } catch (Exception e) {   
  100.             e.printStackTrace();   
  101.         }   
  102.     }   
  103.        
  104.     private void loadList(List<T> list){   
  105.         //读每行记录，构造Listitem，构造每列Listcell   
  106.     }   
  107.   
  108.     public void beginQuery(Integer rowCount, Object objQueryClazz,   
  109.          Method objQueryMethod) {   
  110.           this._totalrows=rowCount;   
  111.           this._paging.setTotalSize(this._totalrows);   
  112.           //this._paging.setTotalSize(rowCount);   
  113.           this.objQueryClazz=objQueryClazz;   
  114.           this.objQueryMethod=objQueryMethod;   
  115.           redraw(0, this.PAGE_SIZE);   
  116.     }   
  117. }  
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);
	}
}

 

 

分享到：
| html转移符
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

Magicbox
Global site tag (gtag.js) - Google Analytics