Hibernate分页（sql和hql）两种方法

1、Hibernate原生sql分页

@Transactional
    public PagerBean<ModelInfoDTO> findModelLabelAll(PagerBean<ModelInfo> pager, String dataSource) {
        PagerBean<ModelInfoDTO> pagerDto = null;
        try {
            Session session = sessionFactory.getCurrentSession();
            StringBuffer sb = new StringBuffer();
            sb.append(" select m.model_id, m.model_name, m.model_type, m.reserved_1, m.create_time, t.task_status from model_info_t m ");
            sb.append(" left join model_task_t mt on m.model_id=mt.model_id ");
            sb.append(" left join task_info_t t on mt.task_id=t.task_id ");
            sb.append("where m.data_source=? and m.model_status!=? and (t.run_schema=? or t.run_schema is null) and  m.model_name like " + pager.getParam().getModelName() + " order by m.create_time desc,m.model_name asc");
            String sql = sb.toString();

            StringBuffer sb_count = new StringBuffer();
            sb_count.append(" select count(*) from model_info_t m ");
            sb_count.append(" left join model_task_t mt on m.model_id=mt.model_id ");
            sb_count.append(" left join task_info_t t on mt.task_id=t.task_id ");
            sb_count.append("where m.data_source=? and m.model_status!=?  and (t.run_schema=? or t.run_schema is null) and  m.model_name like " + pager.getParam().getModelName() + " ");
            String sql_count = sb_count.toString();

            //分页
            List<Object[]> lists = (List<Object[]>)session.createSQLQuery(sql)
                    .setParameter(0, dataSource)
                    .setParameter(1, Constants.MODEL_STATUS_DELETE)
                    .setParameter(2,Constants.MODEL_RUN_SCHEMA_HAND)
                    .setFirstResult((pager.getPage() - 1) * pager.getPageSize()).setMaxResults(pager.getPageSize())
                    .list();
            //查询总数
            Object totals_obj = (Object)session.createSQLQuery(sql_count)
                    .setParameter(0, dataSource)
                    .setParameter(1, Constants.MODEL_STATUS_DELETE)
                    .setParameter(2,Constants.MODEL_RUN_SCHEMA_HAND)
                    .uniqueResult();
            Long totals = Long.valueOf(totals_obj.toString());

            //封装页面数据参数
            List<ModelInfoDTO> rows = object2ModelInfoDTO(lists);
            pagerDto = new PagerBean<ModelInfoDTO>();
            pagerDto.setPage(pager.getPage());
            pagerDto.setPageSize(pager.getPageSize());
            pagerDto.setOrder(pager.getOrder());
            pagerDto.setSort(pager.getSort());

            pagerDto.setTotal(totals);
            pagerDto.setRows(rows);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return pagerDto;
    }

1.1将List<Object[]>数组转对象

//封装页面数据参数 将object 数组转化为DTO 对象
    private List<ModelInfoDTO> object2ModelInfoDTO(List<Object[]> lists) {
        List<ModelInfoDTO> rows = new ArrayList<ModelInfoDTO>();
        ModelInfoDTO dto = null;
        if (lists != null && lists.size() > 0) {
            for(Object[] objects : lists){
                dto = new ModelInfoDTO();
                //m.model_id, m.model_name, m.model_type, m.reserved_1, m.create_time, t.task_status
                dto.setModelId(Long.valueOf(objects[0].toString()));

                if (objects[1] != null && objects[1].toString().length() > 10) {
                    dto.setModelName(objects[1].toString().substring(0, 10) + "...");
                } else {
                    dto.setModelName(objects[1] == null ? "" : objects[1].toString());
                }
                dto.setModelNameTip(objects[1] == null ? "" : objects[1].toString());

                dto.setModelType(getModelTypeName(Long.valueOf(objects[2].toString())));
                dto.setUserName(objects[3]== null ? "" : objects[3].toString());
                dto.setCreateTime(objects[4] == null ? "" : objects[4].toString());
                dto.setTaskStatus(objects[5] == null ? "" : objects[5].toString());
                rows.add(dto);
            }
        }
        return rows;
    }

2、Hibernate hql分页

@Transactional
    public PagerBean<ModelInfoDTO> findModelLabelAll_bak(PagerBean<ModelInfo> pager, String dataSource) {
        PagerBean<ModelInfoDTO> pagerDto = null;
        try {
            Session session = sessionFactory.getCurrentSession();
            StringBuffer sb = new StringBuffer();
            sb.append("select  m from ModelInfo m ");
            sb.append("where m.dataSource=? and m.modelStatus!=? and  m.modelName like " + pager.getParam().getModelName() + " order by m.createTime desc,m.modelName asc");
            String hql = sb.toString();
            //分页
            List<ModelInfo> lists = (List<ModelInfo>) session.createQuery(hql)
                    .setParameter(0, dataSource)
                    .setParameter(1, Constants.MODEL_STATUS_DELETE)
                    .setFirstResult((pager.getPage() - 1) * pager.getPageSize()).setMaxResults(pager.getPageSize())
                    .list();
            //查询总数
            List<ModelInfo> totals = (List<ModelInfo>) session.createQuery(hql)
                    .setParameter(0, dataSource)
                    .setParameter(1, Constants.MODEL_STATUS_DELETE)
                    .list();

            //封装页面数据参数
            List<ModelInfoDTO> rows = getResultModelInfo(lists);
            pagerDto = new PagerBean<ModelInfoDTO>();
            pagerDto.setPage(pager.getPage());
            pagerDto.setPageSize(pager.getPageSize());
            pagerDto.setOrder(pager.getOrder());
            pagerDto.setSort(pager.getSort());

            pagerDto.setTotal(totals.size());
            pagerDto.setRows(rows);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return pagerDto;
    }