CODEWITHSUNDEEP
javaspringhibernatehibernate-criteria
grteibo
grteibo

Reputation: 617

Hibernate Criteria most recent date field of Collection

I have Laboratories with a collection of states, here are my simplified Classes:

    LabState {
        private Integer id;
        private State state;
        private Date date;
        private String comments;
    }

    State{
        private Integer id;
        private String name;

    }

    Lab{
        private Integer id;
        private List<LabState> states = new ArrayList<LabState>();
        private Date date;
        private String name
    }

I want to get via Hibernate Criteria the Last State from a specific laboratory. in mySql this work fine:

SELECT ls.* FROM labs_states les INNER JOIN
    ( SELECT idLab, idLabState, MAX(date) AS lastDate FROM labs_states GROUP BY idLab)
        groupedLabs ON les.idLab = groupedLabs.idLab  AND les.date = groupedLabs.lastDate

Edit: SearchMethod:

   public List<Labs> search(LabSearch labSearch) {

            SessionFactory sessionFactory = hibernateTemplate.getSessionFactory();
            Session session = sessionFactory.openSession();

            Criteria criteria = session.createCriteria(Lab.class);

            criteria.add(Restrictions.like("name", "%" + labSearch.getName() + "%"));

            if(labSearch.getState() != null){
                criteria.createCriteria("states").add(Restrictions.eq("state", labSearch.getState())).addOrder(Order.asc("date")).list().get(0);


            return (List<Lab>) criteria.list();
}

I don't nkow how to do this with Criteria, or if there is any other way to do

thanks

Upvotes: 1

Views: 6966

Answers (1)

Firo
Firo

Reputation: 30813

to get the last state of the given lab

Criteria criteria = session.createCriteria(Lab.class)
    .add(Restrictions.like("name", "%" + labSearch.getName() + "%"));
    .createAlias("states", "labstate")
    .createAlias("labstate", "state")
    .addOrder(Order.desc("date"))
    .setProjection(Projections.list()
        .add(Projections.property("labstate.id"), "id")
        .add(Projections.property("labstate.date"), "date")
        .add(Projections.property("labstate.comments"), "comments")
        .add(Projections.property("state.name"), "state"))
    .setResultTransformer(Transformers.aliasToBean<LabstateDto>())
    .setMaxResults(1);

return (List<LabstateDto>)criteria.list();

if you want more than one lab with its last state returned you'll need another query which is not that easy because you have the backreference from Labstate to Lab ony in db and not in class.

Update: in response to your comment an alternative query

Criteria criteria = session.createCriteria(Lab.class, "lab")
    .add(Restrictions.like("name", "%" + labSearch.getName() + "%"));
    .createAlias("states", "labstate")
    .add(Subqueries.proptertyEq("labstate.Id", DetachedCriteria.for(Lab.class)
        .add(Restrictions.propertyEq("id", "lab.id"));
        .createAlias("states", "lstate")
        .createAlias("labstate.state", "state")
        .add(Restrictions.eq("name", state));
        .addOrder(Order.desc("labstate.date"))
        .setProjection(Projection.property("labstate.Id"))
        .setMaxResults(1)
     );

return (List<Lab>)criteria.list();

Upvotes: 1

Related Questions