CODEWITHSUNDEEP
javahibernatejpahibernate-mapping
cbmeeks
cbmeeks

Reputation: 11420

Why does my OneToOne mapping through a JoinTable not work?

I've searched but cannot seem to find the answer.

I have two tables:

select ts_id, tsjoin_id, workdate from TimeSheets
select e_id, lastname from Employees

I also have a join table:

TSJoin
tsjoin_id, employee_id

There is only one Employee to a TimeSheet. So with any given TimeSheet entity, I'd expect to be able to:

TimeSheet ts = tsService.getTimeSheet(123);
String lastName = ts.getEmployee().getLastName();

In SQL, to get the employee of a TimeSheet:

select e.lastname from TimeSheets t
    join TSJoin x on (x.tsjoin_id = t.tsjoin_id)
    join Employees e on (e.e_id = x.employee_id)
where t.ts_id = 123

In my Hibernate mapping, I have:

@OneToOne
@JoinTable(
        name = "TSJoin",
        joinColumns = {
                @JoinColumn(name = "tsjoin_id", nullable = false)
        },
        inverseJoinColumns = {
                @JoinColumn(name = "e_id", nullable = false)
        }
)

However, the SQL it's generating is:

select * from TimeSheet t
    left outer join TSJoin x on (t.ts_id = x.tsjoin_id)

Which returns null for the Employee.

It's taking the primary key of the TimeSheet and trying to match the primary key of the join table.

What am I doing wrong?

EDIT

I also want to state that I have only setup one direction at this point. Which is a TimeSheet -> Employee (OneToOne) and that Employee is not mapped to TimeSheet yet. Not sure if this makes a difference but I wanted to mention it.

EDIT 2 I also want to state that I believe the error might be because my join table does not contain a reference to the TimeSheet. And Hibernate is assuming that a join table is going to contain the primary keys of each entity involved (legacy database). I could probably create a mapping of TimeSheet -> JoinTable -> Employee and access it as: ts.getJoin().getEmployee() but that's pretty ugly.

Upvotes: 2

Views: 7149

Answers (2)

cbmeeks
cbmeeks

Reputation: 11420

I am almost 100% sure this problem is because the current JPA spec does not allow a non primary key column in the JoinTable. Our legacy database joins to columns that are not primary keys.

Upvotes: 2

JB Nizet
JB Nizet

Reputation: 692063

By default, a join table is assumed to containg the ID of the two joined entities.

In your case, it doesn't: the ID of the TimeSheet entity is mapped to the ts_id column, but you want the join table to have a column which is a foreign key to the tsjoin_id column instead of ts_id.

So you need to tell that to Hibernate. It can't guess it. And the javadoc of JoinColumn says:

referencedColumnName

(Optional) The name of the column referenced by this foreign key column. [...]

Default (only applies if single join column is being used): The same name as the primary key column of the referenced table.

So all you need is

@JoinTable(
    name = "TSJoin",
    joinColumns = {
            @JoinColumn(name = "tsjoin_id", nullable = false, referencedColumnName = "tsjoin_id")
    },
    inverseJoinColumns = {
            @JoinColumn(name = "e_id", nullable = false)
    }
)

Note that having one employee per timesheet isn't sufficient to make your association a OneToOne. To be a OneToOne, there should also be one timesheet per employee. If an emplyee has, in fact, several timesheets, tha association is a ManyToOne, not a OneToOne.

Upvotes: 2

Related Questions