CODEWITHSUNDEEP
javahibernatejpa
davioooh
davioooh

Reputation: 24706

How to map two tables to one entity using foreign-key?

I have a problem very similar to this: How do I join tables on non-primary key columns in secondary tables? But I'm not sure if I can apply the same solution.

I have two tables like these:

CREATE TABLE CUSTOMER
(
    CUSTOMER_ID INTEGER NOT NULL,
    DETAIL_ID INTEGER NOT NULL,
    PRIMARY KEY( CUSTOMER_ID ),
    CONSTRAINT cust_fk FOREIGN KEY( DETAIL_ID ) REFERENCES DETAILS( DETAIL_ID )
)

CREATE TABLE DETAILS
(
    DETAIL_ID INTEGER NOT NULL,
    OTHER INTEGER NOT NULL,
    PRIMARY KEY( DETAIL_ID )
)

I'd like to map these tables to a single class called Customer, so I have:

@Entity
@Table(name = "CUSTOMERS")
@SecondaryTable(name = "DETAILS", pkJoinColumns=@PrimaryKeyJoinColumn(name="DETAIL_ID"))
public class Customer {
    @Id
    @GeneratedValue
    @Column(name = "CUSTOMER_ID")
    private Integer id;

   @Column(table = "DETAILS", name = "OTHER")
   private Integer notes;

   // ...
}

but this works only if DETAIL_ID matches CUSTOMER_ID in the primary table.

So my question is: how can i use a foreign-key field in my primary table to join on the primary-key of the secondary table?

UPDATE I tried to set:

@SecondaryTable(name = "DETAILS", pkJoinColumns=@PrimaryKeyJoinColumn(name="DETAIL_ID", referencedColumnName="DETAIL_ID"))

but when I run the application I get this exception:

org.hibernate.MappingException: Unable to find column with logical name: DETAIL_ID in org.hibernate.mapping.Table(CUSTOMERS) and its related supertables and secondary tables

Upvotes: 3

Views: 8743

Answers (2)

Alberto Rechy
Alberto Rechy

Reputation: 157

For anyone looking for an answer to this, using @SecondaryTable is not the way to join two tables with non-primary key columns, because Hibernate will try to assosiate the two tables by their primary keys by default; you have to use @OneToMany review http://viralpatel.net/blogs/hibernate-one-to-many-annotation-tutorial/ for a solution, here's a code snippet in case that url stops working:

Customer Class:

@Entity
@Table(name="CUSTOMERS")
public class Customer {

    @Id
    @GeneratedValue
    @Column(name="CUSTOMER_ID")
    private Integer id;

    @ManyToOne
    @JoinColumn(name="DETAIL_ID")
    private Details details;

    // Getter and Setter methods...
}

Details Class:

@Entity
@Table(name="DETAILS")
public class Details {

    @Id
    @GeneratedValue
    @Column(name="DETAIL_ID")
    private int detailId;

    @Column(name="OTHER")
    private String other;

    @OneToMany(mappedBy="details")
    private Set<Customer> customers;

    // Getter and Setter methods...
}

This is easily accessible through hibernate with the following code:

Session session = HibernateUtil.getSessionFactory().openSession();
Query query = session.createQuery("select id, details.other from Customer");

I hope this helps anyone out there spending hours searching for a way to achieve this like I did.

Upvotes: 3

Perception
Perception

Reputation: 80633

You can use the referenceColumnName attribute of the @PrimaryKeyJoinColumn annotation to define the join column to the referenced table. In fact, by combining use of name/referencedColumnName you can join on arbitrary on both sides, with the constraint that if duplicates are found your ORM provider will throw an exception.

Upvotes: 0

Related Questions