I have an entity OfficeView. Each OfficeView has many Devices.
Code:
public class OfficeView implements java.io.Serializable {
private Integer officeId;
private List<Devices> devices;
@OneToMany(mappedBy = "officeView", fetch = FetchType.EAGER)
public List<Devices> getDevices() {
return devices;
}
public void setDevices(List<Devices> devices) {
this.devices = devices;
}
The Devices data sometimes contain more than one entry per device, and in this case I only want to fetch the most recent device. I can do this using this very complex sql query (mysql):
Code:
select t.* from
(select dev_id, dev_name, max(last_update) maxValue from devices
where dev_name like 'ssa-%' group by dev_name) x
join devices t on x.dev_name=t.dev_name
and x.maxValue=t.last_update
I can join this with the office_view table like this
Code:
select o.addr1, o.city, o.state, o.site_code, d.dev_name
from office_view o join
(select t.* from
(select dev_id, dev_name, dev_serial_num,
dev_platform, max(last_update)
maxValue from devices
where dev_name like 'ssa-%' group by dev_name) x
join devices t on x.dev_name=t.dev_name
and x.maxValue=t.last_update) d
on d.office_id=o.office_id
where o.office_id=:id
How the heck do I do this in Hibernate?
Login
FAQ
Search