Hibernate Criteria: counting the most recent entries, grouped per attribute

Question

This is a knot I find quite hard to untie.

My requirement is to use the Criteria API.

I have a Team class defined as follows:

@Entity
@Table(name="TEAMS")
public class Team{

    private Set<State> states = new HashSet<State>();

    @OneToMany(cascade=CascadeType.ALL, orphanRemoval=true, mappedBy="team")
    public Set<State> getStates() {
        return states;
    }
}

A Team has a list of States because it transits through different states during its lifecycle. So the State class is defined as follows.

@Entity
@Table(name="STATES")
public class State{

    private Team team;
    private StateType type;
    private Date date;

    @ManyToOne
    @JoinColumn(name="TEAM_ID")
    public Team getTeam() {
        return team;
    }

    @Column(name="STATE_TYPE")
    public StateType getType() {
        return type;
    }

    @Temporal(TemporalType.DATE)
    @Column(name="DATE")
    public Date getDate() {
        return date;
    }
}

Now, what I want is to count the number of teams which are currently in a given state. The current state is the one with the most recent date value.

I tried with the following statement, but with no luck.

public Long getStateTypeNumber(StateType type)
{
    Session session = sessionFactory.getCurrentSession();
    Criteria criteria = session.createCriteria(State.class);
    criteria.add(Restrictions.eq("type", type));
    criteria.addOrder(Order.desc("date"));
    criteria.setProjection(Projections.distinct(Projections.property("team")));
    criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
    criteria.setProjection(Projections.rowCount());
    return (Long) criteria.uniqueResult();
}

can anyone provide some help? Thanks.

score 0 · Answer 1 · answered Mar 06 '15 at 12:57

0

Well from what I see you're missing a join on your Team. But I would try it coming from Team and joining your State class.

Session session = sessionFactory.getCurrentSession();
Criteria criteria = session.createCriteria(Team.class);
criteria.createAlias("states", state);
criteria.add(Restrictions.eq("state.type", type));
criteria.setProjection(Projections.rowCount());
(Long) criteria.uniqueResult();

Didn't test it because I don't have the enviroment with me, hope it works.

answered Mar 06 '15 at 12:57

Jan

1,004
6
23

thanks for your answer. The problem here is that it's still missing the selection of the **most recent** state type. For each Team, I need to find the most recent state type, then I must count the number of these state types. – MaVVamaldo Mar 06 '15 at 16:39
1

Oh sorry, I now get what you want. Does [this](http://stackoverflow.com/questions/19124301/hibernate-criteria-query-with-subquery-joining-two-columns) help you? – Jan Mar 06 '15 at 23:38

MaVVamaldo · Accepted Answer · 2015-03-09T12:23:45.687

I write in the following the solution I found for this problem. First, I have to retrieve a view of my State table containing the rows of the most recent states grouped by team, then I have to add restrictions for the type. The trick here is to generate a subquery with a DetachedCriteria to be used inside a Criteria (thanks to @Jan for pointing it out).

public Long getStateTypeNumber(StateType type)
{   
    DetachedCriteria maxDateQuery = DetachedCriteria.forClass(State.class);     
    maxDateQuery.setProjection(Projections.projectionList().
            add(Projections.max("date")).
            add(Projections.groupProperty("team")));

    Session session = sessionFactory.getCurrentSession();
    Criteria criteria = session.createCriteria(State.class);
    criteria.add(Subqueries.propertiesIn(new String[] {"date", "team"}, maxDateQuery));
    criteria.add(Restrictions.eq("type", type));
    criteria.setProjection(Projections.rowCount());
    return (Long) criteria.uniqueResult();      
}

Hibernate Criteria: counting the most recent entries, grouped per attribute

2 Answers2