多表返回实体
List<Object> resultList = db.query( "from Blog as t0,Usertable as t1 where t0.userId=t1.id").list();// HQL的多表查询 for (int i = 0; i < resultList.size(); i++) { Object[] hql_result_arr = (Object[]) resultList.get(i);//取其中一行的查询结果 //将查询结果分成两半,分别转化为相应的实体 Blog blog = (Blog) hql_result_arr[0]; Usertable usertable = (Usertable) hql_result_arr[1]; }
String hql = "select bg.id as id,bg.groupId as groupId, b.id as businessId,"+" g.name as groupName,b.name as businessName" +" from Business b, Businessgroup bg, Groups g " +" where bg.groupId=g.id and bg.businessId=b.id order by bg.groupId asc"; Session session = this.getSession(); Query query = session.createQuery(hql); query.setFirstResult(start); query.setMaxResults(limit); query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);//这样会把所有查询的列放到一个元素为Map的list中。 return query.list(); //然后遍历list,从map中以列明为键值取出相应的value,再set到po中 for(int i=0; i<list.size(); i++){ map = (Map) list.get(i); }
public List<ArticleInfo> getArticlePage(int start, int limit, List<FilterBean> filterList) {
String hql = "select new ArticleInfo(article, column.name) " + "from ArticleInfo article, ColumnInfo column where 1 = 1 " + "and article.columnId = column.id ";
hql = super.setFilterBean(filterList, hql);
hql = super.orderBy(hql, "article.createDate desc");
Query query = super.createQuery(filterList, hql);
query.setFirstResult(start); query.setMaxResults(limit); return query.list();
}
hql 不支持on
List list = baseDao.findBySQL("SELECT MAX(ID) FROM smsserver_out");
Integer maxId = (Integer)list.get(0);
List<Map<String, Object>> copyList = null; StringBuffer copyInfoBuffer = new StringBuffer(500); copyInfoBuffer.append("SELECT stf.STAFF_ID AS recieverId, stf.STAFF_NAME AS reciever, stf.HANDSET AS phone, dept.DEPARTMENT_ID AS recieverDeptId, CONCAT_WS('丨', dept2.DEPARTMENT_NAME2, dept.DEPARTMENT_NAME) recieverDeptName FROM jso5_jbase.staff stf ") .append("LEFT JOIN jso5_jbase.structure stru ON stf.STAFF_ID = stru.ELEMENT_ID ") .append("LEFT JOIN jso5_jbase.department dept ON stru.ELEMENT_PARENT_ID = dept.DEPARTMENT_ID ") .append("JOIN (SELECT ELEMENT_ID AS ELEMENT_ID2, ELEMENT_PARENT_ID AS ELEMENT_PARENT_ID2 FROM jso5_jbase.structure) stru2 ON dept.DEPARTMENT_ID = stru2.ELEMENT_ID2 ") .append("JOIN (SELECT DEPARTMENT_ID AS DEPARTMENT_ID2, DEPARTMENT_NAME AS DEPARTMENT_NAME2 FROM jso5_jbase.department) dept2 ON stru2.ELEMENT_PARENT_ID2 = dept2.DEPARTMENT_ID2 ") .append("WHERE INSTR ('" + copyIds + "', stf.STAFF_id)"); String[] jsonField = { "recieverId", "reciever", "phone", "recieverDeptId", "recieverDeptName" }; copyList = baseDao.queryForListMap(jsonField, copyInfoBuffer.toString(), new Object[]{});