(Note: this is also posted on stackoverflow, at:
http://stackoverflow.com/questions/1646 ... h-sqlquery)
I have a mapped entity with a property "latestHistory", which is mapped through a join table, like:
Code:
class Record {
@OneToOne(cascade = { CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REMOVE }, fetch = FetchType.LAZY, optional = true)
@JoinTable(name = "latest_history_join_view", joinColumns = { @JoinColumn(name = "record_id") }, inverseJoinColumns = { @JoinColumn(name = "history_id") })
@AccessType("field")
public History getLatestHistory() { ... }
}
The mapping works correctly when I call myRecord.getLatestHistory().
I have a complex native SQL query, which returns a batch of Records, and joins on the History for each record using the join table. I want to return Record entites from the query, and have the History objects populated in the result. My attempt looks like this:
Code:
StringBuffer sb = new StringBuffer();
sb.append("select {r.*}, {latestHistory.*}");
sb.append(" from record r");
sb.append(" left join latest_history_join_view lh on lh.record_id = r.record_id");
sb.append(" left join history latestHistory on latestHistory.history_id = lh.history_id");
SQLQuery query = session.createSQLQuery(sb.toString());
query.addEntity("r", Record.class).addJoin("latestHistory", "r.latestHistory");
When I do this, it generates a query like:
Code:
select
r.record_id, r.name...,
r_1.history_id, --this part is wrong; there is no such alias r_1
latestHistory.history_id, latestHistory.update_date, ...
from record r
left join latest_history_join_view lh on lh.record_id = r.record_id
left join history latestHistory on latestHistory.history_id = lh.history_id
How can I get it to join correctly and fetch my association, without messing up the select list?
Login
FAQ
Search