How to design a Hibernate Query for this case?

deararsaiuk · **Joined:** Sun Oct 11, 2009 11:48 am **Posts:** 2

In a MySQL DB / Table livedata_tbl has two columns: Device_Id (numeric) & Stored_At (Timestamp). Which represents Device POJO Class, which has deviceId as Integer and storedAt as Timestamp.

Records are stored into this database very frequently, and each Device_Id can repeat multiple times in this table.

But the requirement is, How to select each unique device_id with latest timestamp. This query has to written in Java.

In other words, we need to select unique Device Object based on their individual latest timestamp value.

Any thoughts?

Froestel · **Posted:** Sun Oct 11, 2009 4:54 pm

Hi deararsaiuk,

Pre: Using Device as the name of the entity that is mapped to table livedata_tbl.

Code:

session.createCriteria(Device.class)
  .setProjection(Projections.projectionList()
    .add(Projections.groupProperty("deviceId"))
    .add(Projections.max("storedAt"))
  ).list();

Don't know if it works - but it's at least close. Build some proper session and transaction management around and play with it. I'm sure, you'll figure it out.

CU
Froestel