[FIXED] ManyToOne with JoinTable relationship doesn't work properly

Issue

I have a departement entity, a departement can have at most one address, an adress which is also an entity, can be shared between many departments.

I decided for the sake of practicing ManyToOne relationship in hibernate, to have a JoinTable, here is my entities definition:

import lombok.Getter;
import lombok.Setter;

import javax.persistence.*;

@Getter
@Setter
@Entity(name = "Address")
public class Address {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "name")
    private String city;
} 

The Address entity:

@Getter
@Setter
@Entity(name = "department")
public class Department {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @ManyToOne
    @JoinColumn(name = "person_id")
    private Person manager;


    // many departments can have the same address
    // one department should have at most one address
    // todo not working as expected, we are not fetching the address of the department
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinTable(name = "department_address",
            joinColumns = @JoinColumn(name = "address_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "department_id", referencedColumnName = "id"))
    private Address address;
}

The Person entity (just added for the completness of the example):

@Getter
@Setter
@Entity(name = "person")
public class Person {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "address_id")
    private Address address;
}

When retriving a departement from the database using spring data jpa, it's like we don't fetch the address data associated with the department.

Here is the content of the database:

Table department

id	name	person_id
23	IT	3

Table address

id	name
2	Paris
3	kharajiv

Table department_address

id	address_id	department_id
2	2	23

The repository definition:

public interface DepartmentRepository extends JpaRepository<Department, Long> {}

When looking for a given department, for my example, it is the department 23, departments/23:

Department department = departmentRepository.findById(id).get();
Address address = department.getAddress();
System.out.println("address = " + address);

The address is always null, even if for the department 23, there is a row in the table department_address, and here is the output of the department

System.out.println("department = " + department);
department = Department(id=23, name=IT, manager=Person(id=3, name=hamida, address=Address(id=3, city=kharajiv)), address=null)

Here is the printed sql query:

Hibernate: select department0_.id as id1_1_0_, department0_.person_id as person_i3_1_0_, department0_.name as name2_1_0_, department0_1_.department_id as departme1_2_0_, person1_.id as id1_3_1_, person1_.address_id as address_3_3_1_, person1_.name as name2_3_1_, address2_.id as id1_0_2_, address2_.name as name2_0_2_, address3_.id as id1_0_3_, address3_.name as name2_0_3_ from department department0_ left outer join department_address department0_1_ on department0_.id=department0_1_.address_id left outer join person person1_ on department0_.person_id=person1_.id left outer join Address address2_ on person1_.address_id=address2_.id left outer join Address address3_ on department0_1_.department_id=address3_.id where department0_.id=?

Solution

left outer join Address address3_ on department0_1_.department_id=address3_.id whe

Maybe I see wrong but deparment id and address id can not match. It has to be deparment0_1.address_id = address3_.id

Can you try to change inversecolumn to address_id

joinColumns = @JoinColumn(name = "deparment_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "address_id", referencedColumnName = "id"))
    private Address address

Answered By - Gurkan İlleez
Answer Checked By - Clifford M. (JavaFixing Volunteer)