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