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;
}