add more columns in join table using spring, hibernate, JPA Annotations

Question

Can we add more columns in a join table annotation? Generally i see people ending up with adding two columns as shown in the below example.

@JoinTable(name="mapping_table",
               joinColumns={@JoinColumn(name="request_id")},
               inverseJoinColumns={@JoinColumn(name="requester_id")})

Is there any way that we can add another column like having another '@JoinColumn'? I mean something like as shown below.

@JoinTable(name="mapping_table",
                   joinColumns={@JoinColumn(name="request_id")},
                   inverseJoinColumns={@JoinColumn(name="requester_id")
                                       @JoinColumn(name="requester_id")})

or else having another "inverseJoinColumn". I tried finding solution couldn't make it up. Thanks in advance.

Answer 1

Use the @javax.persistence.JoinColumns (note the 's' at the end) annotation to define mappings for composite foreign keys. You should have something like the following:

@JoinTable(name="mapping_table",
               joinColumns={@JoinColumn(name="request_id")},
               inverseJoinColumns={@JoinColumns({
                   @JoinColumn(name="requester_id"),
                   @JoinColumn(name="requester_id")})
               }
)

I think it should work.

Sorry for the first answer: I think that the @JoinColumns annotation can't be used inside a @JoinTable one. So I decided to investigate a little bit more.

I created 2 tables:

1. A Student entity class with an embedded id composed by first name and last name.
2. A Teacher entity class with an automatic long id and a Set<Student> as property.

I mapped the Set property with a @ManyToMany relationship, and I used an array of @JoinColumn annotations in the inverseJoinColumns property:

@ManyToMany(fetch=FetchType.EAGER)
@JoinTable(
        name="STUDENT_TEACHER",
        joinColumns=@JoinColumn(name="TEACHER_ID"),
        inverseJoinColumns={
            @JoinColumn(name="STUDENT_FIRSTNAME"),
            @JoinColumn(name="STUDENT_LASTNAME")
        }
)
private Set<Student> students = new HashSet<Student>();

I made some tests to check that all was working fine, and I had no problems (the fetch type is just to have easier test cases). I add here the code of the 2 entities I'm using:

Student.java

@Entity
public class Student {

    @EmbeddedId
    private Id id = new Id();

    public Id getId() {
        return id;
    }

    public void setId(Id id) {
        this.id = id;
    }

    @Embeddable
    public static class Id implements Serializable {
        private static final long serialVersionUID = 1L;

        @Column(name="FIRST_NAME")
        private String studentFirstname;

        @Column(name="LAST_NAME")
        private String studentLastname;

        public Id() { }

        public Id(String firstname, String lastname) {
            studentFirstname = firstname;
            studentLastname = lastname;
        }

        @Override
        public boolean equals(Object obj) {
            if (obj instanceof Id) {
                Id id = (Id)obj;
                return studentFirstname.equals(id.studentFirstname) &&
                        studentLastname.equals(id.studentLastname);
            }
            return false;
        }

        @Override
        public int hashCode() {
            return studentFirstname.hashCode() + studentLastname.hashCode();
        }
    }
}

Teacher.java

@Entity
public class Teacher {
    @Id @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;

    private String firstname;

    private String lastname;

    @ManyToMany(fetch=FetchType.EAGER)
    @JoinTable(
            name="STUDENT_TEACHER",
            joinColumns=@JoinColumn(name="TEACHER_ID"),
            inverseJoinColumns={
                @JoinColumn(name="STUDENT_FIRSTNAME"),
                @JoinColumn(name="STUDENT_LASTNAME")
            }
    )
    private Set<Student> students = new HashSet<Student>();

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getFirstname() {
        return firstname;
    }

    public void setFirstname(String firstname) {
        this.firstname = firstname;
    }

    public String getLastname() {
        return lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname;
    }

    public Set<Student> getStudents() {
        return students;
    }

    public void addStudents(Student student) {
        students.add(student);
    }
}

You just have to adapt the code to your particular situation. I hope you can solve your problem with this answer.

EDIT:

I let Hibernate create the tables with the hbm2ddl.auto option set to update. It created a STUDENT table, a TEACHER table and a STUDENT_TEACHER join table. I add here the CREATE TABLE statements generated (using MySQL):

STUDENT table:

CREATE TABLE `student` (
  `FIRST_NAME` varchar(255) NOT NULL,
  `LAST_NAME` varchar(255) NOT NULL,
  PRIMARY KEY (`FIRST_NAME`,`LAST_NAME`)
)

TEACHER table:

CREATE TABLE `teacher` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(255) DEFAULT NULL,
  `lastname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

STUDENT_TEACHER table:

CREATE TABLE `student_teacher` (
  `TEACHER_ID` bigint(20) NOT NULL,
  `STUDENT_FIRSTNAME` varchar(255) NOT NULL,
  `STUDENT_LASTNAME` varchar(255) NOT NULL,
  PRIMARY KEY (`TEACHER_ID`,`STUDENT_FIRSTNAME`,`STUDENT_LASTNAME`),
  KEY `FK13A0C19E102E6B8F` (`STUDENT_FIRSTNAME`,`STUDENT_LASTNAME`),
  KEY `FK13A0C19EC177BFF2` (`TEACHER_ID`),
  CONSTRAINT `FK13A0C19EC177BFF2` FOREIGN KEY (`TEACHER_ID`) REFERENCES `teacher` (`id`),
  CONSTRAINT `FK13A0C19E102E6B8F` FOREIGN KEY (`STUDENT_FIRSTNAME`, `STUDENT_LASTNAME`) REFERENCES `student` (`FIRST_NAME`, `LAST_NAME`)
)