hibernate多种方式查询

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);
	}