Hibernate 原生sql语句进行分页查询

public List findStockByStockPOAndTradeDate(int from, int size, StockPO po,
			Date tradeDate) {
		List list = new ArrayList();
		List result = new ArrayList();
		logger.info("##startfindStockByStockPOAndTradeDate");
		SimpleDateFormat sdf = new SimpleDateFormat("dd-M月-yy");
		String date = sdf.format(tradeDate);
		StringBuffer sql = new StringBuffer(
				"SELECT stkmp.* FROM STKMP stkmp INNER JOIN(SELECT  st.STOCK_ID stid ,st.COUNTRY cou FROM STKMP  st INNER JOIN stock sto ON st.STOCK_ID=sto.STOCK_ID and st.COUNTRY=sto.COUNTRY WHERE  sto.country='");
		sql.append(po.getStockPK().getCountry())
				.append("' AND  sto.mkt_type='").append(po.getMarketType())
				.append("' AND  sto.cur_type='").append(po.getCurrencyType());
		if (null != po.getStockPK().getStockNO()) {
			sql.append("'AND sto.STOCK_ID='").append(
					po.getStockPK().getStockNO());
		}
		if (null != po.getStockInternationalId()) {
			sql.append("'AND sto.STOCK_NID='").append(
					po.getStockInternationalId());
		}
		if (null != po.getStockReservationType4Entity()) {
			sql.append("' AND sto.SP_ENT= '").append(
					po.getStockReservationType4Entity());
		}
		if (null != po.getStockReservationType4Strip()) {
			sql.append("' AND sto.SP_PP= '").append(
					po.getStockReservationType4Strip());
		}
		if (null != po.getStockReservationType4TDCC()) {
			sql.append("' AND sto.SP_TSCD= '").append(
					po.getStockReservationType4TDCC());
		}
		sql
				.append(
						"' GROUP BY st.STOCK_ID ,st.COUNTRY) stock2 ON stkmp.STOCK_ID=stock2.stid AND stkmp.TRADE_DATE='")
				.append(date)
				.append(
						"' and stkmp.COUNTRY=stock2.cou ORDER BY stkmp.TRADE_DATE desc");

		this.getSession().clear();
		Query query = this.getSession().createSQLQuery(sql.toString())
				.addEntity("stkmp", StockMarketPricePO.class);
		query.setMaxResults(size);
		query.setFirstResult(from);
		result = query.list();
		logger.info("##message"+result.size());
		list.add(0, result);
		StringBuffer sqltest = new StringBuffer(
				"SELECT count(*) FROM STKMP stkmp INNER JOIN(SELECT  st.STOCK_ID stid ,st.COUNTRY cou FROM STKMP  st INNER JOIN stock sto ON st.STOCK_ID=sto.STOCK_ID and st.COUNTRY=sto.COUNTRY WHERE  sto.country='");
		sqltest.append(po.getStockPK().getCountry()).append(
				"' AND  sto.mkt_type='").append(po.getMarketType()).append(
				"' AND  sto.cur_type='").append(po.getCurrencyType());
		if (null != po.getStockPK().getStockNO()) {
			sqltest.append("'AND sto.STOCK_ID='").append(
					po.getStockPK().getStockNO());
		}
		if (null != po.getStockInternationalId()) {
			sqltest.append("'AND sto.STOCK_NID='").append(
					po.getStockInternationalId());
		}
		if (null != po.getStockReservationType4Entity()) {
			sqltest.append("' AND sto.SP_ENT= '").append(
					po.getStockReservationType4Entity());
		}
		if (null != po.getStockReservationType4Strip()) {
			sqltest.append("' AND sto.SP_PP= '").append(
					po.getStockReservationType4Strip());
		}
		if (null != po.getStockReservationType4TDCC()) {
			sqltest.append("' AND sto.SP_TSCD= '").append(
					po.getStockReservationType4TDCC());
		}
		sqltest
				.append(
						"' GROUP BY st.STOCK_ID ,st.COUNTRY) stock2 ON stkmp.STOCK_ID=stock2.stid AND stkmp.TRADE_DATE='")
				.append(date)
				.append(
						"' and stkmp.COUNTRY=stock2.cou ORDER BY stkmp.TRADE_DATE desc");

		this.getSession().clear();
		Query querycount = this.getSession().createSQLQuery(sqltest.toString());
		int count = ((BigDecimal) querycount.uniqueResult()).intValue();
		logger.info("##endfindStockByStockPO" + list.size() + "--" + count);
		list.add(1, new Integer(count));
		return list;
	}

 

评论

1 楼 waw0931 2016-05-19  

分页在哪里？