hibernate的查询方式有六种，常用的一般是两到三种，SQL,HQL,QBC,实现的功能依次减弱，可维护性依次增强。三种查询都用过，QBC的功能太弱，如果项目的业务逻辑太复杂，不建议用，HQL能实现一般业务的90%以上的功能，如果用hibernate打好了环境，最好还是少用SQL，因为一用SQLhibernate的缓存就被清掉了，不能体现hibernate特有的优势。如果不用又不能实现一些特别难的逻辑。所以说是少用。
先将项目中的SQL代码贴出来。
public List<Feesumsx> getFeesum2(ReportForm reportForm) {
String dbType = reportForm.getDbType();
String startDate = CommUtil.cutDateStr(reportForm.getStartdate());
String endDate = CommUtil.cutDateStr(reportForm.getEnddate());
String bankid = reportForm.getBankid();
String curCode = reportForm.getCurCode();
String curType = reportForm.getCurType();
String clearDate=reportForm.getClearDate();
String startRound=reportForm.getStartRound();
String endRound=reportForm.getEndRound();
String classId=reportForm.getClassId();
String clearBank=reportForm.getClearbank();
String printType=reportForm.getPrintType();
List<Feesumsx> regList = new ArrayList();
dbType = StringUtil.isNoNullString(dbType) ? dbType : "iccdb";
dbType="iccdb";
StringBuffer sb = new StringBuffer("select bankid as BANKID,curcode as CODE,curtype as TYPE,sum(num) as NUM,sum( case dueflag when '2' then fee * -1 when '1' then fee end ) as FEE");
sb.append(" from " + dbType + "..feesum_sx where 1=1 ");
//开始加where条件
if (!CommUtil.cutDateStr(startDate).equals("")) {
sb.append(" and workdate >='").append(startDate).append("'");
}
if (!CommUtil.cutDateStr(endDate).equals("")) {
sb.append(" and workdate <='").append(endDate).append("'");
}
if (StringUtil.isNoNullString(bankid)) sb.append(" and bankid='").append(bankid).append("'");
if (StringUtil.isNoNullString(curCode)) sb.append(" and curcode='").append(curCode).append("'");
if (StringUtil.isNoNullString(curType)) sb.append(" and curtype='").append(curType).append("'");
sb.append(" group by bankid,curcode,curtype");
sb.append(" order by bankid,curcode,curtype");
Session session = null;
System.out.println("feesum sql"+sb.toString());
try {
session = hibernateTemplate.getSessionFactory().openSession();
SQLQuery query = session.createSQLQuery(sb.toString());
System.out.print("***********************"+sb.toString());
query.addScalar("BANKID", Hibernate.STRING);
query.addScalar("CODE", Hibernate.STRING);
query.addScalar("TYPE", Hibernate.STRING);
query.addScalar("NUM", Hibernate.INTEGER);
query.addScalar("FEE", Hibernate.DOUBLE);
List list = query.list();
Iterator it = list.iterator();
while (it.hasNext()) {
Object[] ret = (Object[]) it.next();
FeesumsxId id = new FeesumsxId();
id.setCurcode((String) ret[1]);
id.setCurtype((String) ret[2]);
Feesumsx fee = new Feesumsx();
fee.setId(id);
fee.setBankid((String) ret[0]);
fee.setNum((Integer) ret[3]);
fee.setFee((Double) ret[4]);
regList.add(fee);
}
}
catch (Exception e) {}
finally {
session.flush();
session.clear();
session.close();
}
return regList;
}
再贴HQL代码
public List<Feeportion> findFeeportionList(SysAdminForm sysForm) {
String hql = "from Feeportion where 1=1 ";
String prescbank = sysForm.getPrescbank();
String acptcbank = sysForm.getAcptcbank();
String startDate = CommUtil.cutDateStr(sysForm.getStartDate());
String endDate = CommUtil.cutDateStr(sysForm.getEndDate());
String notetype = sysForm.getNotetype();
String col = StringUtil.null2String(sysForm.getCol());
String orderBy = StringUtil.null2String(sysForm.getOrderBy());
if (StringUtil.isNoNullString(startDate)) hql += " and workdate >='" + startDate + "' ";
if (StringUtil.isNoNullString(endDate)) hql += " and workdate <='" + endDate + "' ";
if (StringUtil.isNoNullString(prescbank)) hql += " and prescbank='" + prescbank + "' ";
if (StringUtil.isNoNullString(acptcbank)) hql += " and acptcbank='" + acptcbank + "' ";
if (StringUtil.isNoNullString(notetype)) hql += " and notetype='" + notetype + "' ";
// 排序
if (!col.equals("") && !orderBy.equals("")) {
hql += " order by " + col + " " + orderBy;
}
return this.feePortionDAO.getHibernateTemplate().find(hql);
}