如何使用Hibernate Criteria使用Hypernate Criteria的分组和别名作为投影，从日期和多列中提取年，月 [英] How to extract year,month from a date and multiple columns using projections with group by and given as alias name by using hibernate Criteria

问题描述

我必须通过使用groupby和投影列表来获取ID的数量，并且我想为某些列设置别名，现在我给出了sql查询示例

I have to get some count of id by using groupby and multiple columns also using projection list and I want to set alias name for some of the column, now I am giving sample of sql query

String sql ="select createdby,count(id) as created_by_count,EXTRACT(YEAR FROM createdon) as year,to_char(createdon, 'Month') as month from projects where createdon BETWEEN '"+fromDate+"' AND '"+todate+"' group by createdby,EXTRACT(YEAR FROM createdon),to_char(createdon, 'Month')  order by createdby,year,month"

我正在写这样的crietria

I am writing like this with crietria

ProjectionList projList = Projections.projectionList();
projList.add(Projections.property("id.createdby"));
projList.add(Projections.groupProperty("id.createdby"));
projList.add(Projections.sqlProjection( "yeard(createdon) as year", new  String[] {"year"}, new Type[] {StandardBasicTypes.INTEGER} ));  
crit.setProjection(projList);
Criterion cn = Restrictions.between("createdOn",fromDate,todate);
crit.add(cn);
crit.addOrder(Order.asc("createdBy"));
crit.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
List projectCount = crit.list();

请有人帮我解决这个问题。

Please anyone help me on this.

推荐答案

除了一个字段外，我几乎做不到

I have done almost apart from only one field

Criteria crit = sessionFactory.getCurrentSession().createCriteria(Project.class);
    ProjectionList projList = Projections.projectionList();
    projList.add(Projections.groupProperty("createdBy").as("createdBy"));
    projList.add(Projections.count("id").as("created_by_count"));
    projList.add(Projections.sqlProjection( "EXTRACT(YEAR FROM createdon) as year", new String[] {"year"}, new Type[] {StandardBasicTypes.INTEGER} ));
    projList.add(Projections.sqlProjection( "to_char(createdon, 'Month') as month", new String[] {"month"}, new Type[] {StandardBasicTypes.STRING} ));
    crit.setProjection(projList);
    Criterion cn = Restrictions.between("createdOn",fromDate,todate);
    crit.add(cn);
    crit.addOrder(Order.asc("createdBy"));
    crit.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
    List projectCount = crit.list();
    return projectCount;

只有一件事正在等待，这是如何从日期中提取年份或月份，这也是我尝试过的使用正在工作的sql投影，但是我想在这两个方面都使用groupby，因为我不知道该怎么做，请任何人在这个上帮我

only one thing is pending which is how to extract year or month from date , that also I have tried using sql projection which is working but I want to use groupby on both things for that I dont know how to do please anyone help me on this

这篇关于如何使用Hibernate Criteria使用Hypernate Criteria的分组和别名作为投影，从日期和多列中提取年，月的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！