我想使用Hibernate Criteria来实现以下SQL查询:
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name <operator> value
GROUP BY column_name
我尝试使用Hibernate Criteria实现这个功能,但是没有成功。
有人可以给我一个示例,说明如何使用Hibernate Criteria实现吗? 谢谢!
我想使用Hibernate Criteria来实现以下SQL查询:
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name <operator> value
GROUP BY column_name
我尝试使用Hibernate Criteria实现这个功能,但是没有成功。
有人可以给我一个示例,说明如何使用Hibernate Criteria实现吗? 谢谢!
groupProperty()
方法以及Projections类提供的相关聚合函数。例如:
SELECT column_name, max(column_name) , min (column_name) , count(column_name)
FROM table_name
WHERE column_name > xxxxx
GROUP BY column_name
它对应的条件对象是:
List result = session.createCriteria(SomeTable.class)
.add(Restrictions.ge("someColumn", xxxxx))
.setProjection(Projections.projectionList()
.add(Projections.groupProperty("someColumn"))
.add(Projections.max("someColumn"))
.add(Projections.min("someColumn"))
.add(Projections.count("someColumn"))
).list();
Hibernate中使用GroupBy
以下是最终代码
public Map getStateCounts(final Collection ids) {
HibernateSession hibernateSession = new HibernateSession();
Session session = hibernateSession.getSession();
Criteria criteria = session.createCriteria(DownloadRequestEntity.class)
.add(Restrictions.in("id", ids));
ProjectionList projectionList = Projections.projectionList();
projectionList.add(Projections.groupProperty("state"));
projectionList.add(Projections.rowCount());
criteria.setProjection(projectionList);
List results = criteria.list();
Map stateMap = new HashMap();
for (Object[] obj : results) {
DownloadState downloadState = (DownloadState) obj[0];
stateMap.put(downloadState.getDescription().toLowerCase() (Integer) obj[1]);
}
hibernateSession.closeSession();
return stateMap;
}
session.createCriteria(SomeTable.class)
.add(Restrictions.ge("someColumn", xxxxx))
.setProjection(Projections.projectionList()
.add(Projections.groupProperty("someColumn"))
.add(Projections.max("someColumn"))
.add(Projections.min("someColumn"))
.add(Projections.count("someColumn"))
).setResultTransformer(Transformers.aliasToBean(SomeClazz.class));
List<SomeClazz> objectList = (List<SomeClazz>) criteria.list();
group by
操作,请使用projections.groupProperty
,如下所示:@Autowired
private SessionFactory sessionFactory;
Criteria crit = sessionFactory.getCurrentSession().createCriteria(studentModel.class);
crit.setProjection(Projections.projectionList()
.add(Projections.groupProperty("studentName").as("name"))
List result = crit.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP).list();
return result;