hibernate Criteria

	/**
	 * 查询
	 * 
	 * @author 曾文锋
	 * @date 2010.11
	 * @param condition 查询条件
	 * @return
	 */
	@Excluded
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> query(Map<String, Object> condition)
	{
		String bookId = (String)condition.get("bookId");
		
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT b.id AS ID, b.code AS CODE, b.name AS NAME, x.stockInQty AS STOCKIN_QTY, y.borrowQty AS BORROW_QTY, z.stockOutQty AS STOCKOUT_QTY FROM Book_Bsc_Book b ");
		sql.append("LEFT JOIN (SELECT item.bookId, SUM(item.qty) AS stockInQty FROM Book_Biz_BookStockIn bill LEFT JOIN Book_Biz_BookStockInItem item ON bill.id = item.fthrid AND bill.checked = '1' GROUP BY item.bookid) x ON b.id = x.bookId ");
		sql.append("LEFT JOIN (SELECT item.bookId, SUM(item.qty - item.commitedQty) AS borrowQty FROM Book_Biz_BookBorrow bill LEFT JOIN Book_Biz_BookBorrowItem item ON bill.id = item.fthrid AND bill.checked = '1' GROUP BY item.bookid) y ON b.id = y.bookId ");
		sql.append("LEFT JOIN (SELECT item.bookId, SUM(item.qty) AS stockOutQty FROM Book_Biz_BookStockOut bill LEFT JOIN Book_Biz_BookStockOutItem item ON bill.id = item.fthrid AND bill.checked = '1' GROUP BY item.bookid) z ON b.id = z.bookId ");
		sql.append("WHERE 1 = 1 ");
		
		if (bookId != null && !bookId.equals(""))
		{
			sql.append("AND b.id = :bookId ");
			condition.put("bookId", bookId);
		}
		
		sql.append("ORDER BY b.code ");
		
		return this.getSessionFactory().getCurrentSession()
				   .createSQLQuery(sql.toString())
				   .setProperties(condition)
				   .setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP)
				   .list();
	}

	/**
	 * 评估标准中学校各项“具体管理要求”得分情况
	 * E:\cemseas-project\cemseas-webapps\src\main\webapp\WEB-INF\views\modules\eva\xxpsy\query.jsp
	 * 
	 * @author ZengWenFeng
	 * @date 2017.10.09
	 * @param filter
	 * @param begin  起始数值
	 * @param end    结束数值
	 * @return
	 */
	public List<RepYjrepJtglyqDataBean> queryBySchool2(RepBaseFilter filter, int begin, int end)
	{
/*
SELECT 
    t3.xh || t3.zbnh AS yjzb,
    t2.xh || t2.zbnh AS ejzb,
    t.sys_id,
    t.xh_id, t.jtglyq, t.fz, t.xxzpdf, t.xsqhcdf, t.sqspgdf,
    t.*
FROM t_yjrep_jtglyq t
    LEFT JOIN t_yj_indicators t1 ON t.xh_id = t1.xh AND t1.cj = '3'
    LEFT JOIN t_yj_indicators t2 ON t1.glzb = t2.sys_id AND t2.cj = '2'
    LEFT JOIN t_yj_indicators t3 ON t2.glzb = t3.sys_id AND t3.cj = '1'
WHERE 1 = 1 
    AND t.xx_id = '1313'
    AND t.xh_id >= 0
    AND t.xh_id <= 10
ORDER BY t.xh_id;
*/
		
//		create("select obj.schoolId, obj.professionalId,count(obj.schoolId) from "+clzName+" obj group by obj.schoolId,obj.professionalId");

		//RepYjrepJtglyq   t_yjrep_jtglyq
		//RepYjIndicators  t_yj_indicators
		
		StringBuilder hql = new StringBuilder();
		hql.append(" SELECT ");
		hql.append(" CONCAT(t3.xh, t3.zbnh) AS yjmc, ");
		hql.append(" CONCAT(t2.xh, t2.zbnh) AS ejmc, ");
		hql.append(" obj.sysId, ");
		hql.append(" obj.xhId, obj.jtglyq, obj.fz, obj.xxzpdf, obj.xsqhcdf, obj.sqspgdf ");
		hql.append(" FROM RepYjrepJtglyq obj ");
		hql.append(" LEFT JOIN RepYjIndicators t1 ON obj.xhId = t1.xh AND t1.cj = '3' ");
		hql.append(" LEFT JOIN RepYjIndicators t2 ON t1.glzb = t2.sysId AND t2.cj = '2' ");
		hql.append(" LEFT JOIN RepYjIndicators t3 ON t2.glzb = t3.sysId AND t3.cj = '1' ");
		hql.append(" WHERE 1 = 1 ");		
		hql.append(" AND obj.xxId = " + filter.getSchoolId() + " ");
		hql.append(" AND obj.xhId >= " + begin + " ");
		hql.append(" AND obj.xhId <= " + end + " ");
		hql.append(" ORDER BY obj.xxId ");
		
		GenericQuery query = create(hql.toString());
		return query.listResult(filter);
	}