JZTXT

Hibernate列表和分页列表用原生sql

发布时间 2023-04-18 14:06:10作者: 岁月淡忘了谁

记得注入SessionFactory：

@Autowired
private SessionFactory sessionFactory;

普通列表查询如下：

public List<Object[]> statisticByYear(Integer year,Integer siteId,
		String statisticType,String statisticValue,
		boolean groupByMonth,Integer orderBy){
	StringBuffer sb = new StringBuffer();
	sb.append("select sum(pv) as pv,sum(ip) as ip,sum(vs) as vs,sum(vsa*vs)/sum(vs) as second,TO_CHAR(to_date(month_,'YYYY-MM'),'MM') as month_ from " +
			"(select max(bean.pv) pv,max(bean.ip) ip,max(bean.visitors) vs,max(bean.visit_second_aver) vsa,to_char(bean.statistic_date,'YYYY-MM-DD') month_ " +
			" from jmy_site_access_statistic bean where bean.site_id =:siteId and year(bean.statistic_date)=:year and bean.statisitc_type =:statisitcType " );
	if(StringUtils.isNotBlank(statisticValue)){
		sb.append("and bean.statistic_column_value=:statisticColumnValue");
	}
	sb.append("group by to_char(bean.statistic_date,'YYYY-MM-DD') )t group by TO_CHAR(to_date(month_,'YYYY-MM'),'MM')");
	if(orderBy!=null){
		if(orderBy==0){
			sb.append(" order by sum(pv) desc");
		}else if(orderBy==1){
			sb.append(" order by sum(ip) desc");
		}else if(orderBy==2){
			sb.append(" order by (sum(vsa*vs)/sum(vs)) desc");
		}else if(orderBy==3){
			sb.append(" order by TO_CHAR(to_date(month_,'YYYY-MM'),'YYYY-MM') asc");
		}
	}
	Query query = sessionFactory.getCurrentSession().createSQLQuery(sb.toString())
			.addScalar("pv", StandardBasicTypes.LONG)
			.addScalar("ip", StandardBasicTypes.LONG)
			.addScalar("vs", StandardBasicTypes.LONG)
			.addScalar("second", StandardBasicTypes.LONG)
			.addScalar("month_", StandardBasicTypes.STRING);
	//设置第一个参数的值为12,即查询ID=12的note
	query.setParameter("siteId", siteId);
	query.setParameter("year", year);
	query.setParameter("statisitcType", statisticType);
	if(StringUtils.isNotBlank(statisticValue)){
		query.setParameter("statisticColumnValue", statisticValue);
	}
	List list = query.list();
	return list;
}

分页列表查询如下：

public Pagination getPage(Integer siteId, int pageNo, int pageSize) {
	StringBuffer sb1 = new StringBuffer();
	sb1.append("select jge.guestbook_id as id,jge.title,jge.content,jge.reply,jge.email,jge.phone,jge.qq,jge.name ");
	sb1.append("from jmy_guestbook jg,jmy_guestbook_ext jge where jg.guestbook_id = jge.guestbook_id and jg.site_id = :siteId");
	Query query = sessionFactory.getCurrentSession().createSQLQuery(sb1.toString());
	query.setParameter("siteId",siteId);
	query.setFirstResult((pageNo-1)*pageSize);
	query.setMaxResults(pageSize);
	//列表数据
	//List list = query.list();
	//List<CmsGuestbookExt> list = query.setResultTransformer(Transformers.aliasToBean(CmsGuestbookExt.class)).list();
	List<CmsGuestbookExt> list = query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(CmsGuestbookExt.class)).list();

	StringBuffer sb2 = new StringBuffer();
	sb2.append("select count(1) from jmy_guestbook jg,jmy_guestbook_ext jge where jg.guestbook_id = jge.guestbook_id and jg.site_id = :siteId");
	query = sessionFactory.getCurrentSession().createSQLQuery(sb2.toString());
	query.setParameter("siteId",siteId);
	BigInteger count = (BigInteger) query.uniqueResult();
	
	//封装成Page类型
	Pagination paginationage = new Pagination();
	paginationage.setList(list);
	paginationage.setPageNo(pageNo);
	paginationage.setPageSize(pageSize);
	paginationage.setTotalCount(count.intValue());
	
	return paginationage;
}

Controller类调用分页方法：

Pagination page = manager.getPage(site.getId(), pageNo, pageSize);
int totalCount = page.getTotalCount();
List<CmsGuestbook> list = (List<CmsGuestbook>) page.getList();
net.sf.json.JSONArray jsonArray = net.sf.json.JSONArray.fromObject(list);
String message = Constants.API_MESSAGE_SUCCESS;
String code = ResponseCode.API_CODE_CALL_SUCCESS;
String body = jsonArray.toString()+",\"totalCount\":"+totalCount;
ApiResponse apiResponse = new ApiResponse(request, body, message, code);
ResponseUtils.renderApiJson(response, request, apiResponse);