I am trying to return an entity with a column that has the count of another table that is a one to many relation. I want to do this using hibernate criteria, not HQL.
select p.*, (select count(*) from child where child.parentid = p.id) as LEVELS
from parent p
If the parent entity also contains a list of children (bi-directional association), you can use criteria to return the count of children as follows:
Criteria criteria = hibernateSessionHelper.getSessionFactory().getCurrentSession().createCriteria(Parent.class);
ProjectionList projList = Projections.projectionList();
projList.add(Projections.countDistinct("children.id"));
projList.add(Property.forName("field1").group());
projList.add(Property.forName("field2").group());
projList.add(Property.forName("field3").group());
.
.
.
criteria.createAlias("children", "children", CriteriaSpecification.LEFT_JOIN);
criteria.setProjection(projList);
List<Object[]> results = crit.list();
Got it to work doing this. Not very dynamic but it will work.
@Basic
@Column(name = "LEVEL")
@Formula(value="(SELECT count(*) FROM BadgeLevels bl WHERE bl.badgeid = this_.id)")
public long getLevel() {
return level;
}
Define an object field mapping like below. Then when you query your Parent objects, each object should have a field of type list called children that you can call size on.
<class name="Person">
<id name="id" column="id">
<generator class="native"/>
</id>
<set name="children">
<key column="parentId"
not-null="true"/>
<one-to-many class="Child"/>
</set>
</class>
<class name="Child">
<id name="id" column="childId">
<generator class="native"/>
</id>
</class>
You might be able to do it by some kind of projection.
See the tutorial on Hibernate projections. You are probably interested in the sqlProjection method in the Projections class for the sub-query.
List results = session.createCriteria(Parent.class, "p")
.setProjection(Projections.projectionList()
.add(Projections.property("field1"))
.add(Projections.property("field2"))
.add(Projections.property("field3"))
.add(Projections.sqlProjection("select count(*) from child where child.parentid = p.id"), new String[] {"LEVELS"}, new Type[] {Hibernate.INTEGER})
).list();
count depends on another relation. If you use the Hibernate Association mappings, then you can map a list of Child objects and just use the Java size method.