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;
}
Hibernate 原生sql语句进行分页查询
最新推荐文章于 2022-12-04 21:04:44 发布