Hibernate query with fetch question

Question

I have a 2 entities in a One-To-Many relationship:

OfficeView.java:

public class OfficeView implements java.io.Serializable {

    private Integer officeId;
    private String addr1;
    private String city;
    private String state;
    private String zip;
    private List<Devices> devices;

getters and setters

    @OneToMany(mappedBy = "officeView", fetch = FetchType.EAGER)
    public List<Devices> getDevices() {
     return devices;
    }

    public void setDevices(List<Devices> devices) {
     this.devices = devices;
    }

}

Devices.java:

public class Devices implements java.io.Serializable {

    private Integer devId;
    private String devName;
    private Date lastUpdate;
    private OfficeView officeView;

getters and setters

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "office_id")
    public OfficeView getOfficeView() {
     return officeView;
    }

    public void setOfficeView(OfficeView officeView) {
     this.officeView = officeView;
    }

}

For some devices, in the table there are 2 almost identical entries except for lastUpdate. I always want to retrieve the most recent entry in the table. And only that entry.

For example you have 2 entries for this device:

SSA-PITTSB-PA-NEF82 2009-10-12 23:51:07 SSA-PITTSB-PA-NEF82 2009-10-15 14:19:35

When I load the OfficeView instance for the office containing this (and other devices) I only want to fetch the most recent of these 2 devices. In the database, I can get that short list from the devices table with this SQL query:

select t.* from
  (select dev_id, dev_name, max(last_update) maxValue 
from devices
  group by dev_name) x
join devices t on x.dev_name=t.dev_name
and x.maxValue=t.last_update

SSA-PITTSB-PA-NEF82 2009-10-15 14:19:35

This is a seam application and OfficeViewHome.java, from which the query is called, extends EntityHome. I think the correct way to do this is to overload loadInstance with a customized query.

I just have no idea how to construct the query. How do I do this?

I know how to write the join query in SQL to join the oFfice_view and devices tables and get all the correct data. But I can't use this query to load the instance (as in createNativeQuery) because the data from the devices table needs to be loaded as a list of Devices objects.

I really hope this makes sense because I am utterly stumped.

april26

Answer 1

You'll have to override collection loader - be aware that this is a Hibernate extension to JPA standard. You can use an SQL query to do this instead of writing HQL - indeed, writing HQL may be somewhat troublesome in this scenario.

@Loader(namedQuery = "latestDevices")
@NamedNativeQuery(name="latestDevices", query="...", resultClass = Devices.class)
@OneToMany(mappedBy = "officeView", fetch = FetchType.EAGER)
public List<Devices> getDevices() {
  return devices;
}

The actual query is the one you wrote above with additional condition on office_id, whose parameter value is going to be supplied by Hibernate:

select t.* from
 (select dev_id, dev_name, max(last_update) maxValue 
    from devices
   group by dev_name) x
  join devices t on x.dev_name=t.dev_name
   and x.maxValue=t.last_update
   and t.office_id = ?

If the names are not unique throughout you'll have to rewrite the above to explicitly join office on id for the inner select because you can't repeat the ? placeholder.

Be very careful with updating / deleting Devices through collection on Office, Hibernate attempts to optimize certain operations and may end up deleting devices for given Office that weren't retrieved by above select.