SSH通用查询DAO(2)
前一篇帖子我采用QBC和QBE实现动态分页查询，本贴用Hibernate的Query接口实现动态分页查询。
通用DAO代码如下：
package com.aostarit.erp.dao;import java.util.Iterator;import java.util.List;import java.util.Map;import org.hibernate.Query;import org.springframework.orm.hibernate3.support.HibernateDaoSupport;public class ExpertManagerDao extends HibernateDaoSupport{/** * @function 查询所有记录数 * @param hql * hql查询语句 * @return 返回记录数的集合 */public List getAllInfo(String hql){List list = this.getHibernateTemplate().find(hql);return list;}/** * @function 查询单个记录数 * @param hql * hql查询语句 * @return 将记录封装成Object */public Object getBeanInfo(String hql){Query query = this.getSession().createQuery(hql);List list = query.list();return list.get(0);}/** * @function 根据查询条件查询记录数的个数 * @param hql * hql查询语句 * @return 数据库中满足查询条件的数据的条数 */public int getTotalCount(String hql){List result = this.getHibernateTemplate().find(hql);Integer i = (Integer) result.get(0);return i;}/** * @function 根据查询条件查询记录数的个数 * @param hql * hql查询语句 * @param map * 用map封装查询条件 * @return 数据库中满足查询条件的数据的条数 */public int getTotalCount(String hql, Map map){try{Query query = this.getSession().createQuery(hql);if (map != null){Iterator it = map.keySet().iterator();while (it.hasNext()){Object key = it.next();query.setParameter(key.toString(), map.get(key));}}Integer i = (Integer) query.list().get(0);return i;} catch (RuntimeException re){throw re;}}/** * @function 分页显示符合所有的记录数，将查询结果封装为Pager * @param pageNo * 当前页数 * @param pageSize * 每页显示的条数 * @return 查询结果Pager */public List findPageAll(int pageNo, int pageSize, String hql){try{Query query = this.getSession().createQuery(hql);query.setFirstResult((pageNo - 1) * pageSize);query.setMaxResults(pageSize);List result = query.list();return result;} catch (RuntimeException re){throw re;}}/** * @function 分页显示符合所有的记录数，将查询结果封装为Pager * @param pageNo * 当前页数 * @param pageSize * 每页显示的条数 * @param instance * 将查询条件封装为专家Bean * @return 查询结果Pager */public List findPageByQuery(int pageNo, int pageSize, String hql,Map map){List result = null;try{Query query = this.getSession().createQuery(hql);Iterator it = map.keySet().iterator();while (it.hasNext()){Object key = it.next();query.setParameter(key.toString(), map.get(key));}query.setFirstResult((pageNo - 1) * pageSize);query.setMaxResults(pageSize);result = query.list();} catch (RuntimeException re){throw re;}return result;}}package com.aostarit.erp.bo;import java.util.HashMap;import java.util.List;import java.util.Map;import com.aostarit.erp.dao.ExpertManagerDao;import com.aostarit.erp.po.EicExpert;import com.aostarit.erp.util.Pager;public class ExpertBo{/** * 依靠Spring的依赖注入，注入专家的Dao */private ExpertManagerDao expertManagerDao;public ExpertManagerDao getExpertManagerDao(){return expertManagerDao;}public void setExpertManagerDao(ExpertManagerDao expertManagerDao){this.expertManagerDao = expertManagerDao;}/** * @function 根据传递过来的列名，在数据库中查询该列不重复的记录 * @param column * 传递过来的列名 * @return 该列不重复的记录 */public List getColumn(String column){StringBuffer hql = new StringBuffer();hql.append("select distinct(").append(column).append(") from EicExpert where ").append(column).append(" is not null");return expertManagerDao.getAllInfo(hql.toString());}/** * @function 根据传递过来的分页的参数，分页查找数据库中的记录，前台用下拉列表显示 * @param pageNo * 当前的页码 * @param pageSize * 每页显示的记录数 * @return 符合条件的记录数 */public Pager getAllInfo(int pageNo, int pageSize){StringBuffer hql = new StringBuffer();hql.append("from EicExpert ");List result = expertManagerDao.findPageAll(pageNo, pageSize, hql.toString());int rowCount = expertManagerDao.getTotalCount("select count(expertid) from EicExpert ");Pager pager = new Pager(pageSize, pageNo, rowCount, result);return pager;}/** * @function 将传递过来的参数封装成专家Bean，分页查询符合条件的记录 * @param pageNo * 当前的页码 * @param pageSize * 每页显示的记录数 * @param expertName * 专家的名称 * @param expertSpecialty * 专家的专业类别 * @param post * 专家的行政职位 * @return 将符合条件的记录数以及页码信息封装成PagerBean返回 */public Pager getInfoByQuery(int pageNo, int pageSize, String expertName,String expertSpecialty, String post){StringBuffer hql = new StringBuffer();hql.append("select count(expertid) from EicExpert where 1=1 ");Map map = new HashMap();if (expertName != null && expertName.length() > 0){map.put("expertname", "%" + expertName + "%");hql.append("and expertname like :expertname ");}if (expertSpecialty != null && expertSpecialty.length() > 0){map.put("expertspecialty", expertSpecialty);hql.append("and expertspecialty like :expertspecialty ");}if (post != null && post.length() > 0){map.put("post", post);hql.append("and post like :post ");}String queryHql = hql.substring(22);List result = expertManagerDao.findPageByQuery(pageNo, pageSize,queryHql, map);int rowCount = expertManagerDao.getTotalCount(hql.toString(), map);Pager pager = new Pager(pageSize, pageNo, rowCount, result);return pager;}/** * @function 根据所属单位的Id,分页查询相关信息 * @param pageNo * 当前页码 * @param pageSize * 每页显示的记录数 * @param deptId * 所属单位的Id * @return 将符合条件的记录数以及页码信息封装成PagerBean返回 */public Pager getInfoByDept(int pageNo, int pageSize, String deptId){if (deptId != null && deptId.length() > 0){StringBuffer hql = new StringBuffer();hql.append("select count(expertid) from EicExpert e where e.aorganization.orgCode = :orgCode ");Map map = new HashMap();map.put("orgCode", deptId);List result = expertManagerDao.findPageByQuery(pageNo, pageSize,hql.substring(22).toString(), map);int rowCount = expertManagerDao.getTotalCount(hql.toString(), map);Pager pager = new Pager(pageSize, pageNo, rowCount, result);return pager;} else{return null;}}/** * @function 根据专家的主键Id，查询专家的详细信息 * @param expertId * 专家的主键 * @return 将查询结果封装成专家Bean返回 */public EicExpert getBeanInfo(String expertId){StringBuffer hql = new StringBuffer();hql.append("from EicExpert where expertid = ").append(expertId);return (EicExpert) expertManagerDao.getBeanInfo(hql.toString());}}