Hibernate Dao中直接使用sql实现复杂的多表联合查询
public List<Object> getAllFiltersJobIDs(Integer days) {
StringBuffer sql = new StringBuffer().append("select DISTINCT a.id as job_id ").append(" from JobDaoEntity as a, JobRecordDaoEntity as b")
.append(" where a.id=b.jobId and a.inUseFlag='true'")
.append(" and b.result != 'success' ")
.append(" and b.startTime >= ADDDATE(NOW(),-")
.append(days.toString())
.append(") ");
Query query = getCurrentSession().createQuery(sql.toString()).setResultTransformer(Transformers.TO_LIST);
return query.list();
}
注意:
1.TO_LIST:返回List,除了这个选项外,还有一个ALIAS_TO_ENTITY_MAP,返回MAP。
返回LIST时,Object本身也是一个List,使用时注意,
ret = getAllFilters(1);
for (int i = 0; i < ret.size(); i++) {
(List<Long>)ret.get(i)).get(0)
}
2.from 后面需要写类名称,否则Hibernate中出现表名(XXX) is not mapped的问题。
3. 如果返回的内容有对应的Bean,可以直接映射成Bean,而不用LIST/MAP返回结果:Query query = session.createSQLQuery(sql).addEntity(XXXXXXX.class);