CODEWITHSUNDEEP
javapostgresqlhibernate
sourav78611
sourav78611

Reputation: 99

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

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"

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.

Upvotes: 0

Views: 1570

Answers (2)

sourav78611
sourav78611

Reputation: 99

I have done fully with below commands.

    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.sqlGroupProjection("EXTRACT(YEAR FROM createdon) as year","EXTRACT(YEAR FROM createdon)",  new String[] {"year"}, new Type[] { StandardBasicTypes.INTEGER }));
    projList.add(Projections.sqlGroupProjection("to_char(createdon, 'Month') as month","to_char(createdon, '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;

Upvotes: 1

sourav78611
sourav78611

Reputation: 99

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;

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

Upvotes: 0

Related Questions