trying to understand how @JoinTable and @JoinColumn works

Question

I am learning hibernate and stuck a bit with the below problem

have two tables

CREATE TABLE department (
 department_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
 caption varchar(255) DEFAULT NULL) ENGINE=InnoDB;
CREATE TABLE employee (
 employee_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
 fio varchar(255) DEFAULT NULL,
 fk_department_id int(11) NOT NULL,
 FOREIGN KEY (fk_department_id) REFERENCES department (department_id) 
) ENGINE=InnoDB ;

and two classes (in the first class commented out code looks like working solution)

@Entity
@Table(name = "department")
public class Department {
....
    @OneToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "employee", joinColumns = {
            @JoinColumn(name = "fk_department_id", referencedColumnName = "department_id") })
    /*
     * @OneToMany(fetch = FetchType.LAZY, mappedBy = "department", cascade =
     * CascadeType.ALL)
     */
    public Set<Employee> getEmployies() {
        return employees;
    }

---

@Entity
@Table(name = "employee")
public class Employee {
......
    @ManyToOne
    @JoinColumn(name = "fk_department_id")
    public Department getDepartment() {
        return department;
    }

this results into

INFO: HHH000423: Disabling contextual LOB creation as JDBC driver reported JDBC version [3] less than 4
Exception in thread "main" org.hibernate.MappingException: Foreign key (FK3cspe1b06hmsik5l8y1i11xmd:employee [employies_employee_id])) must have same number of columns as the referenced primary key (employee [fk_department_id,employies_employee_id])
    at org.hibernate.mapping.ForeignKey.alignColumns(ForeignKey.java:148)
    at org.hibernate.mapping.ForeignKey.alignColumns(ForeignKey.java:130)

Please help me to understand why this doesn't work

Answer 1

The following should work just fine. You'll notice I am not specifying any join column relations because I am allowing Hibernate to generate those automatically for me.

@Entity
public class Department {
  @OneToMany
  @JoinTable(name = "department_employees")
  private List<Employee> employees;
}

@Entity
public class Employee {
  @ManyToOne
  private Department department;
}

But lets assume you want to be explicit about the join columns.

@Entity
public class Department {
  @Id
  @Column(name = "department_id")
  private Integer id;
  @OneToMany
  @JoinTable(
     name = "department_employees",
     joinColumns = @JoinColumn(name = "department_id"),
     inverseJoinColumns = @JoinColumn(name = "employee_id"))
  private List<Employee> employees;
}

@Entity
public class Employee {
  @Id
  @Column(name = "employee_id")
  private Integer id;
  @ManyToOne
  @JoinTable(
     name = "department_employees",
     joinColumns = @JoinColumn(name = "department_id", insertable = false, updatable = false),
     inverseJoinColumns = @JoinColumn(name = "employee_id", insertable = false, updatable = false))
  private Department department;
}

The key points to take away from this are:

• The name of the join table specifies the middle table that maintains the relationship between the Department and Employee entities. It should not refer to the Employee table as your code illustrates.
• The joinColumns attribute represents the primary key attributes of the containing entity, in this case that is Department, hence I used department_id.
• The inverseColumns attribute represents the primary key attributes of the associated entity, in this case that is Employee, hence I used employee_id.

Update:

If you'd like to eliminate the @JoinTable and merely maintain the relationship between Department and Employee, you'd change your mappings as follows:

@Entity
public class Department {
  @OneToMany(mappedBy = "department")
  private List<Employee> employees;
}

@Entity
public class Employee {
  @ManyToOne
  private Department department;
}

Hope that helps.