I'm working on a hibernate entity mapping for a database view; when I do a criteria query against it, hibernate is generating bad SQL. Any help figuring out what the problem is with my mapping would be greatly appreciated!
I have two mapped entities which I am trying to grab from a database view; the view has no other columns, just the FK of each entity. One of these FK's can be treated as a primary key, since the view has a row for each primary entity. So my DB schema for the view looks like:
primary(primary_id, some_other_fields)
history(history_id, primary_id, some_other_fields)
view_latest_status_history(primary_id, history_id)
Note the view is used because I want to pick out only the latest history for each primary, not all mapped history records. Here is the object I am using for the view, with entity annotations:
@Entity
@org.hibernate.annotations.Entity(dynamicUpdate = true)
@Table(name = "view_latest_status_history")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class ViewLatestStatusHistoryRow implements Serializable {
private Primary primary;
private History history;
/**
* @return Returns the history.
*/
@ManyToOne(cascade = { CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REMOVE }, fetch = FetchType.LAZY)
@JoinColumn(name = "history_id", nullable = true)
@AccessType("field")
public History getHistory() {
return history;
}
//equals() and hashCode() implementations are omitted
/**
* @return Returns the primary.
*/
@Id
@ManyToOne(cascade = { CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REMOVE }, fetch = FetchType.LAZY)
@JoinColumn(name = "primary_id", nullable = false)
@AccessType("field")
public Primary getPrimary() {
return primary;
}
}
Both the Primary and History objects have complete, working entity annotations.
My criteria setup:
criteria.add(Restrictions.in("primary", [collection of primary objects]));
criteria.setFetchMode("primary", FetchMode.JOIN);
criteria.setFetchMode("history", FetchMode.JOIN);
And the (wrong) generated SQL:
select this_.primary as primary78_1_, this_.primary_id as prim2_78_1_, primary2_.history_id as unique1_56_0_, ...history fields
from DB_CATALOG.dbo.view_latest_status_history this_
left outer join DB_CATALOG.dbo.history primary2_ on this_.primary_id=primary2_.primary_id
where this_.specChange in (?, ?...)
I might've mucked up a few things when editing out the specifics of our project's DB schema, but the point is the first field in the 'select' clause is wrong:
this_.primary (view_latest_status_history.primary) is not a field; the field should be called primary_id. I think this may have something to do with the @Id annotation on the primary field? Any ideas how to fix this? If I remove the @Id, I get an error telling me that the entity has no primary key.
Update:
I no longer map the view as a field using a join table notation (as suggested below). The annotations have been revised, as follows. This solution works correctly in HQL, and generates the expected schema when hbm2ddl is enabled, but I have not re-tested it using the criteria query.
@Entity
@Table(name = "view_latest_status_history")
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class ViewLatestStatusHistoryRow implements Serializable {
private String id;
private Primary primary;
private History history;
/**
* @return Returns the history.
*/
@OneToOne(optional = true)
@JoinColumn(name = "history_id", nullable = true)
@AccessType("field")
public History getHistory() {
return history;
}
//equals() and hashCode() implementations are omitted
@Id
@Column(name = "primary_id", nullable = false)
@Override
@AccessType(value = "field")
public String getId() {
return id;
}
/**
* @return Returns the primary.
*/
@PrimaryKeyJoinColumn(name = "primary_id", referencedColumnName = "unique_id")
@OneToOne(optional = false)
@AccessType("field")
public Primary getPrimary() {
return primary;
}
}