Hibernate分页查询

package org.heardy.dao.impl;import java.util.List;import javax.annotation.Resource;import org.heardy.dao.GenericDao;import org.hibernate.Criteria;import org.hibernate.SessionFactory;import org.hibernate.criterion.CriteriaSpecification;import org.hibernate.criterion.DetachedCriteria;import org.hibernate.criterion.Projections;import org.springframework.stereotype.Repository;import org.springframework.transaction.annotation.Transactional;@Repository("genericDao")/* * 只在单元测试时使用 */@Transactionalpublic class GenericDaoImpl implements GenericDao {@ResourceSessionFactory sessionFactory;public Object uniqueResult(DetachedCriteria dc) {Criteria criteria = dc.getExecutableCriteria(sessionFactory.getCurrentSession());return criteria.uniqueResult();}public List<?> dynamicQuery(DetachedCriteria dc) {Criteria criteria = dc.getExecutableCriteria(sessionFactory.getCurrentSession());return criteria.list();}// criteria.setProjection(Projections.projectionList().add(Projections.rowCount()));// //查询总行数// criteria.add(Projections.avg(propertyName)); //求平构值// criteria.add(Projections.min(propertyName)); //最小值// criteria.add(Projections.max(propertyName)); //最大值// criteria.add(Projections.sum(propertyName)); //求和// criteria.add(Projections.groupProperty(propertyName)); //分组public Integer sum(DetachedCriteria dc, String propertyName) {Criteria criteria = dc.getExecutableCriteria(sessionFactory.getCurrentSession());Integer sum = (Integer) criteria.setProjection(Projections.sum(propertyName)).uniqueResult();criteria.setProjection(null);criteria.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);// 如果查询的对象有外键，需要添加此语句return sum;}public Integer count(DetachedCriteria dc) {Criteria criteria = dc.getExecutableCriteria(sessionFactory.getCurrentSession());Integer totalCount = (Integer) criteria.setProjection(Projections.rowCount()).uniqueResult();criteria.setProjection(null);criteria.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);// 如果查询的对象有外键，需要添加此语句return totalCount;}public List<?> pageQuery(DetachedCriteria dc, int limit, int start) {Criteria criteria = dc.getExecutableCriteria(sessionFactory.getCurrentSession());criteria.setFirstResult(start);criteria.setMaxResults(limit);return criteria.list();}}

package org.heardy.service.impl;import java.util.ArrayList;import java.util.List;import javax.annotation.Resource;import org.heardy.bean.PageBean;import org.heardy.dao.GenericDao;import org.heardy.service.GenericService;import org.heardy.tool.TypeChange;import org.hibernate.criterion.DetachedCriteria;import org.springframework.transaction.annotation.Propagation;import org.springframework.transaction.annotation.Transactional;@Transactionalpublic abstract class GenericServiceImpl implements GenericService {@ResourceGenericDao genericDao;@Transactional(propagation = Propagation.NOT_SUPPORTED, readOnly = true)public PageBean findPageList(DetachedCriteria dc, PageBean page) {int count = TypeChange.objectToInt(genericDao.count(dc));@SuppressWarnings("rawtypes")List<?> purchases = new ArrayList();if (page.getLimit() != 0) {purchases = genericDao.pageQuery(dc, page.getLimit(),page.getStart());}page.setResult(count, purchases);return page;}public Integer getCount(DetachedCriteria dc) {return TypeChange.objectToInt(genericDao.count(dc));}public List<?> findAllList(DetachedCriteria dc) {return genericDao.dynamicQuery(dc);}public Object findBean(DetachedCriteria dc) {return genericDao.uniqueResult(dc);}}

在hibernate中，在查询总数时，会使用如下方法;
  public Integer getCount(final DetachedCriteria detachedCriteria) {
         return (Integer) getHibernateTemplate().execute(new HibernateCallback() {


             @Override
             public Object doInHibernate(Session session)
                     throws HibernateException, SQLException {
                 Criteria criteria = detachedCriteria.getExecutableCriteria(session);


                 int totalCount = (Integer) criteria.setProjection(
                         Projections.rowCount()).uniqueResult();


                 criteria.setProjection(null);
//
                 criteria.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);
                 return totalCount;
             }
         }, true);
     }


有时在int totalCount = (Integer) criteria.setProjection(
                         Projections.rowCount()).uniqueResult();这一句会报NullPointException。


原因是这句代码之前出现了criteria.addOrder(Order.desc("sc.createTime"));这样的排序语句。


把排序语句放到总数查询的后面即可。有时候你注释了缓存也仍然会影响的，需要把注释的语句也删掉。 




 




--------------------------------通用的记录查询总数-------------------------------------------
protected <T> int countQueryResult(Criteria c) {
CriteriaImpl impl = (CriteriaImpl) c;


// 先把Projection、OrderBy、ResultTransformer取出来,清空三者后再执行Count操作
Projection projection = impl.getProjection();


ResultTransformer transformer = impl.getResultTransformer();
List<CriteriaImpl.OrderEntry> orderEntries;
try {
orderEntries = (List) BeanUtils.forceGetProperty(impl, "orderEntries");
BeanUtils.forceSetProperty(impl, "orderEntries", new ArrayList());
} catch (Exception e) {
throw new InternalError(" Runtime Exception impossibility throw ");
}


// 执行Count查询
long totalCount = (Long) c.setProjection(Projections.rowCount()).uniqueResult();


// 将之前的Projection和OrderBy条件重新设回去
c.setProjection(projection);


if (projection == null) {
c.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);
}


if (transformer != null) {
c.setResultTransformer(transformer);
}


try {
BeanUtils.forceSetProperty(impl, "orderEntries", orderEntries);
} catch (Exception e) {
throw new InternalError(" Runtime Exception impossibility throw ");
}


return Integer.valueOf(String.valueOf(totalCount));
}




CriteriaImpl impl = (CriteriaImpl) c;


        // 先把Projection、ResultTransformer、OrderBy取出来,清空三者后再执行Count操作
        Projection projection = impl.getProjection();
        ResultTransformer transformer = impl.getResultTransformer();


        List<CriteriaImpl.OrderEntry> orderEntries = null;
        try {
            orderEntries = (List) ReflectionUtils.getFieldValue(impl, "orderEntries");
            ReflectionUtils.setFieldValue(impl, "orderEntries", new ArrayList());
        } catch (Exception e) {
            logger.error("不可能抛出的异常:{}", e.getMessage());
        }


        // 执行Count查询
        int totalCount = (Integer) c.setProjection(Projections.rowCount()).uniqueResult();


        // 将之前的Projection,ResultTransformer和OrderBy条件重新设回去
        c.setProjection(projection);


        if (projection == null) {
            c.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);
        }
        if (transformer != null) {
            c.setResultTransformer(transformer);
        }
        try {
            ReflectionUtils.setFieldValue(impl, "orderEntries", orderEntries);
        } catch (Exception e) {
            logger.error("不可能抛出的异常:{}", e.getMessage());
        }