前几天系统中用到复杂的SQL,想用HQL 来实现结果HQl语句写不出来。想想hibernate也支持SQL 的,就只能写SQL 封装来实现了。
SQL
select *
from wcsc_service_log a,
(select id, max(end_time) as end_time
from wcsc_service_log
group by id) b
where a.id = b.id
and a.end_time = b.end_time
and COMPCODE = '0001'
JAVA 代码封装这段SQL
public Pagination getServiceLog(String compcode, int pageNo, int pageSize) {
String sql ="select a.* from wcsc_service_log a ,(select id,max(end_time)as end_time from wcsc_service_log group by id)b"+
" where a.id=b.id and a.end_time=b.end_time and a.COMPCODE='"+compcode+"'";
Query queryObject =getSession().createSQLQuery(sql).addEntity(ServiceLog.class);
queryObject.setFirstResult((pageNo - 1) * pageSize);
queryObject.setMaxResults(pageSize);
List<ServiceLog> list =queryObject.list();
int count=getCount(compcode);
Pagination p = new Pagination(pageNo, pageSize, count);
if (count < 1) {
p.setList(new ArrayList<ServiceLog>());
return p;
}
p.setList(list);
return p;
}
代码说明
Query queryObject =getSession().createSQLQuery(sql).addEntity(ServiceLog.class);
这句是执行SQL映射HIBERNATE 查询,注意createSQLQuery 该方法是SQL 语句,我们知道HQL 的方法是
createQuery 另外封装成映射对象addEntity(ServiceLog.class),ServiceLog是你SQL JavaBEAN
返回查询语句这样就可以HIBERNATE待用SQL 了
queryObject.setFirstResult((pageNo - 1) * pageSize);
queryObject.setMaxResults(pageSize);
这2端是分页的语句 如果不分页可以不需要了
这样就实现了HIBERNATE 调用复杂的SQL