0

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.

Not a real meerkat
  • 5,604
  • 1
  • 24
  • 55
  • If your view is only for reading porpouses, maybe you can map ALL the columns as ID/PK. Don't know you exact view, so I can't be sure if this will work for you, but we used this as a workaround some time ago. – Germán Aug 17 '17 at 18:07
  • @German, thanks for your comment. Unfortunately this fails for the same reason my second example does: Any `null` column will make the query return a `null` object. – Not a real meerkat Aug 17 '17 at 18:56
  • Seems a hibernate limitation. see https://stackoverflow.com/questions/3963963/null-list-returned-from-hibernate-query-with-embedded-id/9753759#9753759 – Chris Aug 17 '17 at 19:23

0 Answers0