Hibernate Criteria and Count Column

Question

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

Is your "where" clause supposed to be a "from"? – Feb 19 '11 at 19:22 — , Feb 19 '11 at 19:22
Thanks fixed that syntax error – Mike Flynn Feb 19 '11 at 19:28 — Mike Flynn, Feb 19 '11 at 19:28

score 5 · Answer 1 · answered Aug 12 '11 at 08:48

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();

score 4 · Accepted Answer · answered Feb 19 '11 at 22:21

4

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;
    }

answered Feb 19 '11 at 22:21

Mike Flynn

22,342
54
182
341

score 0 · Answer 3 · 2011-02-19T21:25:33.617

0

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();

edited Feb 19 '11 at 21:25

answered Feb 19 '11 at 19:32

I am going to try that but is there anyway not to use raw sql, and use a detached query somehow? – Mike Flynn Feb 19 '11 at 19:49
The tricky part seems to be that, your `count` depends on another relation. If you use the Hibernate [Association](http://docs.jboss.org/hibernate/core/3.3/reference/en/html/associations.html) mappings, then you can map a list of Child objects and just use the Java `size` method. – Feb 19 '11 at 19:59
See my other answer for the associations method. – Feb 19 '11 at 20:13
That original project gave me select * from ( select select count(*) from badgelevels bl where bl.badgeid = b.id from CISCO.BADGES this_ where this_.ACTIVE=? ) where rownum <= ? – Mike Flynn Feb 19 '11 at 20:15
How can I return the rest of the columns from Parent.class with the count? Am I missing a projection? – Mike Flynn Feb 19 '11 at 20:39
You can add the additional columns by chaining calls on the `projectionList`. I've amended my original answer for this. – Feb 19 '11 at 21:24
Yea but how do you do it in one call because I might add more properties in the future and don't want to type out 20 columns to bring back. – Mike Flynn Feb 19 '11 at 22:22

score 0 · Answer 4 · answered Feb 19 '11 at 20:11

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>

I am returning a list of parents, and I don't want to return all the join childs of that object. I just want the count of the children with the parent columns. — Mike Flynn, Feb 19 '11 at 20:16

Hibernate Criteria and Count Column

4 Answers4