JPA对原生SQL的支持

public String test() {

String year = "2018";
int quarterly = 4;
Long brokerId = Long.valueOf(2001);
Map<String, Date> datesMap = dateService.getStandardDateSection(year,
quarterly);
Date insureDateStart = datesMap.get("startDate");
Date insureDateEnd = datesMap.get("endDate");
try {
StringBuffer sql = new StringBuffer();
sql.append(" SELECT D.STD_PREM_SAMT FROM TBL_ORDER D ")
// .append("LEFT JOIN TBL_BROKER B ON D.BROKER_ID =
// :brokerId ")
.append("LEFT JOIN TBL_BROKER_LEVEL L ON L.BROKER_ID = :brokerId AND L.BROKER_LEVEL = 2 ")
.append("LEFT JOIN TBL_ORDER_TRACK T ON T.ORDER_ID = D.ID AND T.END_TYPE = 70 AND T.START_DATE < date_sub(D.INSURE_DATE,interval -10 DAY)")
.append(" where D.BROKER_ID = :brokerId AND D.INSURE_DATE > :insureDateStart AND D.INSURE_DATE < :insureDateEnd ")
.append(" AND D.ACK_DATE < date_sub(date_sub(D.APPLY_DATE,interval -1 month),interval DAYOFMONTH(date_sub(D.APPLY_DATE,interval -1 month))-10 day) ");
Query query = em.createNativeQuery(sql.toString());
if (brokerId != null) {
query.setParameter("brokerId", brokerId);
}
if (insureDateStart != null) {
query.setParameter("insureDateStart", insureDateStart);
}
if (insureDateEnd != null) {
query.setParameter("insureDateEnd", insureDateEnd);
}
List<Object> list = query.getResultList();
BigDecimal amt = BigDecimal.ZERO;
BigDecimal ret = BigDecimal.ZERO;
for (int i = 0; i < list.size(); i++) {
Object object = list.get(i);
if (object instanceof BigDecimal) {
ret = (BigDecimal) object;
amt = amt.add((BigDecimal) object);
}
}
} catch (Exception e) {
throw new ProcessException(e.getMessage(), e);
}
}