How do I join tables on non-primary key columns in secondary tables?

Question

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)

Answer 1

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.