views:

1031

answers:

1

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;
    }
}
+1  A: 

It most certainly is due to @Id annotation - primary_id is NOT a primary key in this case. Nor can you realistically have @Id and @ManyToOne on the same property.

Let me ask you this - why are you mapping ViewLatestStatusHistoryRow as an entity to begin with? It's not like you ever going to persist it. Consider mapping your latest history entry directly (as read-only) on primary (as many-to-one) and using your view as join table.

ChssPly76
I will try mapping it to the primary, and mark as answer if it works! Thanks for the quick reply. Do you know how I would handle this in the general case for a view with various other fields? And also how I can ensure that the property is updated on the primary object when a new (latest) history is added?
RMorrisey
Property won't have to be updated because you won't be maintaining it. You need to map it using JoinTable: http://docs.jboss.org/hibernate/stable/annotations/reference/en/html/entity.html#d0e1177As far as "view with fields" goes, if you need to map it, you need to map it as entity. That means having a **real** primary key rather than something that happens to be unique :-) You can use composite id to map PK that consists of multiple columns, but that may have ugly side effects. Look at Hibernate docs for composite identifiers for more details.
ChssPly76
This lets me eliminate the n+1 selects problem and speed up batch processing substantially for a couple thousand primaries. Thanks =)
RMorrisey