views:

1574

answers:

3

I have a situation where I need to join tables on an object in an ORM class hierarchy where the join column is NOT the primary key of the base class. Here is an example of the table design:

CREATE TABLE APP.FOO
(
    FOO_ID INTEGER NOT NULL,
    TYPE_ID INTEGER NOT NULL,
    PRIMARY KEY( FOO_ID )
)

CREATE TABLE APP.BAR
(
    FOO_ID INTEGER NOT NULL,
    BAR_ID INTEGER NOT NULL,
    PRIMARY KEY( BAR_ID ),
    CONSTRAINT bar_fk FOREIGN KEY( FOO_ID ) REFERENCES APP.FOO( FOO_ID )
)

CREATE TABLE APP.BAR_NAMES
(
    BAR_ID INTEGER NOT NULL,
    BAR_NAME VARCHAR(128) NOT NULL,
    PRIMARY KEY( BAR_ID, BAR_NAME),
    CONSTRAINT bar_names_fk FOREIGN KEY( BAR_ID ) REFERENCES APP.BAR( BAR_ID )
)

And here are the mappings (getters and setters eliminated for brevity

@Entity
@Table(name = "FOO")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "TYPE_ID", discriminatorType =    javax.persistence.DiscriminatorType.INTEGER)
public abstract class Foo {
    @Id
    @Column(name = "FOO_ID")
    private Long fooId;
}

@Entity
@DiscriminatorValue("1")
@SecondaryTable(name = "BAR", pkJoinColumns = { @PrimaryKeyJoinColumn(name = "FOO_ID", referencedColumnName = "FOO_ID") })
public class Bar extends Foo{
    @Column(table = "BAR", name = "BAR_ID")
    Long barId;
 }

How can I add the mapping for BAR_NAMES given that its join column is not FOO_ID, but BAR_ID?

I Have tried the following:

@CollectionOfElements(fetch = FetchType.LAZY)
@Column(name = "BAR_NAME")
@JoinTable(name = "BAR_NAMES", joinColumns = @JoinColumn(table = "BAR", name = "BAR_ID", referencedColumnName="BAR_ID"))
List<String> names = new ArrayList<String>();

This fails because the SQL for retrieving the Bar object tries to get a BAR_ID value from the FOO table. I have also tried replacing the JoinTable annotation with

@JoinTable(name = "BAR_NAMES", joinColumns = @JoinColumn(name = "BAR_ID"))

This produces no SQL error, but also retrieves no data because the query against BAR_NAMES is using the FOO_ID as the join value instead of the BAR_ID.

For testing purposes, I've populated the DB with the following commands

insert into FOO (FOO_ID, TYPE_ID) values (10, 1);
insert into BAR (FOO_ID, BAR_ID) values (10, 20);
insert into BAR_NAMES (BAR_ID, BAR_NAME) values (20, 'HELLO');

Many solutions which appear to work will return an empty collection when getting Foo object for ID 10 (as opposed to a collection containing 1 name)

A: 

Not sure how to do this with JPA/Annotations, but with Hibernate XML mapping files it would be something like:

<class name="Bar" table="BAR">
    <id name="id" type="int">
        <column name="BAR_ID"/>
        <generator class="native"/>
    </id>
    <set name="barNames" table="BAR_NAMES">
        <!-- Key in BAR_NAMES table to map to this class's key -->
        <key column="BAR_ID"/> 
        <!-- The value in the BAR_NAMES table we want to populate this set with -->
        <element type="string" column="BAR_NAME"/>
    </set>
</class>
matt b
I believe this is equivalent to @JoinTable(name = "BAR_NAMES", joinColumns = @JoinColumn(name = "BAR_ID")), which does not work because it uses the value of FOO_ID to join the BAR_NAMES table.
Jherico
Sorry, I misread what you were saying. I can't map the BAR_ID as the primary key for the Bar class because its a child of the Foo object using either single table inheritance with secondary tables or using joined subclass inheritance.
Jherico
+1  A: 

You will not be able to do what you want. @CollectionOfElements (and @OneToMany, for that matter) are always mapped via owner's entity primary key.

The way you map Foo / Bar inheritance is rather strange as well - they clearly are not in the same table; seems like using JoinedSubclass would be a better approach. Keep in mind that still won't help you map bar_names to bar_id because primary key value is shared among the hierarchy (even though column name may be different for subclass).

A possible alternative is to use @OneToOne mapping between Foo and Bar instead of inheritance. That's the only way you will be able to map bar_names to bar_id and the most appropriate mapping for you table structure (though, perhaps, not for your domain model).

ChssPly76
In the live environment Foo is a base class to many sub-classes with different type ids. We've used both the joined subclass model and the single class model with secondary tables and found both to have their strengths and weaknesses. Changing the hierarchy is not an option.
Jherico
To be honest, once you augment "table-per-hierarchy" approach with secondary tables you lose the only advantage it has (somewhat faster performance) compared with "table-per-class" approach but that's not relevant to your question. As I said, you will not be able to map your collection the way you want without changing either hierarchy mapping or your database structure (e.g. forgoing `bar_id` and using `foo_id` as PK throughout the entire hierarchy).
ChssPly76
The referencedColumnName is specifically there to address this kind of issue. If I create a table TYPE_NAMES which joins on the TYPE_ID of the FOO table, that works as expected. Failure to allow mapping on a column in a secondary table is either a bug or a design limitation. I'mt trying to determine which.
Jherico
`referencedColumnName` addresses a completely different issue - it allows you to specify the name of the column on the other end of "to one" association (be it an actual @OneToOne mapping, @SecondaryTable mapping or ManyToOne via composite key).
ChssPly76
Uh, except like I said, it works if I'm using a column in the primary table.
Jherico
A: 

I was able to find a solution to this. If you map the Bar class like so

@Entity
@DiscriminatorValue("1")
@SecondaryTable(name = "BAR", pkJoinColumns = { @PrimaryKeyJoinColumn(name = "FOO_ID", referencedColumnName = "FOO_ID") })
public class Bar extends Foo {
    @OneToOne
    @JoinColumn(table = "BAR", name = "BAR_ID")
    MiniBar miniBar;
}

and add the following class

@Entity
@SqlResultSetMapping(name = "compositekey", entities = @EntityResult(entityClass = MiniBar.class, fields = { @FieldResult(name = "miniBar", column = "BAR_ID"), }))
@NamedNativeQuery(name = "compositekey", query = "select BAR_ID from BAR", resultSetMapping = "compositekey")
@Table(name = "BAR")
public class MiniBar {
    @Id
    @Column(name = "BAR_ID")
    Long barId;
}

You can then add any sort of mapping you want to the MiniBar class as if barId were the primary key, and then further make it available in the outer Bar class.

Jherico