Hi,
I am new to hibernate. Please Help.
I am trying to use Oracle NVL function with Group By which works fine in SQL. Group by is to be applied on property(sch) which itself is an Entity(School Master Table) inside another Main Entity Students(Some Table). There is a case when Student is not attached to any School and School(sch) object comes as null in list of Students.

@Entity
Public Class Students {

private School sch;
private String JoinedByUser;
private Date joiningdttm;
private Long count; //Made as transient since not part of Table.

// Getter and Setters for all;

}

@Entity
public Class School{
private String scoolName;
private String schoolId;

// Getter and Setters for schoolName and schoolId;

}


I want to use criteria. Below is the criteria I am using criteria as below.

Criteria criteria = createCriteria(Students.class); // Custom Class to create criteria
ProjectionList projList = Projections.projectionList();
projList.add(Projections.alias(Projections.groupProperty("sch"),"sch"));
projList.add(Projections.alias(Projections.count("sch"),"sch"));
projList.add(Projections.alias(Projections.groupProperty("JoinedByUser"),"JoinedByUser"));
projList.add(Projections.alias((Projections.sqlGroupProjection(
"trunc(joining_dttm) as joiningdttm",
"trunc(joining_dttm)", new String[] { "joiningdttm" },
new Type[] {StandardBasicTypes.DATE})),"joiningdttm"));

criteria.setProjection(projList);
criteria.createAlias("screenState", "State",JoinType.LEFT_OUTER_JOIN);
criteria.add(Restrictions.sqlRestriction("trunc(joining_dttm) <=sysdate"));
criteria.setResultTransformer(Transformers.aliasToBean(Students.class));


My Query should be something like below. Both the Classes are properly annotated for association using schoolId column which is present in both tables. I could have used sqlGroupProjections just like I used for joining_date but not sure how to use it for nested entities and what should be StandardBasicTypes.DATE in that case. Projections.groupProperty only works with property and not with functions on property.

select nvl(schoolId, ' ') as schoolId,
count(schoolId) as count,
nvl(JoinedBy_USR, ' ') as JoinedBy_USR,
trunc(joining_dttm) as joiningdttm
from STD left outer join SCH on STD.schoolId=SCH.schoolId
where joining_dttm <= trunc(sysdate)
GROUP BY nvl(schoolId, ' '),
nvl(JoinedBy_USR,' '),
trunc(joining_dttm)

This is what troubling me GROUP BY nvl(schoolId, ' ')

Thanks