[FIXED] One to Many Multi Column Join Spring Data JPA

Issue

I have an entities Event and Category. Each Event can have multiple Category and a category belongs to a single event. In Event entity I have ageLevelsAllowed(List of age levels allowed) & genders(List of genders) based on which categories are created and mapped to tournament.

For example, if there are age levels U10 and U20 in the list and genders M and F, I want to create 4 categories for an event E1,

E1 Category1 U10 M

E1 Category2 U10 F

E1 Category3 U20 M

E1 Category4 U20 F

Currently, my event entity looks like this -

import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.*;
import org.springframework.format.annotation.DateTimeFormat;

import javax.persistence.*;
import javax.validation.constraints.NotBlank;
import java.util.Date;
import java.util.List;
import java.util.Set;


@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name="TBL_EVENT", uniqueConstraints = {
        @UniqueConstraint(columnNames = "name")
})
public class Event {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private int id;

    @NotBlank
    @Column(name = "name",unique=true)
    private String name;

    @NotBlank
    @ElementCollection
    @CollectionTable(name = "event_ageLevels", joinColumns = @JoinColumn(name = "id"))
    @Column(name = "ageLevels")
    private Set<AgeLevel> ageLevels;

    @NotBlank
    @ElementCollection
    @CollectionTable(name = "event_genders", joinColumns = @JoinColumn(name = "id"))
    @Column(name = "genders")
    private Set<Gender> genders;

    @NotBlank
    @DateTimeFormat
    private String registrationEndDate;

    @OneToMany(mappedBy = "event")
    private Set<Category> categories;

}

Category entity is defined this way.

import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import javax.persistence.*;
import javax.validation.constraints.NotBlank;

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
@Entity
@Table(name="TBL_Categories")
public class Category {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private int category_id;

    @NotBlank
    private String categoryName;

    @ManyToOne
    @JsonIgnore
    @JoinColumns({
            @JoinColumn(name="id", nullable = false),
            @JoinColumn(name="ageLevels", nullable = false),
            @JoinColumn(name="genders", nullable = false)
    })
    private Event event;
}

When I try to execute this to create tables with three foreign key references, I'm getting an Error as below.

2021-11-24 00:58:58.050 ERROR 31404 --- [           main] j.LocalContainerEntityManagerFactoryBean : Failed to initialize JPA EntityManagerFactory: A Foreign key refering com.proj.entity.Event from com.proj.entity.Category has the wrong number of column. should be 1
2021-11-24 00:58:58.050 ERROR 31404 --- [           main] o.s.b.web.embedded.tomcat.TomcatStarter  : Error starting Tomcat context. Exception: org.springframework.beans.factory.UnsatisfiedDependencyException. Message: Error creating bean with name 'jwtFilter': Unsatisfied dependency expressed through field 'groupUserDetailsService'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'groupUserDetailsService': Unsatisfied dependency expressed through field 'repository'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'userRepository' defined in com.proj.repository.UserRepository defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Cannot create inner bean '(inner bean)#465b38e6' of type [org.springframework.orm.jpa.SharedEntityManagerCreator] while setting bean property 'entityManager'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name '(inner bean)#465b38e6': Cannot resolve reference to bean 'entityManagerFactory' while setting constructor argument; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is org.hibernate.AnnotationException: A Foreign key refering com.ser515.proj.entity.Event from com.proj.entity.Category has the wrong number of column. should be 1
2021-11-24 00:58:58.067  INFO 31404 --- [           main] o.apache.catalina.core.StandardService   : Stopping service [Tomcat]
2021-11-24 00:58:58.068  WARN 31404 --- [           main] o.a.c.loader.WebappClassLoaderBase       : The web application [ROOT] appears to have started a thread named [HikariPool-1 housekeeper] but has failed to stop it. This is very likely to create a memory leak. Stack trace of thread:
 [email protected]/jdk.internal.misc.Unsafe.park(Native Method)

Solution

You are having problem with this part:

@JoinColumns({
        @JoinColumn(name="id", nullable = false),
        @JoinColumn(name="ageLevels", nullable = false),
        @JoinColumn(name="genders", nullable = false)
})
private Event event;

The primary key of Event should be a composite primary key in your case so you need to use @EmbeddedId with id, ageLevels and genders as your primary key. This answer could help: https://stackoverflow.com/a/31389777/7214251

Answered By - Granit Krasniqi
Answer Checked By - Pedro (JavaFixing Volunteer)