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 <= 10ORDER 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_indicatorsStringBuilder 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);}