CODEWITHSUNDEEP
hibernatecountsubqueryhibernate-criteria
Mike Flynn
Mike Flynn

Reputation: 24325

Hibernate Criteria and Count Column

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

Upvotes: 9

Views: 12206

Answers (4)

EkcenierK
EkcenierK

Reputation: 1439

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

Upvotes: 5

Mike Flynn
Mike Flynn

Reputation: 24325

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

Upvotes: 4

user276055
user276055

Reputation:

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

Upvotes: 0

user276055
user276055

Reputation:

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>

Upvotes: 0

Related Questions