I have a view on which the only possible candidate key may have a null value. Consider:
CREATE TABLE FOO (
FOO_ID NUMBER(10,0),
CONSTRAINT PK_FOO PRIMARY KEY (FOO_ID)
);
CREATE TABLE BAR (
BAR_ID NUMBER(10,0),
FOO_ID NUMBER(10,0),
CONSTRAINT PK_BAR PRIMARY KEY (BAR_ID),
CONSTRAINT FK_BAR_FOO FOREIGN KEY (FOO_ID) REFERENCES FOO(FOO_ID)
) ;
-- I'm interested in this view!
CREATE VIEW VW_FOO_BAR AS
SELECT FOO.FOO_ID, BAR.BAR_ID
FROM FOO
LEFT JOIN BAR ON FOO.FOO_ID = BAR.FOO_ID
Needless to say, this is only a MCVE. The actual DDL is much more complex.
Note that, in this view, BAR_ID
may be null
, since it's a LEFT JOIN
. But FOO_ID
may be repeated multiple times, so it can't be the key by itself. As such, the only possible candidate key is (FOO_ID, BAR_ID)
It's not possible to make any changes on the database or its views. Also, this view has some important information that is only accessible through it, so I have to use it.
Since it's a view, I will never be making insertions or updates on it. Only queries.
Question: Is there a way to map this view in JPA, without triggering the infamous "More than one row with the given identifier" exception?
Discarded solutions:
This implementation may return multiple rows with the same identifier, causing the above mentioned exception:
@Entity
@Table(name = "VW_FOO_BAR")
class VwFooBar {
@Id
@Column(name="FOO_ID")
private Long idFoo;
@Column(name="BAR_ID")
private Long idBar
}
This one will return null objects whenever idBar
is null
:
@Entity
@Table(name = "VW_FOO_BAR")
class VwFooBar {
@EmbeddedId
private VwFooBarPk pk;
}
@Embeddable
class VwFooBarPk {
@Column(name="FOO_ID")
private Long idFoo;
@Column(name="BAR_ID")
private Long idBar
}
I'm accepting any solution that allows me to query the view, altough I prefer to avoid pure JDBC, if possible.
I'm using JPA 1/Hibernate 3.3 with the org.hibernate.dialect.Oracle9iDialect
dialect.